Interview Q&A / PostgreSQL

1. How does PostgreSQL implement transaction isolation levels?

   PostgreSQL offers Read Committed (the default), Repeatable Read and Serializable. Dirty reads never happen, even if you ask for Read Uncommitted. Read Committed takes a new snapshot per statement, Repeatable Read keeps one snapshot for the whole transaction, and Serializable adds checks that abort a transaction if the result could not have come from running transactions one at a time.

   AdvancedIn-depth4 min read
2. How do you add a foreign key or CHECK constraint to a huge table without a long lock?

   Add it as NOT VALID, which skips the check of existing rows and only needs a brief lock, then run VALIDATE CONSTRAINT separately. Validation scans the table but only takes a weaker lock, so writes continue.

   AdvancedQuick answer1 min read
3. How do you stop two bookings from overlapping in PostgreSQL?

   Use an exclusion constraint with a range column and the overlap operator && on a GiST index. The database then rejects any row whose range overlaps an existing row for the same resource.

   AdvancedQuick answer1 min read
4. What are the jsonb ? operators and jsonpath queries used for?

   The ? operator tests whether a key or string element exists at the top level; ?| and ?& test any or all of several keys. jsonpath (@? and @@ operators, jsonb_path_query) lets you filter inside nested structures with an expression.

   AdvancedQuick answer1 min read
5. What do MATERIALIZED and NOT MATERIALIZED do on a CTE?

   They control whether PostgreSQL computes the CTE once and stores the result (MATERIALIZED) or folds it into the outer query like a subquery (NOT MATERIALIZED). Before version 12, every CTE was materialized and acted as an optimisation fence.

   AdvancedQuick answer1 min read
6. What is a DEFERRABLE constraint in PostgreSQL and when is it needed?

   A deferrable constraint can be checked at COMMIT instead of after each statement. This lets you insert rows that reference each other, or swap unique values, as long as everything is consistent by the end of the transaction.

   AdvancedQuick answer1 min read
7. What is a HOT update in PostgreSQL and how does fillfactor help?

   A HOT (heap-only tuple) update happens when no indexed column changes and the new row version fits on the same page, so no new index entries are needed. Lowering fillfactor leaves free space on each page to make that more likely.

   AdvancedQuick answer1 min read
8. What is transaction ID wraparound in PostgreSQL and how is it prevented?

   Transaction IDs are 32-bit numbers, so after about two billion, old ones would look like the future and rows would vanish. VACUUM freezes old row versions to prevent this, and PostgreSQL forces aggressive vacuums and finally stops accepting writes if it falls too far behind.

   AdvancedQuick answer1 min read
9. Why can a quick ALTER TABLE freeze every query on a busy table?

   ALTER TABLE needs a strong lock that waits behind any running query on the table, and every new query then queues behind the ALTER. One long transaction can therefore stall the entire table. Set lock_timeout so the DDL gives up quickly.

   AdvancedQuick answer1 min read
10. Why does PostgreSQL sometimes show Index Only Scan but still read the table?

    An index does not store row visibility. PostgreSQL checks the visibility map, and for pages not marked all-visible it must fetch the heap row. EXPLAIN ANALYZE shows this as Heap Fetches, which VACUUM reduces.

    AdvancedQuick answer1 min read