PostgreSQL Internals: How a Relational Database Really Works

1. What PostgreSQL is

PostgreSQL is an object-relational database management system that grew out of POSTGRES 4.2 from the University of California at Berkeley . It is an open-source descendant of that original Berkeley code . It supports a large part of the SQL standard, plus features such as complex queries, foreign keys, triggers, updatable views, transactional integrity and multiversion concurrency control .

This deep dive follows a query from the moment a client connects to the moment its changes are safely on disk. Along the way it looks at the processes that make up a server, how the planner chooses a plan, how rows are laid out in pages, how the buffer cache and the write-ahead log work together, and how many sessions can read and write the same rows without blocking each other.

The version this study describes

Everything here describes PostgreSQL 18. The current minor release is 18.6; version 18 was first released on September 25, 2025 and its final release is scheduled for November 14, 2030 . A new major version comes out about once a year , and each is supported for 5 years after its first release .

Since PostgreSQL 10 the first number is the major version and the second is the minor release, so 10 to 11 is a major upgrade and 10.0 to 10.1 a minor one . Before version 10 a major version changed either of the first two numbers, as in 9.5 to 9.6 .

Licence

PostgreSQL is released under the PostgreSQL License, a liberal open-source licence similar to the BSD or MIT licences . Anyone may use, copy, modify and distribute the software for any purpose, without a fee or a written agreement, as long as the copyright notice is kept .

Standards

Development aims to conform to the latest official SQL standard, SQL:2023 (ISO/IEC 9075:2023), where that does not contradict traditional features or common sense . PostgreSQL supports at least 170 of the 177 mandatory features needed for full Core SQL:2023 conformance, and the documentation adds that no current database system claims full conformance .

How to read this study

Every claim links to its source: the PostgreSQL 18 documentation or, where the documentation is silent, the PostgreSQL source code and its READMEs. Three chapters contain simulations you can run in the browser. They are deliberately small models of the real mechanisms, and each one lists what it leaves out.

2. Processes and shared memory

PostgreSQL uses a client/server model: a server process manages the database files, accepts connections from client applications and performs database actions for them. The server program is called postgres .

To handle many connections at once, the server starts ("forks") a new process for each one; the client and its new server process then talk directly, without the original process . The documentation calls this a "process per user" model: each client process connects to exactly one backend process .

The postmaster

The supervisor process is called the postmaster. It listens on a TCP/IP port and spawns a new backend for each connection request; the documentation says a supervisor is needed because the number of connections is not known in advance . It is always running and waiting for connections, while client processes and their backends come and go .

The glossary defines the postmaster as the first process of an instance: it starts and manages the auxiliary processes and creates backend processes on demand . An instance is a group of backend and auxiliary processes that share one memory area, managed by one postmaster and serving exactly one database cluster .

Crash isolation

The postmaster creates the shared memory and semaphore pools at startup but as a rule does not touch them. That keeps it simpler and more reliable, so it can recover from a backend crash by resetting shared memory . When a backend or other child process crashes, the postmaster logs it and signals all other server processes to terminate .

Auxiliary processes

Besides one backend per connection, an instance runs a set of auxiliary processes. The glossary lists the autovacuum launcher, background writer, checkpointer, logger, startup process, WAL archiver, WAL receiver, WAL summarizer and WAL writer, and notes that autovacuum workers and WAL senders are not auxiliary processes . The PostgreSQL 18 source has a slightly different list: archiver, background writer, checkpointer, IO worker, startup, WAL receiver, WAL summarizer and WAL writer, with the logger kept separate because it has no shared memory; only one of each runs at a time, except IO workers . Auxiliary processes are not attached to any database and cannot run transactions or take heavyweight locks .

Process — What it does; Background writer — Writes dirty pages from shared memory to the file system, waking periodically to spread out I/O ; the pause between rounds defaults to 200 ms; Checkpointer — Executes checkpoints; by default at least every 5 minutes, or sooner when 1 GB of WAL has accumulated; WAL writer — Writes WAL records from shared memory to WAL files, every 200 ms by default; Autovacuum launcher and workers — The launcher starts workers, trying one in each database every autovacuum_naptime; each worker runs VACUUM and/or ANALYZE where needed . At most 3 workers run at once by default; Startup process — Replays WAL during crash recovery and on a physical replica; its name is historical; WAL archiver — When enabled, saves copies of WAL files for backups or replicas; WAL sender / receiver — A WAL sender is a special backend that streams WAL; a WAL receiver on a replica receives it for the startup process to replay; WAL summarizer — Summarizes WAL for incremental backups; Logger — When enabled, writes server events to the log file; IO workers — Asynchronous I/O uses worker processes by default: io_method defaults to worker and io_workers to 3

pg_stat_activity has one row per server process, and its backend_type column tells them apart . The documentation's list of values does not include IO workers, even though they run by default in PostgreSQL 18 .

