PostgreSQL

PostgreSQL is an open-source relational database: typed tables, constraints, joins and ACID transactions, served by one primary that writes every change to a write-ahead log before acknowledging a commit, then streams that log to replicas. JSONB and extensions stretch it to documents, geospatial, vector and fuzzy-text search. In an interview it is the default system of record for users, orders, payments and bookings; your job is to say what it guarantees, where one machine stops being enough, and what comes next.

This page tracks PostgreSQL 18 (18.6 is the current minor release); PostgreSQL 19 is a release candidate, due for general availability on 19 November 2026.

Picture it first

Picture the records room of an old bank branch. Before touching the big ledgers, a clerk writes the change in a bound journal: “A/c 42: ₹500 → ₹300, voucher 205”. Once that line is in ink, the customer gets a receipt; the ledgers are updated later, a page at a time, and after a power cut the morning shift replays the journal from the last page known to be copied.

Nobody erases a ledger line. A correction is a new line, and the old one is struck through with the voucher that replaced it, so an auditor who started at 10 a.m. keeps reading the 10 a.m. lines; at night a cleaner removes struck-out lines nobody needs. Each customer gets their own clerk, an usher seats people only while they transact, and a courier carries journal copies to an annexe that answers enquiries a few moments behind.

Map it back:

• Journal → the write-ahead log (WAL); ink before receipt → the WAL flush at COMMIT; ledgers updated later → checkpoints.
• Struck-through lines → row versions with xmin and xmax (MVCC); auditor → a snapshot; cleaner → autovacuum.
• A clerk per customer → a process per connection; the usher → a pooler such as PgBouncer.
• Courier and annexe → streaming replication to a read replica, with lag.

Data model

Data lives in typed tables, grouped into schemas and databases; one server (a database cluster) hosts many databases. Types go beyond numbers and text to uuid, arrays, ranges and jsonb, and constraints make the database enforce your invariants: primary and foreign keys, UNIQUE, CHECK, NOT NULL and exclusion constraints. A UPI-style payments table:

