- Published on
How PGSimCity Turns PostgreSQL Complexity Into a Virtual City 3D Simulation
- Authors
- Name
- DeepHorus
PGSimCity, a new open-source visualization tool by Nikolay Samokhvalov, transforms PostgreSQL 18 cluster internals into an interactive 3D city simulation that runs entirely in a web browser. The project bridges the gap between high-level SQL queries and low-level kernel execution for backend developers, site reliability engineers, and database architects. It is available through the PGSimCity Live Visualisation sandbox and requires no local dependencies.
The core abstraction maps PostgreSQL mechanics into municipal districts defined in src/world/layout.ts. Client connections arrive from the north sky into the Postmaster supervisor, which forks worker processes along backend avenue. The shared_buffers pool forms a central 1024-frame grid alongside wal_buffers, the ProcArray, lock tables, and the commit log. Beneath the city, storage excavations represent heap data as 8 KB page fields, B-trees, free space maps, and visibility maps. Write-ahead logging flows to the eastern WAL district, where walwriter and walsender threads handle replication, while checkpointer, bgwriter, and autovacuum workers occupy the western maintenance yard.
To preserve architectural fidelity, the presentation layer strictly decouples three.js rendering from core state transitions. Simulation mutations are computed in isolated TypeScript state machines using SimState, ensuring frame-rate fluctuations never desynchronize the internal model. Backend developers can trace statement lifecycles through parse, rewrite, plan, and execute stages, while SREs can trigger operational pathologies to inspect engine failure modes.
For example, setting shared_buffers to 16 MB forces clock-sweep eviction races where backends write dirty pages before reading new data. Limiting work_mem causes Sort and HashAggregate nodes to spill temporary files into base/pgsql_tmp. Long-running transactions depress the xmin horizon, starving autovacuum and causing table bloat. Heavy write bursts trigger checkpoint storms that flood pg_wal with full-page writes (FPW) across the max_wal_size threshold.
On Hacker News, the project sparked discussion around AI-assisted software architecture visualization and cognitive load. Samokhvalov noted the initial prototype was built through multi-billion-token LLM prompting before extensive manual calibration against PostgreSQL REL_18_STABLE source code. Community feedback has led to reduced UI pop-up density and inspired spin-offs such as CHSimCity for ClickHouse.
PGSimCity incorporates PGlite to execute real in-memory PostgreSQL compiled to WebAssembly directly in the browser. The roadmap outlines plans for statement-pooling visualization modes, alignment with PostgreSQL 18's dynamic io_combine_limit and effective_io_concurrency rules, expanded interactive query plan paths, and nightly mutation testing gates targeting the REL_18_STABLE branch. The full codebase and test suites are available on GitHub under the Apache-2.0 License, encouraging developers to fork, contribute, and provide feedback.
