Nikolay Samokhvalov has unveiled PGSimCity, an innovative open-source educational visualization tool that transforms the intricate mechanics of PostgreSQL clusters into an engaging 3D spatial simulation. This tool operates entirely within a web browser, eliminating the need for local dependencies, and can be accessed through the PGSimCity Live Visualization sandbox. It effectively bridges the conceptual divide between high-level SQL queries and low-level kernel execution, catering to backend developers, site reliability engineers, and database architects alike.
Core Abstraction and Architecture
The fundamental abstraction of PGSimCity reinterprets the internals of PostgreSQL 18 into distinct municipal districts, as outlined in src/world/layout.ts. Client connections make their entrance from the northern sky into the Postmaster supervisor, which orchestrates the forking of worker processes along the backend avenue. At the heart of this simulation lies the shared_buffers pool, represented as a central 1024-frame grid, alongside other crucial components such as wal_buffers, the ProcArray, lock tables, and the Commit Log (CLOG). Below the city structure, storage excavations reveal data heaps organized as 8 KB page fields, B-trees, Free Space Maps (FSM), and Visibility Maps (VM). The Write-Ahead Logging is directed to the eastern WAL district, where walwriter and walsender threads disseminate replication streams, while the western maintenance yard accommodates checkpointer, bgwriter, and autovacuum workers.
Decoupled Presentation Layer
To ensure architectural integrity, the presentation layer of PGSimCity maintains a strict separation between three.js rendering and core state transitions. Simulation changes are computed within isolated TypeScript state machines located at src/sim/state.ts, utilizing SimState to guarantee that fluctuations in frame rates do not disrupt the internal state.
Operational Insights and Simulations
Backend developers can meticulously trace statement lifecycles through the stages of parse, rewrite, plan, and execute. Meanwhile, principal database engineers and site reliability engineers can provoke operational anomalies to scrutinize engine failure modes. For instance, setting shared_buffers to 16 MB induces clock-sweep eviction races, compelling backends to write dirty victim pages before they can read new data. Simulating restricted work_mem leads to Sort and HashAggregate execution nodes spilling temporary files into base/pgsql_tmp.
Long-running transactions can depress the xmin horizon, resulting in autovacuum starvation and subsequent table bloat, while significant write bursts may trigger checkpoint storms that inundate pg_wal with full-page writes (FPW), surpassing the max_wal_size / (1 + checkpoint_completion_target) threshold.
Community Engagement and Future Directions
The project has ignited vibrant discussions on Hacker News, particularly regarding AI-assisted software architecture visualization and cognitive load. Samokhvalov shared that the initial prototype was crafted using multi-billion-token LLM prompting, followed by extensive manual calibration against the PostgreSQL REL_18_STABLE source code. Feedback from the community has been instrumental in efforts to reduce UI pop-up density and has inspired spin-offs such as CHSimCity for ClickHouse.
Furthermore, PGSimCity integrates PGlite, enabling the execution of real, in-memory PostgreSQL compiled to WebAssembly directly within the browser’s client thread. Looking ahead, the project’s ROADMAP.md outlines several key technical milestones, including the introduction of statement-pooling visualization modes, alignment of the buffer-frame ring-sizing model with PostgreSQL 18’s dynamic io_combine_limit and effective_io_concurrency rules, expansion of interactive query plan paths, and implementation of nightly mutation testing gates to fortify the deterministic verification engine against the upstream REL_18_STABLE branch. The complete codebase, documentation, and operational test suites are available on the PGSimCity GitHub repository, licensed under the Apache-2.0 License, inviting developers to provide feedback, fork, and contribute.
