top 10 interview questions and answers of postgress SQL

🔹 1. What are the key differences between PostgreSQL and other RDBMS like MySQL or Oracle?

Answer: PostgreSQL is an object-relational database (ORDBMS), while MySQL is a relational database. Key differences include:

• Extensibility → PostgreSQL supports custom data types, operators, and functions.
• ACID Compliance → PostgreSQL ensures strict ACID compliance in all configurations; MySQL requires specific storage engines like InnoDB.
• MVCC (Multi-Version Concurrency Control) → PostgreSQL handles concurrency without read locks; MySQL uses different isolation techniques.
• Advanced Features → PostgreSQL supports JSON/JSONB, window functions, CTEs, materialized views, GIS (PostGIS).
• Standards Compliance → PostgreSQL is highly compliant with ANSI SQL standards.
• Licensing → PostgreSQL uses a permissive open-source license (PostgreSQL License), Oracle is commercial.

---

🔹 2. How do you perform backup and recovery in PostgreSQL?

Answer: There are two main backup types:

1. Logical Backup:

   • pg_dump → exports single databases
   • pg_dumpall → exports all databases
   • Advantage: Portable, easy to restore on different versions.
   • Disadvantage: Slow for large datasets.

2. Physical Backup:

   • pg_basebackup → copies data directory and WAL files.
   • Can be used to set up streaming replication.
   • Useful for large databases.

3. Point-in-Time Recovery (PITR):

   • Use continuous WAL archiving.
   • Restore backup + replay WAL logs up to a specific time.

Best practice: Automate backups, test recovery regularly, and combine logical + physical strategies.

---

🔹 3. Explain MVCC (Multi-Version Concurrency Control) in PostgreSQL.

Answer: MVCC allows concurrent reads and writes without blocking.

• When a transaction updates a row, PostgreSQL creates a new version of the row while keeping the old version.
• Readers accessing the old version aren’t blocked, and writers can continue updating.
• The VACUUM process later removes dead tuples (old row versions).

This ensures:

• Readers don’t block writers
• Writers don’t block readers
• Consistent snapshots for transactions

---

🔹 4. What are VACUUM, ANALYZE, REINDEX, and CLUSTER used for?

Answer:

• VACUUM: Removes dead tuples left by updates/deletes, freeing space.
• VACUUM FULL: Rewrites the entire table to reclaim unused disk space.
• ANALYZE: Updates statistics used by the query planner for better execution plans.
• REINDEX: Rebuilds indexes to fix corruption or improve performance.
• CLUSTER: Physically orders table rows based on an index, improving sequential scan performance.

---

🔹 5. How do you set up replication in PostgreSQL?

Answer:

Replication types:

1. Streaming Replication (Physical):

   • Primary sends WAL changes to Standby.

   • Standby replays WALs to stay in sync.

   • Setup:

     • Enable wal_level = replica
     • Configure replication slots (pg_create_physical_replication_slot)
     • Use pg_basebackup to copy data directory
     • Configure primary_conninfo in standby’s recovery.conf

2. Logical Replication:

   • Replicates at table/data level (not entire cluster).
   • Useful for version upgrades and partial replication.
   • Setup: Create publication on primary → subscription on standby.

---

🔹 6. How do you optimize query performance in PostgreSQL?

Answer:

• Use EXPLAIN / EXPLAIN ANALYZE to inspect query execution.

• Add proper indexes:

  • B-tree for equality and range queries.
  • GIN/ GiST for full-text search, JSON.
  • BRIN for large sequential data.

• Partitioning: Divide large tables for efficiency.

• Tune PostgreSQL configs:

  • shared_buffers (~25% RAM)
  • work_mem (per query memory)
  • effective_cache_size (OS cache estimate)

• Optimize SQL queries: Avoid SELECT *, use joins wisely.

---

🔹 7. What is WAL (Write Ahead Logging), and why is it important?

Answer:

• WAL ensures durability: Changes are first written to a log file (WAL) before being applied to the database.

• If a crash occurs, WAL is replayed to recover committed transactions.

• WAL enables:

  • Crash recovery
  • Replication (streaming replication relies on WAL shipping)
  • Point-in-Time Recovery (PITR)

---

🔹 8. How do you secure a PostgreSQL database?

Answer:

• Authentication & Authorization: Use pg_hba.conf for client auth (MD5, SCRAM-SHA-256).
• Roles & Privileges: Implement least privilege principle using GRANT/REVOKE.
• SSL/TLS Encryption: Secure client-server connections.
• Auditing: Enable logging (log_statement, pg_audit).
• Updates & Patching: Keep PostgreSQL updated with security fixes.
• Network Security: Restrict DB access to trusted hosts only (firewall rules).

---

🔹 9. What’s the difference between a Tablespace, Schema, and Database in PostgreSQL?

Answer:

• Database: A collection of schemas, owned by a PostgreSQL instance.
• Schema: Logical grouping of tables, views, and functions inside a database.
• Tablespace: Physical storage location on disk where database objects are stored.

Example:

• One PostgreSQL cluster can have multiple databases.
• Each database can have multiple schemas.
• Tablespaces determine where data is physically stored.

---

🔹 10. How do you monitor a PostgreSQL database in production?

Answer:

1. Built-in Views:

   • pg_stat_activity → active queries
   • pg_stat_database → database-level stats
   • pg_stat_replication → replication lag
   • pg_locks → lock monitoring

2. Extensions:

   • pg_stat_statements → track query performance
   • auto_explain → log slow queries

3. External Tools:

   • pgAdmin for admin tasks
   • Prometheus + Grafana for metrics visualization
   • pganalyze / EDB tools for deep insights

4. Alerts:

   • Monitor replication lag, long-running queries, disk space, and connection count.