PostgreSQL is running under the hood of half of our self-hosted stack — Immich, Nextcloud, Paperless, Gitea, a bunch of dashboards and bots store data there. But what actually happens inside when you perform an INSERT is known to few. The project PGSimCity, which stayed at the top of Hacker News for two weeks (~930 points), explains PostgreSQL internals in an unconventional way — as an interactive 3D city you can explore. A great reason to understand what’s really going on there.
What it is
PGSimCity is an independent non-profit educational visualization of PostgreSQL’s internal architecture (modeling version 18.4). Instead of a wall of documentation, you’re shown a 3D city (a browser with WebGL2 required), where different “districts” are the DBMS subsystems:
- WAL (Write-Ahead Log) — the write-ahead log;
- storage — how data sits on disk;
- standbys / recovery / continuity — replicas, recovery, fault tolerance;
- maintenance — maintenance (autovacuum and more).
A separate feature is the “Machine room,” where beside the model a real PostgreSQL runs so you can map the abstraction to real behavior.
Why you should understand these internals
This is not idle curiosity — three things from the “city” directly affect your self-hosted service:
- WAL. Every change is first written to the log, and only then to the main files. From this arise two practical topics: how WAL writes are configured, which determines resilience to power loss; and WAL itself is the basis for replication and point-in-time recovery. If you back up Immich/Nextcloud “live” without understanding WAL — you risk getting an inconsistent dump.
- Autovacuum. PostgreSQL, due to MVCC, does not delete old row versions immediately — autovacuum cleans them up. Ignore it — you get bloating, a bloated database, and slowdowns. Classic pitfalls on long-running self-hosted installations.
- Checkpoints and storage. Understanding when dirty pages are flushed to disk explains periodic I/O spikes, which is why Nextcloud might stall on a weak NAS.
A sober assessment
Let’s set expectations honestly.
- This is an early prototype. The authors explicitly label the project as an “early, reviewed prototype” and invite contributions to the model and explanations. So this is an educational illustration, not the final truth or a replacement for documentation.
- Model ≠ reality. The 3D city is a metaphor; details are simplified. For production tuning you still need official PostgreSQL docs,
EXPLAIN,pg_stat_*, and good sense. - WebGL2 needed. On a weak machine or in a spartan browser the 3D can be slow — this is an interactive visualization, not text.
- You won’t take away concrete “do this” steps from it — it’s about understanding the model; take concrete commands from the docs.
What to do
- Stroll through the “city” and map the districts to what runs on your setup: WAL ↔ your backups, maintenance ↔ autovacuum, storage ↔ the disk on your server.
- Check autovacuum on your installations:
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;— if there are many dead rows, it’s time to tune the settings. - Revisit your backup strategy with WAL in mind: for production databases —
pg_dumpon a consistent snapshot or a physical backup + WAL archiving, rather than live file copying. - Use it as onboarding — it’s a great way to explain to a junior or to yourself what WAL and vacuum are, in 20 minutes instead of a manual chapter.
Sources
- PGSimCity — Interactive 3D visualization of PostgreSQL internals
- Discussion on Hacker News (~930 points)
Have you dug into the internals of your DB or is it a “black box that just works” for you? Have you encountered bloat or slowdowns due to autovacuum on self-hosted services — how did you fix it?