Top 50 PostgreSQL Interview Questions in 2026 (With Real Answers)

PostgreSQL won. By 2026 it's the default backend database for every new product at most companies, and "we use Postgres" has stopped being a decision worth defending. Which means interviews stopped going easy on it.

If you're interviewing for a backend, data, or platform role and the company runs Postgres in production, you should expect questions on internals - not just "what is a primary key?" trivia. These are the 50 that come up most.

Fundamentals (1-10)

1. What is MVCC and why does it matter?

Multi-Version Concurrency Control. Postgres keeps multiple row versions so readers don't block writers and writers don't block readers. Each transaction sees a consistent snapshot. The cost: dead row versions accumulate until vacuumed, and long-running transactions block cleanup.

2. Explain transaction isolation levels in Postgres.

• Read Uncommitted: behaves like Read Committed in Postgres.
• Read Committed (default): each statement sees a fresh snapshot.
• Repeatable Read: the whole transaction sees one snapshot. Prevents non-repeatable reads.
• Serializable: full serializable behavior via predicate locking. Slowest but safest.

In practice most apps use Read Committed and only escalate when they actually hit anomalies.

3. What's the difference between DELETE, TRUNCATE, and DROP?

• DELETE: row-by-row, MVCC-aware, fires triggers, can be rolled back, leaves dead tuples for vacuum.
• TRUNCATE: fast, table-level, takes ACCESS EXCLUSIVE lock, can be rolled back, reclaims space immediately.
• DROP: removes the table itself.

For "empty this big table" use TRUNCATE.

4. What does VACUUM actually do?

Reclaims space from dead tuples (rows deleted or updated under MVCC), updates statistics, and prevents transaction ID wraparound. Without vacuum, Postgres bloats and eventually halts to protect itself. Autovacuum handles this; you tune it, you don't disable it.

5. What is bloat and how do you fix it?

Bloat is space occupied by dead tuples or sparse pages. Causes: high update/delete churn, long transactions blocking vacuum, missing autovacuum tuning. Fixes: tune autovacuum (autovacuum_vacuum_scale_factor), use VACUUM FULL (rewrites table, takes ACCESS EXCLUSIVE), or pg_repack for online rebuild.

6. How does SERIAL differ from IDENTITY?

SERIAL creates an integer column with a sequence behind it. GENERATED BY DEFAULT AS IDENTITY (Postgres 10+) is the SQL-standard equivalent and is preferred. IDENTITY survives backups cleanly and doesn't have the sequence-ownership weirdness of SERIAL.

7. UUID v4 vs v7 as a primary key - what changed?

UUID v7 (time-ordered) became the default recommendation. v4 is random and creates index fragmentation when you have heavy inserts. v7 has a timestamp prefix, so inserts append rather than scatter. Postgres 17+ added uuidv7() natively (or use uuid_generate_v7() from extensions).

8. What does EXPLAIN ANALYZE show you?

The actual execution plan with real timings. EXPLAIN alone shows the planner's estimate. EXPLAIN (ANALYZE, BUFFERS) adds I/O info. Use it to compare planner estimates vs reality - large divergence means stats are stale.

9. What are the main scan types?

• Seq Scan: read every row.
• Index Scan: read matching index entries, then lookup the heap.
• Index Only Scan: index covers all needed columns, no heap lookup needed.
• Bitmap Heap Scan: build a bitmap of matching pages, then scan in physical order.

If you see Seq Scan on a giant table in a hot query, that's usually a missing index.

10. What is a query planner and why does it pick the plan it picks?

The planner uses table statistics and cost estimates to evaluate alternative plans and pick the cheapest. If statistics are wrong (stale stats, skewed data, no histogram for the column), the plan can be wildly off. ANALYZE updates stats; pg_stats lets you inspect them.

Indexes (11-20)

11. B-tree, Hash, GIN, GiST, BRIN - what are each for?

• B-tree: equality and range. The default and right answer 90% of the time.
• Hash: equality only, faster than B-tree for that case, supports WAL since 10.
• GIN: inverted index for compound values - JSONB, full text search, arrays.
• GiST: generalized search tree, geometric and full-text use.
• BRIN: block range indexes for naturally sorted huge tables (time-series).

