How PostgreSQL Works - Visualized as a City

PGSimCity

Nikolay Samokhvalov

2024

02

What is PGSimCity?

An interactive browser-based simulation that visualizes PostgreSQL internals using a city metaphor

Database as a Living City

PGSimCity transforms complex PostgreSQL architecture into an intuitive visual experience. Instead of abstract diagrams, imagine a bustling city where each building and vehicle represents a database component working together.

PostgreSQL internals made visual and interactive

PostgreSQL Components as City Elements

account_balance

Postmaster

The City Hall - accepts connections and spawns backend processes

memory

Shared Buffers

Public buses carrying data between processes

hub

Background Workers

City workers maintaining operations

bolt

WAL Writer

Record keeper ensuring data durability

visibility

Checkpointer

Creates snapshots for recovery

sync

Autovacuum

Cleanup crew reclaiming space

Query Execution Flow

1

Client Connects

Postmaster accepts connection and creates backend process

→

2

Query Received

Backend parses, plans, and begins execution

→

3

Data Access

Shared buffers checked, disk read if needed

→

4

WAL Logged

Changes written to Write-Ahead Log

→

5

Result Returned

Data sent back to client application

542

Hacker News Points

Demonstrating strong interest in database education tools

What You Can Learn

01

Process Architecture Understand how PostgreSQL's multi-process model works
02

Memory Management Visualize shared buffers and memory allocation
03

Concurrency Model See how MVCC handles concurrent transactions
04

Background Processes Learn about autovacuum, checkpointer, and WAL writer
05

Real-time Dynamics Watch database behavior under different workloads

Explore PGSimCity

Visualize. Learn. Understand PostgreSQL.

https://nikolays.github.io/PGSimCity/