If you’ve ever built a backend, you’ve probably written an INSERT statement, gotten a 200 OK back, and moved on with your life.
But what actually happens inside the database during that millisecond? How does the database guarantee it won't lose your data if someone unplugs the server? And how do massive companies stream millions of these changes in real-time to analytics dashboards without crashing their main database?
Let’s dive deep into the internals of PostgreSQL, moving from a single query on a hard drive all the way up to a distributed Change Data Capture (CDC) pipeline.
To make this concrete, imagine we are building the backend for QuickBite, a massive food delivery app. We have thousands of delivery drivers updating their live GPS locations every few seconds.
1. The RAM vs. Disk Reality (How INSERT Works)
When our backend executes:
INSERT INTO driver_locations (driver_id, lat, long) VALUES (105, 40.71, -74.00);
You might think Postgres immediately writes this new row to the hard drive (SSD). It doesn't. Writing directly to a massive table file is a "random I/O" operation, which is incredibly slow.
Instead, Postgres does this:
- The Free Space Map (FSM): Postgres checks a lightweight map to find an 8KB page on the disk that has empty space.
- Shared Buffers: It copies that entire 8KB page from the slow SSD into a dedicated chunk of RAM called shared_buffers.
- The Dirty Page: It inserts the new GPS coordinates into that page in RAM, and marks the page as "dirty" (meaning the RAM version is now newer than the disk version).
If another user runs a SELECT query to find that driver, Postgres checks a Hash Map, realizes the page is already in RAM, and serves it instantly without touching the disk.
Notice how data moves from Shared Buffers to the WAL before ever hitting the main Data Files.
2. The Lifesaver: The Write-Ahead Log (WAL)
Here is the obvious problem: If the new location is only in RAM, what happens if the server loses power? The data is gone forever, right?
Enter the Write-Ahead Log (WAL).
Before Postgres tells your backend "Insert Successful!", it appends the exact byte-level change to the WAL file on the disk.
Why is writing to the WAL fast, but writing to the table slow?
- The Table (Random I/O): Think of a delivery driver having to drive to 100 random houses across a city. The physical disk has to seek and jump around to find specific pages.
- The WAL (Sequential I/O): Think of delivering 100 pizzas to the same office building. The WAL is an append-only file. Postgres just drops the data at the very end of the file in one continuous, lightning-fast motion.
Sequential I/O (the WAL) requires zero jumping around, making it exponentially faster than Random I/O (Table updates).
Later, a background process called the Checkpointer wakes up, takes all the "dirty" pages from RAM, and quietly does the slow work of overwriting the actual table files on the SSD. Once finished, it updates the WAL with a "Checkpoint."
If the server crashes, Postgres reboots, looks at the last Checkpoint in the WAL, and reapplies all the missing changes to RAM. Zero data loss.
3. The MVCC Plot Twist (How UPDATE Works)
Five seconds later, Driver 105 moves. We run an UPDATE query.
Here is the biggest secret in Postgres: It never updates a row in place.
If it overwrote the row, and an analytics query was reading that exact row at the same millisecond, the database would lock up. Instead, Postgres uses MVCC (Multi-Version Concurrency Control).
When you UPDATE, Postgres:
- Leaves the old location exactly where it is, but tags it internally as a "Dead Tuple" (using a hidden
xmaxcolumn). - Treats the update like a brand new
INSERT, finding new space and writing the new location.
Both versions exist simultaneously! This is why readers never block writers in Postgres. (A background janitor called VACUUM eventually comes by and deletes the dead rows to save space).
4. Scaling Up: Physical Replication
QuickBite goes viral. One database can't handle the read traffic. We add a "Slave" (Read Replica) database. How do we keep it in sync?
We use Physical Replication (wal_level = replica).
The Master database doesn't send SQL queries to the Slave. It simply streams the raw binary WAL files over the network. The Slave receives those raw 8KB block changes, saves them to its own disk, and continuously runs in a state of "Crash Recovery"—replaying the Master's disk bytes into its own memory.
It’s an exact, byte-for-byte clone.
5. Analytics & CDC: The Logical Pipeline
Eventually, the business team wants to run massive OLAP analytics ("What is the average delivery time per region this month?"). If they run that on the Postgres Replica, the CPU will spike and crash the app. We need to move the data to a columnar database like ClickHouse.
We can't use physical replication because ClickHouse doesn't understand Postgres's raw disk bytes. We need Change Data Capture (CDC) using a tool like Debezium and Kafka.
The Flow:
- Logical Decoding: We change Postgres to
wal_level = logical. Now, instead of just raw disk bytes, Postgres includes table and row metadata in the WAL. - The Bookmark: Debezium connects to Postgres and creates a Replication Slot. This is a permanent bookmark on the server tracking exactly which WAL logs Debezium has consumed.
- The Stream: Postgres translates the WAL into a logical format and streams it to Debezium. Debezium formats it into a beautiful JSON payload (with
beforeandafterstates) and pushes it to Kafka. - The Ingestion: ClickHouse continuously consumes this Kafka topic, instantly making the data available for analytics.
The Disaster Scenarios:
- What if Debezium is added 2 years late? Debezium first locks the table, runs a massive
SELECT *to snapshot the historical data into Kafka, and then seamlessly switches to reading the live WAL stream. - What if Kafka crashes? Debezium stops sending "ACKs" (acknowledgments) to Postgres. Postgres looks at the Replication Slot and says, "Debezium is stuck. I will hoard the WAL files on my hard drive until it comes back."
- What if Kafka is down for a week? To prevent the Postgres hard drive from reaching 100% and crashing the entire food delivery app, we configure
max_slot_wal_keep_size. Once the hoarded WAL hits this limit (e.g., 50GB), Postgres deletes the Replication Slot to save itself. We lose the CDC pipeline temporarily, but the main database survives!
Conclusion
The next time you write a simple INSERT, take a moment to appreciate the incredible, highly-orchestrated dance happening under the hood. From the Buffer Manager juggling RAM, to the WAL ensuring durability, to replication slots enabling real-time global analytics—PostgreSQL is an absolute engineering marvel.
Have you ever brought down a database with a bad replication setup? Let me know in the comments!
Top comments (0)