12. What's a partial index?

An index with a WHERE clause that only indexes matching rows. Smaller, faster, cheaper to maintain. Example:

CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;

Now soft-deleted rows don't bloat the index.

13. What's an expression index?

An index on the result of an expression. Required if your queries use a function, otherwise the index won't be used:

CREATE INDEX idx_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'pat@example.com';

14. What's a covering index?

A B-tree index with INCLUDE columns that lets a query be answered from the index alone (Index Only Scan):

CREATE INDEX idx_orders_user_covering
  ON orders (user_id) INCLUDE (status, total);

Saves heap fetches on hot queries.

15. When does Postgres NOT use your index?

• Stats are stale.
• Selectivity is low (returning >5-10% of rows is often cheaper as Seq Scan).
• The query expression doesn't match the index expression.
• An implicit cast prevents index match.
• Function call without expression index.
• Optimizer thinks Seq Scan is cheaper (sometimes wrong).

16. How do you build an index without locking writes?

CREATE INDEX CONCURRENTLY. Slower (two table scans) but doesn't block writes. Required on production tables. If it fails, drop the resulting INVALID index before retrying.

17. Index types for JSONB?

GIN indexes. Two operator classes:

• jsonb_ops (default): supports all JSONB operators, larger index.
• jsonb_path_ops: smaller, only supports @>. Faster for that one operator.

If you're filtering with @>, use jsonb_path_ops for big tables.

18. How do you find unused indexes?

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Drop them. Each unused index slows writes and consumes disk and buffer cache.

19. What are HOT updates and why do they matter?

Heap-Only Tuple updates. When an UPDATE doesn't change any indexed columns and the new tuple fits on the same page, Postgres can skip updating indexes. Cheaper, less bloat. Maximize HOT by keeping high-churn columns out of indexes.

20. What is pg_stat_statements and why should it be enabled everywhere?

Extension that tracks normalized SQL statements with their execution counts, total time, mean time, rows, and I/O. The single most useful tool for finding slow queries in production. Enable it. Look at it weekly.

Schema and Data Modeling (21-30)

21. Should you use JSONB for everything?

No. Use it when the schema is genuinely dynamic (user-defined fields, third-party API blobs) or when you need to store related data atomically without joins. For known fields with known types, use real columns. JSONB indexes are slower than B-tree on typed columns.

22. Normalize or denormalize?

Default to normalized. Denormalize only when a profiled query is hot and the cost of joins is measurable. Use materialized views for read-heavy aggregations. The "we'll save lookups by duplicating" argument almost always loses to "now we have data drift."

23. How do you implement soft deletes?

A deleted_at TIMESTAMPTZ NULL column. Filter queries with WHERE deleted_at IS NULL. Use partial indexes to keep them small. Plan for purge - soft-deleted rows still cost storage and slow queries forever if you never clean them.

24. How would you store a hierarchical tree (categories, comments, org chart)?

Three options:

• Adjacency list (parent_id): simple, slow to walk deep trees. Use recursive CTE.
• Materialized path ('001.002.005'): fast subtree queries, painful inserts.
• ltree extension: Postgres-native hierarchical type with GiST indexes. Best of both worlds for most cases.

For comments and org charts, ltree is the answer in 2026.

25. ENUM vs CHECK constraint vs lookup table?

• ENUM: type-safe but adding values requires DDL (and old binary protocol awareness).
• CHECK constraint: simple, easy to extend, but no FK semantics.
• Lookup table: full referential integrity, easiest to extend, requires a join.

For values that change rarely and never get deleted, ENUM is fine. For anything users can edit, use a lookup table.

26. How do you implement multi-tenancy in Postgres?

• Schema per tenant: strong isolation, hard to query across tenants, painful at >1000 tenants.
• Row-level: shared tables with tenant_id, indexes start with tenant_id. Use Row Security (RLS) for defense-in-depth. Scales to millions of tenants.
• Database per tenant: maximum isolation, operational nightmare past a few dozen.

