PostgreSQL Interview Questions

PostgreSQL is an open-source, standards-compliant object-relational database. Compared with MySQL it has fuller SQL support (complete window functions, CTEs, LATERAL joins), richer types (arrays, JSONB, ranges, custom types), stricter data integrity by default, and multi-version concurrency control that lets readers and writers avoid blocking each other.

Multi-Version Concurrency Control means each transaction sees a consistent snapshot of the data; writers create new row versions instead of overwriting, so readers never block writers and writers never block readers. This gives high concurrency without read locks, at the cost of needing VACUUM to reclaim old row versions.

Because MVCC leaves behind dead row versions after updates and deletes, VACUUM reclaims that space and updates the planner's statistics; VACUUM FULL compacts the table but takes an exclusive lock. Autovacuum runs this automatically in the background. Neglected autovacuum leads to table bloat and, eventually, transaction-ID wraparound problems.

JSON stores an exact text copy and reparses it on each access. JSONB stores a decomposed binary form: slightly slower to insert, but much faster to query, and it supports indexing (GIN) and operators like @> for containment. Use JSONB unless you must preserve exact formatting and key order.

B-tree (default, for equality and range on ordered types), Hash (equality only), GIN (multi-value columns — arrays, JSONB, full-text), GiST (geometric/range/nearest-neighbour), SP-GiST, and BRIN (very large, naturally ordered tables where a tiny summary per block suffices).

An index built only over rows matching a WHERE condition — for example CREATE INDEX ON orders (created_at) WHERE status = 'open'. It's smaller and faster to maintain when queries only ever target that subset, such as active or unprocessed rows.

EXPLAIN shows the planner's chosen plan and estimates; EXPLAIN ANALYZE actually runs the query and shows real row counts and timings. Compare estimated vs actual rows to spot bad statistics, and look for unexpected Seq Scans on large tables where an index scan was expected.

The Oversized-Attribute Storage Technique. When a row's value is too big for a page (about 8 KB), PostgreSQL transparently compresses it and/or stores it out-of-line in a companion TOAST table. It's why you can store large text and JSONB values without manual blob handling.

With INSERT ... ON CONFLICT. On a unique-key collision you can either skip (DO NOTHING) or update (DO UPDATE SET ...), referring to the attempted row via EXCLUDED.

INSERT INTO inventory (sku, qty) VALUES ('A1', 5)
ON CONFLICT (sku)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;

A LATERAL subquery in the FROM clause can reference columns from tables listed before it, so it runs once per outer row — ideal for "top N per group" queries, such as the three most recent orders for each customer.

A WITH RECURSIVE query walks hierarchical or graph data — organisation charts, category trees, bill-of-materials. It has an anchor member (the starting rows) unioned with a recursive member that references the CTE itself until no new rows are produced.

SERIAL is shorthand that creates an integer column backed by a sequence for auto-incrementing keys. The SQL-standard GENERATED ... AS IDENTITY (Postgres 10+) is now preferred: it's clearer, portable, and lets you control whether the value can be overridden.

Read Committed (the default), Repeatable Read, and Serializable. (Read Uncommitted is accepted but behaves as Read Committed, since MVCC never exposes dirty reads.) Serializable uses Serializable Snapshot Isolation to guarantee transactions behave as if run one at a time, aborting one if a dangerous dependency cycle is detected.

Declarative partitioning (Postgres 10+) splits a large logical table into child partitions by RANGE, LIST, or HASH. Queries with a matching predicate scan only the relevant partitions (partition pruning), and you can drop a whole partition to purge old data cheaply — common for time-series and log tables.

A regular view re-runs its query every time it's referenced and always reflects current data. A materialized view stores the result physically and must be updated with REFRESH MATERIALIZED VIEW; it trades freshness for fast reads on expensive aggregations. REFRESH ... CONCURRENTLY avoids locking readers during the refresh.

It converts documents to a tsvector (normalised lexemes) and queries with a tsquery, matched by the @@ operator and accelerated by a GIN index. It supports stemming, ranking, and multiple languages without an external search engine for moderate workloads.