PostgreSQL Interview Questions

Summary

Every row version (tuple) carries xmin (the transaction that created it) and xmax (the transaction that deleted or updated it). An UPDATE writes a new tuple and marks the old one dead; each transaction's snapshot decides which versions it can see. Readers never block writers and writers never block readers.

Longer Answer

Unlike InnoDB, PostgreSQL keeps old versions in the table itself rather than in an undo log. That makes rollbacks instant, but dead tuples accumulate and must be cleaned up by VACUUM. Updates also write new index entries unless the update is HOT (heap-only tuple: no indexed column changed and there is room on the same page).

Visibility checks use the snapshot (which transactions were in progress) and commit status from the commit log; hint bits cache that status on tuples after the first check.

Key Points

• Tuples carry xmin/xmax.
• UPDATE = new tuple + dead old one.
• Old versions live in the table; VACUUM removes them.
• HOT updates avoid index writes.

Summary

VACUUM marks space from dead tuples as reusable, updates the visibility map (enabling index-only scans) and freezes old transaction IDs. Autovacuum runs it automatically when a table's dead tuples exceed a threshold, and also runs ANALYZE to refresh planner statistics.

Longer Answer

Plain VACUUM does not shrink the file; it makes space reusable inside it. VACUUM FULL rewrites the table compactly but takes an ACCESS EXCLUSIVE lock, blocking all access; pg_repack does the same online.

Autovacuum triggers when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor x rows (defaults 50 + 20%). On large, busy tables 20% is far too late, so lower the scale factor per table, and give autovacuum more throughput (autovacuum_vacuum_cost_limit, more workers). Autovacuum is blocked from cleaning tuples still visible to any open transaction, so long transactions and abandoned replication slots defeat it.

Code Example

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.02);

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

Key Points

• Reclaims dead tuples for reuse; freezes XIDs.
• VACUUM FULL locks; pg_repack is online.
• Tune per-table scale factors on big tables.
• Long transactions block cleanup.

Summary

B-tree (default: equality, ranges, sorting), Hash (equality only), GIN (inverted index for JSONB, arrays, full-text, trigrams), GiST (geometric data, ranges, nearest-neighbour, exclusion constraints), SP-GiST (partitioned search trees such as quadtrees), and BRIN (tiny block-range summaries for huge, naturally ordered tables such as time-series).

Longer Answer

• B-tree: almost everything; supports multicolumn, unique, INCLUDE columns and index-only scans.
• GIN: "does this document contain key X / this array contain element Y / this text match these words"; slower to update (uses a pending list), fast to search.
• GiST: PostGIS geometries, tsrange overlaps, <-> distance ordering (k-nearest neighbour).
• BRIN: stores min/max per range of pages; a few kilobytes for billions of rows, effective only when values correlate with physical order (append-only created_at).
• Extensions add more, for example HNSW and IVFFlat indexes in pgvector for similarity search.

Key Points

• B-tree default.
• GIN for JSONB/arrays/full-text.
• GiST for geometry, ranges, KNN.
• BRIN for huge ordered tables.

Summary

Read the plan tree from the innermost nodes outward. For each node compare estimated rows with actual rows, look at actual time (per loop, multiplied by loops), and add BUFFERS to see how many pages came from cache versus disk. Big estimate errors and nodes with most of the time are where to look.

Longer Answer

• Node types: Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan; joins Nested Loop, Hash Join, Merge Join; Sort (watch for external merge Disk), HashAggregate.
• rows=1000 estimated versus actual rows=950000 usually means bad statistics or correlated columns, leading to a wrong join strategy.
• Rows Removed by Filter shows wasted work an index could avoid.
• EXPLAIN (ANALYZE, BUFFERS) shows shared hit (cache) versus read (disk).

EXPLAIN ANALYZE executes the statement; wrap writes in BEGIN ... ROLLBACK. Tools such as explain.depesz.com or explain.dalibo.com visualise plans.

Key Points

• Inner nodes first; time x loops.
• Estimated vs actual rows.
• BUFFERS: cache vs disk.
• Wrap DML in a rolled-back transaction.

Summary

READ COMMITTED (the default: each statement sees data committed before it started), REPEATABLE READ (snapshot isolation: the whole transaction sees one snapshot and concurrent updates of the same row cause a serialization error), and SERIALIZABLE (Serializable Snapshot Isolation: detects dangerous read/write dependency patterns and aborts one transaction so the result equals some serial order). READ UNCOMMITTED behaves like READ COMMITTED.

Longer Answer

