Top 40 PostgreSQL Interview Questions & Answers 2025

PostgreSQL offers: ACID compliance, advanced data types (JSON, arrays, hstore), full-text search, extensibility (custom functions, operators), MVCC for concurrency, robust replication options, support for both SQL and NoSQL workloads, strong standards compliance, and active open-source community. It excels at complex queries and data integrity.

MVCC allows concurrent transactions without locking for reads. Each transaction sees a snapshot of data at its start time. Writers create new row versions; old versions remain for ongoing transactions. Vacuum process cleans dead tuples. Benefits: readers never block writers, writers never block readers. Trade-off: storage overhead from multiple versions.

B-tree (default): equality and range queries. Hash: equality only (rarely used). GiST: geometric data, full-text search. GIN: arrays, JSONB, full-text search (better for static data). BRIN: large sequential data (time-series). Partial indexes: index subset of rows. Expression indexes: index computed values. Choose based on query patterns and data characteristics.

Steps: (1) Use EXPLAIN ANALYZE to understand query plans, (2) Add appropriate indexes, (3) Update statistics with ANALYZE, (4) Tune postgresql.conf (shared_buffers, work_mem, effective_cache_size), (5) Use connection pooling (PgBouncer), (6) Partition large tables, (7) Optimize joins and subqueries, (8) Consider materialized views for complex aggregations.

Streaming replication: async or sync, entire database, low latency. Logical replication: table-level, allows different schemas/versions, selective replication. Physical vs logical: physical copies bytes (faster), logical copies changes (more flexible). Use cases: HA failover (streaming), data distribution (logical), version upgrades (logical).