postgres-core-architecture

Quick Reference :

PostgreSQL is a multi-process (NOT multi-threaded) RDBMS that implements Multi-Version Concurrency Control. Every row version (tuple) carries xmin (inserting transaction) and xmax (deleting/updating transaction) header fields. Readers and writers never block each other because each statement (READ COMMITTED) or transaction (REPEATABLE READ / SERIALIZABLE) sees a consistent snapshot. UPDATE never overwrites a tuple in place : it inserts a new tuple version and marks the old one with xmax. DELETE only sets xmax. Reclamation of dead tuples is the job of VACUUM and autovacuum. The Write-Ahead Log is the single source of durability : WAL records flush before dirty data pages, so crash recovery replays WAL since the last checkpoint to rebuild any committed transaction. DDL is transactional (almost all of it) : BEGIN; ALTER TABLE ... ; CREATE INDEX ... ; ROLLBACK; rolls back the entire schema change. The one exception that matters daily is CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY, which run outside any transaction.

The architectural consequences shape every design decision : max_connections is a hard limit because each connection is a forked OS process (use PgBouncer in transaction mode); shared_buffers should be ~25% of RAM (PostgreSQL also relies on the OS page cache, so do not push past 40%); synchronous_commit controls how many disk flushes happen before COMMIT returns; the visibility map enables index-only scans and is what makes "almost all" queries cheap on tables that have been VACUUMed; hint bits are set lazily on first read after INSERT/UPDATE/DELETE, which is why an otherwise-warm SELECT immediately after a bulk load can still produce write I/O.

When To Use This Skill :

ALWAYS use this skill when :

• Designing concurrent write patterns and choosing an isolation level
• A user reports "the SELECT sees old rows" / "where did my updates go" / "the table is huge but count(*) is tiny"
• Tuning shared_buffers, work_mem, wal_buffers, synchronous_commit, or max_connections
• Reasoning about whether a DDL statement is safe inside a transaction
• Choosing between PostgreSQL built-in types (numeric, jsonb, array, range, uuid, enum, domain) : see references/type-system.md