Background workers run system- or user-supplied code; they support features such as logical replication and parallel queries, and extensions can add their own . Logical replication itself is carried out by walsender and apply processes .

Shared memory

Shared memory is RAM used in common by an instance's processes. It mirrors parts of the database files, holds WAL records temporarily and stores other common information, and it belongs to the whole instance rather than one database . Backends communicate with each other and with the rest of the instance through semaphores and shared memory .

• Shared buffers, the largest part, hold pages of data files; shared_buffers defaults to typically 128 MB .
• WAL buffers hold WAL not yet written to disk, sized by default at 1/32 of shared buffers, between 64 kB and one WAL segment .
• The lock table has room for max_locks_per_transaction (default 64) objects per process or prepared transaction .
• Cumulative statistics: each process collects statistics locally and updates the shared copy at intervals .

By default the main shared memory area uses anonymous mmap, with only a very small System V segment . One instance manages exactly one database cluster; several instances can share a machine if they use different data areas and ports . postgres can also run in single-user mode, used mainly by initdb, which does no background processing .

3. Connections and authentication

A session starts when the client opens a connection and sends a startup message naming the user, the database and the protocol version, optionally with run-time parameter settings . The server listens on TCP port 5432 by default , and listen_addresses defaults to localhost, which allows only local loopback connections .

The handshake

• Optional TLS. For SSL the client first sends an SSLRequest. The server answers with a single byte, S or N, and after the handshake (or without encryption after N) the client sends the normal startup message .
• Choosing a rule. The server uses the startup message and its configuration files, such as pg_hba.conf, to decide whether the connection is provisionally acceptable and which authentication it requires .
• Authentication. The server sends an authentication request and the client replies, for example with a password; the exchange ends with ErrorResponse or AuthenticationOk . A client that does not finish within authentication_timeout (default 1 minute) is disconnected .
• Ready. After AuthenticationOk a backend is being started. It normally sends ParameterStatus messages, BackendKeyData and finally ReadyForQuery . ReadyForQuery means the client may send commands, and it is sent again after each simple query .

The postmaster forks as soon as a request arrives and lets the child do the authentication. This keeps the authentication code single-threaded and stops a blocking SSL or PAM library from denying service to other clients .

pg_hba.conf

Client authentication is controlled by pg_hba.conf (host-based authentication) in the data directory. It is read at startup and whenever the main server process receives SIGHUP . The first record that matches the connection type, client address, database and user decides the method; later records are not tried if it fails, and if no record matches, access is denied .

Methods include trust, reject, scram-sha-256, md5, password, gss, sspi, ident, peer, ldap, radius, cert, pam, bsd and oauth. MD5 password support is deprecated .

Cancelling and closing

BackendKeyData carries secret-key data the client must keep if it wants to cancel requests later . To cancel, the client opens a new connection and sends a CancelRequest instead of a startup message; for security, the server sends no direct reply . A normal shutdown is a Terminate message, after which both sides close the connection and the backend exits .

Connection limits

max_connections defaults to typically 100 and can only be changed at server start . Some resources, including shared memory, are sized directly from it, so raising it increases those allocations . Because there is one process per connection, the operating system must allow at least that many processes , and PostgreSQL uses one semaphore per allowed connection, autovacuum worker, WAL sender and background process .

Three slots are reserved for superusers by default (superuser_reserved_connections) ; reserved_connections (default 0) can reserve more for roles with pg_use_reserved_connections, used before the superuser reserve .

Connection pooling is not covered here. The official core documentation pages this study used say nothing about it, and give no reason for process-per-connection beyond the supervisor and fork-before-authentication rationale above .

4. The path of a query

Once connected, the client sends query text. The backend parses it, creates an execution plan, runs it and returns the rows over the same connection . Inside the backend that work passes through four stages.

• Parser. Checks the text for valid syntax and builds a parse tree, or returns an error . Raw parsing uses only the fixed SQL grammar and does not look anything up in the system catalogs, so it cannot yet tell what the query means . A separate transformation step (semantic analysis) works out which tables, functions and operators are referenced and builds the query tree .
• Rewriter. Looks for rules stored in the system catalogs and applies them . Its input and output are both query trees .
• Planner/optimizer. Generates possible paths, estimates the cost of each and turns the cheapest into a plan .
• Executor. Walks the plan tree, scans relations through the storage system, sorts, joins, evaluates conditions and returns rows .

Parsing and analysis are separate because catalog lookups need a transaction. Raw parsing alone is enough to recognise transaction commands such as BEGIN and ROLLBACK .

Views are rewrite rules

Views are built on the rule system. A query against a view is rewritten to read the base tables in the view definition; a view is essentially an empty table with an ON SELECT DO INSTEAD rule .

The executor pulls rows

The executor is pull-based: each time a plan node is called it returns one more row, or reports that it has no more .