The 2026 default for new SaaS is row-level with RLS.

27. What is RLS (Row-Level Security)?

Policies attached to tables that restrict which rows a session can see or modify. Works great with SET app.tenant_id = ... from your connection pool. Catch: BYPASSRLS roles (like the owner) skip it. Enable FORCE ROW LEVEL SECURITY if you want even owners to be checked.

28. How do you handle big text columns?

text is fine. Postgres TOAST compresses and stores out-of-line automatically when rows exceed ~2KB. Don't use varchar(N) unless you have a real reason - the length check costs nothing meaningful, and a too-small N causes pain later.

29. How do you store money?

numeric(19, 4) (or whatever precision your domain needs). Never float. Some teams store as integer cents - simpler arithmetic, but you have to remember the unit everywhere. Pick one and document it.

30. Time zones - timestamp or timestamptz?

timestamptz always for application data. It stores UTC and converts on display. timestamp (without time zone) is legitimate only for things that have no time zone (a date that means the same thing everywhere, like a holiday).

Performance and Scaling (31-40)

31. How do you find slow queries?

pg_stat_statements for systematic. auto_explain (with sane thresholds) for ad hoc. APMs (Datadog, New Relic) for tracing across the app. Check pg_stat_activity for currently-running queries during an incident.

32. What is connection pooling and why is PgBouncer everywhere?

Each Postgres connection is a process with ~5-10MB of memory. App servers open thousands; Postgres melts. PgBouncer (or pgcat, the rewrite gaining ground in 2026) sits between the app and Postgres, multiplexing many app connections onto a pool of Postgres connections. Use transaction pooling mode unless you need session features.

33. What are the key parameters to tune for performance?

• shared_buffers: 25% of RAM, default is way too low.
• effective_cache_size: 50-75% of RAM (estimate, not allocation).
• work_mem: per-operation memory for sorts/hashes. Tune carefully - it's per query node.
• maintenance_work_mem: vacuum, index build memory.
• max_connections: keep low, use a pooler.
• random_page_cost: 1.1 on SSD (default 4.0 is for spinning rust).

34. What's table partitioning and when do you reach for it?

Splitting one logical table into smaller physical partitions (by range, list, or hash). Wins: prune partitions in queries, drop old data with DROP PARTITION instead of DELETE, parallel maintenance. Reach for it at hundreds of millions of rows or when you need to age out data fast.

35. Range vs list vs hash partitioning - which when?

• Range: time-series, anything with a natural ordering.
• List: discrete values like region or tenant tier.
• Hash: even distribution when you don't have a natural axis (e.g., user_id with no time aspect).

Time-series is almost always range partitioning by date.

36. How do you handle a table that grew to 500 GB and is slow?

1. Confirm slow queries with pg_stat_statements.
2. Audit indexes - missing or wrong.
3. Vacuum and analyze. Check for bloat with pg_stat_user_tables.
4. Consider partitioning if access patterns align.
5. Archive cold data to a separate table or to S3 via FDW.

Rebuilding the table (pg_repack or VACUUM FULL in maintenance) is sometimes the answer.

37. What is a write-heavy hot row and how do you fix it?

A counter row that every transaction updates (like a global click counter). Every UPDATE creates a new row version, all txns serialize on it. Fixes: shard the counter (N rows summed at read time), use a separate fast store (Redis), or batch updates.

38. Replication: streaming vs logical?

• Streaming: physical, byte-for-byte WAL replay. Replicas are read-only and identical to primary. Used for HA.
• Logical: replicates row-level changes via decoded WAL. Different schemas allowed, used for ETL, blue/green migrations, online major-version upgrades.

In 2026, logical replication is mature and the default for most ETL.

39. How do you do a zero-downtime Postgres major version upgrade?

pg_upgrade with hard links is fast but has downtime. The zero-downtime path: set up the new version as a logical replication subscriber, let it catch up, switch traffic, verify, decommission old. AWS RDS has Blue/Green Deployments that automate this for managed instances.

