How PostgreSQL Works: An Interactive Deep Dive Using PGSimCity

PostgreSQL is one of the most robust relational databases in the world, powering everything from startups to Fortune 500 companies. But for many developers, Postgres remains a black box. You write SQL, you get results, but do you truly understand how PostgreSQL works under the hood?

When queries slow down, when deadlocks appear, or when bloat creeps into your tables, surface-level knowledge isn't enough. That's where PGSimCity comes in.

In this article, we'll explore PGSimCity—an interactive visualization tool that demystifies PostgreSQL internals. You'll learn how pages, B-Trees, MVCC, and transactions actually function, and how you can use this knowledge to write better code and optimize performance.

Why You Need to Understand PostgreSQL Internals

Before diving into the tool, let's address the "why." Understanding PostgreSQL internals isn't just for database administrators. As a developer, this knowledge helps you:

1. Debug Performance Issues: Knowing how data is stored on disk helps you tune queries and indexes effectively.
2. Avoid Pitfalls with MVCC: Misunderstanding Multi-Version Concurrency Control leads to bugs like phantom reads or unexpected locking behavior.
3. Design Better Schemas: Awareness of page sizes and row sizes influences how you structure your tables for optimal I/O.
4. Communicate Effectively: Speaking the language of buffers, WAL, and checkpoints bridges the gap between dev and ops.

PGSimCity makes these abstract concepts tangible. Instead of reading dense documentation, you can see exactly what happens when you run a query.

What is PGSimCity?

PGSimCity is an open-source, interactive simulation of PostgreSQL internals created by Nikolay Shcherbakov. It provides a visual environment where you can manipulate data and immediately see the internal state changes in real-time.

The tool covers core components of PostgreSQL:

• The Page: How data is chunked and stored in 8KB blocks.
• The Buffer Pool: How data moves between disk and memory.
• B-Tree Indexes: How lookups are optimized.
• MVCC: How multiple versions of a row coexist.
• Transactions: How isolation levels and locks work.

You can access PGSimCity directly in your browser at nikolays.github.io/PGSimCity. It's free, requires no installation, and is an invaluable resource for learning.

Accessing the Tool

Getting started is straightforward. Navigate to the PGSimCity website, and you'll see a workspace with a SQL editor on the left and a visualization panel on the right. You can execute SQL commands and watch the database internals respond instantly.

For those who prefer to tinker with the code, PGSimCity is hosted on GitHub. You can fork the repository to create custom simulation scenarios or contribute to the project:

git clone https://github.com/nikolays/PGSimCity.git
cd PGSimCity
# Follow the setup instructions in the README

Forking the project is a great way to build custom educational tools for your team or extend the simulation to cover edge cases specific to your application.

Core PostgreSQL Concepts Visualized

Let's walk through the key concepts PGSimCity helps you master.

The Page and the Buffer Pool

PostgreSQL reads and writes data in fixed-size chunks called pages, typically 8KB. When you query a table, Postgres loads pages from disk into the buffer pool (shared memory) before processing the data.

In PGSimCity, you can visualize this process:

1. Create a table with some columns.
2. Insert rows and watch how they fill up pages.
3. Observe overflow: When a row is too large for a page, PGSimCity shows how data spills over to follow-up pages.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    bio TEXT
);

INSERT INTO users (name, email, bio)
SELECT 
    'User ' || i,
    'user' || i || '@example.com',
    repeat('Lorem ipsum dolor sit amet. ', 50)
FROM generate_series(1, 100) AS i;

As you execute this, notice how the visualization highlights which pages are being accessed. This helps you understand the cost of sequential scans versus index scans. If your queries often touch many pages, you might need to adjust random_page_cost or rethink your indexing strategy.

B-Tree Indexes: How Lookups Happen

The B-Tree is PostgreSQL's default index method. It organizes data in a balanced tree structure, allowing for efficient range queries and point lookups.

PGSimCity excels at showing you the structure of a B-Tree as you insert and delete data. You can see:

• Root and Leaf Nodes: How the tree grows downward.
• Page Splits: What happens when a page becomes full.
• Search Paths: How a query traverses the tree to find a row.

CREATE INDEX idx_users_email ON users (email);

When you run a query like SELECT * FROM users WHERE email = '[e-mail]';, PGSimCity highlights the exact path through the B-Tree. This visualization makes it clear why indexes speed up lookups: instead of scanning every page, Postgres jumps directly to the relevant leaf node.

MVCC and Transaction Isolation

Multi-Version Concurrency Control (MVCC) is one of PostgreSQL's most powerful features. It allows readers and writers to operate concurrently without blocking each other. But MVCC comes with complexity, especially around dead tuples and table bloat.

PGSimCity lets you simulate MVCC behavior:

1. Open two transactions.
2. Modify a row in one transaction.
3. Read the row in the other transaction.

You'll see how Postgres creates new versions of the row, tagged with xmin (transaction ID that inserted it) and xmax (transaction ID that deleted or updated it). Each transaction sees a consistent snapshot based on when it started.

-- Transaction 1
BEGIN;
UPDATE users SET name = 'Updated User' WHERE id = 1;
-- Do not commit yet

-- Transaction 2
BEGIN;
SELECT name FROM users WHERE id = 1;
-- You'll see the old name, demonstrating MVCC
COMMIT;

Understanding MVCC is crucial for managing bloat. When rows are updated or deleted, Postgres doesn't immediately reclaim space. Instead, it marks them as dead. The Autovacuum daemon is responsible for cleaning up these dead tuples.

Practical Exercises with PGSimCity

Theory is great, but practice cements knowledge. Here are some exercises you can run in PGSimCity to deepen your understanding.

Experimenting with Locks and Deadlocks

PostgreSQL uses a granular locking mechanism to ensure data integrity. PGSimCity allows you to trigger and observe lock conflicts.

Try this scenario:

1. Start two transactions.
2. In Transaction A, update row ID 1.
3. In Transaction B, try to update row ID 1.
4. Observe how Transaction B waits for Transaction A to release the lock.

Then, create a deadlock:

1. Transaction A updates row 1, then tries to update row 2.
2. Transaction B updates row 2, then tries to update row 1.
3. Watch PGSimCity detect the circular dependency and abort one of the transactions.

This hands-on experience demystifies lock modes like ShareLock, ExclusiveLock, and AccessExclusiveLock. It also highlights the importance of consistent lock ordering in your application code to prevent deadlocks.

Observing Autovacuum and Bloat

Table bloat can severely impact performance. Autovacuum is designed to prevent bloat by cleaning up dead tuples and reusing page space.

In PGSimCity, you can:

1. Insert a large number of rows.
2. Update or delete most of them.
3. Trigger Autovacuum manually.
4. Observe how dead tuples are reclaimed and pages are recycled.

This exercise shows the lifecycle of a row in PostgreSQL and emphasizes the need to monitor Autovacuum settings, especially for tables with high write activity.

Integrating PGSimCity into Your Workflow

PGSimCity isn't just a learning tool; it can be part of your daily development workflow. Here's how:

Document Your Insights

As you experiment with PGSimCity, you'll uncover patterns and best practices. Keep track of your findings in a centralized knowledge base. Tools like Notion are excellent for this. You can create templates for common scenarios, store SQL snippets, and link to visualizations.

Start a dedicated workspace to document PostgreSQL internals, query optimization tips, and team learnings. You can try Notion

---

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.