PostgreSQL Interview Questions for Data Engineers and Analysts

Postgres is a standards-strict ORDBMS with transactional DDL, richer types (arrays, ranges, JSONB, native UUID), six index access methods, and a real extension ecosystem. Architecturally, its MVCC keeps old row versions in the table heap and cleans them with VACUUM, where InnoDB uses undo logs; and Postgres forks a process per connection versus MySQL's thread per connection. For analytics-leaning work, Postgres's planner and CTE/window maturity led for years, though MySQL 8 closed much of the gap.

A CTE names a subquery with WITH so complex logic reads top-down, and WITH RECURSIVE handles hierarchies like org charts or category trees. Since Postgres 12, CTEs are inlined into the main query when safe (referenced once, no side effects), so they are no longer an automatic optimization fence; you can force the old behaviour with AS MATERIALIZED. Mentioning the version-12 change is an easy way to show you are current.

There is no performance difference — both use the same underlying varlena storage, and varchar(n) just adds a length check. Idiomatic Postgres uses TEXT, with a CHECK constraint if you genuinely need a limit. This surprises people coming from MySQL, where declared VARCHAR length affects memory use in sorts and temporary tables.

Six access methods: B-tree (the default — equality, ranges, ORDER BY), Hash (equality only, WAL-logged since Postgres 10), GIN (inverted index for JSONB, arrays, full-text), GiST (geometric, range, and nearest-neighbour searches), SP-GiST (space-partitioned structures like tries), and BRIN (block-range summaries for huge naturally ordered tables). On top of any of these you can create partial, expression, and covering (INCLUDE) indexes. Interviewers rarely want all six recited — they want GIN, BRIN, and partial matched to use cases.

JSON stores the raw text — preserving whitespace, key order, and duplicate keys — and reparses it on every access. JSONB stores a decomposed binary format: slightly slower to write but much faster to query, it deduplicates keys and supports GIN indexing and operators like @> and ?. Use JSONB in practically every case; keep JSON only when you must preserve the exact original document.

Every row version carries xmin (the creating transaction) and xmax (the deleting or updating transaction); each query runs against a snapshot and sees only versions committed before it, so readers never block writers and writers never block readers. An UPDATE is really an insert of a new row version plus expiring the old one, which is why heavily updated tables accumulate dead tuples. The causal chain interviewers want stated explicitly: MVCC creates garbage, VACUUM collects it.

VACUUM removes dead tuples so their space is reusable (it rarely shrinks the file — that is VACUUM FULL, which rewrites the table under an exclusive lock), updates the free space and visibility maps, and freezes old transaction IDs to prevent XID wraparound, the failure mode where writes eventually stop. Autovacuum triggers per table when dead tuples exceed autovacuum_vacuum_scale_factor (default 20% of the table) — too lazy for very large tables, so real deployments lower it per table. Autovacuum also runs ANALYZE to refresh planner statistics.

GIN for containment queries on multi-value columns — JSONB @>, array &&, full-text @@ — at the cost of larger size and slower writes. BRIN stores min/max per block range, so a 500 GB append-only events table gets an index of a few megabytes — but only when physical row order correlates with the column, as with timestamps. A partial index (CREATE INDEX ... WHERE status = 'pending') covers just the hot subset, and paired with an expression index like lower(email), these are the cheap wins interviewers fish for.

EXPLAIN shows the estimated plan; EXPLAIN ANALYZE actually executes the query and reports real rows and timings per node — so wrap writes in BEGIN/ROLLBACK. The first thing to check is estimated vs actual rows: a large mismatch means stale or insufficient statistics and often a wrong join strategy, like a nested loop that should have been a hash join. Add BUFFERS to see shared-buffer hits versus disk reads, and treat "Sort Method: external merge" as a cue to raise work_mem.

The ANSI core: ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD, and running aggregates with frame clauses (ROWS BETWEEN ...), with top-N per group via ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...). Postgres extras worth naming: FILTER (WHERE ...) on aggregates, and DISTINCT ON as a Postgres-specific shortcut for "latest row per key" — DISTINCT ON (user_id) ... ORDER BY user_id, created_at DESC. Knowing DISTINCT ON usually marks a real Postgres user.

Postgres 10+ supports RANGE, LIST, and HASH partitioning; the planner prunes partitions at both plan and execution time, and retention becomes DETACH or DROP of a partition instead of a massive DELETE that VACUUM must then clean up. Gotchas: primary keys and unique constraints must include the partition key, each partition carries its own indexes, and thousands of partitions inflate planning time. Partition when a table reaches hundreds of gigabytes with a natural time key — not as a reflex.

Postgres forks an OS process per connection, each with non-trivial memory, so thousands of application connections hurt even when idle. PgBouncer multiplexes them: session pooling is transparent, while transaction pooling gives the biggest win but breaks session state — SET commands, advisory locks, and historically prepared statements, which PgBouncer only began supporting at protocol level in 1.21. The expected answer to "how do 2,000 pods talk to one Postgres": transaction-mode PgBouncer in front of a deliberately low max_connections.

When an UPDATE changes no indexed column and the new version fits on the same page, Postgres performs a heap-only tuple (HOT) update and writes no new index entries. Otherwise every index on the table gets a new entry, so an update-heavy table with many indexes bloats both heap and indexes. Mitigations: drop unused indexes, lower fillfactor (e.g. to 90) to leave page room for HOT chains, and tighten per-table autovacuum; monitor with pg_stat_user_tables.n_dead_tup and pgstattuple.

Compare estimated vs actual rows in EXPLAIN ANALYZE; if they diverge, run ANALYZE, raise that column's statistics target (ALTER TABLE ... ALTER COLUMN ... SET STATISTICS), and for correlated predicates like city plus pincode create extended statistics with CREATE STATISTICS (dependencies, ndistinct). If estimates are fine but the plan is still slow, check work_mem for spilled sorts and hashes, non-sargable expressions blocking index use, and missing partial or expression indexes. Core Postgres has no hints — pg_hint_plan exists — but the interview answer is "fix the statistics, not the plan."

The default GIN opclass jsonb_ops indexes every key and value and supports key-existence operators (?, ?|, ?&) plus containment @>; jsonb_path_ops indexes only value paths, making it considerably smaller and faster, but it supports only @> and the JSONPath operators @? and @@. For one frequently filtered scalar field, a B-tree expression index on (data->>'customer_id') beats GIN for equality and ranges and supports ORDER BY. The senior-signal detail: ->> returns text, so cast identically in the index and the query — ((data->>'amount')::numeric) — or the index will not be used.

Start with pg_stat_statements ordered by total_exec_time and mean_exec_time to find aggregate offenders, not just the single slowest run, and capture real plans with auto_explain for statements over a threshold. Then EXPLAIN (ANALYZE, BUFFERS) the candidates and fix statistics, indexes, or work_mem; confirm with pg_stat_user_tables (seq_scan counts) and check live blocking via pg_stat_activity's wait_event and pg_blocking_pids(). Naming pg_stat_statements unprompted is one of the strongest signals you can send in a Postgres interview.