PostgreSQL Internals and Operations

Second part of PostgreSQL. That note = using Postgres. This note = how it stores data on disk and how to run it in production.

1. Storage: pages & tuples

Postgres stores all its data in a directory sensibly called /var/lib/postgresql/data

Directory	Explanation
base/	One subdirectory per database, holding the actual table and index files
global/	Cluster-wide tables like pg_database
pg_wal/	Write-Ahead Log files (section 2)
pg_xact/	Commit status of every transaction (committed / aborted)
pg_multixact/	Multitransactions → when several sessions row-lock the same row
pg_subtrans/	Subtransactions (savepoints)
pg_commit_ts/	Commit timestamps (if enabled)
pg_replslot/	Replication slots → keep WAL around for a replica (section 7)
pg_logical/	Logical decoding status (turning WAL back into row changes)
pg_snapshots/	Exported snapshots, e.g. parallel pg_dump
pg_serial/	Committed serializable transactions info
pg_twophase/	Prepared transactions for two-phase commit
pg_notify/	LISTEN/NOTIFY queue
pg_dynshmem/	Dynamic shared memory between the processes (processes, not threads)
pg_stat/, pg_stat_tmp/	Statistics files
pg_tblspc/	Links to tablespaces (put a hot index on a fast disk)
pg_hba.conf	Who can connect from where and how (section 5)
postgresql.conf	All server parameters. Human explanations at https://postgresqlco.nf/
postgresql.auto.conf	Written by ALTER SYSTEM
PG_VERSION, postmaster.opts	Major version, last start command

Source https://drew.silcock.dev/blog/how-postgres-stores-data-on-disk/

How a table is stored

• Each table and each index = its own file base/<db_oid>/<relfilenode>, split into 1GB segments.
• File is a list of 8KB pages. Postgres always reads/writes whole pages.
• Page layout: header → line pointers (grow down) → free space → tuples (grow up from the end).

 8KB page
┌──────────────────────────────────────────────┐
│ Page header (24 bytes: LSN, free space ptrs) │
├──────────────────────────────────────────────┤
│ lp1 │ lp2 │ lp3 │ ...  line pointers  ──►     │
├──────────────────────────────────────────────┤
│                                              │
│              free space                      │
│                                              │
├──────────────────────────────────────────────┤
│     ◄── tuples   │ tuple 3 │ tuple 2 │tuple 1│
└──────────────────────────────────────────────┘
 ctid (0,2) = page 0, line pointer 2 -> tuple 2

• ctid = (page, item) → physical address of a tuple. Indexes store this.
• Each tuple has a ~23 byte header: xmin, xmax, ctid (points to newer version after update), hint bits.
• TOAST → values bigger than ~2KB are compressed and/or moved to a side table. Big jsonb/text won’t bloat main pages, but reading them costs extra.
• Extra files per table: _fsm (free space map: which pages have room) and _vm (visibility map: pages where all tuples are visible to everyone → vacuum skips them, index only scan works).

SELECT pg_relation_filepath('acc'); -- -> base/16384/16390
 
CREATE EXTENSION pageinspect;
SELECT lp, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('acc', 0));
-- -> after one update you see 2 tuples: old one with t_xmax set and t_ctid pointing to (0,2)

HOT update (Heap Only Tuple) → if the updated columns are not indexed and the new version fits in the same page, Postgres skips updating every index. Leave room in pages for this with ALTER TABLE orders SET (fillfactor = 90);

2. WAL & crash recovery

In nutshell → write the change to a sequential log first, write data pages later. Same idea as WAL in LSM tree.

Write path of an UPDATE

1. Find the page in shared_buffers (load from disk if not there), modify it in memory → page is now dirty
2. Add a WAL record describing the change to WAL buffers
3. COMMIT → WAL is flushed (fsync) to pg_wal/ → client gets OK
4. Later the checkpointer / background writer writes the dirty page to the data file

sequenceDiagram
    participant C as Client
    participant B as Backend
    participant SB as shared_buffers
    participant WB as WAL buffers
    participant W as pg_wal (disk)
    participant D as Data files (disk)
    C->>B: UPDATE ...
    B->>SB: modify page (now dirty)
    B->>WB: append WAL record
    C->>B: COMMIT
    B->>W: flush WAL + fsync
    B-->>C: OK
    Note over SB,D: later: checkpointer / bgwriter
    SB->>D: write dirty page

So a commit only costs one sequential WAL write, not random writes to data files.

flowchart LR
    CK1["Checkpoint 10:00<br/>all pages up to here on disk"] --> W1[WAL records] --> W2[WAL records] --> CRASH(("Crash 10:03"))
    CRASH --> RE["Restart: replay WAL from 10:00 checkpoint"] --> OK[Committed state back]