Simple and extended protocol

In the simple query protocol the client sends one Query message containing the SQL text . The extended protocol splits the work into steps:

Message — Effect — Lifetime; Parse — Carries the query text and creates a prepared statement — A named statement lasts until the end of the session; Bind — Turns a prepared statement plus parameter values into a portal — A named portal lasts until the end of the transaction; Execute — Runs the portal — —; Sync — Ends the series and gives a resynchronisation point for error recovery — —

Generic and custom plans

A prepared statement runs with either a generic plan, the same for every execution, or a custom plan built for one call's parameter values . With plan_cache_mode at its default of auto, the first five executions of a parameterised statement use custom plans; after that a generic plan is used if its estimated cost is not much higher than the average custom plan . The setting also accepts force_custom_plan and force_generic_plan, and a statement with no parameters always uses a generic plan .

A generic plan is the same for every execution, so it is not built for any particular parameter value; a custom plan is, but has to be planned on every call . The default switches to the generic plan only when its estimated cost is not much higher than the custom plans' average .

5. How the planner chooses

The planner tries to build the best execution plan. When it is computationally feasible it examines every possible plan and picks the one expected to run fastest . To compare plans it needs numbers, so it estimates a cost for each.

Cost units

Costs are in arbitrary units, by convention measured in disk page fetches: seq_page_cost is 1.0 and the other parameters are set relative to it .

Parameter — Meaning — Default; seq_page_cost — A page fetched as part of a sequential series — 1.0; random_page_cost — A page fetched out of sequence — 4.0; cpu_tuple_cost — Processing one row — 0.01; cpu_index_tuple_cost — Processing one index entry — 0.005; cpu_operator_cost — One operator or function call — 0.0025

A worked example from the documentation

The table tenk1 has 345 disk pages and 10,000 rows. Its sequential scan is estimated at (345 × 1.0) + (10,000 × 0.01) = 445 . Adding WHERE unique1 < 7000 raises the estimate to 470: all 10,000 rows are still read, and each costs an extra cpu_operator_cost for the check, 10,000 × 0.0025 = 25 .

The planner always creates a sequential scan plan for each table and, if a usable index exists, an index scan plan as well . A plain index scan fetches rows in index order, which makes each fetch expensive, so it pays off mostly when few rows are needed . The lab below puts the two side by side.

Statistics

Costs depend on how many rows each step will produce, so the planner has to estimate row counts. Total row and page counts per table and index are kept in pg_class.reltuples and pg_class.relpages . Per-column statistics live in pg_statistic, are updated by ANALYZE and VACUUM ANALYZE, and are always approximate, even right after an update ; the pg_stats view is the readable way to look at them .

default_statistics_target (default 100) caps the number of most-common values and histogram buckets kept per column, and can be raised for individual columns . Extended statistics objects created with CREATE STATISTICS only register interest; ANALYZE collects the data .

effective_cache_size (default 4 GB) is only the planner's assumption about the disk cache one query can use. It allocates no memory, and a higher value makes index scans more likely .

Joins

Method — How it works; Nested loop — The right side is scanned once for every row of the left side; simple, but slow unless the right side can use an index keyed by the left row's values; Merge join — Both sides are sorted on the join keys (by a sort or an index), then read in step so each is read once; Hash join — The right side is loaded into a hash table on its join keys; each left row probes it

The number of possible join orders grows exponentially with the number of tables , so the planner limits its search. It merges a subquery into its parent only if the resulting FROM list has no more than from_collapse_limit (8) items , and flattens explicit JOINs up to join_collapse_limit, also 8; setting it to 1 keeps the written join order . From 12 FROM items (geqo_threshold) the genetic query optimizer takes over , a probabilistic search that takes less time but may not find the best plan .

6. Indexes and scan types

Indexes let the server find specific rows much faster, but they add overhead to the whole system . Every index must be kept in sync with its table, which slows data changes and can prevent heap-only tuple (HOT) updates, so rarely used indexes should be removed .

Index types

PostgreSQL has B-tree, Hash, GiST, SP-GiST, GIN and BRIN indexes, plus the bloom extension . CREATE INDEX makes a B-tree unless another type is chosen with USING .

Type — Good for; B-tree — <, <=, =, >=, >, BETWEEN, IN, IS NULL, and LIKE patterns anchored at the start; Hash — Equality only; GiST, SP-GiST — Frameworks for many strategies; SP-GiST supports non-balanced structures such as quadtrees, k-d trees and radix trees; GIN — An inverted index for values with several components, such as arrays; BRIN — Summaries of values over ranges of consecutive physical blocks

Three ways to use an index

