(cache)PGSimCity · How PostgreSQL Works, in 3D

PGSIMCITY1m 28s

Source

7 FPS333 particles

Console

Defaults

What the application is asking for

Transactions / sec10tps

How hard the application hammers the database. Everything downstream scales from here.

Writes20%

Share of statements that modify data. Reads are cheap; writes create WAL, dirty pages and dead tuples.

Updates vs inserts60%

An UPDATE in Postgres writes a new row version and leaves the old one behind for vacuum.

Sequential scans15%

Reads that walk the whole table instead of using an index — watch the buffer cache churn.

How much of the database fits in RAM

shared_buffers2 GiB

Postgres's own page cache, sized here in real MiB/GiB. The plaza is a fixed 1,024-frame sample of that pool; each MiB implies 128 8 KiB buffers.

Background writeron

Trickles dirty pages out just ahead of the clock sweep so backends rarely have to write a victim themselves. There is no on/off GUC — in Postgres you disable it with bgwriter_lru_maxpages = 0, the slider below; bgwriter_delay only changes how often it wakes.

bgwriter_lru_maxpages100pages/round

Ceiling on how much the background writer may clean per round.

Durability, and what it costs

synchronous_commiton

How long COMMIT waits before telling the client yes. The single biggest latency/durability trade-off in Postgres.

wal_levelreplica

How much detail goes into the WAL. More detail means more bytes, and more things you can build on it.

full_page_writeson

The first write to a page after a checkpoint logs the entire 8 KiB page — protection against torn writes, and the reason WAL volume surges from the moment each checkpoint starts.

Getting dirty pages onto disk

max_wal_size256MiB

When WAL grows past this, a checkpoint is forced whether it was due or not.

checkpoint_timeout60s1m 00s

Maximum time between checkpoints. Longer means less write amplification but slower crash recovery.

checkpoint_completion_target0.90

Spreads the checkpoint write phase over this fraction of the interval instead of dumping it all at once.

Reclaiming dead rows

autovacuumon

Turn it off and watch dead rows pile up until the tables are mostly corpses. Never do this in production.

autovacuum_vacuum_scale_factor0.02

A table is vacuumed once this fraction of its rows are dead. Lower means more frequent, cheaper vacuums. PostgreSQL defaults to 0.2; this city starts at 0.02, the kind of per-table setting the docs recommend for a busy relation, so the yard is not idle for a whole visit.

Keeping a second copy

Standby connectedon

Whether a physical standby is streaming from this primary.

Network latency30ms

One-way network delay to the standby. synchronous_commit = on waits for a LOCAL flush and pays none of it; only remote_apply makes a commit wait for the round trip.

Slow replayoff

The standby receives WAL fine but cannot apply it fast enough — the classic source of replication lag.

Long standby queryoff

A long read on the standby reports its xmin through hot_standby_feedback and pins cleanup on the primary.

The failure modes worth recognising

Long-running transactionoff

One forgotten open transaction pins the xmin horizon, so vacuum can no longer remove row versions whose deleting transaction has not fallen behind it. Bloat forever.

Lock contentionoff

A conflicting lock on a hot table. Watch the waiters queue up and latency go vertical.

Simulation controls

Speed1.0×

Simulation speed. Slow it down to watch a single commit; speed it up to watch a day of checkpoints.

Pauseoff

Freeze the city mid-flight and fly around it.

Nothing selected

Click a building to open it up

Every structure in the city is one real mechanism inside Postgres. Open one and it explains itself, with its own live counters and the parameters that govern it.

Start here

1–8 jump between districts · T takes the guided tour

Where this comes from

The explanations lean on the PostgreSQL documentation, Bruce Momjian’s talks and slides, Hironobu Suzuki’s “The Internals of PostgreSQL”, and Egor Rogov’s “PostgreSQL 14 Internals”. With thanks — and to be clear, none of them is involved in this project or has reviewed it. Every mistake you find here is this project’s own.

Scenario

01/ 14

Guided tour

A client connects

Everything starts with a TCP connection. The postmaster has been listening since the server booted: it checks who you are, checks the database exists, and then does something no thread pool would ever do — it forks an entire new process to serve you, and steps out of the way. Watch the pulse leave the tower and land in the row of buildings ahead.

16s

City map — click a district to fly there