In READ COMMITTED, an UPDATE that finds a row changed by a concurrent committed transaction re-evaluates its WHERE clause on the new version and continues, which is convenient but allows anomalies such as lost updates in read-modify-write code.

REPEATABLE READ prevents those by raising could not serialize access due to concurrent update. SERIALIZABLE also prevents write skew (two doctors both going off call because each saw the other on call). Both require the application to retry failed transactions; SERIALIZABLE is a valid, practical choice in PostgreSQL if you build the retry loop.

Key Points

• Default READ COMMITTED.
• REPEATABLE READ = snapshot isolation.
• SERIALIZABLE = SSI, prevents write skew.
• Higher levels need retry loops.

Deep dive: Transactions, Locking & Concurrency in Code

Summary

Many ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which blocks reads and writes. Even a fast DDL statement can cause an outage if it waits behind a long query, because every new query then queues behind the waiting DDL. Set lock_timeout, retry, and use lock-friendly variants.

Longer Answer

Safe patterns:

• SET lock_timeout = '2s'; before DDL, and retry on timeout.
• CREATE INDEX CONCURRENTLY instead of CREATE INDEX.
• Adding a column with a constant default is instant since PostgreSQL 11 (no table rewrite).
• Adding NOT NULL or a foreign key: first add a CHECK (col IS NOT NULL) NOT VALID or the FK NOT VALID (fast), then VALIDATE CONSTRAINT (takes a weaker lock while scanning).
• Changing a column type usually rewrites the table; use expand-and-contract (new column, backfill, switch).

Tools such as squawk lint migrations for these problems.

Key Points

• ACCESS EXCLUSIVE blocks everything.
• Lock queueing turns a waiting DDL into an outage.
• lock_timeout + retry.
• CONCURRENTLY, NOT VALID + VALIDATE, instant defaults.

Summary

JSONB stores a parsed binary form: it is slightly slower to write but much faster to query, supports containment and existence operators and can be indexed with GIN. JSON keeps the exact text (whitespace, key order, duplicate keys) and is rarely what you want.

Longer Answer

• CREATE INDEX ON events USING gin (payload); supports @>, ?, ?|, ?&.
• jsonb_path_ops makes a smaller, faster GIN index that supports only @> (containment).
• For a single field used in equality or ranges, an expression B-tree index is better: CREATE INDEX ON events ((payload->>'user_id'));.
• SQL/JSON path queries (jsonb_path_query, @?, @@) and, in PostgreSQL 16+/17, SQL-standard JSON_TABLE and constructors add expressiveness.

Keep frequently filtered, joined or constrained fields as real columns; JSONB statistics are coarse, so the planner often misestimates JSONB predicates.

Code Example

CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);
SELECT id FROM events WHERE payload @> '{"type": "checkout", "country": "DE"}';

Key Points

• Use JSONB.
• GIN (default or jsonb_path_ops) for containment.
• B-tree expression indexes for single keys.
• Hot fields belong in columns.

Summary

PostgreSQL forks a backend process per connection, which costs memory and makes thousands of connections expensive (contention, context switching). PgBouncer lets many clients share a small number of server connections. Session mode assigns a server connection for the whole client session; transaction mode only for each transaction (the common choice); statement mode for each statement.

Longer Answer

Transaction mode gives the best multiplexing but breaks features that rely on session state: session-level advisory locks, SET without LOCAL, temporary tables across transactions, LISTEN, and (historically) named prepared statements, which PgBouncer 1.21+ can now track. A good server-side pool size is small, roughly a few times the number of CPU cores, not hundreds.

Serverless and autoscaled applications are the classic case: every function instance opens connections and exhausts max_connections without a pooler (or a managed proxy such as RDS Proxy).

Key Points

• Process per connection is expensive.
• PgBouncer session/transaction/statement modes.
• Transaction mode breaks session state.
• Small server pools.

Summary

A partial index covers only rows matching a WHERE condition, for example CREATE INDEX ON jobs (created_at) WHERE status = 'queued'. It is small, cheap to maintain, and fast for queries that use the same condition.

Longer Answer

Typical uses: the few 'active' or 'pending' rows in a large table, soft-deleted tables (WHERE deleted_at IS NULL), and partial unique indexes (CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL), which allow re-registering an email after a soft delete. The query's WHERE clause must imply the index predicate for the planner to use it.

Code Example

CREATE UNIQUE INDEX users_email_active ON users (lower(email)) WHERE deleted_at IS NULL;

Key Points

• Indexes only matching rows.
• Great for small 'hot' subsets.
• Partial unique indexes for soft deletes.
• Query must imply the predicate.

Summary