• Index scan. Fetches table rows in index order, so each fetch is expensive; used mostly when only a few rows are needed .
• Bitmap scan. A Bitmap Index Scan collects the locations of matching rows, then a Bitmap Heap Scan sorts them into physical order before reading the table . It can beat a sequential scan because not every page has to be visited , and bitmaps from several indexes can be ANDed and ORed together . Physical order loses the index's ordering, so ORDER BY then needs a separate sort .
• Index-only scan. Answers from the index alone, without reading the table. It needs an index type that supports it (B-tree always does) and a query that uses only indexed columns .

Why index-only scans depend on VACUUM

An index-only scan still has to respect row visibility, so it checks the visibility map: if a table page's all-visible bit is set, the row can be returned directly; otherwise the table page must be read anyway, which removes the advantage . It pays off only when a significant fraction of pages are marked all-visible , and VACUUM is what sets those bits (see the VACUUM chapter). A covering index adds non-key columns with INCLUDE so more queries qualify .

Multicolumn, partial and expression indexes

A multicolumn B-tree works best with conditions on its leading columns , although B-tree skip scan can apply later-column constraints through repeated searches when an earlier column has no equality condition . An index can have at most 32 columns, including INCLUDE columns .

A partial index covers only rows matching a predicate, such as WHERE billed is not true . An expression index indexes a computed value, and costs more to maintain because the expression is evaluated on every insert and non-HOT update .

Building an index

By default, building an index lets reads continue but blocks INSERT, UPDATE and DELETE until it finishes; CREATE INDEX CONCURRENTLY avoids that, with caveats .

7. Pages, tuples and files

Every table and index is stored as an array of fixed-size pages, 8 kB by default; another size can only be chosen when compiling the server . Each table and index lives in its own file named after its filenode number in pg_class.relfilenode , split into 1 GB segment files (filenode, filenode.1, …) as it grows .

Inside a page

• A 24-byte page header with general information, including free-space pointers .
• An array of 4-byte item identifiers (line pointers), each an (offset, length) pair pointing to an item on the page .
• Free space in the middle: new item identifiers are taken from its start and new tuples from its end, so the two grow toward each other .
• The tuples themselves, then a special space at the end used by index access methods and empty in ordinary tables .

In the header, pd_lower and pd_upper mark the start and end of the free space, and pd_lsn identifies the WAL record of the page's last change; a page is full when nothing fits between pd_lower and pd_upper . That LSN is what lets crash recovery skip changes a page already contains .

Inside a tuple

Each row version (heap tuple) has a fixed header of 23 bytes on most machines, followed by an optional null bitmap, an optional object ID and the user data . The header holds t_xmin (the inserting transaction, 4 bytes), t_xmax (the deleting transaction, 4 bytes) and t_ctid (6 bytes, pointing at this or a newer version of the row) . They are how MVCC tells which transaction created and which removed each version .

TOAST: values too big for a page

TOAST starts only when a row to be stored is wider than about 2 kB (TOAST_TUPLE_THRESHOLD) . It compresses and/or moves values out of line until the row is under TOAST_TUPLE_TARGET, also normally 2 kB . Under the default EXTENDED strategy compression is tried first, then out-of-line storage; the other strategies are PLAIN, EXTERNAL and MAIN . Out-of-line values go to a separate TOAST table in chunks of about 2,000 bytes, four to a page , and any TOAST-able value is limited to 1 GB .

Forks

A relation is more than its main data file:

Fork — Suffix — Contents; Free space map — _fsm — Free space per page, one byte per page at its bottom level; every heap and index except hash indexes; Visibility map — _vm — One bit per heap page for all-visible and one for all-frozen; Initialization — _init — Unlogged tables and indexes only

8. Shared buffers and the clock sweep

Backends do not read table files directly into their own memory. Pages are brought into shared buffers, which mirror pages of data files in memory; a page changed there is dirty until it is written back to the file system . shared_buffers defaults to 128 MB, or less if kernel settings do not allow it .

Pins and usage counts

A backend must pin a buffer (increment its reference count) before doing anything with it; an unpinned buffer can be recycled for another page at any time . Each buffer header also holds a usage count, incremented whenever the buffer is pinned, up to a small limit . That limit is BM_MAX_USAGE_COUNT = 5 .

Choosing a victim

When a backend needs a page that is not in memory, it must free a buffer. PostgreSQL uses a clock sweep: a hand, nextVictimBuffer, moves circularly through all buffers .

• Take the buffer at the hand and advance the hand .
• If the buffer is pinned or its usage count is above zero, it cannot be used: decrement the count (if nonzero) and move on .
• The first buffer with no pins and a usage count of zero becomes the victim .
• If the victim is dirty, it must be written out before it is reused , and the WAL must first be flushed up to that page's LSN .

In the source, a pinned buffer's count is not decremented, and if every buffer is pinned over a full pass the backend raises an error rather than loop forever . You can watch the hand move in the simulation in the checkpoints chapter.

Why the limit is 5