• Crash → on restart Postgres replays WAL from the last checkpoint and gets back to the committed state.
• Checkpoint → flushes all dirty pages and marks “everything before here is on disk”. Triggered by checkpoint_timeout (5min) or max_wal_size. Too frequent → IO spikes. Rare → longer recovery.
• Full page writes → the first change to a page after a checkpoint writes the whole 8KB page to WAL (protects against half-written pages). That’s why WAL volume jumps right after each checkpoint.
• LSN → byte position in the WAL. SELECT pg_current_wal_lsn();
• synchronous_commit = off → don’t wait for the fsync. Faster, crash may lose the last few hundred ms of commits, but never corrupts.
• WAL also powers replication and point-in-time recovery (sections 6, 7).

3. Vacuum, bloat & XID wraparound

In nutshell → MVCC leaves dead tuples behind on every update/delete (6. Transactions, MVCC & locks). Vacuum makes that space reusable.

flowchart LR
    I[INSERT] --> L[Live tuple]
    L -- "UPDATE / DELETE commits" --> D[Dead tuple]
    D -- "still visible to an old snapshot" --> D
    D -- "VACUUM, no one can see it" --> F[Free space in page]
    F -- "new INSERT / UPDATE reuses it" --> L
    L -- "old enough, VACUUM freezes" --> FR[Frozen: visible to all forever]

• VACUUM → marks dead tuple space as free for new rows, updates free space map + visibility map. Doesn’t shrink the file (only trims empty pages at the end). No blocking.
• VACUUM FULL → rewrites the table, gives space back to OS, but takes ACCESS EXCLUSIVE lock = downtime. Use pg_repack instead on live systems.
• ANALYZE → refresh planner statistics.
• Autovacuum does both automatically. Triggers when dead tuples > 50 + 0.2 × rows. For a 100M row table that’s 20M dead rows before it starts → tune big tables:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

Bloat → table/index much bigger than its live data because dead space isn’t reused fast enough. Causes slower scans and more cache misses.

What stops vacuum from cleaning (it can only remove versions no one can still see):

• long running transactions / idle in transaction sessions
• abandoned replication slots
• forgotten prepared transactions
• long queries on a replica with hot_standby_feedback = on

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

Transaction ID wraparound

• Transaction IDs are 32-bit (~4 billion) and compared in a circle: ~2 billion are “past”, ~2 billion “future”.

              current XID
                  │
        past ◄────┼────► future
   (2B older txns │ 2B newer txns)
                  │
 tuple xmin = current - 2.1B  -> falls into "future" half -> row disappears
 frozen tuple                 -> always "past", safe

• If an old tuple’s xmin gets 2 billion transactions behind, it suddenly looks like it’s from the future → invisible → looks like data loss.
• Fix is freezing: vacuum marks old tuples as “visible to everyone forever”. Autovacuum forces an anti-wraparound vacuum at autovacuum_freeze_max_age (200M), even if autovacuum is disabled.
• If freezing can’t keep up (blocked by the things above), Postgres stops accepting writes to protect data → full outage. This has hit Sentry, Mailchimp and others.

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC; -- -> alert way before 1 billion

4. Configuration

Most defaults are tiny (made to run on anything). The ones that matter:

Setting	Start with	Why
shared_buffers	~25% of RAM	Postgres’ own page cache (OS cache does the rest)
effective_cache_size	~50–75% of RAM	Only a hint to the planner about total cache
work_mem	16–64MB	Memory per sort/hash node per query. 100 connections × 3 nodes × 64MB adds up
maintenance_work_mem	512MB–2GB	Vacuum, index builds
random_page_cost	1.1 on SSD	Default 4 assumes spinning disk → planner avoids indexes too much
max_wal_size	few GB+	Fewer forced checkpoints
max_connections	keep low, use a pooler	Each connection is a process
statement_timeout	per role	Stop runaway queries
idle_in_transaction_session_timeout	e.g. 60s	Kill sessions that hold locks and block vacuum
log_min_duration_statement	e.g. 500ms	Log slow queries
shared_preload_libraries	pg_stat_statements	Needs restart

ALTER SYSTEM SET work_mem = '32MB';            -- -> writes postgresql.auto.conf
SELECT pg_reload_conf();                       -- -> reload without restart
SELECT name, setting, context FROM pg_settings WHERE name = 'shared_buffers'; -- -> context=postmaster means restart needed
ALTER ROLE api SET statement_timeout = '5s';   -- -> per role

Starting point generator: https://pgtune.leopard.in.ua

5. Security, roles & RLS

• Users and groups are both roles. A role with LOGIN is a user.
• App should never connect as superuser or as the table owner (owner bypasses RLS).

Least privilege setup