Workers select queued rows with FOR UPDATE SKIP LOCKED, which locks the rows they take and skips rows other workers have locked, so many workers can claim jobs concurrently without blocking or double-processing.

Longer Answer

WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'queued' AND run_at <= now()
  ORDER BY run_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running', started_at = now()
FROM next WHERE jobs.id = next.id
RETURNING jobs.*;

Add a partial index on (run_at) WHERE status = 'queued', handle crashed workers by timing out stale running jobs, and keep the table small (archive finished jobs) because high churn means much vacuuming. Libraries such as Oban, river, graphile-worker and pg-boss implement this pattern. Other lock strengths: FOR NO KEY UPDATE (does not block foreign-key checks), FOR SHARE, FOR KEY SHARE, and NOWAIT.

Key Points

• SKIP LOCKED lets workers claim different rows.
• Claim and mark in one statement.
• Partial index on queued jobs.
• Recover stale running jobs; archive done ones.

Summary

It builds the index without blocking writes, using multiple passes over the table and waiting for existing transactions to finish. It takes longer, cannot run inside a transaction block, and if it fails it leaves an INVALID index that you must drop and rebuild.

Longer Answer

Check for leftovers with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;. Many migration frameworks wrap migrations in a transaction, so concurrent index creation needs a non-transactional migration. REINDEX CONCURRENTLY (PostgreSQL 12+) rebuilds bloated or corrupted indexes online. DROP INDEX CONCURRENTLY avoids blocking on removal.

Key Points

• No write blocking; slower.
• Not inside a transaction block.
• Failure leaves an INVALID index.
• REINDEX CONCURRENTLY for rebuilds.

Summary

It is PostgreSQL's atomic upsert: insert a row, and if it violates a specified unique constraint or index, either do nothing (DO NOTHING) or update the existing row (DO UPDATE SET ..., with EXCLUDED referring to the proposed row).

Longer Answer

INSERT INTO page_views (page_id, day, views)
VALUES (42, current_date, 1)
ON CONFLICT (page_id, day) DO UPDATE SET views = page_views.views + EXCLUDED.views
RETURNING views;

The conflict target must match a unique index (or constraint). It is safe under concurrency, unlike "select, then insert or update". DO UPDATE can affect each row only once per statement, so deduplicate input batches. MERGE (PostgreSQL 15+) handles more complex insert/update/delete synchronisation; it is not a drop-in concurrency-safe replacement for ON CONFLICT.

Key Points

• Atomic upsert on a unique index.
• EXCLUDED = proposed row.
• Concurrency-safe.
• MERGE for complex sync (PG 15+).

Summary

A partitioned table is split into child tables by RANGE, LIST or HASH on a key. Queries filtering on the key touch only relevant partitions (pruning), and old data can be removed by detaching or dropping a partition instantly. It suits very large, time-based or tenant-based tables.

Longer Answer

Benefits: fast retention (DROP TABLE events_2024_01), smaller per-partition indexes, vacuuming per partition, and pruning. Costs: primary keys and unique constraints must include the partition key; queries that do not filter on the key scan every partition; thousands of partitions increase planning time. Create future partitions ahead of time (pg_partman automates this), and use DETACH PARTITION CONCURRENTLY (PostgreSQL 14+) to detach without blocking.

Key Points

• RANGE / LIST / HASH.
• Pruning on the partition key.
• Instant retention by dropping partitions.
• Unique keys must include the partition key.

Summary

Streaming replication ships WAL to standbys, which are byte-for-byte copies of the whole cluster (read-only hot standbys, used for HA and read scaling). Logical replication publishes row changes for chosen tables to subscribers, which can be different major versions, have different indexes, or receive only some tables.

Longer Answer

Physical: simple, complete, same major version and architecture; the standby cannot have its own writable tables.

Logical: publications and subscriptions; used for zero-downtime major-version upgrades, consolidating or splitting databases, and feeding other systems. It does not replicate DDL, sequences (until handled manually) or large objects, and needs primary keys or replica identity on tables.

Both use replication slots to retain WAL until consumers have it; an abandoned slot retains WAL forever and can fill the disk. Monitor pg_replication_slots.

Key Points

• Physical = whole cluster via WAL.
• Logical = selected tables' row changes.
• Logical for upgrades and integration.
• Monitor replication slots.

Deep dive: Scaling Databases & Data Consistency

Summary

Take physical base backups (pg_basebackup, or tools such as pgBackRest, Barman, WAL-G) and archive WAL continuously. To recover to a point in time, restore the base backup and replay archived WAL up to a target time, transaction or named restore point. pg_dump makes logical backups of single databases, useful for migrations and smaller data sets.

