PostgreSQL Interview Questions

A curated set of PostgreSQL interview questions, ordered roughly from fundamentals to more advanced — the kind you'll actually be asked in real screens and on-sites.

MVCC and concurrency#

Q: Explain MVCC and why it matters for concurrency. Multi-Version Concurrency Control means PostgreSQL never updates a row in place — an UPDATE creates a new version of the row and marks the old one obsolete rather than overwriting it, and every transaction sees a consistent snapshot of the data as of when it started. This lets readers and writers operate on the same rows at the same time without blocking each other, because a reader is looking at a snapshot rather than contending for a lock on the live row a writer is currently modifying.

Q: What is a dead tuple, and why does VACUUM exist? A dead tuple is an old row version left behind after an UPDATE or DELETE, no longer visible to any transaction but still physically occupying disk space because MVCC doesn't delete it immediately. VACUUM reclaims that space for reuse (and VACUUM ANALYZE refreshes planner statistics); without it, a table under heavy write churn would grow unboundedly relative to its actual logical data size. In practice, PostgreSQL's autovacuum process handles this automatically for most workloads.

Q: How does PostgreSQL's default isolation level differ from MySQL's? PostgreSQL defaults to READ COMMITTED, taking a fresh snapshot on every statement within a transaction; MySQL's InnoDB defaults to REPEATABLE READ, taking one snapshot for the entire transaction. This means the same isolation level name doesn't guarantee the same behavior across the two databases — a transaction re-reading the same row twice may see a value change mid-transaction on Postgres's default but not on MySQL's.

Data types#

Q: When would you use JSONB instead of a normalized set of columns? JSONB fits data whose shape genuinely varies row to row — third-party API payloads, event data, or user-defined custom fields — where forcing every possible field into its own column would mean a huge number of mostly-null columns. It's the wrong tool once specific fields inside the JSON are queried, filtered, or joined on constantly; at that point those fields have earned being pulled out into real, indexed columns.

Q: What's the difference between the -> and ->> JSONB operators? -> retrieves a value by key and returns it as JSONB, useful for chaining into nested structures or re-inserting the result elsewhere. ->> retrieves a value by key as plain text, which is what you need to actually compare it against a string or use it in ordinary WHERE/ORDER BY expressions.

Indexing#

Q: Why would you use a GIN index instead of the default B-tree? A B-tree index treats a JSONB or array column's entire value as one unit, which only helps with whole-value equality — it can't accelerate a "does this contain X" query. A GIN index instead indexes each individual element (each key in a JSON object, each array element), making containment operators like @> fast on exactly the columns where B-tree falls short.

Trade-offs#

Q: When would you choose PostgreSQL over MySQL for a new project, and when might you choose MySQL instead? PostgreSQL tends to win for applications with complex querying needs, heavy reliance on JSON-shaped or semi-structured data, geospatial requirements (via PostGIS), or a need for advanced constraints and data types beyond standard SQL. MySQL remains an excellent, often simpler choice for straightforward, high-throughput read-heavy web applications, and benefits from an enormous, mature hosting and tooling ecosystem. Neither choice is wrong in the abstract — it comes down to the specific data shapes and query patterns the application actually needs.

Search and extensions#

Q: How is PostgreSQL's full-text search different from a plain LIKE '%word%' query? LIKE matches literal substrings, can't use a normal index when the pattern starts with a wildcard, and has no concept of word variants or relevance ranking. Full-text search converts text into a tsvector (a normalized list of word stems) and a search into a tsquery, matched with @@ — so a search for "run" also matches "running" via stemming, the match can be accelerated with a GIN index, and results can be ranked by relevance with ts_rank() rather than treated as a flat yes/no.

Q: What is a PostgreSQL extension, and what privilege does installing one typically require? An extension is a packaged bundle of SQL objects — types, functions, operators, even entire index access methods — that can be loaded into a specific database with CREATE EXTENSION without modifying PostgreSQL's core at all; pg_trgm, PostGIS, and pgcrypto are common real examples. Installing one typically requires superuser privileges (or an equivalent managed-hosting role), and many hosted providers only permit a curated allowlist of extensions, so not every extension is available on every hosted instance.

Q: When would you reach for pg_trgm instead of full-text search? Full-text search's stemming handles word variants ("running" matching "run") but does nothing for typos — "databse" won't match "database." pg_trgm solves the opposite problem: it breaks text into overlapping three-character sequences and measures how many two strings share, making it the right tool for typo-tolerant fuzzy matching (and for accelerating a plain ILIKE with a trigram GIN index), not for understanding linguistic word forms.