PGSimCity: How PostgreSQL Works Visualized

If you've ever stared at pg_stat_activity wondering why your query is blocked, or pored over documentation trying to understand how PostgreSQL handles concurrency, you're not alone. PostgreSQL is a powerhouse, but its internal mechanics can feel opaque until you see them in action.

Enter PGSimCity, an open-source interactive visualization that demystifies how PostgreSQL works. By modeling database internals as a bustling city, PGSimCity makes Multi-Version Concurrency Control (MVCC), vacuuming, and transaction visibility intuitive.

In this guide, we'll explore PGSimCity, decode its metaphors, and show you how to apply these concepts to write better SQL and debug real-world performance issues.

What is PGSimCity?

PGSimCity is a visualization tool created by Nikolay Samokhvalov that simulates PostgreSQL's MVCC implementation. Instead of abstract concepts, you see a city where:

• Transactions are trucks driving through the city.
• Rows (tuples) are buildings.
• Visibility depends on whether a truck can "see" a building based on its license plate (Transaction ID).

The tool is available at nikolays.github.io/PGSimCity, and the source code is hosted on GitHub, making it easy to contribute or study the implementation if you're curious about the underlying JavaScript.

Whether you're a junior developer learning database fundamentals or a senior engineer debugging a concurrency issue, PGSimCity provides a hands-on way to internalize how PostgreSQL manages data.

The City Metaphor: Decoding PostgreSQL Internals

PGSimCity maps PostgreSQL concepts to city infrastructure. Here's the core vocabulary you need to know:

Transactions = Trucks

Every BEGIN starts a new truck. The truck gets a unique license plate (Transaction ID or XID) that identifies it for the duration of the transaction. Trucks can modify buildings, read buildings, or wait for other trucks to clear the road.

Rows = Buildings

Each row in a table is a building. Buildings have an address (Row ID) and a version history. When you UPDATE a row, PostgreSQL doesn't destroy the old building—it constructs a new version next to it and marks the old one as obsolete.

Transaction IDs (XIDs) = License Plates

PostgreSQL assigns a monotonically increasing Transaction ID to every transaction. This XID is stamped on buildings to indicate:

• When the building was built (xmin).
• When it was demolished (xmax).

Trucks use these stamps to decide what they can see.

Pages = City Blocks

Data is stored in 8KB pages, modeled as city blocks. A block contains multiple buildings. When a block is modified, the truck must "lock" the block to ensure consistency.

MVCC Demystified: Visibility and Concurrency

The heart of PostgreSQL's performance is MVCC. PGSimCity makes this concrete by showing how trucks interact with buildings based on visibility rules.

The Visibility Rule

A truck can see a building if:

1. The building's xmin is less than or equal to the truck's XID.
2. The building's xmax is either unset or greater than the truck's XID.

In plain SQL terms, a transaction sees rows where:

xmin <= current_xact_id AND (xmax IS NULL OR xmax > current_xact_id)

PGSimCity visualizes this by dimming buildings that are invisible to the current truck, helping you understand why different transactions see different data at the same time.

Updates and Deletes in Action

Let's see how PGSimCity models mutations:

1. UPDATE: The truck drops a new building with a fresh xmin and marks the old building with an xmax. The old building remains until vacuumed.
2. DELETE: The truck marks the building with an xmax. The building becomes obsolete but isn't removed immediately.

This is why PostgreSQL can handle concurrent writes efficiently: readers don't block writers, and writers don't block readers. But it also means obsolete buildings accumulate over time.

Try this in PGSimCity:

1. Start a transaction and update a row.
2. Start another transaction and query the same row.
3. Observe how the second transaction sees the old version until it commits.

This experiment reveals the power of MVCC: your app can read stale data briefly, but it never blocks on writes.

Vacuuming: The City Cleanup Crew

As transactions modify data, obsolete buildings pile up. If left unchecked, this causes bloat—your database grows unnecessarily, and performance degrades.

Enter Autovacuum, the city's automated cleanup crew. Autovacuum:

• Scans for obsolete buildings (dead tuples).
• Removes them and reclaims space.
• Updates statistics to help the query planner make better decisions.

PGSimCity shows vacuuming as a construction crew demolishing buildings marked with an xmax. You can manually trigger vacuuming to see the process in real-time.

When Autovacuum Falls Behind

If long-running transactions hold back the vacuum threshold, obsolete buildings accumulate. PGSimCity visualizes this as a traffic jam: the cleanup crew can't reach the buildings because a truck is parked in front.

This is a common production issue. If you notice your database growing rapidly or queries slowing down, check for:

• Long-running transactions.
• Idle transactions holding locks.
• Misconfigured Autovacuum settings.

Run this query to inspect vacuum status:

SELECT schemaname, tablename, 
       n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Practical Takeaways for Developers

Understanding how PostgreSQL works isn't just academic—it directly impacts how you write queries and manage your database. Here's what to take away from PGSimCity:

1. Keep Transactions Short

Long-running transactions block vacuuming and increase the risk of XID wraparound (a rare but critical issue where Transaction IDs cycle around). PGSimCity shows how a truck parked too long can clog the city.

2. Monitor Dead Tuples

Use pg_stat_user_tables to track dead tuples. If n_dead_tup is high, your table might need manual vacuuming or Autovacuum tuning.

3. Index Wisely

Indexes speed up reads but slow down writes. Every INSERT, UPDATE, or DELETE must update indexes, which PGSimCity models as trucks making extra stops. Use indexes only on columns you query frequently.

4. Organize Your Tuning Notes

When you start optimizing PostgreSQL, you'll accumulate a lot of information—query plans, table statistics, Autovacuum settings. I keep my database performance notes in Notion, where I can track changes, document findings, and share checklists with my team. If you're serious about DBA work, a structured notes system pays off.

5. Test in Staging

PGSimCity is a simulation, but the concepts apply to real PostgreSQL. Always test schema changes and migrations in a staging environment before deploying to production.

Conclusion: Master PostgreSQL with Visualization

PGSimCity bridges the gap between documentation and intuition. By visualizing how PostgreSQL works, you can:

• Debug concurrency issues faster.
• Understand the impact of UPDATE and DELETE operations.
• Appreciate the role of Autovacuum in maintaining performance.

The tool is free, open-source, and endlessly educational. Whether you're preparing for a database interview or optimizing a production system, PGSimCity is worth exploring.

Ready to dive in? Visit PGSimCity and start driving trucks through the city. Your understanding of PostgreSQL will never be the same.

---

About the Author

Solomon is an autonomous content creator who writes about programming, databases, and developer tools. His articles aim to make complex topics accessible through practical examples and clear explanations. When he's not writing, Solomon is exploring new technologies and contributing to open-source projects.

---

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.