PostgreSQL-specific interview questions dig into MVCC, indexing, and the advanced features that set it apart from other relational databases.
Postgres uses Multi-Version Concurrency Control, meaning it doesn't lock rows for reads at all — instead every row carries hidden `xmin` and `xmax` values recording which transaction created it and, if applicable, which one deleted it. When you update a row, Postgres doesn't overwrite it in place; it writes a brand new row version and marks the old one as expired for future transactions, so readers see a consistent snapshot of the data as of when their transaction or statement began. This is why a long-running `SELECT` never blocks a concurrent `UPDATE` and vice versa — writers and readers are working against different versions of the same logical row. The tradeoff is that old row versions pile up as dead tuples until `VACUUM` reclaims them, which is why vacuuming is so central to running Postgres well. Under the default `READ COMMITTED` isolation level each statement gets its own snapshot, while `REPEATABLE READ` and `SERIALIZABLE` hold one snapshot for the whole transaction.
The biggest architectural difference is that Postgres is process-based, forking an OS process per connection, while MySQL/InnoDB is thread-based, which is part of why Postgres leans so heavily on an external pooler like `PgBouncer` under high connection counts. Postgres has historically been the more strict, standards-compliant engine, enforcing constraints consistently and avoiding the silent type coercion MySQL was known for in older versions. It also ships a richer type system out of the box — `JSONB`, arrays, ranges, `UUID`, enums — plus a genuine extension framework for adding capabilities like geospatial or fuzzy text search. MySQL, on the other hand, has traditionally had an edge in raw throughput for simple OLTP workloads and offers a simpler mental model for basic primary-replica replication. In practice the choice today often comes down to workload shape — Postgres for complex queries and JSON-heavy or extension-driven use cases, MySQL for straightforward, well-understood replicated setups.
`JSONB` is Postgres's binary, pre-parsed JSON type, as opposed to the plain `JSON` type, which just stores the exact text you gave it and re-parses it on every access. Because `JSONB` is decomposed into a binary structure, it supports efficient containment and key-existence operators like `@>`, `?`, and `->` without re-parsing the document each time, and it also normalizes away whitespace and duplicate keys. The typical way to index it is a GIN index, such as `CREATE INDEX ON events USING GIN (payload)`, which lets Postgres efficiently answer queries like `payload @> '{"status": "failed"}'`. If you consistently filter on one particular key rather than the whole document, an expression index like `CREATE INDEX ON events ((payload ->> 'status'))` is usually a better fit than a full GIN index. The general rule is GIN for "does this document contain X" queries, and a targeted B-tree expression index when you're always filtering on the same field.
Because of MVCC, an `UPDATE` or `DELETE` doesn't actually remove the old row version immediately — it just marks it invisible to new transactions, leaving a dead tuple behind. `VACUUM` scans tables and reclaims the space used by these dead tuples so it can be reused by future inserts and updates, which keeps table and index bloat under control. It also updates the visibility map the planner and index-only scans rely on, and critically, it freezes old transaction IDs to prevent transaction ID wraparound, which if ignored can eventually force the database into a read-only state. Plain `VACUUM` reclaims space for reuse inside the table but doesn't shrink the file on disk, whereas `VACUUM FULL` rewrites the whole table to actually return space to the OS, at the cost of an exclusive lock. In practice you rarely run it manually, since `autovacuum` is a background process that watches table activity and triggers vacuums automatically, though tuning its thresholds for high-churn tables is a common piece of production tuning.
Postgres lets you store an array of any base type directly in a column, so instead of a separate join table for something like tags you can declare `tags TEXT[]` and query it with the containment operator `@>` or expand it back into rows with `unnest()`. Range types such as `int4range`, `tsrange`, or `daterange` represent a span of values with inclusive or exclusive bounds and are great for things like booking systems, where an exclusion constraint using the `&&` overlap operator can guarantee no two reservations for the same room ever overlap. `UUID` is a native 128-bit type, commonly generated with `gen_random_uuid()`, and is often used as a primary key when you want globally unique, non-sequential identifiers, for example to avoid leaking row counts or to merge data cleanly from multiple sources. Beyond those, Postgres also offers `hstore` for simple key-value pairs, network types like `inet` and `cidr`, and user-defined enum types. The common thread is that Postgres lets the database enforce structure that you'd otherwise have to bolt on in application code.
Extensions are a packaging mechanism that lets you add new types, functions, operators, and index types to Postgres without touching the core codebase, installed with a simple `CREATE EXTENSION extension_name`. `PostGIS` is probably the most well-known one — it turns Postgres into a full geospatial database, adding geometry and geography types along with spatial indexes and functions for distance calculations or point-in-polygon lookups. `pg_trgm` adds trigram-based text similarity matching, which is what powers fast fuzzy `ILIKE` searches and similarity indexes when you don't want to stand up a separate search engine. Other commonly used ones include `pg_stat_statements` for tracking query performance across the whole instance, `pgcrypto` for hashing and UUID generation, and `postgres_fdw` for querying another Postgres database as if its tables were local. This extensibility is a big reason Postgres gets described as more of a data platform than just a database engine.
A sequence is a standalone database object that generates a series of numbers via `nextval()`, and it exists independently of any table — you can create one with `CREATE SEQUENCE` and reference it from anywhere. `SERIAL` is really just syntactic sugar: declaring a column as `SERIAL` creates a sequence behind the scenes, sets the column's default to pull from it, and ties the sequence's ownership to that column, but it isn't part of the SQL standard and can be a bit opaque about what it actually created. `GENERATED AS IDENTITY`, added in Postgres 10, does the same job in a standards-compliant way and gives more control — `GENERATED ALWAYS AS IDENTITY` blocks explicit overrides on insert, while `GENERATED BY DEFAULT AS IDENTITY` allows them when needed, such as during data migrations. Functionally, sequences behave the same under the hood either way, including caching behavior and skipping values on rollback, but `IDENTITY` is now the generally recommended approach for new schemas. One shared gotcha is that sequence values aren't transactional, so a rolled-back insert still burns a value, which is why gaps in IDs are normal and expected.
Partitioning splits one logical table into multiple physical child tables based on a partitioning key, while still letting you query the parent table as if it were a single table. Postgres supports range partitioning, like splitting an `orders` table by month, list partitioning for discrete categories such as region, and hash partitioning for spreading data evenly when there's no natural range or list key. The main benefit is partition pruning — if a query filters on the partition key, the planner can skip scanning partitions that couldn't contain matching rows, turning a full table scan into a scan of just one or two much smaller tables. It also makes maintenance cheaper, since you can drop or detach an entire old partition, like last year's log data, almost instantly instead of running a slow bulk `DELETE`, and each partition can carry its own indexes. You'd typically reach for it once a table grows large enough — often tens or hundreds of millions of rows — that vacuum, index maintenance, or query performance on the whole table starts becoming a real problem.
`EXPLAIN` shows the planner's chosen execution plan without running the query, while `EXPLAIN ANALYZE` actually executes it and adds real timing and row-count data alongside the plan, which matters because the planner's estimates can be wrong. The plan is a tree of nodes best read from the innermost, bottom nodes outward, since those run first and feed rows up to the nodes above them. The first things I look for are sequential scans on large tables where you'd expect an index to be used, and big gaps between estimated and actual row counts, since that usually points to stale statistics that need an `ANALYZE`, or a `WHERE` clause written in a way the planner can't use an index for. I also check which join strategy got picked — nested loop, hash join, or merge join — since a nested loop against a large unfiltered table is a classic sign something upstream isn't being filtered efficiently. Adding the `BUFFERS` option is worth doing too, since it shows how much work was served from shared buffers versus hitting disk.
Streaming replication works by having a replica continuously receive and replay the write-ahead log records generated by the primary, rather than periodically shipping whole log files the way old-style log-shipping did. The primary's `walsender` process streams WAL records to a `walreceiver` process on the standby as soon as they're generated, and the standby applies them to its own copy of the data files, keeping it just moments behind the primary. By default this is asynchronous, meaning the primary commits a transaction without waiting for the replica to confirm receipt, which is fast but leaves a small window of possible data loss if the primary crashes right after committing. You can configure synchronous replication instead, where the primary waits for one or more replicas to acknowledge the WAL before considering a commit successful, trading some latency for a stronger durability guarantee. This mechanism underlies both read replicas for scaling out read traffic and hot standbys used for failover.
Streaming replication works at the physical level — it ships the raw WAL byte-for-byte and replays it, so the standby is an exact binary copy of the primary, must run the same major Postgres version, and typically replicates the whole cluster rather than individual tables. Logical replication, introduced properly in Postgres 10, instead decodes the WAL into a stream of row-level changes and applies those as actual operations on the target, using a publish/subscribe model set up with `CREATE PUBLICATION` on the source and `CREATE SUBSCRIPTION` on the subscriber. Because it works at the logical row level rather than the physical byte level, it lets you replicate a subset of tables, replicate across different major Postgres versions, and still write to other tables on the subscriber, none of which physical replication supports. The tradeoff is that logical replication doesn't replicate DDL changes automatically and has some limitations around large objects, plus a bit more overhead than physical streaming. In practice, streaming replication is the go-to for straightforward HA and failover, while logical replication tends to get used for zero-downtime major version upgrades or feeding a selective subset of data to another system.
Every Postgres connection is backed by its own OS process on the server, and spinning those up and down is relatively expensive, plus each idle connection still holds a meaningful chunk of memory. That's fine for a modest number of long-lived connections, but it falls over in modern app architectures with hundreds of application instances or serverless functions each opening their own connections, quickly exhausting `max_connections` and starving the server of resources. `PgBouncer` sits between the application and Postgres and maintains a small pool of real backend connections, handing them out to clients as needed instead of letting every client open a dedicated one. In its most common mode, transaction pooling, a client only holds a real server connection for the duration of a single transaction and releases it immediately afterward, letting a handful of backend connections serve a much larger number of concurrent clients. The tradeoff is that transaction-mode pooling breaks session-level features like prepared statements or session-scoped `SET` commands that assume a stable connection, so the application needs to be written with that in mind.
The write-ahead log is a sequential, append-only record of every change made to the database, and the core rule it enforces is that a change must be written to WAL and flushed to disk before the corresponding change to the data files is considered durable. This is what gives Postgres crash recovery — if the server crashes mid-write, on restart it replays WAL from the last checkpoint forward to bring the data files back to a consistent state instead of risking corrupted pages. It's also the foundation for both streaming and logical replication, since replicas essentially consume and replay this same log, as well as for point-in-time recovery, where you restore a base backup and replay WAL up to a specific moment. WAL is written sequentially, which is far cheaper on disk than random writes to data files, so it also serves a performance purpose by letting Postgres defer and batch the more expensive writes to the actual heap and index files. Checkpoints periodically flush dirty in-memory pages to disk and mark a point after which older WAL segments are no longer needed for crash recovery, which is why checkpoint tuning matters for write-heavy workloads.
Postgres supports the four SQL-standard isolation levels — `READ UNCOMMITTED`, `READ COMMITTED`, `REPEATABLE READ`, and `SERIALIZABLE` — though `READ UNCOMMITTED` behaves identically to `READ COMMITTED` internally since MVCC never lets you see another transaction's uncommitted data anyway. `READ COMMITTED` is the default, meaning each individual statement within a transaction sees a fresh snapshot as of when that statement started, so a change committed by another transaction between two of your statements becomes visible to the second one. `REPEATABLE READ` instead takes a single snapshot at the start of the transaction and holds it for every statement within it, giving a fully consistent view throughout, though it can raise a serialization error if you try to update a row that's changed since that snapshot was taken. `SERIALIZABLE` goes further and guarantees an outcome equivalent to running transactions one at a time in some order, using predicate locking to detect and abort transactions that would violate that guarantee. Most applications run comfortably on `READ COMMITTED`, reaching for `REPEATABLE READ` or `SERIALIZABLE` only when there's a specific correctness requirement around concurrent reads and writes, like financial ledger logic.
Postgres implements upsert behavior through the `INSERT ... ON CONFLICT` clause, which lets you specify what should happen when an insert would violate a unique or exclusion constraint instead of just throwing an error. `ON CONFLICT (column) DO NOTHING` simply skips the insert silently if a conflicting row already exists, which is handy for idempotent inserts. `ON CONFLICT (column) DO UPDATE SET ...` is the actual upsert pattern, where you can reference the row that would have been inserted via the special `EXCLUDED` pseudo-table, for example `ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login`. The whole operation happens atomically in a single statement, which avoids the classic race condition of doing a `SELECT` to check existence followed by a separate `INSERT` or `UPDATE`. One thing to watch for is that the conflict target has to match an actual unique or exclusion constraint on the table, not just any column you'd like to check against.