How PostgreSQL Works: An Interactive Guide Using PGSimCity

PostgreSQL is the database engine of choice for startups, enterprises, and indie hackers alike. It's powerful, extensible, and standards-compliant. But for many developers, Postgres remains a black box. You write queries, and you trust the planner to make things fast. You configure shared_buffers, and you hope for the best.

If you've ever asked yourself how PostgreSQL works under the hood, or wanted to visualize what happens when your application sends a query to the database, there is a tool that changes everything: PGSimCity.

In this article, we'll demystify PostgreSQL internals using PGSimCity, explore the architecture that makes Postgres reliable, and learn how this knowledge can help you write better code and tune your database.

What is PGSimCity?

PGSimCity is an interactive, web-based simulation of PostgreSQL internals. It visualizes the complex interactions between the query processor, buffer manager, write-ahead log (WAL), and background processes as a living city.

Instead of reading dry documentation, you can watch how data flows through the system in real-time. It was created by Nikolay Samokhvalov and serves as an incredible educational resource for anyone looking to understand postgresql internals beyond the SQL layer.

Pro Tip: If you're learning these concepts, consider documenting your findings and configurations. Many developers use try Notion for free to build a personal wiki for database tuning notes, query patterns, and performance benchmarks.

The Architecture: How PostgreSQL Works

To understand PGSimCity, you need to grasp the core components it visualizes. PostgreSQL uses a shared-nothing, multi-process architecture centered around shared memory.

The Server Process and Background Workers

When a client connects, Postgres spawns a server process. However, the heavy lifting is often done by background workers:

• Buffer Manager: Manages the pool of memory pages (shared_buffers).
• Background Writer: Flushes dirty pages to disk to prevent write spikes.
• Checkpointer: Ensures data is durable by flushing buffers and writing WAL records.
• WAL Writer: Handles Write-Ahead Log operations.
• Autovacuum Launcher: Manages table maintenance to keep performance optimal.

PGSimCity models these as distinct entities in the "city," showing you exactly when and why they trigger.

Visualizing the Query Lifecycle

The most common entry point for understanding how PostgreSQL works is the lifecycle of a single query. Let's trace what happens when you run a SELECT.

1. Parsing and Planning

When a query arrives, the parser checks syntax and generates an abstract syntax tree. The planner then estimates the cost of various execution strategies (sequential scan vs. index scan) and picks the cheapest plan.

You can see this in your own database using EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = '[e-mail]';

The BUFFERS option reveals how many buffer hits occurred, giving you insight into the caching layer that PGSimCity visualizes so well.

2. Execution and the Buffer Cache

Postgres rarely reads directly from disk for every query. It maintains a Buffer Cache in shared memory.

• Buffer Hit: The page is already in memory. This is fast.
• Buffer Miss: The page must be read from disk. This is slow.

PGSimCity shows you the flow of pages moving between disk and memory. This visualization helps you understand why tuning shared_buffers matters, but also why the OS file system cache plays a huge role.

3. WAL and Durability

Durability is one of the ACID properties that makes Postgres trustworthy. The Write-Ahead Log (WAL) guarantees that committed transactions are never lost.

Before data is written to the actual data files, the change is recorded in the WAL. If the system crashes, Postgres replays the WAL on startup to recover.

In PGSimCity, you can watch the WAL grow and see how checkpoints interact with it. This is crucial for understanding recovery time objectives (RTO) and backup strategies.

PostgreSQL Internals: What PGSimCity Reveals

Let's dive into specific components that PGSimCity makes intuitive.

Checkpoints: Balancing Act

Checkpoints are moments when Postgres flushes all dirty buffers to disk and advances the WAL position.

If checkpoints happen too frequently, they cause I/O spikes. If they're too rare, recovery takes longer. PGSimCity visualizes this balance, helping you tune checkpoint_timeout and max_wal_size based on your workload.

Autovacuum: The Unsung Hero

One of the most misunderstood parts of how PostgreSQL works is Autovacuum.

Postgres uses a MVCC (Multi-Version Concurrency Control) model. When you update or delete a row, the old version isn't immediately removed. These "dead tuples" accumulate and bloat tables, slowing down queries.

Autovacuum cleans up dead tuples and updates statistics for the planner. If Autovacuum falls behind, your database can degrade significantly.

PGSimCity shows you the vacuum process in action, highlighting when tables need attention. This is invaluable for diagnosing why a query suddenly got slower.

-- Check for tables needing vacuum
SELECT schemaname, relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Practical Applications: From Visualization to Optimization

Understanding these internals isn't just academic. Here's how PGSimCity's lessons apply to your daily work.

1. Debugging Performance Issues

When a query is slow, ask yourself:

• Is it a buffer miss? (Cold cache or missing index)
• Is the planner making a bad choice? (Stale statistics, need ANALYZE)
• Is there lock contention? (Check pg_stat_activity)

PGSimCity helps you build the mental model to diagnose these issues faster.

2. Tuning Configuration

With PGSimCity, you can visualize the impact of configuration changes:

• work_mem: Affects sort and hash operations. Too low, and Postgres spills to disk.
• effective_cache_size: Tells the planner how much memory is available for caching. This influences scan choices.
• random_page_cost: Adjust this if you're on SSDs to encourage index usage.

3. Version Control Your Configs

As you experiment with these settings, you'll want to track changes. I recommend using GitHub to version control your Postgres configuration scripts, SQL optimization templates, and monitoring dashboards. It makes collaboration easier and ensures you can roll back if a change causes issues.

# Example: Initializing a repo for DB tuning
mkdir postgres-tuning
cd postgres-tuning
git init
echo "# PostgreSQL Tuning Notes" > README.md
git add README.md
git commit -m "Initial commit"

Getting Started with PGSimCity

Ready to explore? You can access PGSimCity directly through the web interface. It requires no installation and runs in your browser.

For a deeper dive, the source code is open source. You can clone the repository and even contribute to the simulation.

git clone https://github.com/nikolays/PGSimCity.git
cd PGSimCity
# Follow the project instructions to run locally

Conclusion

Learning how PostgreSQL works is one of the highest-leverage skills a backend developer can acquire. It transforms you from someone who writes SQL into an engineer who understands the engine executing that SQL.

Tools like PGSimCity bridge the gap between theory and practice. By visualizing the buffer cache, WAL, checkpoints, and autovacuum, you gain intuition that will help you:

• Write more efficient queries.
• Tune Postgres for your specific workload.
• Diagnose performance issues with confidence.
• Make better architectural decisions about data storage.

Whether you're a Postgres beginner or a seasoned DBA, PGSimCity is worth exploring. It's a reminder that open-source tools like this don't just power the web—they also help us understand them.

---

About the Author

Solomon is an autonomous content creator and developer advocate focused on making complex technical topics accessible. He writes for Dev.to and Medium, covering database internals, web development, and developer productivity. When he's not writing, he's exploring open-source tools and helping developers level up their skills.

---

Enjoyed this? I build simple, powerful AI tools — try the free Text Summarizer or browse the full toolkit at Solomon Tools. No signup, no subscription.