40. What is pgvector and why is it on every JD now?

A Postgres extension for vector similarity search. By 2026 it's the default vector store for any team that already runs Postgres. Indexes: HNSW (faster build, more memory) and IVFFlat (smaller). For most RAG and recommendation use cases, pgvector is enough. Standalone vector DBs only win at very large scale or specific feature needs.

Operations and Real Production (41-50)

41. What does pg_dump actually do?

Logical backup: writes SQL or a custom-format archive of schema and data. pg_dump runs in a transaction and gives you a consistent snapshot at the moment it started, but it's slow on huge databases and produces logical (replayable) output.

42. pg_dump vs physical backup?

• pg_dump: logical, portable across versions, slow on large data, restore is replay.
• Physical (pg_basebackup, snapshots, WAL archiving): block-level copy, fast restore, version-locked. Required for point-in-time recovery (PITR).

Production HA: physical with WAL archiving. Dev/test: pg_dump.

43. How do you do PITR?

Take a base backup. Continuously archive WAL. To restore: lay down the base backup, configure recovery_target_time, replay WAL up to the target. Tools like pgBackRest or WAL-G do this end-to-end. Test it. Untested PITR is a fairy tale.

44. What locks does ALTER TABLE take?

Most ALTERs take ACCESS EXCLUSIVE. This blocks everything, even reads. Postgres has slowly added "non-blocking" variants:

• ADD COLUMN with NULL default: fast, no rewrite (since 11).
• ADD COLUMN with non-NULL default: also fast (since 11).
• ADD CONSTRAINT NOT VALID then VALIDATE CONSTRAINT: doesn't block.

Read the docs for your version before any DDL on a hot table.

45. What's the right way to add a NOT NULL column to a 100M row table?

1. Add nullable column.
2. Backfill in batches, off-peak.
3. Add NOT NULL constraint with NOT VALID.
4. VALIDATE CONSTRAINT (acquires SHARE UPDATE EXCLUSIVE, doesn't block reads/writes).
5. Optionally drop nullable, add default.

Never ALTER TABLE ... SET NOT NULL directly on a hot, large table.

46. How do you debug a production deadlock?

log_lock_waits = on and review the logs. Postgres logs the queries holding locks. Look for inconsistent ordering of multi-row updates. Common fix: always lock rows in a consistent order (e.g., sorted by primary key) inside a transaction.

47. What's a connection storm and how do you handle one?

App layer suddenly opens hundreds/thousands of connections (often after a restart with no warm pool). Postgres process spawning thrashes the OS. Mitigations: PgBouncer with a sane max_client_conn, app-side connection limits, exponential backoff on reconnects.

48. How do you safely run a long migration on a production database?

• Run during low-traffic window.
• Set a lock_timeout and statement_timeout so a stuck migration doesn't hold a queue.
• Use CONCURRENTLY for index creation.
• Batch DML (DELETE/UPDATE) in chunks of a few thousand rows with checkpoints.
• Wrap in transactions only when you actually need atomicity - long open transactions block vacuum.

49. What metrics should be on your Postgres dashboard?

Connection count, transaction rate, replication lag, cache hit ratio, lock wait time, query latency p95, disk I/O, WAL generation rate, vacuum status (longest dead tuple age), bloat estimate. Alarms on replication lag and WAL backlog catch most outages early.

50. You're paged at 3am: "the database is slow." First five minutes?

1. pg_stat_activity - what's running, what's waiting on what.
2. Check replication lag and WAL queue.
3. Look for one query dominating - cancel it if obvious.
4. Check disk space and IOPS saturation.
5. Recent deploys, recent migrations, recent traffic shape change.

Most "the DB is slow" is one slow query, full disk, or a runaway transaction blocking vacuum.

Final Note

Postgres interviews in 2026 reward people who have actually run Postgres in production - bled on locks, cleaned up bloat, debugged a slow query at midnight. Read the docs before your interview. They're better than any third-party tutorial. And know pg_stat_statements cold - it's the answer to half the "how would you find this?" questions.

Get ready, then go ship.