PostgreSQL Internals: MVCC, Indexing, and Transaction Behavior

PostgreSQL's SQL surface hides a small set of internal mechanisms - concurrency control, indexing, and transaction isolation - that determine correctness and performance under real production load.

Overview

Postgres uses a process-based architecture (one backend process per connection, forked by postmaster), so connection count directly maps to memory overhead, unlike thread-based databases.
MVCC (Multi-Version Concurrency Control) is the core concurrency model: readers never block writers and vice versa, because every transaction sees its own consistent snapshot rather than contending for a single copy of the data.
Durability and replication both flow from the Write-Ahead Log (WAL): changes are logged before being applied, enabling crash recovery, streaming replication, and point-in-time recovery from the same mechanism.
Index type is a deliberate choice, not a default - B-tree, GIN, and GiST solve different query shapes, and picking the wrong one silently degrades to a sequential scan.
The default isolation level (Read Committed) allows different statements in the same transaction to see different snapshots - a frequent, non-obvious source of "flaky" concurrency bugs.

Key Concepts

Process Architecture & Connection Overhead

Each client connection gets its own OS-level backend process (not a thread) - real memory overhead per connection, even when idle.
Large numbers of idle-but-open connections degrade instance performance directly.
Standard fix: a connection pooler (e.g. PgBouncer) between application and Postgres, multiplexing many app-level connections onto fewer real backend processes.

MVCC (Multi-Version Concurrency Control)

Each transaction gets a consistent snapshot of the database at a point in time, instead of using blocking read locks.
UPDATE creates a new row version and marks the old one invisible; DELETE marks a row invisible rather than removing it immediately - nothing is overwritten in place.
This is why readers and writers don't block each other, but it also means old row versions ("dead tuples") accumulate and must be cleaned up.
Autovacuum is the background process that reclaims dead tuples; a misconfigured or overwhelmed autovacuum is one of the most common causes of unexplained performance degradation and table/index bloat.

Write-Ahead Log (WAL)

Every change is written to the WAL before being applied to actual data files - guarantees crash recovery by replaying the log on restart.
Same mechanism underlies physical streaming replication (WAL records shipped to replicas) and point-in-time recovery (base backup + WAL segment replay).

Index Types

B-tree (default): equality/range queries on sortable data - automatic on primary keys and plain CREATE INDEX.
GIN: columns with multiple searchable values per row - full-text search, JSONB, arrays.
GiST: geometric containment, range overlap, and other non-standard comparison types.
Wrong choice doesn't just underperform - a B-tree on a JSONB column typically won't be used at all for containment queries, silently falling back to a sequential scan.

-- Without this index, a JSONB containment query forces a full table scan:
CREATE INDEX idx_products_attributes_gin ON products USING GIN (attributes);

Transaction Isolation Levels

Read Committed (default): each statement sees data committed before that statement began - different statements in the same transaction can see different snapshots.
Repeatable Read: one consistent snapshot for the transaction's entire duration.
Serializable: full protection against concurrency anomalies, at the cost of serialization failures that require retrying the transaction.
Bugs blamed on "flaky" app logic are frequently Repeatable-Read-shaped code running under the Read Committed default.

Locking

Row-level locks (implicit on UPDATE/DELETE, explicit via SELECT ... FOR UPDATE) prevent concurrent modification of the same row; this layers on top of MVCC rather than replacing it.
Table-level locks apply to schema changes - critically, CREATE INDEX without CONCURRENTLY takes a lock strong enough to block ordinary reads/writes for the full build duration.

Trade-offs / Caveats

Autovacuum neglect (default settings mismatched to actual write volume) causes silent, gradual table/index bloat - visible in advance via pg_stat_user_tables dead-tuple counts, but rarely checked until performance has already degraded.
Schema migrations that skip lock-strength awareness can pass instantly in staging and take production down - CREATE INDEX CONCURRENTLY avoids the blocking lock but isn't the default.
JSONB flexibility is not a substitute for schema discipline: pushing core business data into JSONB to avoid migrations loses foreign key constraints, NOT NULL guarantees, and type checking. JSONB fits variable/sparse/user-defined attributes, not stable, well-known schemas.

Example in Practice

A funds-transfer operation needs stronger guarantees than the Read Committed default provides across multiple statements, so it explicitly sets isolation level and takes a row lock:

def transfer_funds(from_account_id, to_account_id, amount_cents):
    with pool.connection() as conn:
        conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
        with conn.transaction():
            cur = conn.execute(
                "SELECT balance_cents FROM accounts WHERE id = %s FOR UPDATE",
                (from_account_id,),
            )
            balance = cur.fetchone()[0]
            if balance < amount_cents:
                raise ValueError("Insufficient funds")
            conn.execute(
                "UPDATE accounts SET balance_cents = balance_cents - %s WHERE id = %s",
                (amount_cents, from_account_id),
            )
            conn.execute(
                "UPDATE accounts SET balance_cents = balance_cents + %s WHERE id = %s",
                (amount_cents, to_account_id),
            )

FOR UPDATE takes a row lock on the source account, closing the gap that Read Committed alone leaves open - a second concurrent transfer can't read a stale balance mid-transaction.

Paul Serban