Index/PostgreSQL

SponsorGitHub
Key technologyRelational store4 min

PostgreSQL

The relational default: transactions, joins and constraints on one primary, until scale forces you off it.

At a glance

Model
Relational rows, SQL, joins and constraints
Consistency
ACID; MVCC snapshots, read committed by default
Writes
One primary — name that ceiling before you're asked
Reads
Streaming replicas, usually milliseconds behind
Indexes
B-tree, GIN, GiST, BRIN; partial and covering
More than rows
JSONB documents, PostGIS geo, full-text search

Key concepts and capabilities

The short listwhat it gives you
WAL first — every write is an append plus an fsync before any data page moves; replicas and CDC both read that log
MVCC — an update writes a new row version, so readers never block writers; VACUUM reclaims the dead ones
Read committed by default — a fresh snapshot per statement, which does not prevent lost updates
Row locks — SELECT … FOR UPDATE holds a row for the transaction; SKIP LOCKED turns a table into a work queue
Constraints are correctness — a UNIQUE index on an idempotency key is the cheapest exactly-once there is
Indexes — B-tree by default, GIN for JSONB and full-text, GiST for ranges and geometry, BRIN for append-only data
More than a row store — JSONB documents, PostGIS radius queries, full-text search good to a few million documents
Replication — async by default, synchronous_commit trades write latency for no acknowledged loss
Logical decoding — the WAL as a change stream, which is how derived stores stay in sync without dual writes
Connections are processes — past a few hundred, put PgBouncer in front
Scaling path — vertical, then read replicas, then partitioning, then sharding; stop where the numbers stop

Use cases

Orders and money, in one transaction

The reason to reach for Postgres first. An order row and its payment attempt are written in the same transaction, so a crash between them is impossible, and the UNIQUE (order_id, idempotency_key) index means a retried checkout returns the original result instead of charging twice.

primary keyforeign key → referenced columnnullableHover a table or column to trace its keys

Read scaling, and the read-after-write trap

Reads scale out on replicas; writes do not. The catch is the user who posts and immediately reloads: their own write may not have arrived on the replica yet. Route that one read to the primary, or return the write's own result.

DatabaseFocusClick a node for details

A work queue, without a queue

SELECT … FOR UPDATE SKIP LOCKED lets several workers drain one table safely: each claims rows nobody else holds, and a worker that dies rolls back and the rows become claimable again. Good enough for outbox rows, scheduled emails and retries — and one fewer system than Kafka.

FocusClick a node for details

Feeding a search index from the WAL

Logical decoding turns the same write-ahead log into a stream of row changes, so a search index, a cache or a warehouse is fed from the database's own log rather than by a second write from the application. That is what makes the index rebuildable, and what keeps it from silently diverging.

DatabaseQueue / streamFocusClick a node for details

Growing past one primary

Partitioning splits one table by range or hash inside the same database, so queries and vacuum touch less data. Sharding splits it across independent clusters keyed by tenant or user id — at which point transactions become sagas and joins become application-side fan-out, so it is the step you justify.

FocusClick a node for details