PostgreSQL Interview Questions and Answers
Last updated 2026-11-19
PostgreSQL interviews go beyond standard SQL into how the database works: MVCC and VACUUM, the variety of index types, JSONB, upsert with ON CONFLICT, transactional DDL and reading EXPLAIN ANALYZE. Product companies and data-heavy teams often choose PostgreSQL, and interviewers use these topics to separate people who have operated it from those who have only written queries.
The questions below give the mechanism, a short example and the production consequence. Version notes assume PostgreSQL 13 and later. For the standard-SQL core see SQL interview questions, and for the closest comparison see MySQL interview questions. Strategy for the round is in how to crack a SQL interview.
| Version | PostgreSQL 13 and later |
|---|---|
| Strengths | MVCC, rich types, extensions, standards compliance |
| Compared with | MySQL where it helps |
Concepts behind most PostgreSQL answers
- MVCC: readers do not block writers because each row has versions; old versions remain until VACUUM.
- Heap tables: the table is unordered, and every index points to a row location (ctid).
- Write-ahead log (WAL): changes are logged before data files, giving durability and replication.
- Planner: cost-based, depends on statistics refreshed by ANALYZE.
- Extensions: PostGIS, pg_trgm, pg_stat_statements and others add features without forking.
What to be able to demonstrate
Be ready to read a plan from EXPLAIN (ANALYZE, BUFFERS), to explain why a table is bloated, and to choose between B-tree, GIN and BRIN for a stated workload. Concrete answers about dead tuples, autovacuum and index choice are what distinguish experience from reading.
PostgreSQL Interview Questions and Answers: questions with worked answers
1 What is PostgreSQL, and how does it differ from MySQL?
easyPostgreSQL is an open-source object-relational database known for standards compliance, rich data types (arrays, JSONB, ranges, geometric, custom types), extensions and strong concurrency. Differences interviewers raise: PostgreSQL has transactional DDL, full-featured window functions and CTEs for longer, FULL OUTER JOIN, partial and expression indexes and INSERT ... ON CONFLICT; MySQL InnoDB clusters rows by primary key and defaults to REPEATABLE READ, while PostgreSQL stores heap tables and defaults to READ COMMITTED. Comparisons in PostgreSQL strings are case-sensitive by default.
2 Explain MVCC in PostgreSQL.
mediumMulti-version concurrency control keeps multiple versions of a row so that readers see a consistent snapshot without blocking writers. Every row version has creation and deletion transaction ids (xmin and xmax). An UPDATE does not change a row in place: it marks the old version as deleted and inserts a new version. A transaction sees only versions that were committed before its snapshot. Old versions become dead tuples once no transaction can see them, and VACUUM reclaims their space. This is why long-running transactions are harmful: they hold back cleanup and cause bloat.
3 What does VACUUM do, and what is the difference from VACUUM FULL and autovacuum?
mediumVACUUM removes dead tuples and marks their space as reusable within the table, updates the visibility map (enabling index-only scans) and freezes old transaction ids to avoid wraparound. It does not usually shrink the file or block normal reads and writes. VACUUM FULL rewrites the whole table into a new file, returning space to the operating system, but takes an exclusive lock for the duration. Autovacuum runs these automatically based on thresholds, and tuning it per table is a common fix for bloat. ANALYZE updates planner statistics and is also run by autovacuum.
sqlVACUUM (VERBOSE, ANALYZE) orders; SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;4 What index types does PostgreSQL offer, and when do you use each?
mediumB-tree (default): equality, range, sorting. Hash: equality only, rarely better. GIN: many keys per row, such as full-text search, arrays and JSONB containment. GiST: geometric data, ranges and nearest-neighbour search. SP-GiST: partitioned structures such as tries and quadtrees. BRIN: very large tables whose values correlate with physical order, such as append-only timestamps, with a tiny index. Beyond types, partial indexes (WHERE clause), expression indexes (on lower(email)) and covering indexes (INCLUDE) are heavily used.
sqlCREATE INDEX idx_email_lower ON users (lower(email)); CREATE INDEX idx_docs_gin ON docs USING GIN (data jsonb_path_ops); CREATE INDEX idx_log_brin ON logs USING BRIN (created_at);5 What is the difference between JSON and JSONB?
mediumjson stores the exact input text, preserving whitespace, key order and duplicate keys, and reparses it on every operation. jsonb stores a decomposed binary format: slightly slower to insert, but faster to query, it removes duplicate keys (last wins), does not keep key order, and supports GIN indexes and containment operators (@>). Use jsonb nearly always. Operators: -> returns json(b), ->> returns text, #>> follows a path as text.
sqlSELECT data->>'city' AS city FROM customers WHERE data @> '{"vip": true}'; CREATE INDEX idx_customers_data ON customers USING GIN (data);6 How do you do an upsert in PostgreSQL?
mediumINSERT ... ON CONFLICT specifies what to do on a violation of a unique index or constraint: DO NOTHING, or DO UPDATE SET ... using the special EXCLUDED row that holds the proposed values. It is atomic and safe under concurrency, unlike a SELECT-then-INSERT sequence. PostgreSQL 15 also added the SQL-standard MERGE statement. MySQL spells it ON DUPLICATE KEY UPDATE.
sqlINSERT INTO page_views (page_id, views) VALUES (7, 1) ON CONFLICT (page_id) DO UPDATE SET views = page_views.views + EXCLUDED.views;7 How do you read EXPLAIN ANALYZE output?
hardEXPLAIN shows the plan and estimates, EXPLAIN ANALYZE also executes the query and reports actual time and rows for each node. Read from the innermost node outward. Compare estimated rows with actual rows: a large mismatch means stale or insufficient statistics, so run ANALYZE or raise the statistics target. Look for Seq Scan on big tables, nested loops over large outer sets, sorts using disk ("external merge"), and high "Rows Removed by Filter". Add BUFFERS to see cache hits versus reads. ANALYZE really runs INSERT, UPDATE and DELETE, so wrap those in a transaction and roll back.
sqlEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;8 What are PostgreSQL's transaction isolation levels and default?
mediumThe default is READ COMMITTED: each statement sees data committed before it started. REPEATABLE READ gives the whole transaction one snapshot and, unlike the standard requirement, also prevents phantom reads, though it can fail with a serialisation error on conflicting updates. SERIALIZABLE uses serializable snapshot isolation and aborts transactions that could produce a non-serial result, so the application must retry. READ UNCOMMITTED is accepted but behaves as READ COMMITTED, since PostgreSQL never exposes dirty reads.
9 Does PostgreSQL support transactional DDL? Why does it matter?
mediumYes. CREATE TABLE, ALTER TABLE and DROP can run inside a transaction and be rolled back, so a multi-step migration either fully applies or leaves the schema untouched. MySQL and Oracle commit implicitly on DDL. A few commands cannot run inside a transaction block, such as CREATE INDEX CONCURRENTLY and VACUUM. For large tables, CREATE INDEX CONCURRENTLY builds an index without blocking writes at the cost of a longer build and the need to check for an invalid index afterwards.
10 What are CTEs' performance characteristics in PostgreSQL?
mediumBefore version 12, every CTE was materialised and acted as an optimisation fence. From 12, a non-recursive, side-effect-free CTE referenced once is inlined into the main query, and you can force either behaviour with AS MATERIALIZED or AS NOT MATERIALIZED. Data-modifying CTEs (INSERT, UPDATE or DELETE with RETURNING) always execute exactly once. See CTE questions.
11 How does PostgreSQL implement replication?
mediumStreaming (physical) replication ships WAL records from the primary to standbys that replay them, giving a byte-identical read-only replica for failover and read scaling; it can be asynchronous or synchronous. Logical replication publishes row changes by table (publications and subscriptions), which allows replicating a subset, between major versions or to other systems. Standbys can serve read queries (hot standby), and lag grows if the primary generates WAL faster than they replay. Tools such as Patroni manage failover.
12 What are SERIAL, IDENTITY and UUID for primary keys?
easySERIAL creates an integer column backed by a sequence, a PostgreSQL convention. GENERATED ... AS IDENTITY (PostgreSQL 10+) is the SQL-standard replacement with cleaner ownership and permissions and is preferred for new tables. UUID columns store 128-bit values; gen_random_uuid() is built in from PostgreSQL 13. Random UUIDs spread inserts across the whole index, hurting cache locality compared with sequential ids, but they let clients generate ids without coordination. Sequences are not rolled back, so gaps are normal.
sqlCREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email TEXT NOT NULL UNIQUE );13 What are window functions and other PostgreSQL-specific query features worth knowing?
mediumBeyond standard windows (see window function questions), know DISTINCT ON (keeps the first row per group by the ORDER BY), FILTER on aggregates, array_agg and string_agg, generate_series for series, LATERAL joins, RETURNING on DML, percentile_cont for medians, and ILIKE for case-insensitive matching. DISTINCT ON is the idiomatic way to take the latest row per key.
sqlSELECT DISTINCT ON (customer_id) customer_id, order_id, created_at FROM orders ORDER BY customer_id, created_at DESC; -- latest order per customer14 What is table bloat and how do you detect and fix it?
hardBloat is wasted space from dead tuples that VACUUM marked reusable but that the table file still holds, or from index pages that are mostly empty after heavy updates and deletes. Detect it with pg_stat_user_tables (n_dead_tup), the pgstattuple extension or bloat-estimation queries. Fix by tuning autovacuum for the hot table (lower scale factor), avoiding long transactions and idle-in-transaction sessions, and reclaiming space with REINDEX CONCURRENTLY or pg_repack, since VACUUM FULL locks the table. Fillfactor below 100 leaves room for HOT updates.
Frequently asked questions
Is PostgreSQL asked in fresher interviews?
Occasionally. Freshers mostly get standard SQL; PostgreSQL internals such as MVCC and VACUUM come up for backend and data engineering roles.
What is the difference between PostgreSQL and Oracle?
Both are full-featured. Oracle uses PL/SQL and proprietary features such as CONNECT BY; PostgreSQL uses PL/pgSQL and is open source. See PL/SQL questions.
What is pg_stat_statements?
An extension that tracks execution statistics for every normalised query, which is the first place to look for the slowest or most frequent queries.
Which PostgreSQL version should I know?
Know the features of 12 to 16: inlined CTEs, generated columns, MERGE in 15, and improvements to parallelism and partitioning.
Related guides
More in Programming Language Interview Questions by Language
- Python Coding Interview Questions with Solutions
- Python Decorators Interview Questions
- Python Exception Handling Interview Questions
- Python File Handling Interview Questions
- Python Generators and Iterators Interview Questions
- Python Interview Questions and Answers
- Python Lambda, Map, Filter and Reduce Interview Questions
- Python List, Dict and Set Interview Questions
- Python Memory Management Interview Questions
- Python Multithreading and Multiprocessing Interview Questions
- Python OOPs Interview Questions
- Python String Interview Questions