--- title: "10 — Postgres Internals: Memory, Buffers, and Vacuum" uid: internals-architecture tags: ["buffer-pool", "vacuum", "roadmap:postgresql-dba", "locks", "postgresql", "checkpoints", "internals", "storage", "memory"] excerpt: "One connection is one OS process; the shared buffer pool is the hot cache everyone reads through; and VACUUM exists because MVCC leaves dead row versions behind." date: 2026-08-13T03:27:51+0000 source: https://www.aveshina.my.id/en/blog/internals-architecture --- "Postgres magic" was my label for the internals, and it held until I needed to tune something. The model that organized them: **one connection is one OS process; every process shares a single hot cache (the shared buffer pool) for data pages and a separate WAL buffer for log records; and VACUUM exists specifically because MVCC never overwrites rows, so dead versions accumulate and must be reclaimed.** [1][2] Once those three pieces clicked, configuration, vacuuming, and lock waits stopped being mysterious and became consequences of the architecture. ## Process-per-connection Unlike databases with a threaded model, Postgres spawns a **backend process per connection** [1]. When a client connects, the postmaster (the supervisor) forks a backend that handles all SQL for that connection until it disconnects. That process has its own private memory for sorting, hashing, and query execution, and it shares memory with every other backend for the buffer pool and WAL. ```figure ``` The implication that mattered for me: every connection costs an OS process with its own memory, so connection count is a real resource. That's why **connection poolers** like PgBouncer exist — to multiplex many application connections onto a small set of backend processes. ## The shared buffer pool The **shared buffer pool** is the central cache — a fixed-size region of shared memory (sized by shared_buffers, typically 25% of RAM) holding 8KB data pages [3]. When a backend needs a page, it reads from the buffer pool first; only on a miss does it go to disk. Pages that are modified in memory become **dirty** and are written back to disk later, by the background writer and the checkpointer — not by the transaction that changed them. This is why shared_buffers is the single most impactful tuning knob. ## Checkpoints and the background writer A **checkpoint** is the act of flushing all dirty buffer pages to disk and recording a checkpoint record in the WAL [4]. On crash recovery, Postgres replays the WAL *from the last checkpoint* forward — anything before the checkpoint is already safely on disk. The checkpointer process runs periodically (checkpoint_timeout) or when WAL volume crosses max_wal_size. The **background writer** is a separate process that trickles dirty pages out continuously, smoothing I/O so checkpoints have less to do at once. Tuning checkpoint_completion_target spreads checkpoint writes to avoid I/O spikes. ## WAL and the planner, briefly Every modification appends a record to the **WAL buffers**, which is flushed to pg_wal/ on disk at commit (giving durability). The same WAL stream is what streaming replication ships to standbys. The **query planner** then decides, per query, the cheapest way to execute — which index, which join order, sequential scan vs. index scan — based on table statistics gathered by ANALYZE [5]. Stale statistics produce bad plans, which is why ANALYZE (run automatically by autovacuum) matters as much as reclaiming dead rows. ## Vacuum: the price of MVCC Because MVCC writes new row versions rather than overwriting, every UPDATE and DELETE leaves a **dead row** behind [2][6]. Dead rows accumulate as bloat — they consume disk and they slow scans, because Postgres has to skip over them. VACUUM reclaims them, marking the space reusable for future inserts. VACUUM ANALYZE also refreshes planner statistics. VACUUM FULL rewrites the table to physically shrink it, but takes an exclusive lock — use it rarely. The practical mechanism is **autovacuum**, a background process that runs VACUUM and ANALYZE automatically based on thresholds (a table is vacuumed when a percentage of rows changed) [6]. The failure mode I had to learn: if autovacuum falls behind (long-running transactions block it, or the thresholds are wrong), bloat grows, indexes degrade, and performance craters. The fix is tuning autovacuum thresholds up or running manual vacuums on the worst tables, and identifying the long transactions blocking reclamation. ## Locks: the currency of concurrency Locks coordinate concurrent access [7]. The broad categories: - **Row-level locks** — acquired by UPDATE/DELETE/SELECT ... FOR UPDATE on specific rows. Relatively cheap; MVCC means readers don't take them. - **Table-level locks** — ACCESS EXCLUSIVE (blocks everything, taken by DROP/TRUNCATE/ALTER), SHARE UPDATE EXCLUSIVE (taken by VACUUM), down to ACCESS SHARE (taken by SELECT). - **Advisory locks** — application-defined locks for coordination that isn't tied to a specific row. The classic pain is a long-running transaction holding ACCESS EXCLUSIVE on a table while every other query waits. pg_locks and pg_stat_activity show who holds what; the fix is killing the blocker or, better, avoiding it with shorter transactions and online-schema-change patterns. ## Physical storage: where it lives on disk Underneath it all, $PGDATA has a known layout [8]: - base/ — per-database files (each table and index is a file, segmented at 1GB). - global/ — cluster-wide tables (roles, databases). - pg_wal/ — the WAL files. - pg_tblspc/ — symlinks to tablespaces on other volumes. - postgresql.conf, pg_hba.conf, pg_ident.conf — config. Pages are 8KB; large values use **TOAST** storage (oversized attributes are moved to a side table). I rarely touch this layer directly, but knowing it makes the size and performance conversation concrete — a "bloated" table is one whose files have grown with dead rows VACUUM hasn't reclaimed. ## How I use this Three habits fall out. First, I keep connection count low and put PgBouncer in front for anything with many clients — the process-per-connection model punishes uncontrolled connection growth. Second, I monitor bloat and autovacuum health: a table whose dead-row ratio climbs is a table I vacuum manually and whose autovacuum thresholds I tune. Third, when a query is slow, I check the planner via EXPLAIN first (is the plan reasonable?) and the statistics second (is ANALYZE fresh?) before blaming indexes or memory. The internals aren't magic; they're a small set of mechanisms that explain everything else. ## References [1] T. Austen, "Understanding the process and memory architecture of PostgreSQL," dev.to, 2023. [Online]. Available: [https://dev.to/titoausten/understanding-the-process-and-memory-architecture-of-postgresql-5hhp](https://dev.to/titoausten/understanding-the-process-and-memory-architecture-of-postgresql-5hhp) [2] PostgreSQL Global Development Group, "Routine Vacuuming," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/routine-vacuuming.html](https://www.postgresql.org/docs/current/routine-vacuuming.html) [3] PostgreSQL Global Development Group, "pg_buffercache," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/pgbuffercache.html](https://www.postgresql.org/docs/current/pgbuffercache.html) [4] Cybertec, "What is a checkpoint?," 2024. [Online]. Available: [https://www.cybertec-postgresql.com/en/postgresql-what-is-a-checkpoint/](https://www.cybertec-postgresql.com/en/postgresql-what-is-a-checkpoint/) [5] PostgreSQL Global Development Group, "Planner/Optimizer," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/planner-optimizer.html](https://www.postgresql.org/docs/current/planner-optimizer.html) [6] EnterpriseDB, "PostgreSQL VACUUM and ANALYZE best practice tips," 2024. [Online]. Available: [https://www.enterprisedb.com/blog/postgresql-vacuum-and-analyze-best-practice-tips](https://www.enterprisedb.com/blog/postgresql-vacuum-and-analyze-best-practice-tips) [7] S. Sisodiya, "Understanding Postgres locks and managing concurrent transactions," Medium, 2023. [Online]. Available: [https://medium.com/@sonishubham65/understanding-postgres-locks-and-managing-concurrent-transactions-1ededce53d59](https://medium.com/@sonishubham65/understanding-postgres-locks-and-managing-concurrent-transactions-1ededce53d59) [8] PostgreSQL Global Development Group, "Database Physical Storage," 2024. [Online]. Available: [https://www.postgresql.org/docs/current/storage-toast.html](https://www.postgresql.org/docs/current/storage-toast.html) ```quiz Q: In PostgreSQL, how many OS processes back a single connection? - One shared thread pool handles all connections - One backend process per connection, forked by the postmaster correct: 1 explain: Postgres uses a process-per-connection model. Each connection is a separate OS process with private memory, plus shared memory for buffers. This is why connection count is a real resource and why poolers like PgBouncer exist. Q: What is the shared buffer pool? - A per-connection cache for sort results - A fixed-size shared-memory region of 8KB data pages that all backends read through before going to disk correct: 1 explain: shared_buffers is the central hot cache. Backends look there first; a miss goes to disk. Dirty pages are flushed later by the background writer and checkpointer. Q: Why does VACUUM exist? - To compact the WAL - Because MVCC leaves dead row versions after UPDATE/DELETE, and VACUUM reclaims that space and refreshes planner statistics correct: 1 explain: MVCC never overwrites rows, so old versions accumulate as bloat. VACUUM marks their space reusable; ANALYZE refreshes statistics the planner depends on. Q: On crash recovery, where does PostgreSQL start replaying the WAL? - From the very first WAL record - From the last checkpoint; earlier changes are already safely on disk correct: 1 explain: A checkpoint flushes all dirty pages and records a position. Recovery replays the WAL forward from that checkpoint, redoing committed changes and undoing uncommitted ones. Q: What is the most likely cause when many queries suddenly wait on a single table? - The shared buffer pool is too small - A transaction is holding an ACCESS EXCLUSIVE lock (e.g., from an ALTER or long DDL), blocking all other access correct: 1 explain: ACCESS EXCLUSIVE (taken by DDL like ALTER/DROP/TRUNCATE) conflicts with every other lock mode. A long DDL or an uncommitted transaction holding it stalls all queries on that table until it finishes. ```