CREATE TABLE payments (
  id              uuid        PRIMARY KEY DEFAULT uuidv7(),  -- time-ordered (new in 18)
  idempotency_key text        NOT NULL UNIQUE,               -- a retry can't pay twice
  payer_id        bigint      NOT NULL REFERENCES accounts (id),
  payee_id        bigint      NOT NULL REFERENCES accounts (id),
  amount_paise    bigint      NOT NULL CHECK (amount_paise > 0),
  status          text        NOT NULL DEFAULT 'pending',
  meta            jsonb       NOT NULL DEFAULT '{}',         -- device, channel, extras
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON payments (payer_id, created_at DESC);           -- "my recent payments"
CREATE INDEX ON payments (created_at) WHERE status = 'pending'; -- partial: only pending rows
CREATE INDEX ON payments USING gin (meta jsonb_path_ops);       -- meta @> '{"channel": "qr"}'

The UNIQUE idempotency key turns a retry into a constraint error, not a second debit (idempotency); the CHECK blocks negative amounts despite app bugs (database constraints); and uuidv7() keys are time-ordered, so new rows land together in the index rather than on random pages.

JSONB stores JSON parsed into binary (dropping whitespace, key order and duplicate keys): slightly slower to write than json, much faster to query, and indexable. A GIN index answers containment (@>) and key-exists (?) queries; the jsonb_path_ops operator class is smaller and more selective but drops key-exists. Keep JSONB for the long tail of optional attributes; what you filter or join on daily deserves a column.

Index types

CREATE INDEX builds a B-tree unless you ask for another of the built-in methods:

Modifiers matter as much: a partial index (WHERE status = 'pending') holds only the rows you query, an expression index covers lower(email), and a covering index (INCLUDE (amount)) lets a query skip the table (an index-only scan) once vacuum has marked pages all-visible. PostgreSQL 18 adds B-tree skip scan for queries without an equality condition on the leading column. More in database indexes and composite index design.

Extensions

Extensions add types, operators and index methods. Three come up in interviews:

• PostGIS: geometry and geography types; ST_DWithin(place, me, 3000) uses a GiST index to find places within 3 km (proximity search).
• pgvector (0.8.7): exact or approximate (HNSW, IVFFlat) nearest-neighbour search, indexing up to 2,000 dimensions (vector) or 4,000 (halfvec). Filters apply after the approximate scan, so enable iterative scans (0.8.0+) for filtered queries (vector databases).
• pg_trgm: trigram GIN or GiST indexes serve LIKE '%kurta%', ILIKE and regexes, which a B-tree cannot; with built-in full-text search it covers “good enough” search before a search engine.

Inside one server

One UPDATE and COMMIT, step by step:

1. The backend changes the row’s 8 kB page in shared buffers, the page cache (128 MB by default; start at 25% of RAM).
2. It appends a change record to the WAL buffers.
3. At COMMIT, the WAL is written and fsynced to the WAL files; only then does the client get OK. Concurrent commits can share one flush.
4. Later the checkpointer (every 5 minutes or 1 GB of WAL by default) writes dirty pages to the data files.
5. A WAL sender streams the same records to each standby.

Connections and pooling

The postmaster forks a backend process per connection; max_connections defaults to 100, and shared memory is sized from it. Each process has private memory too: work_mem (4 MB) applies per sort or hash step, several per query. Three hundred app pods with 10 connections each mean 3,000 processes, mostly idle.

PgBouncer, at about 2 kB per client connection, maps them onto a few server connections:

• Session pooling: a server connection per client session. Everything works; little is saved.
• Transaction pooling: a server connection per transaction, the usual choice. Session state breaks: SET, LISTEN, session-level advisory locks, WITH HOLD cursors and SQL PREPARE (protocol-level prepared statements work once max_prepared_statements is above 0).
• Statement pooling: no multi-statement transactions.

Size the server pool with Little’s law, busy connections ≈ transactions per second × seconds each holds one. At 3,000 transactions/s of 5 ms each, 3,000 × 0.005 = 15, so a pool of 40 absorbs bursts (connection pooling).

Guarantees

MVCC: every update writes a new version

Postgres never updates a row in place. Each row version carries xmin, the transaction that created it, and xmax, the one that deleted or replaced it (0 while live). An UPDATE writes a new version and stamps the old one’s xmax. Each query reads through a snapshot, a record of which transactions had committed, so it sees exactly one version of each row.

Reading never blocks writing and writing never blocks reading; only writers to the same row queue. The bill has three parts:

• Dead versions pile up until VACUUM marks them reusable. Autovacuum visits a table once 50 rows plus 20% of it are updated or deleted (capped at 100 million in 18), with 3 workers by default.
• Updates touch indexes, unless the update is HOT (heap-only): no indexed column changed and the page had room. So leave hot columns unindexed and lower fillfactor on busy tables.
• Old snapshots pin garbage: one transaction open for an hour keeps every version created since.

Details in MVCC and vacuum and bloat.

Isolation levels

You can request all four SQL-standard levels, but Postgres implements three:

Read Uncommitted acts as Read Committed, and Postgres’s Repeatable Read also blocks phantoms. Serializable (SSI) adds no blocking beyond Repeatable Read: non-blocking predicate locks track what each transaction read, and one member of any pattern no serial order could produce is aborted, so code must retry the whole transaction. See transaction isolation levels and serializable isolation.

Durability: the WAL and synchronous_commit

Data files change only after the WAL describing the change is on disk, so a commit costs one sequential flush, not scattered page writes. Recovery replays WAL from the last checkpoint; full_page_writes lets it rebuild pages torn mid-write, and clusters created with 18 have checksums on by default.

synchronous_commit sets how far COMMIT waits, even per transaction. With standbys listed in synchronous_standby_names, say the quorum ANY 1 (az_b, az_c) (either of the two), the wait crosses the network:

With off, a crash loses at most 3 × wal_writer_delay = 600 ms of commits by default but corrupts nothing: recovery acts as if they had rolled back. With on, a commit is lost only if the primary and every synchronous standby lose their storage. The network levels cost a round trip per commit, and commits wait indefinitely if no synchronous standby answers, so list more than one. Mix them: SET LOCAL synchronous_commit = off for click logs, a quorum standby for the ledger (write-ahead logging).

The guarantees at a glance

How it scales

All writes go to one primary, so scaling is a ladder: climb a rung only when the one below runs out, because each adds moving parts.

Read replicas and failover

Streaming replication ships WAL records to standbys as they are generated. It is physical (a byte-for-byte copy, so the same major version) and asynchronous by default, with lag “typically under one second” when the standby keeps up. Hot standbys serve reads, with two catches:

• Stale reads. Route a user’s reads to the primary for a few seconds after they write, or use remote_apply where it matters (scaling reads).
• Cancelled queries. If replay must remove rows a standby query still reads, it waits up to max_standby_streaming_delay (30 s), then cancels the query. hot_standby_feedback prevents that by holding back vacuum on the primary: bloat.

Postgres won’t detect a dead primary or promote a standby itself. Tools such as Patroni, keeping leader state in etcd, ZooKeeper, Consul or Kubernetes, promote a standby and fence the old primary, without which both may act as primary. Asynchronous standbys lose unstreamed commits in a failover (failover, data replication).

Logical replication and CDC

Logical replication sends row changes from a publication to subscribers, keyed by primary key, even across major versions (hence low-downtime upgrades); it skips DDL and sequences. The same stream feeds change data capture: Debezium reads it into Kafka. Each consumer’s replication slot keeps WAL until confirmed, so an abandoned slot can fill the disk; 18 can drop idle slots (idle_replication_slot_timeout).

Partitioning

Declarative partitioning splits a table by range, list or hash, say by month. You gain pruning, per-partition indexes that fit in memory, and retention by DETACH or DROP instead of a vast DELETE; the docs suggest it once a table outgrows the server’s RAM. You pay with unique keys that must include the partition key, and key-less queries visiting every partition. It is still one primary (table partitioning).

Sharding: Citus or do it yourself

When writes or hot data outgrow the biggest primary, split by a key. Citus, an open-source (AGPLv3) extension whose version 14 supports PostgreSQL 18, adds a coordinator and workers:

SELECT create_distributed_table('accounts', 'id');                  -- hash rows into shards
SELECT create_distributed_table('ledger_entries', 'account_id',
                                colocate_with := 'accounts');      -- same key, same node
SELECT create_reference_table('merchant_categories');              -- copied to every node

Shards are ordinary tables on the workers. A query filtering on the distribution column goes to one worker, others fan out in parallel, and co-located tables keep joins and foreign keys on one node. App-level sharding routes with your own shard map (tenant → cluster): simpler, but rebalancing and cross-shard queries are yours to build.

Either way, pick the key most transactions stay inside: tenant, account or user. A transfer across two shards needs two-phase commit or a saga. More in sharding a relational database.

Limits and numbers

Hard limits from Appendix K; defaults from the 18.6 configuration reference.

Sizing example: a UPI-style payments ledger. Assume 50 million payments a day, a peak hour at 5× average, and one transaction per payment: insert the payment and two ledger entries (debit, credit), update two balances.

• Write rate. 50M ÷ 86,400 s ≈ 580/s on average; × 5 ≈ 2,900 transactions/s at peak.
• Bytes per payment. A ledger entry: ≈ 56 bytes of columns + 24-byte header (after alignment) + 4-byte line pointer ≈ 84 bytes, plus ≈ 50 bytes in two B-tree indexes ≈ 135 bytes. A payment row with idempotency key and small JSONB: ≈ 250 bytes + ≈ 140 bytes of index entries. Per payment: 390 + 2 × 135 = 660 bytes; with 20% page slack, 0.8 KB.
• Storage. 50M × 0.8 KB = 40 GB a day, about 14.6 TB a year before replicas. One table would hit 32 TB in about two years (32 ÷ 14.6 ≈ 2.2), so partition by month: 40 GB × 30 ≈ 1.2 TB each, old months detached and archived.
• WAL. Assume twice the data written (check pg_stat_wal): 80 GB a day, ≈ 0.9 MB/s on average, 4.6 MB/s at peak; easy to stream to two standbys.
• Connections. At 5 ms per transaction, including the standby round trip: 2,900 × 0.005 ≈ 15 busy connections; a pool of 40–50 covers bursts.
• Verdict. There is no universal throughput figure, so run this transaction in pgbench on the target box: if it sustains, say, 10,000 a second, peak uses 29% and one primary is enough. Storage is the pressure: partition first; shard by account only when writes or hot data outgrow the box.

When to pick it, and when not

More on MySQL in PostgreSQL vs MySQL; the distributed SQL column follows the CockroachDB docs (Raft-replicated ranges, Serializable by default).

Pick PostgreSQL when the data is relational, correctness matters (money, inventory, bookings), queries vary, and writes and hot data fit one primary with headroom: most products at most stages.

Pick something else, or something alongside, when:

• Writes outgrow one machine with no clean shard key, or several regions need strongly consistent writes: distributed SQL.
• One key at enormous scale (sessions, carts, device state): DynamoDB or Cassandra, or Redis if it is hot and rebuildable.
• Analytics over billions of rows: a columnar warehouse fed by CDC (OLTP vs OLAP).
• Relevance-ranked search: Elasticsearch beside Postgres.
• Files and media: blob storage, with only the object key in Postgres.
• A replayable event log for many consumers: Kafka, fed by a transactional outbox.

Common interview usage

Two idioms for the whiteboard:

-- Job queue: each worker claims a different row; locked rows are skipped, not waited on
UPDATE jobs SET state = 'running', claimed_by = 'worker-17', claimed_at = now()
WHERE id = (SELECT id FROM jobs
            WHERE state = 'queued' AND run_at <= now()
            ORDER BY run_at LIMIT 1
            FOR UPDATE SKIP LOCKED)
RETURNING id, payload;

-- Hotel rooms: the database itself rejects overlapping stays in the same room
CREATE EXTENSION btree_gist;
CREATE TABLE stays (
  room_id bigint    NOT NULL,
  during  daterange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

Going deeper: failure modes

Vacuum falls behind

Bloat builds when autovacuum can’t remove dead rows: a session left idle in transaction, a long report, an abandoned replication slot, or hot_standby_feedback from a replica with long queries. Tables and indexes grow; queries slow. Set idle_in_transaction_session_timeout, tune autovacuum per busy table, and alert on the oldest open transaction. VACUUM FULL shrinks a table but locks it (ACCESS EXCLUSIVE) while rewriting.

Transaction ID wraparound

Transaction IDs are 32-bit and compare correctly only within about 2 billion of each other, so vacuum must freeze old rows. Autovacuum forces this on any table whose oldest unfrozen ID passes 200 million transactions, even when autovacuum is off. If something keeps blocking it, the server warns 40 million IDs before wraparound and, at 3 million, refuses new writes until a vacuum completes. Monitor age(datfrozenxid).

Connection storms and hot rows

A deploy or autoscaling burst opens thousands of connections; past max_connections they fail, so cap pools per service behind a pooler. Separately, a popular merchant’s balance or a sale’s stock counter is one row: writers queue on its lock and each update leaves a dead version. Make the ledger append-only and roll balances up, split the counter into N rows, or absorb the burst in Redis.

Failover gaps

A promoted asynchronous standby lacks whatever the primary had not streamed, and an unfenced old primary can return and accept writes. Use a synchronous quorum where losing a commit is unacceptable, and a failover manager backed by a consensus store.

Schema changes that lock

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock. If one waits behind a long query, every new query on that table queues behind it, and a two-second migration becomes an outage. Set a short lock_timeout and retry, build indexes with CREATE INDEX CONCURRENTLY, and follow online schema migrations.

Recent versions

Cheat sheet

How to say it in the interview

“Postgres is the source of truth for accounts, payments and the ledger, because a transfer is a multi-row transaction with constraints. I’d keep Read Committed, make every balance change a conditional UPDATE, and put a unique idempotency key on payments. Commits wait for a synchronous standby in another zone, any one of two, so promoting the most advanced standby loses nothing; statement history reads from async replicas. PgBouncer in transaction mode holds the server to about 40 connections. At 3,000 transactions a second one primary is fine; storage is the pressure, so the ledger is partitioned by month, and if writes outgrow the box I’d shard by account with Citus.”

Why Postgres and not a NoSQL store? The invariants span rows: a transfer changes two balances and writes three rows atomically, and constraints catch bugs. A key-value store pushes that into application code. I’d move an access pattern elsewhere only when a number says it doesn’t fit.

How do you scale writes? Make each write cheaper: fewer indexes, batching, HOT-friendly updates. Then partition. Past one machine, shard by a key most transactions stay inside, with Citus or in the app; cross-shard transfers need two-phase commit or a saga.

Can a committed transaction be lost? Not in a primary crash with defaults: the WAL is fsynced before OK. In a failover to an asynchronous replica, yes, the unstreamed tail. With a synchronous standby and synchronous_commit = on, only if the primary and every synchronous standby lose their storage.

How do you stop two people booking the same seat? Let the database decide: UPDATE seats SET holder = $1 WHERE id = $2 AND holder IS NULL, treating zero rows as “taken”; or SELECT … FOR UPDATE; or a unique or exclusion constraint. Serializable works too, with retries.

Why is a table three times bigger than its data? MVCC leaves dead versions that vacuum can’t remove while an old snapshot might need them. Look for long or idle-in-transaction sessions, stale replication slots and hot_standby_feedback, then tune autovacuum for that table.

Where to go next

• PostgreSQL vs MySQL: engines, replication, and when MySQL wins.
• MVCC and vacuum and bloat: versions, snapshots and cleanup in depth.
• Transaction isolation levels: the anomalies each level allows, with examples.
• Write-ahead logging: why logging first makes commits durable and fast.
• Connection pooling: pool sizing and pooler modes.
• Sharding a relational database: shard keys and resharding.
• Cassandra and DynamoDB: the other side of the comparison table.
• Redis: the cache and counter tier next to Postgres.

References and further reading

1. PostgreSQL 18 documentation: Transaction Isolationpostgresql.orgRead Committed as the default, Repeatable Read as snapshot isolation, Serializable via SSI with predicate locks, and the 40001 retry rule.
2. PostgreSQL 18 documentation: Routine Vacuumingpostgresql.orgWhy dead row versions need VACUUM, the visibility map, transaction ID wraparound, freezing, and the 40-million and 3-million safety thresholds.
3. PostgreSQL 18 documentation: Write Ahead Log settings (synchronous_commit)postgresql.orgThe five synchronous_commit levels and what each survives, fsync, full_page_writes, wal_writer_delay, checkpoint_timeout and max_wal_size defaults.
4. PostgreSQL 18 documentation: Index Typespostgresql.orgWhat B-tree, hash, GiST, SP-GiST, GIN and BRIN indexes are each for.
5. PostgreSQL 18 documentation: Log-Shipping Standby Serverspostgresql.orgStreaming replication (asynchronous by default, lag typically under a second), synchronous and quorum replication, and the need for an external failover tool.
6. PostgreSQL 18 documentation: Table Partitioningpostgresql.orgRange, list and hash partitioning, when partitioning pays off, and the rule that unique keys must include the partition key.
7. PostgreSQL 18 documentation: Appendix K, PostgreSQL Limitspostgresql.orgHard limits quoted in the limits table: 32 TB per relation, 1 GB per field, 1,600 columns, 65,535 query parameters.
8. PostgreSQL 18 Released! (PostgreSQL Global Development Group, September 2025)postgresql.orgAsynchronous I/O, uuidv7(), virtual generated columns, OAuth, skip scan, statistics kept across upgrades and checksums on by default.
9. PgBouncer featurespgbouncer.orgSession, transaction and statement pooling, the per-connection memory cost, and which PostgreSQL features break in transaction mode.
10. Citus: distributed PostgreSQL as an extension (GitHub README)github.comCoordinator and workers, distributed and reference tables, co-location and query routing by distribution column.