Autocoder

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.

VersionPostgreSQL 13 and later
StrengthsMVCC, rich types, extensions, standards compliance
Compared withMySQL where it helps

Concepts behind most PostgreSQL answers

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. 1

    What is PostgreSQL, and how does it differ from MySQL?

    easy

    PostgreSQL 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. 2

    Explain MVCC in PostgreSQL.

    medium

    Multi-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. 3

    What does VACUUM do, and what is the difference from VACUUM FULL and autovacuum?

    medium

    VACUUM 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.

    sql
    VACUUM (VERBOSE, ANALYZE) orders;
    SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;
  4. 4

    What index types does PostgreSQL offer, and when do you use each?

    medium

    B-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.

    sql
    CREATE 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. 5

    What is the difference between JSON and JSONB?

    medium

    json 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.

    sql
    SELECT data->>'city' AS city FROM customers WHERE data @> '{"vip": true}';
    CREATE INDEX idx_customers_data ON customers USING GIN (data);
  6. 6

    How do you do an upsert in PostgreSQL?

    medium

    INSERT ... 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.

    sql
    INSERT INTO page_views (page_id, views) VALUES (7, 1)
    ON CONFLICT (page_id) DO UPDATE SET views = page_views.views + EXCLUDED.views;
  7. 7

    How do you read EXPLAIN ANALYZE output?

    hard

    EXPLAIN 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.

    sql
    EXPLAIN (ANALYZE, BUFFERS)
    SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;
  8. 8

    What are PostgreSQL's transaction isolation levels and default?

    medium

    The 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. 9

    Does PostgreSQL support transactional DDL? Why does it matter?

    medium

    Yes. 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. 10

    What are CTEs' performance characteristics in PostgreSQL?

    medium

    Before 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. 11

    How does PostgreSQL implement replication?

    medium

    Streaming (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. 12

    What are SERIAL, IDENTITY and UUID for primary keys?

    easy

    SERIAL 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.

    sql
    CREATE TABLE users (
      id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      email TEXT NOT NULL UNIQUE
    );
  13. 13

    What are window functions and other PostgreSQL-specific query features worth knowing?

    medium

    Beyond 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.

    sql
    SELECT DISTINCT ON (customer_id) customer_id, order_id, created_at
    FROM orders ORDER BY customer_id, created_at DESC;   -- latest order per customer
  14. 14

    What is table bloat and how do you detect and fix it?

    hard

    Bloat 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

See all in this section