Ashwani Kumar Yadav Get in touch
← All posts

PostgreSQL Internals Worth Knowing

A developer’s overview of MVCC, WAL, vacuum, indexes, locks, and replication.

PostgreSQL internals matter when diagnosing concurrency, locking, storage, and query performance.

MVCC lets transactions work with different row versions. An update typically produces a new version, leaving an older version that vacuum can eventually reclaim for reuse. The visibility map helps vacuum and can allow an index-only scan to skip heap accesses for all-visible pages. HOT updates can avoid new index entries under the right conditions.

Write-ahead logging supports crash recovery. Log sequence numbers identify positions in WAL, while logical replication can publish selected table changes to subscribers. TOAST manages oversized values. Bitmap heap scans combine index results before visiting table pages, and advisory locks let applications coordinate work with locks they manage explicitly.