The cap is kept small on purpose: the clock hand may need up to BM_MAX_USAGE_COUNT+1 full cycles to find a free buffer, and a large value would approximate LRU but be slow . In current development code the buffer state is a single 64-bit word combining an 18-bit reference count, a 4-bit usage count, flags and lock bits, so many updates can be made with a compare-and-swap loop without locking the header .

Rings for big scans

A query that touches many pages once — a large sequential scan, or VACUUM — would otherwise flush everything useful out of the cache. Instead it reuses a small ring of buffers for the whole scan .

Operation — Ring size; Bulk read (sequential scan) — 256 kB in the buffer README; on master it starts at 256 kB and can grow with io_combine_limit and effective_io_concurrency; VACUUM — vacuum_buffer_usage_limit, default 2 MB; Bulk write (COPY IN, CREATE TABLE AS) — 16 MB, but at most 1/8 of shared buffers

The background writer

The background writer scans ahead of the clock hand, without moving it, and writes buffers that are dirty, unpinned and have zero usage count, so that backends find clean buffers . That makes it less likely a query must write a dirty page itself, at the cost of a net increase in total I/O . It runs every 200 ms by default and writes at most 100 buffers per round .

Sizing

On a dedicated server with 1 GB or more of RAM the documentation suggests 25% of memory as a starting value for shared_buffers . Because PostgreSQL also relies on the operating system's page cache, more than 40% is unlikely to help .

9. The write-ahead log

The write-ahead log (WAL) rests on one rule: changes to data files may be written only after the WAL records describing them have been flushed to permanent storage .

Because of that rule, data pages do not need to be flushed at every commit. After a crash, any change not yet in the data files is redone from the WAL — roll-forward recovery, or REDO . To guarantee a commit only the WAL has to be flushed, and since it is written sequentially, syncing it is much cheaper than flushing data pages .

Records and LSNs

A WAL record is a low-level description of one data change, with enough information to replay it after a failure . Its position is a log sequence number (LSN), a byte offset into the WAL that only ever increases . Each data page records the LSN of the latest WAL record that affected it; during replay a change is skipped if the page is already at or beyond that LSN .

WAL lives in pg_wal as segment files of 16 MB by default, divided into 8 kB pages . Records are first placed in WAL buffers in shared memory, sized by default at 1/32 of shared buffers , and the WAL writer flushes unwritten WAL every wal_writer_delay, 200 ms by default .

What COMMIT waits for

synchronous_commit is on by default . The server then waits until the transaction's WAL records are flushed before reporting success, so a reported commit survives an immediate crash .

With synchronous_commit = off the commit returns before the flush. A crash in that window loses those transactions; the window is at most three times wal_writer_delay . The database is left as if they had aborted cleanly — unlike fsync = off, which risks corruption .

Torn pages and full-page writes

An operating-system crash in the middle of writing a page can leave it torn: partly old data, partly new . WAL records describe changes, not whole pages, so replaying them onto a torn page would not help. full_page_writes (on by default) therefore writes the entire page to WAL the first time it changes after a checkpoint . The server detects that first change by checking whether the page's LSN is older than the last checkpoint's redo pointer; replay then restores the page copy .

Group commit

commit_delay (default 0) adds a short delay before a WAL flush so more transactions can commit through a single flush, at the cost of added latency .

Every reported commit costs at least one WAL flush unless you relax synchronous_commit . Asynchronous commit lets COMMIT return before that flush, at the price of a bounded window of lost transactions but without risking corruption .

10. Checkpoints and crash recovery

If the WAL grew forever, recovery after a crash would have to replay it from the beginning. Checkpoints bound that work. At a checkpoint all dirty data pages are flushed to disk and a special checkpoint record is written to WAL , by a dedicated auxiliary process, the checkpointer .

Crash recovery uses the latest checkpoint record to find the redo record, the WAL position where REDO starts; changes before that point are guaranteed to be on disk . WAL segments before the one containing the redo record are no longer needed and can be recycled or removed .

What recovery does

• Read pg_control and the checkpoint record, then scan forward from the checkpoint's redo location, performing REDO .
• Skip any record a page already contains, by comparing LSNs .
• Replaying to the end of the log yields a consistent state with no partially performed transactions .

Abort records never need flushing. After a crash, a transaction without a durable commit record is presumed aborted, so its changes are not visible . With asynchronous commit, recovery replays up to the last flushed record: the database is consistent, but transactions whose commit was not yet flushed are missing .

Try it

The simulation below has four buffers, six one-row pages and a WAL. Change a page, commit, and pull the plug before and after a checkpoint. Then turn synchronous_commit off and see which commits a crash takes with it.

When checkpoints happen

A checkpoint starts every checkpoint_timeout or when max_wal_size is about to be exceeded, whichever comes first; the defaults are 5 minutes and 1 GB . If no WAL has been written since the previous checkpoint, a new one is skipped . checkpoint_completion_target (default 0.9) spreads the writes over about 90% of the interval to keep I/O even , and min_wal_size defaults to 80 MB . After a checkpoint the WAL is flushed and the checkpoint's position is saved in pg_control .