CREATE ROLE app_rw NOLOGIN;
GRANT CONNECT ON DATABASE shop TO app_rw;
GRANT USAGE ON SCHEMA public TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw; -- -> tables created later too
 
CREATE ROLE api LOGIN PASSWORD '...' IN ROLE app_rw;

Built-in roles: pg_read_all_data (read only user, PG14+), pg_monitor (for monitoring tools).

pg_hba.conf → rules are checked top to bottom, first match wins. Use scram-sha-256. trust (no password) only for local dev. Turn on SSL for anything over the network.

# TYPE  DATABASE  USER  ADDRESS       METHOD
host    shop      api   10.0.0.0/16   scram-sha-256

Row Level Security (multi-tenant)

DB itself filters rows, so a missing WHERE tenant_id = ... in app code can’t leak another tenant’s data.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::bigint);
 
-- app, per request, inside the transaction
BEGIN;
SET LOCAL app.tenant_id = '42'; -- -> SET LOCAL is safe with PgBouncer transaction mode
SELECT * FROM invoices;         -- -> only tenant 42 rows
COMMIT;

6. Backup & PITR

	Logical (pg_dump)	Physical (pg_basebackup + WAL)
What	SQL / objects of one database	byte copy of the whole cluster
Restore to another major version	yes	no
Speed on big DB	slow	fast
Point in time recovery	no	yes

pg_dump -Fc -d shop -f shop.dump      # -> custom format, compressed
pg_restore -j 4 -d shop_new shop.dump # -> parallel restore, can pick single tables
pg_dumpall --globals-only > roles.sql # -> roles aren't in pg_dump

PITR (point in time recovery)

1. Take a base backup regularly
2. Continuously archive WAL files (archive_command / archive_library) to S3 etc.
3. Someone ran DELETE FROM orders at 15:00 → restore base backup, replay WAL with recovery_target_time = '2026-10-28 14:59:00'

flowchart LR
    BB["Base backup<br/>Sunday 02:00"] --> W1[WAL Mon] --> W2[WAL Tue] --> W3["WAL Wed<br/>until 14:59"] --> STOP(("Stop here"))
    STOP -.-> BAD["15:00 DELETE FROM orders<br/>(never replayed)"]

Use a tool instead of scripts: pgBackRest, WAL-G, Barman. Managed DBs (RDS, Cloud SQL) give PITR built in.

A backup you never test-restored is not a backup. Schedule restore drills.

7. Replication, HA & scaling

Streaming (physical) replication

flowchart LR
    APP[App] -- writes --> P[(Primary)]
    APP -- reads --> R1[(Replica 1)]
    APP -- reads --> R2[(Replica 2)]
    P -- "WAL stream (WAL sender)" --> R1
    P -- "WAL stream" --> R2
    P -- "archive WAL" --> S3[("S3 / backup storage")]
    SL["Replication slot per replica<br/>keeps WAL until replica confirms"] -.-> P

• Replica receives the WAL stream from the primary and replays it → exact copy of the whole cluster, read-only queries allowed.
• Async by default → primary doesn’t wait for replica. Primary dies → last commits can be lost (same problem as in Redis master-replica).
• synchronous_standby_names → commit waits for replica too. No loss, but slower writes, and a dead sync replica blocks writes.
• Replication lag → user writes, then reads from replica and doesn’t see their own write. Read your own writes from the primary.

SELECT client_addr, state, replay_lag FROM pg_stat_replication; -- -> on primary

Replication slots → primary keeps WAL until the replica has consumed it. Replica dies and slot stays → WAL piles up forever → disk full. Monitor pg_replication_slots and set max_slot_wal_keep_size.

Logical replication

Decodes WAL into row changes and sends selected tables. Works across major versions → used for near-zero downtime upgrades and CDC (Debezium → Appache Kafka).

-- on source
CREATE PUBLICATION pub_orders FOR TABLE orders;
-- on target
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=primary dbname=shop user=repl'
  PUBLICATION pub_orders;

Doesn’t replicate DDL or sequences → apply schema changes on both sides yourself.

High availability

Postgres has no built-in automatic failover. Use Patroni (leader election via etcd, like Kubernetes uses etcd) or a managed service. Hard part is fencing the old primary so you don’t get split brain (two primaries taking writes).

flowchart TB
    ETCD[("etcd: who holds the leader key")]
    subgraph N1[Node 1]
        PA1[Patroni] --> PG1[(Primary)]
    end
    subgraph N2[Node 2]
        PA2[Patroni] --> PG2[(Replica)]
    end
    subgraph N3[Node 3]
        PA3[Patroni] --> PG3[(Replica)]
    end
    PA1 <-->|renews leader key| ETCD
    PA2 <-->|watches| ETCD
    PA3 <-->|watches| ETCD
    APP[App] --> VIP["HAProxy / VIP -> current leader"] --> PG1

