PostgreSQL

MVCC and tuple visibility

PostgreSQL never overwrites a row in place. An UPDATE writes a new tuple version and marks the old one as deleted. Visibility is decided per-query by comparing each tuple’s xmin and xmax against the transaction snapshot taken at statement (or transaction) start. This is what makes readers never block writers and writers never block readers.

Dead tuples accumulate and consume space until VACUUM reclaims them. Autovacuum is a background launcher that schedules vacuum workers when the dead-tuple fraction in a table crosses a threshold.

Query lifecycle

A client sends SQL over the wire protocol. The backend parses it into a parse tree, rewrites it against the rule system, analyzes it (binding names to catalog entries), and hands it to the planner. The planner produces a tree of plan nodes: Seq Scan, Index Scan, Hash Join, Aggregate, Sort. The executor pulls tuples top-down through the plan.

Each backend is a separate OS process. A connection-pooling proxy (pgBouncer) is the usual way to keep thousands of clients from spawning thousands of backends.

              CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

EXPLAIN (ANALYZE, BUFFERS)
SELECT relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 10;
            

Indexes beyond B-tree

PostgreSQL ships with five built-in index access methods. B-tree handles the 80% case. GIN indexes an inverted list per key (great for full-text, JSONB, arrays). BRIN summarizes a range of pages into a tiny min/max (great for time-series with monotonic keys). GiST and SP-GiST are extensible balanced-tree frameworks for spatial, range, and custom types.

Because indexes are plug-in, the community has built hundreds of them: PostGIS for geometry, pg_trgm for fuzzy text, vector for embeddings.