The cost of frequent checkpoints

Shorter intervals increase WAL volume when full_page_writes is on, because each page's first change after every checkpoint logs the whole page . If checkpoints happen closer together than checkpoint_warning seconds, the server logs a suggestion to increase max_wal_size . With full-page writes on, every page changed since the checkpoint is restored to a consistent state during recovery .

11. MVCC: row versions and snapshots

PostgreSQL keeps data consistent with a multiversion model (MVCC): each SQL statement sees a snapshot of the data as it was some time ago, regardless of the current state of the underlying data . Read locks and write locks do not conflict, so reading never blocks writing and writing never blocks reading .

Versions, not overwrites

An UPDATE does not overwrite a row in place. It creates a new row version for the same logical row, and the old version is not removed immediately . Every version carries two system columns:

• xmin — the transaction ID that inserted this version ;
• xmax — the transaction that deleted or replaced it, or zero. A visible version can have a nonzero xmax if that transaction has not finished .

Because updates create new versions, a row's physical location (ctid) changes when it is updated, so it should not be used as a row identifier .

Snapshots

A snapshot records which transactions are in progress. Its text form is xmin:xmax:xip_list :

• every transaction ID below xmin has finished: committed and visible, or rolled back and dead ;
• every ID at or above xmax had not finished when the snapshot was taken, so it is invisible ;
• IDs in between are visible unless listed in xip_list, the transactions still running .

A snapshot therefore sees everything committed as of the moment it was taken, plus its own transaction's earlier commands, but not transactions shown as running or started later .

The visibility check, step by step

The heap's visibility function, HeapTupleSatisfiesMVCC, decides in two halves.

• Insert side. If the inserting transaction is still in progress according to the snapshot, the version is invisible; if it aborted, invisible; if it committed, go on to xmax. Even a committed inserter counts as in progress if the snapshot lists it .
• Delete side. Once the inserter has committed, the version is visible if xmax is empty or aborted, is only a row lock, or is still in progress per the snapshot. It is invisible only if the deleting transaction committed .

Within its own transaction, a version inserted by the current command is not yet visible to that command, and one it deleted in an earlier command is not visible .

Where commit status lives

The visibility code checks the snapshot for "in progress" before looking up a transaction's commit status . That status lives in pg_xact, which records for every transaction that was assigned an ID whether it is in progress, committed, aborted or sub-committed , at two bits per transaction .

Looking that up for every row would be slow, so the visibility routines cache what they learn as hint bits on the tuple once a transaction is known to have committed or aborted . They are flags in the tuple header such as HEAP_XMIN_COMMITTED and HEAP_XMAX_INVALID . A committed hint may only be set once the commit record is guaranteed to reach disk before the data page; otherwise it is postponed .

Row locks from many transactions

When several transactions lock a row at once, the lock information is encoded as a multixact ID, and only that ID is stored in the tuple's xmax .

The snapshot functions use the 64-bit xid8 type, which does not wrap around during an installation's life; the older txid_* functions from before PostgreSQL 13 are deprecated but still supported .

12. Isolation levels

You can ask for any of the four standard isolation levels, but PostgreSQL implements three: Read Uncommitted behaves like Read Committed .

Level — Dirty read — Nonrepeatable read — Phantom read — Serialization anomaly; Read Uncommitted — Allowed, but not in PG — Possible — Possible — Possible; Read Committed — Not possible — Possible — Possible — Possible; Repeatable Read — Not possible — Not possible — Allowed, but not in PG — Possible; Serializable — Not possible — Not possible — Not possible — Not possible

The table follows the documentation .

Read Committed (the default)

default_transaction_isolation is read committed . Each SELECT sees only data committed before that query began, plus its own transaction's earlier changes , so two SELECTs in one transaction can see different data if others commit in between .

When an UPDATE or DELETE finds that its target row was changed by a concurrent transaction, it waits. If that transaction commits, a deleted row is skipped; otherwise the WHERE clause is re-evaluated on the new version, and the operation applies to it if it still matches . The executor calls this re-check EvalPlanQual .

Repeatable Read

Repeatable Read takes one snapshot for the whole transaction, at its first statement, so successive SELECTs see the same data . If a concurrent transaction that updated or deleted the target row commits, the repeatable-read transaction is rolled back with could not serialize access due to concurrent update .

Applications using Repeatable Read or Serializable must be ready to retry. These errors always have SQLSTATE 40001, serialization_failure .

Try it in the MVCC simulation in the previous chapter: open a Read Committed and a Repeatable Read session, update the same row from a third session, and commit it while the others wait.

Serializable