Longer Answer

• pg_dump -Fc is consistent (it uses a snapshot) and portable across versions, but restores of large databases are slow and it does not capture everything cluster-wide (roles: use pg_dumpall --globals-only).
• Physical backups + WAL archiving give PITR with RPO measured in seconds, and fast restores.
• pgBackRest adds parallelism, compression, encryption, incremental backups and retention policies. PostgreSQL 17 added built-in incremental base backups.

Test restores regularly and measure the time; monitor that WAL archiving keeps succeeding (pg_stat_archiver).

Key Points

• Base backups + WAL archive = PITR.
• pg_dump for logical, portable backups.
• pgBackRest/Barman/WAL-G.
• Test restores; monitor archiving.

Summary

shared_buffers is PostgreSQL's own page cache (commonly about 25% of RAM); effective_cache_size is only a planner hint of how much data the OS cache plus shared buffers can hold (often 50-75% of RAM); work_mem is the memory each sort or hash operation may use before spilling to disk.

Longer Answer

work_mem is per operation, per query, per connection: a complex query with five sorts across 100 connections could use 500 x work_mem. Keep the global value modest and raise it per session or role for reporting queries (SET work_mem = '256MB'). Spills show as external merge Disk in EXPLAIN ANALYZE and as temp files in pg_stat_database. maintenance_work_mem speeds up VACUUM and index builds.

Key Points

• shared_buffers ~25% RAM.
• effective_cache_size is a planner hint.
• work_mem is per operation; raise per session.
• Watch temp-file spills.

Summary

In PostgreSQL text and varchar perform the same; use text (with a CHECK constraint if you need a limit). For moments in time use timestamptz, which stores an absolute instant (UTC internally) and converts to the session time zone on output; timestamp without time zone is just a wall-clock value with no zone.

Longer Answer

varchar(n) limits are mostly a convenience and changing them used to require a rewrite. char(n) pads with spaces and is rarely useful.

timestamptz does not store the time zone name, only the instant; keep the user's zone separately if you need to display it. Use timestamp only for truly zone-less values (a store's opening time "09:00 local"). Mixing the two causes off-by-hours bugs at DST changes.

Key Points

• text = varchar in performance.
• timestamptz for instants.
• timestamptz stores no zone name.
• timestamp only for zone-less wall times.

Summary

An open transaction holds back the oldest snapshot horizon, so VACUUM cannot remove dead tuples created after it started anywhere in the database: tables and indexes bloat, and queries slow down. It may also hold locks that block DDL and other writers.

Longer Answer

Common causes: an application that opens a transaction and then waits on a slow HTTP call or user input, a crashed client leaving a session idle in transaction, or analysts running hours-long queries on the primary.

Defences: idle_in_transaction_session_timeout, statement_timeout for application roles, transaction_timeout (PostgreSQL 17+), keeping external calls outside transactions, running reports on replicas (with hot_standby_feedback trade-offs), and monitoring pg_stat_activity for xact_start age.

Key Points

• Old snapshots block VACUUM cleanup.
• Bloat and lock contention.
• Timeouts for idle transactions and statements.
• No external calls inside transactions.

Summary

Do not read a value, compute in the application and write it back. Use an atomic statement (UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1), lock the row first with SELECT ... FOR UPDATE, use optimistic concurrency with a version column, or run the transaction at REPEATABLE READ or SERIALIZABLE and retry on serialization failures.

Longer Answer

Under READ COMMITTED two transactions can both read balance 100, both subtract 30 in the application, and both write 70: one update is lost. The atomic UPDATE is simplest and fastest; row locks suit multi-step logic; higher isolation levels detect the conflict but require retries.

Key Points

• Read-modify-write loses updates.
• Atomic UPDATE with a guard.
• FOR UPDATE, version columns, or higher isolation + retry.

Deep dive: Transactions, Locking & Concurrency in Code

Summary

pg_stat_statements (query performance statistics), pg_trgm (fuzzy text search), PostGIS (geospatial), pgvector (vector similarity search for embeddings), citext (case-insensitive text), pgcrypto, pg_partman (partition management) and auto_explain (log plans of slow queries).

Longer Answer

Extensions are a major reason teams choose PostgreSQL: they add types, index methods and functions without leaving the database. On managed services you can only use the extensions the provider supports, so check before designing around one. Extensions also need upgrading alongside major versions (ALTER EXTENSION ... UPDATE).

Key Points

• pg_stat_statements is essential.
• PostGIS, pgvector, pg_trgm, citext.
• Managed services limit available extensions.