PostgreSQL Interview Questions & Answers (2026)
These interviews test your grasp of PostgreSQL fundamentals, query optimization, concurrency control, and extensions. Demonstrate clear understanding of data modeling, indexing strategies, transaction isolation, and replication. Show practical experience with EXPLAIN, partitioning, and JSON handling. Explain trade‑offs, performance impacts, and best practices to convince interviewers you can design and maintain robust PostgreSQL solutions.
21 questions · updated Aug 29, 2026
Quick facts
| Typical rounds | Phone screen, technical coding, system design, and senior‑level deep dive |
| Core topics | SQL syntax, indexing, ACID, MVCC, replication, partitioning, JSONB |
| Preferred experience | 2‑5 years with production PostgreSQL, performance tuning, and backup/restore |
| Common tools | psql, pgAdmin, pg_dump, pgbench, EXPLAIN ANALYZE |
| Key metrics | query latency, index hit ratio, WAL throughput, replication lag |
Questions
Beginner
What is MVCC and how does PostgreSQL implement it?
MVCC (Multi‑Version Concurrency Control) lets readers see a consistent snapshot without blocking writers. PostgreSQL stores each row version with xmin and xmax transaction IDs. When a transaction starts, it records its snapshot; queries return rows whose xmin is older and xmax is either not set or newer than the snapshot. This design avoids read‑write locks, reduces contention, and enables repeatable reads. Interviewers expect you to mention transaction IDs, visibility rules, and the trade‑off of increased storage due to dead tuples that require vacuuming.
How does PostgreSQL’s VACUUM work and when should you run it?
VACUUM reclaims space occupied by dead tuples created by MVCC. It scans tables, marks obsolete rows for reuse, and updates visibility maps. Autovacuum runs in the background based on thresholds of dead tuples and table size, but manual VACUUM (or VACUUM FULL) is needed after massive deletes, bulk loads, or when table bloat hurts performance. Explain that VACUUM FULL rewrites the whole table, locking it, while regular VACUUM is non‑blocking. A strong candidate mentions tuning autovacuum parameters and monitoring pg_stat_user_tables.
When would you choose a B‑Tree index versus a GIN index?
B‑Tree indexes excel for equality and range queries on scalar columns, providing log‑N lookup. GIN (Generalized Inverted Index) is optimal for array, full‑text search, and JSONB containment operations because it indexes individual keys or tokens. Use B‑Tree for primary keys, timestamps, or numeric ranges; use GIN for @> on JSONB, @@ on tsvector, or ANY/ALL on arrays. Interviewers look for you to discuss index size, insert overhead, and query planner cost estimates.
Explain the difference between SERIAL and IDENTITY columns.
SERIAL is a pseudo‑type that creates a sequence and sets a default nextval() call; it’s legacy and ties the column to a sequence object. IDENTITY (introduced in PostgreSQL 10) is a true column attribute with GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY, offering better control, no separate sequence name, and support for ALTER TABLE to change generation. Candidates should note that IDENTITY integrates with the SQL standard, simplifies schema dumps, and avoids accidental sequence reuse.
What is a CTE and when is it materialized?
A CTE (Common Table Expression) defines a temporary result set for a single query, using WITH clause. By default, PostgreSQL materializes the CTE, storing its result in a temporary work table, which can be beneficial for repeated references but adds I/O. Since version 12, the planner can inline non‑recursive CTEs when it deems it cheaper, treating them like subqueries. Explain that materialization occurs when the CTE is recursive, contains side effects, or the planner cannot prove inlining is safe.
Intermediate
How does PostgreSQL handle deadlock detection?
PostgreSQL builds a wait‑for graph of lock requests. When a new lock request would create a cycle, the deadlock detector walks the graph, identifies the cycle, and aborts one transaction with an error. The chosen victim is usually the one that has waited the longest or holds the fewest locks. Interviewers expect you to discuss lock types (row‑level, table‑level), the role of the lock manager, and how to avoid deadlocks by ordering accesses and using SELECT … FOR UPDATE.
What are the trade‑offs between logical and physical replication?
Physical replication streams WAL files byte‑for‑byte, providing exact standby copies with low latency, but it replicates the whole cluster and cannot filter tables. Logical replication uses the logical decoding output plugin to stream DML changes, allowing selective table replication, cross‑version upgrades, and transformation via publication/subscription. However, it incurs higher CPU overhead, may lag more, and does not replicate DDL automatically. Candidates should mention use‑cases: hot standby vs. data warehousing or sharding.
How would you tune a slow query that scans a large table?
First run EXPLAIN ANALYZE to see the plan and identify sequential scans, missing indexes, or high cost nodes. Add appropriate indexes (B‑Tree for range, GIN for JSONB), consider partitioning the table by date or key, and ensure statistics are up‑to‑date with ANALYZE. Adjust work_mem for hash aggregates, enable parallel query, and verify that the query uses index-only scans when possible. Explain that you’d also check for function calls preventing index usage and consider rewriting the query.
Describe how partitioning works and when to use it.
Partitioning splits a large table into child tables based on a key (range, list, or hash). Queries targeting a specific partition prune irrelevant partitions, reducing I/O. It improves maintenance (vacuum, drop old partitions) and can enable parallelism. Use partitioning for time‑series data, large fact tables, or when you need to purge old data efficiently. Mention that constraints on partitions enforce data integrity, and that foreign keys to partitioned tables have restrictions in older versions.
What is the purpose of the pg_stat_activity view?
pg_stat_activity shows one row per backend process, exposing current query text, state (active, idle, waiting), start time, and client address. It helps diagnose long‑running queries, blocking sessions, and connection leaks. Interviewers expect you to discuss using it to identify bottlenecks, kill offending backends with pg_terminate_backend, and combine it with pg_locks to see lock wait chains.
How does PostgreSQL implement foreign keys and what are performance implications?
Foreign keys are enforced via triggers that check referenced rows on INSERT/UPDATE and delete dependent rows on DELETE/UPDATE (depending on ON DELETE/UPDATE actions). Each check requires a lookup on the parent table, typically using its primary key index. This adds overhead on write‑heavy workloads, especially if the parent key is not indexed. Explain that you can mitigate impact by ensuring the referenced column is indexed and by batching writes to reduce trigger firing frequency.
Advanced
Explain the difference between REPEATABLE READ and SERIALIZABLE isolation levels.
Both prevent dirty reads, but REPEATABLE READ guarantees that rows read once remain unchanged for the transaction, using snapshot isolation. SERIALIZABLE adds a safety net: the system detects dangerous read‑write patterns that could lead to anomalies and aborts one of the conflicting transactions, providing true serializability. In PostgreSQL, SERIALIZABLE uses predicate locking and may raise serialization failures that callers must retry. Candidates should discuss when to choose SERIALIZABLE for financial correctness despite higher abort rates.
What are advisory locks and when would you use them?
Advisory locks are application‑controlled locks stored in pg_advisory_lock tables, identified by a 64‑bit key. They are not tied to any table rows and do not block normal DML. Use them for coordinating jobs, ensuring single‑instance execution, or protecting critical sections across connections. Explain that they are session‑level (or transaction‑level with pg_advisory_xact_lock) and that they avoid deadlocks when used consistently, but they rely on disciplined application logic.
How does PostgreSQL’s JSONB indexing differ from plain JSON?
JSONB stores a binary representation that is decomposed into key/value pairs, enabling GIN or BTREE indexes on specific paths. Indexes can be created on expressions like (data->'field') or using the @> containment operator. Plain JSON stores text, requiring full scans and parsing at query time. Interviewers look for you to mention that JSONB supports indexing, faster containment checks, and that you can create partial indexes for frequent queries.
What is the purpose of the pg_hint_plan extension?
pg_hint_plan allows developers to embed optimizer hints directly in SQL comments, influencing join order, index usage, and parallelism without altering the query logic. It is useful when the planner consistently picks sub‑optimal plans due to outdated statistics or complex queries. Discuss that it should be used sparingly, as it bypasses the planner’s cost model, and that maintaining hints adds technical debt.
Explain how WAL works and its role in crash recovery.
Write‑Ahead Logging (WAL) records every change to data pages before the actual data files are modified. Each change is appended to WAL segment files, ensuring durability. On crash recovery, PostgreSQL replays WAL records from the last checkpoint to bring the database to a consistent state. This design enables point‑in‑time recovery and supports streaming replication. Interviewers expect you to discuss checkpoint intervals, WAL archiving, and the trade‑off between write latency and recovery speed.
When would you use a materialized view versus a regular view?
A materialized view stores the query result physically, offering fast reads at the cost of storage and refresh overhead. Use it for expensive aggregations, reporting dashboards, or when the underlying data changes infrequently. Regular views are virtual and always reflect current data, suitable for dynamic queries. Discuss REFRESH MATERIALIZED VIEW CONCURRENTLY to avoid locking and the need for indexes on the materialized view for performance.
How can you monitor replication lag in a streaming replica?
Query pg_stat_replication on the primary to see the write_lag, flush_lag, and replay_lag columns, which report time differences between primary and standby. On the replica, pg_last_xlog_replay_timestamp() (or pg_last_wal_replay_lsn()) can be compared to pg_current_wal_lsn() on the primary. Tools like pg_stat_replication and pg_stat_wal_receiver provide real‑time metrics. Explain that high lag indicates network bottlenecks or slow replay, and you may need to tune wal_receiver_status_interval or max_wal_senders.
What are the benefits and drawbacks of using table inheritance for partitioning?
Table inheritance allows child tables to automatically inherit columns from a parent, enabling partitioning before native declarative partitioning existed. Benefits include flexible partitioning schemes and easy addition of new partitions. Drawbacks are that constraints are not automatically enforced on children, queries may need UNION ALL for complete scans, and planner support is limited, leading to sub‑optimal plans. Modern PostgreSQL prefers declarative partitioning for better optimizer integration.
How does the planner decide between a hash join and a merge join?
The planner estimates cost based on input sizes, available memory (work_mem), and join conditions. Hash joins are chosen when equality predicates exist and the estimated hash table fits in memory, offering O(N) build time. Merge joins require sorted inputs; they are preferred when inputs are already ordered (e.g., via index scans) or when the cost of sorting exceeds hash building. Explain that the planner also considers parallelism and that you can influence the choice with enable_hashjoin/enable_mergejoin GUCs.
What is a TOAST table and when does PostgreSQL use it?
TOAST (The Oversized-Attribute Storage Technique) automatically moves large column values (by default >2KB) to a separate auxiliary table to keep the main table row size manageable. It compresses data and stores it in chunks, retrieving it on demand. Use cases include large text, bytea, or JSONB fields. Interviewers look for you to mention that TOAST is transparent, but can affect performance due to extra I/O, and that you can control it with storage parameters like toast_tuple_target.
Common mistakes
- Neglecting to run ANALYZE after bulk loads, causing stale statistics and poor plans
- Creating indexes without considering write overhead, leading to insert bottlenecks
- Relying on default autovacuum settings for high‑write tables, resulting in bloat
- Using SERIAL instead of IDENTITY, causing unnecessary sequence objects and dump issues
- Ignoring replication lag metrics, which can hide data consistency problems
Study plan
- Review core PostgreSQL concepts: MVCC, WAL, transaction isolation, and data types
- Practice query optimization with EXPLAIN ANALYZE on sample datasets; add/remove indexes
- Implement and test partitioning, replication, and JSONB queries in a local cluster
- Study advanced topics: logical replication, advisory locks, and planner join strategies
- Mock interview: answer 18+ questions aloud, focusing on reasoning and trade‑offs
FAQ
How many rows can a PostgreSQL table hold?
PostgreSQL can store billions of rows; the practical limit is disk space and maintenance overhead. Proper partitioning and indexing keep performance acceptable even at petabyte scale.
Is PostgreSQL ACID compliant?
Yes. PostgreSQL guarantees atomicity, consistency, isolation, and durability using MVCC, WAL, and strict transaction handling. Different isolation levels affect how strictly these guarantees are enforced.
Can I change a column type without downtime?
For many simple casts, ALTER TABLE … TYPE can be done online, especially with the USING clause. For large tables, use pg_repack or create a new column, backfill, and drop the old one to avoid long locks.
What is the best way to backup a large production database?
Use pg_basebackup for physical backups combined with WAL archiving for point‑in‑time recovery. For logical backups, pg_dump works but can be slower; parallel pg_dump can reduce time. Choose based on RPO/RTO requirements.
How do I secure connections to PostgreSQL?
Enable SSL in postgresql.conf, require client certificates, and use pg_hba.conf to restrict authentication methods. Rotate passwords regularly and apply role‑based privileges to limit exposure.
Related
Ready for your next interview?
Download MiPrep AI. Load your resume and the job description. Show up ready.
Free tier · No credit card · macOS 14+ · Windows 10+
Free tier · No credit card · Runs on your Mac or Windows machine