Engine Architecture & Storage Internals

A checkpoint flushes modified in-memory pages to disk and records a position in the transaction log. Without it, crash recovery would have to replay the log from the beginning, and the log could never be truncated, so it would grow forever.

TCP connect, a protocol handshake naming the database and user, an optional TLS upgrade, then authentication. The server allocates a session (a backend process or thread with its own memory), loads role metadata, and signals it is ready for queries.

Opening a connection costs a network handshake, authentication, and server-side setup - milliseconds plus memory - and the server can only hold a limited number. A pool keeps a bounded set of authenticated connections open, lends one to each unit of work, takes it back afterwards, and makes callers wait when all are busy.

The transaction log decides. A transaction whose commit record reached durable storage before the crash is kept, and reapplied if needed. Every transaction without a durable commit record is rolled back completely. So an acknowledged commit survives; in-flight work disappears atomically, never half-applied.

Storage devices and the operating system move data in blocks, and per-request overhead dominates, so reading one row would cost about the same as reading a whole page. Fixed size also makes addressing trivial: page number times page size gives the byte offset. Consequences: caching, logging and locking all work in page units, and reading one row pulls in its neighbours.

A row store keeps all of a row's columns together, so reading or writing one whole row touches one place. A column store keeps each column's values contiguous, so scanning a few columns over many rows reads far less data. Rows suit point reads and writes; columns suit wide scans and aggregates.

The catalog is the database describing itself: tables that list tables, columns, indexes, constraints, views, roles and statistics. Keeping metadata in ordinary tables lets the engine reuse its own storage, transactions and permissions, so you read metadata with a plain SELECT.

Under MVCC an UPDATE or DELETE does not overwrite data; it marks the old version dead so transactions with older snapshots can still read it. The space is only reclaimable once no running transaction can see that version, and a background cleanup process (vacuum or undo purge) reclaims it later.

Only the transaction log records for that transaction must be durable at commit. The modified data pages stay dirty in the buffer pool and are written later by the background writer or the checkpointer, so commit costs one sequential log flush rather than scattered page writes.

Plain LRU is accurate but needs a global list updated on every hit, which contends. Clock-sweep approximates it with a usage counter and a rotating pointer, so hits are lock-free. Midpoint insertion admits new pages into the middle of the list so a scan cannot flush the hot end.

Size it to the database's real concurrency, not to the request rate. Useful parallelism is bounded by CPU cores and storage, so a small pool (often around twice the cores plus an allowance for I/O waits) maximises throughput; larger pools add contention and context switching, so queries slow down while queueing simply moves into the database.

Every data page stores the log sequence number of the last change applied to it. Before replaying a record, recovery compares the record's LSN with the page's stored LSN; if the page is already at or beyond it, the change is skipped. That makes replay idempotent, which matters because many logged changes are not repeatable operations.

Three passes. Analysis reads forward from the last checkpoint to rebuild the list of in-flight transactions and dirty pages and to find where redo must start. Redo replays every logged change, committed or not, until the data files match the log. Undo then rolls back the transactions that never committed.

A page has a header at the front, an array of slots growing forward, and row data filling from the back. Free space is the gap in the middle. Each slot holds the offset and length of one row. Rows are addressed by slot number, not byte offset, so the engine can move rows within the page to compact free space without invalidating any external reference.

Shared memory - mainly the buffer cache - is allocated once for the whole instance and used by everyone. Per-operation working memory is granted separately to each sort, hash join or hash aggregate in each concurrent query, so the same setting can be multiplied by operators per query times concurrent queries, and a value that looks safe alone can exhaust RAM under load.

The engine computes a horizon: the oldest snapshot any active transaction could use. A version is reclaimable only if it was expired by a committed transaction older than that horizon, so nobody could still see it. One old transaction holds the horizon back and blocks cleanup for the whole database.

The log record describing a change must reach stable storage before the changed data page does, and before a commit is acknowledged. One cheap sequential flush makes a crash recoverable: replay the log to rebuild lost page writes.

Redo information lets the engine re-apply a committed change whose data page never reached disk. Undo information lets it reverse a change made by a transaction that aborted or was still open at crash time. Redo moves forward, undo backward.

Each connection costs a server-side process or thread, several megabytes of private memory, plus per-query workspace that multiplies under load. At the cap, new connections are rejected outright. Set it just above what your pools can actually use, since real throughput is bounded by cores and disk, not by connection count.

Connections are being held rather than used. Look for leaked connections never returned, transactions left open across slow non-database work, long or unindexed queries, and lock waits. Fix the holder; enlarging the pool only multiplies held connections and pushes contention into the database.

Both pin the cleanup horizon, so no version expired since they started can be reclaimed anywhere. Undo segments grow, tables and indexes bloat, version chains lengthen and reads slow down. Diagnose by finding the oldest transaction and its state; prevent with idle-in-transaction and statement timeouts, chunked batches, and monitoring oldest-transaction age.

Typically: a log/WAL writer that flushes the transaction log, a background writer that trickles dirty data pages out of the buffer pool, a checkpointer that periodically writes all dirty pages to bound recovery time, statistics collection and auto-analyze, and a vacuum/purge daemon reclaiming dead row versions.

The background writer keeps clean, reusable buffers available so a query needing a free buffer does not have to flush one itself — it targets buffer availability. The checkpointer flushes everything dirty as of a point in time to bound crash-recovery work and let old log be recycled.

A sharp checkpoint quiesces the system so the flushed image is transaction-consistent. A fuzzy checkpoint lets writes continue while pages are flushed, so the image is inconsistent, and records enough state - such as the oldest dirty page's log position and the active-transaction list - for recovery to fix it up.

A page is larger than the sector the device writes atomically, so a crash mid-write can leave a page half old, half new - internally corrupt and unusable by redo. Engines fix it by first writing a whole clean copy of the page: a full-page image in the log, or a doublewrite area, from which recovery restores it.

Process per connection gives strong isolation but the highest per-session cost, so connection counts must stay low. Thread per connection is cheaper but shares one address space, so a crash or leak is riskier. Worker pools multiplex many sessions onto few threads, scaling to many connections at the cost of complexity and restrictions on session-bound work.

A session holds its transaction, session parameters (timezone, isolation level, search path, autocommit), temporary tables, prepared statements and cursors, session-level locks, sequence state, and the authenticated identity. If a connection is reused without resetting that state, the next user of the connection inherits it and behaves unpredictably.

The value is moved out of the row into overflow storage: chopped into chunks held in a separate structure, often compressed first, with only a short pointer left inline. The main row therefore stays small, so scans and queries that do not select the column never read the large data at all; touching it costs extra I/O.

The row header holds bookkeeping the engine needs: version or visibility information, a NULL bitmap with one bit per column, flags such as whether a value is stored off-page, and often a column count. The physical identifier is just the row's current address; updates, page compaction and table reorganisation move rows, so it changes and can even be reused later by a different row.