Serializable is implemented with Serializable Snapshot Isolation (SSI), which builds on snapshot isolation and adds checks for serialization anomalies . It uses predicate locks (mode SIReadLock in pg_locks) that never block and cannot cause deadlocks; they only flag dependencies between concurrent serializable transactions . When no serial order is consistent with the results, one transaction is rolled back with could not serialize access due to read/write dependencies among transactions .

That is how reading never blocks writing even at the strictest level .

The simulation does not model Serializable. It shows the two snapshot rules (per statement and per transaction) and the Read Committed re-check.

13. VACUUM, freezing and HOT

MVCC leaves old row versions behind. An UPDATE or DELETE cannot remove the old version immediately, because other transactions may still see it; once none can, VACUUM reclaims its space .

VACUUM must process every table regularly to reclaim that space, update planner statistics, update the visibility map and protect against transaction ID wraparound .

• Plain VACUUM removes dead versions from tables and indexes and marks the space for reuse, but generally does not return it to the operating system . It does not take an exclusive lock, so it runs alongside normal reads and writes .
• VACUUM FULL rewrites the table into a new file with no dead space and returns the rest to the OS. It is much slower, needs room for the copy and takes an ACCESS EXCLUSIVE lock .
• VACUUM also maintains the visibility map: pages whose rows are visible to everyone can be skipped by later vacuums and by index-only scans .

Autovacuum

Autovacuum vacuums a table when the rows made obsolete since the last VACUUM exceed

`` threshold = Minimum(autovacuum_vacuum_max_threshold, autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples) ``

as the documentation defines it . The defaults are a base of 50 rows , a scale factor of 0.2 and a cap of 100,000,000 . For a table of one million rows that is min(100,000,000, 50 + 0.2 × 1,000,000) = 200,050 obsolete rows.

It also vacuums after inserts, with a base of 1,000 rows plus 20% of the table's unfrozen part , and runs ANALYZE after 50 rows plus 10% of the table have changed .

Transaction ID wraparound

Transaction IDs are 32 bits. A cluster that ran more than about 4 billion transactions would see the counter wrap to zero and past transactions appear to be in the future, so every table must be vacuumed at least once every two billion transactions . IDs are compared with modulo-2³² arithmetic: for every normal ID, two billion are "older" and two billion "newer" .

VACUUM prevents that by freezing old rows: frozen rows count as older than every normal ID, so they stay visible regardless of wraparound . Since 9.4, freezing sets a flag and keeps the original xmin; before 9.4 it replaced xmin with FrozenTransactionId .

Setting — Default — Effect; vacuum_freeze_min_age — 50 million — Cutoff age for freezing rows; vacuum_freeze_table_age — 150 million — Age at which VACUUM scans every page that may hold unfrozen IDs; autovacuum_freeze_max_age — 200 million — Age at which a VACUUM is forced, even with autovacuum off

If old IDs are still not cleared, the server warns when they are forty million transactions from wraparound and refuses new IDs when fewer than three million remain .

HOT updates

A heap-only tuple (HOT) update applies when the update changes no indexed column and the old row's page has room for the new version . Then no new index entries are needed, and intermediate versions can be removed during normal operation, even by SELECTs, without waiting for VACUUM . The index points only at the root of the chain, the versions are linked by t_ctid, and the chain must stay on one page . An update that changes no indexed column is "HOT-safe", but becomes a HOT update only if room is found on the same page .

Lowering a table's fillfactor leaves room on each page and raises the chance of HOT updates; pg_stat_all_tables shows how many updates were HOT .

Workers and pacing

The launcher tries to start one worker in each database every autovacuum_naptime (default one minute), so with N databases a worker starts every naptime/N seconds . At most 3 workers run at once by default . Truncating empty pages at the end of a table returns space to the OS but needs an ACCESS EXCLUSIVE lock .

14. Trade-offs and failure modes

Each mechanism in this study buys something and costs something. This chapter collects the costs the documentation itself describes.

MVCC costs space

• Each update adds a new row version, may need new index entries, and removing old versions and their index entries can be expensive . Without HOT, every version gets its own index entries even when no indexed column changed .
• Tables can bloat between vacuums. Under routine vacuuming a table occupies its minimum size plus whatever space is used between runs; an unexpected burst of updates can bloat it until VACUUM FULL is needed .
• Indexes bloat too: empty B-tree pages are reused, but a page with a few remaining keys stays allocated, so periodic REINDEX is recommended for some usage patterns .

Long transactions hold everything back

An open transaction prevents removal of recently-dead rows that only it may still see, so sessions left idle in transaction add to bloat . Long-running transactions, old prepared transactions and stale replication slots can also stop old transaction IDs being cleared, and must be resolved to recover from wraparound protection .

Maintenance has a price

VACUUM creates substantial I/O that can hurt other sessions; configuration parameters can reduce the impact of background vacuuming . Raising autovacuum_freeze_max_age costs disk: at the 200-million default, about 50 MB of pg_xact and about 2 GB of pg_commit_ts when commit timestamps are tracked .