Primary stops renewing the key (node died) → key expires → a replica’s Patroni grabs it, promotes its Postgres, proxy points to it. Old primary demotes itself when it can’t renew.

Scaling order

Do the cheap things first:

1. Fix queries & indexes (8. Query planning & performance)
2. Bigger machine
3. Connection pooling
4. Read replicas
5. Partitioning
6. Cache hot reads in Redis
7. Sharding (Citus) last, it changes everything

Partitioning

One logical table split into child tables by a key.

flowchart TB
    Q["SELECT ... WHERE created_at >= '2026-10-15'"] --> E["events parent table, no data"]
    E -. pruned .-> P1[events_2026_08]
    E -. pruned .-> P2[events_2026_09]
    E -- scanned --> P3[events_2026_10]

CREATE TABLE events (id bigint, created_at timestamptz, payload jsonb)
  PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_10 PARTITION OF events
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

• Win: queries with created_at filter only touch matching partitions (pruning). Dropping old data = DROP TABLE events_2025_01 (instant), instead of a huge DELETE + vacuum.
• Cost: primary key / unique must include the partition key. Thousands of partitions slow down planning. Use pg_partman to create/drop partitions.

8. Monitoring & incident response

View	Tells you
pg_stat_activity	what every connection is doing right now, waits
pg_stat_statements	which queries cost the most over time
pg_stat_user_tables	seq vs index scans, dead tuples, last vacuum
pg_stat_user_indexes	unused indexes
pg_locks	who holds / waits for locks
pg_stat_replication	replica lag
pg_stat_database	cache hits, deadlocks, temp files
pg_stat_io (PG16+)	IO by backend type

-- top queries by total time
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
 
-- oldest open transactions
SELECT pid, now() - xact_start AS age, state, query
FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY age DESC LIMIT 10;
 
-- cache hit ratio, should be > 99% for OLTP
SELECT sum(blks_hit) * 100.0 / sum(blks_hit + blks_read) FROM pg_stat_database;
 
-- biggest tables including indexes and TOAST
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;
 
SELECT pg_cancel_backend(1234);    -- -> cancel the query, keep connection
SELECT pg_terminate_backend(1234); -- -> kill the connection

”DB is slow” playbook

flowchart TD
    S[DB is slow] --> A{"pg_stat_activity:<br/>many waiting on Lock?"}
    A -- yes --> A1["pg_blocking_pids -> root blocker<br/>cancel / terminate"]
    A -- no --> B{"connections near max?"}
    B -- yes --> B1["app leak or missing pooler"]
    B -- no --> C{"one query dominates<br/>pg_stat_statements?"}
    C -- yes --> C1["EXPLAIN ANALYZE -> index / stats / rewrite"]
    C -- no --> D{"CPU or IO saturated?"}
    D -- yes --> D1["top, iostat -> bigger box or cut load"]
    D -- no --> E{"disk filling?"}
    E -- yes --> E1["pg_wal size, stuck slot, archive failing, bloat"]

1. pg_stat_activity → how many active? Waiting on Lock?
2. Blocked → pg_blocking_pids(pid) → find the root blocker → cancel / terminate it
3. Connections maxed out → connection leak in app or missing pooler
4. One query dominating → pg_stat_statements + EXPLAIN ANALYZE
5. CPU / IO saturated → check the box with top, iostat (Linux)
6. Disk filling → look at pg_wal size (stuck slot or failing archive), bloat, logs
7. After the fire → add the timeout / index / alert so it doesn’t happen again

Logging worth turning on: log_min_duration_statement, log_lock_waits = on, log_autovacuum_min_duration, auto_explain for slow query plans. Dashboards with postgres_exporter + Grafana.

9. Upgrades

• Minor (17.4 → 17.5) → same data format, swap binaries and restart. Always apply them (bug + security fixes).
• Major (16 → 17), pick one:
  • pg_upgrade --link → minutes of downtime, in place
  • logical replication to a new cluster, then switch → near-zero downtime
  • dump & restore → simple, fine for small DBs
• After pg_upgrade on versions before 18 the planner stats are not carried over → run vacuumdb --all --analyze-in-stages or queries will be slow at first.
• Check extensions exist for the new version and run the app’s test suite against it first.

Resources

• https://pgrun.dev/postgres-internals
• https://drew.silcock.dev/blog/how-postgres-stores-data-on-disk/
• https://www.interdb.jp/pg/ (The Internals of PostgreSQL)
• https://www.postgresql.org/docs/current/routine-vacuuming.html
• https://www.postgresql.org/docs/current/wal-intro.html
• https://postgresqlco.nf/