How PGSimCity Turns PostgreSQL Complexity Into a Virtual City 3D Simulation

Nikolay Samokhvalov unveiled PGSimCity, an open-source educational visualization tool that translates PostgreSQL cluster mechanics into an interactive 3D simulation. The web-based, dependency-free project is hosted on the PGSimCity Live Visualization platform. By bridging high-level SQL queries and low-level kernel execution, PGSimCity caters to backend developers, site reliability engineers, and database architects, unveiling the complexities of PostgreSQL 18 internals.

The core abstraction maps PostgreSQL structures to virtual municipal districts, such as the Postmaster supervisor, worker processes, shared_buffers pool, WAL district, maintenance yard, and more. Data is stored as 8 KB page fields, B-trees, Free Space Maps, and Visibility Maps beneath the city's surface. Write-Ahead Logging occurs in the east WAL district, while checkpointer, bgwriter, and autovacuum workers reside in the western maintenance yard.

The presentation layer separates three.js rendering from core state transitions, ensuring that simulation mutations calculated in isolated TypeScript state machines in src/sim/state.ts do not synchronize with frame-rate fluctuations. Developers can trace SQL statement lifecycles through parse, rewrite, plan, and execute stages, while operational pathologies can be tested to inspect engine failure modes.

Adjusting parameters like shared_buffers or work_mem triggers different simulation scenarios, such as clock-sweep eviction races or temporary file spills during Sort and HashAggregate execution. Long-running transactions starve autovacuum and induce table bloat, while heavy writes cause checkpoint storms. Hacker News users engaged with the project, discussing AI-assisted software architecture visualization and cognitive load, with Samokhvalov acknowledging the project's origins in multi-billion-token LLM prompting before meticulous manual calibration against PostgreSQL REL_18_STABLE source code.

Community suggestions have led to UI improvements and spin-offs like CHSimCity for ClickHouse. PGSimCity integrates PGlite, executing real, in-memory PostgreSQL compiled to WebAssembly within the browser's client thread. The project's roadmap includes introducing statement-pooling visualization modes, aligning buffer-frame ring-sizing with PostgreSQL 18's rules, expanding interactive query plan paths, and implementing nightly mutation testing gates.

The complete codebase, documentation, and test suites are available on the PGSimCity GitHub repository under the Apache-2.0 License, inviting developers to provide feedback and contribute.