Process per connection

PostgreSQL's "process per user" model spawns a backend for every connection . max_connections defaults to typically 100, and some resources, including shared memory, are sized from it . Memory per operation also multiplies: work_mem (default 4 MB) applies to each sort or hash operation, and several sessions may run such operations at once, so total use can be many times work_mem .

Whether the official documentation recommends an external connection pooler for this overhead could not be confirmed from the pages this study used .

Summary

Mechanism — What it buys — What it costs; Process per connection — A supervisor that stays out of shared memory and can reset it after a backend crash — A process, semaphore and memory per connection; WAL + checkpoints — Commits need only a sequential WAL flush — Full-page images after each checkpoint; Asynchronous commit — COMMIT returns before the WAL flush — A bounded window of lost transactions; MVCC — Readers and writers never block each other — Dead versions, bloat, VACUUM; Repeatable Read / Serializable — One snapshot per transaction ; Serializable also prevents anomalies — Serialization failures to retry; Indexes — Fast lookups — Slower writes, fewer HOT updates

Technologies

• PostgreSQL: Object-relational database system; this study describes version 18.

Sources

1. 13.1. Introduction (Concurrency Control) — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
2. 66.7. Heap-Only Tuples (HOT) — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
3. 24.2. Routine Reindexing — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
4. src/backend/access/heap/heapam_visibility.c — PostgreSQL source (master), undated (master)
5. src/include/utils/snapshot.h — PostgreSQL source (master), undated (master)
6. src/include/access/htup_details.h — PostgreSQL source (master), undated (master)
7. src/backend/access/transam/README — PostgreSQL source (master), undated (master)
8. src/backend/access/heap/README.HOT — PostgreSQL source (master), undated (master)
9. 5.6. System Columns — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
10. src/backend/executor/README — PostgreSQL source (master), undated (master)
11. 9.27. System Information Functions and Operators — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
12. 13.2. Transaction Isolation — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
13. 13.5. Serialization Failure Handling — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
14. 19.11. Client Connection Defaults — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
15. VACUUM — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
16. 1. What Is PostgreSQL? — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
17. src/backend/postmaster/postmaster.c — PostgreSQL source (REL_18_STABLE), undated (REL_18_STABLE)
18. 18.4. Managing Kernel Resources — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
19. 19.4. Resource Consumption — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
20. 19.5. Write Ahead Log — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
21. 19.13. Lock Management — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
22. 24.1. Routine Vacuuming — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
23. 19.10. Vacuuming — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
24. 19.6. Replication — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
25. 29.9. Architecture (Logical Replication) — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
26. postgres — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
27. License — PostgreSQL Global Development Group, undated
28. 19.3. Connections and Authentication — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
29. 54.2. Message Flow — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
30. 20.1. The pg_hba.conf File — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
31. Versioning Policy — PostgreSQL Global Development Group, undated
32. Appendix D. SQL Conformance — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
33. 1.2. Architectural Fundamentals — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
34. 51.2. How Connections Are Established — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
35. Appendix M. Glossary — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
36. 27.2. The Cumulative Statistics System — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
37. src/include/miscadmin.h — PostgreSQL source (REL_18_STABLE), undated (REL_18_STABLE)
38. 51.1. The Path of a Query — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
39. 14.1. Using EXPLAIN — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
40. Statistics Used by the Planner — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
41. Controlling the Planner with Explicit JOIN Clauses — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
42. Chapter 11. Indexes — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
43. Indexes: Introduction — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
44. Index Types — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
45. Multicolumn Indexes — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
46. Combining Multiple Indexes — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
47. Indexes on Expressions — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
48. Partial Indexes — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
49. The Parser Stage — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
50. Index-Only Scans and Covering Indexes — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
51. The PostgreSQL Rule System — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
52. Views and the Rule System — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
53. Planner/Optimizer — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
54. 51.6. Executor — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
55. PREPARE — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
56. 19.7. Query Planning — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
57. 66.6. Database Page Layout — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
58. 28.3. Write-Ahead Logging (WAL) — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
59. 28.6. WAL Internals — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
60. 28.5. WAL Configuration — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
61. 28.4. Asynchronous Commit — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
62. src/include/storage/bufpage.h — PostgreSQL source (master), undated (master)
63. 66.1. Database File Layout — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
64. 66.2. TOAST — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
65. 66.3. Free Space Map — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
66. 66.4. Visibility Map — PostgreSQL documentation, undated (current docs, PostgreSQL 18)
67. src/backend/storage/buffer/README — PostgreSQL source (master), undated (master)
68. src/include/storage/buf_internals.h — PostgreSQL source (master), undated (master)
69. src/backend/storage/buffer/freelist.c — PostgreSQL source (master), undated (master)