When choosing a relational database for a real-world project, developers often debate surface-level features like JSON support, PostGIS, or clustering capabilities. However, the core performance determinant of a system lies at the lowest tier: how the database organizes physical storage and processes write operations (INSERT, UPDATE, DELETE).
This article will delve into the underlying architecture of InnoDB (MySQL's default engine) and PostgreSQL. This will help you clearly understand the strengths and weaknesses of each database management system based on fundamental data modification operations.
Core Differences in Physical Data Organization
Before analyzing each operation, we must understand how these two systems arrange data on the hard drive. (Practical Evidence: This difference is exactly why a table containing 1 billion records in PostgreSQL often takes up 10-20% more disk space than MySQL, due to the over-allocation of the Heap buffer).
1. MySQL (InnoDB): Clustered Index Storage (B+Tree)
In InnoDB, the table itself is a B+Tree heavily sorted by the Primary Key. The data of a row is stored directly at the leaf nodes of this tree. All Secondary Indexes point to the Primary Key of that row.
2. PostgreSQL: Heap Storage
PostgreSQL uses a free memory region called a Heap to store tuples (rows). Data pushed into the Heap does not need to be sorted by any order or Primary Key. Subsequently, all Indexes (including the Primary and Secondary Indexes) are built on a private B-Tree structure. The leaf nodes of this Index tree contain physical pointers (CTIDs) pointing directly to the exact location of the record in the Heap.
Understanding this crucial difference will reveal how they behave with I/O.
1. The INSERT Operation: The Battle of Random IDs
Analyzing the data insertion operation heavily depends on the data type of the Primary Key. (Practical Evidence: Choosing the wrong storage engine when dealing with massive amounts of Random UUIDs can severely degrade the maximum disk write speed from 50,000 IOPS down to just 2,000 IOPS because the disk is locked by the continuous restructuring of tree elements).
With Auto-Increment / Sequential IDs:
MySQL truly shines. Due to the Clustered Index characteristic, sequential data is continuously appended to the extreme right edge of the B+Tree. The write operation is extremely smooth, avoiding disk read head distraction. The physical data sits closely together, giving Range Scan queries exceptionally high speeds.
PostgreSQL also performs this task well; however, it consumes slightly more I/O because it has to write to two different places: inserting data into the empty Heap space, then updating the Index B-Tree.
With Random IDs (UUIDv4):
PostgreSQL takes absolute priority. The Heap structure is a massive advantage here. It simply finds available memory (Free Space) and randomly inserts data without breaking any memory arrays.
Conversely, this is a nightmare for MySQL. Attempting to insert random, unordered data into a B+Tree means the engine must constantly perform Page Splits to squeeze data in between. This creates severe local locks and skyrockets I/O latency under high load. Therefore, when working with MySQL, engineers are forced to switch to UUIDv7 (time-sequential) or Snowflake IDs instead of UUIDv4.
2. The UPDATE Operation: The Dark Side of MVCC Architecture
This is the area that shows the most distinct contrast in the design philosophy of the two systems. (Practical Evidence: Continuously updating driver coordinates every second in Ride-Hailing applications is the classic "Update In-place" problem, where InnoDB's hardware-protecting advantage helps the Database Server avoid suffocating under a mountain of dead data).
MySQL (InnoDB): Update In-Place
When the system receives an UPDATE command for a record, if the data size does not bloat larger than the old version, MySQL goes straight to that physical location and confidently overwrites the new data. Naturally, to solve the problem of concurrent readers while writing (MVCC - Multi-Version Concurrency Control), InnoDB uses the Undo Log. The pre-modified version is placed in the Undo Log to serve the read process of older transactions.
Benefit: Only strictly necessary data is modified; the hard drive does not generate unnecessary garbage. If the updated column is not part of any Index, the Index trees remain completely untouched, perfectly preserving hardware resources.
PostgreSQL: Update is Essentially Delete + Insert New Row
Unlike MySQL, to ensure the integrity of MVCC, PostgreSQL never overwrites old data. Every time you execute an UPDATE command, PostgreSQL treats it as a logical deletion of the current record and immediately writes the entire information of that row into a completely new physical record.
Consequences:
The first is Write Amplification. Even if you only change the value of one column, Postgres generates a whole new physical row with the exact integrity of the old row.
Next, because the newly generated row resides at a new memory address, PostgreSQL is forced to scan and update ALL the tips of the Index trees pointing to that table to reconnect the physical pointers, regardless of whether the Index pointers were actually changed or not.
To better understand the hidden price of managing MVCC in PostgreSQL, you can refer to the in-depth blog post: Internals of MVCC in Postgres: Hidden Costs of updates vs inserts. The article sharply analyzes why heavily updated systems easily bog down due to Index Thrashing (destroying I/O just to maintain Indexes).
Although PostgreSQL has a defensive technique (HOT - Heap Only Tuples) by reserving a buffer called fillfactor right inside the same data block, this technique only works if the updated column is not part of any Index structure.
3. The DELETE Operation: The Garbage Collection Process
Both MySQL and PostgreSQL utilize a Soft-delete model at the system level (marking as deleted instead of immediately sweeping the disk) to save response time, but they differ in their recovery mechanisms. (Practical Evidence: Many startups using PostgreSQL have experienced Database crashes during hot growth phases simply because their autovacuum configuration was too superficial, causing a massive accumulation of "Dead Tuples" that paralyzed the CPU and I/O bandwidth during full table scans).
MySQL (InnoDB):
At the leaf nodes of the Clustered Index, the deleted record gets a "deleted_flag" bit flipped, and that memory address is attached to a Free List. As soon as another INSERT command of matching size arrives, InnoDB immediately reuses that gap, avoiding wasted space.
PostgreSQL:
Similarly, the deleted row transforms into a "Dead Tuple" by attaching the transaction info (txid) of the recent deletion and hiding that row from subsequent arrivals.
Combined with the UPDATE in-place nightmare described above, PostgreSQL spawns an extreme amount of Dead Tuples on the hard drive. The only way to clean up and reclaim this space is to wait for the background garbage collection process (VACUUM Worker) to sweep the entire system. If the data volatility is too high and the autovacuum configuration is not well-tuned, the table will bloat out of control (Table Bloat), forcing the hard drive bandwidth to bear the burden of scanning garbage data before touching real data.
4. Conclusion via Trade-offs
Choosing a data architecture never involves the concept of an "absolutely superior technology"; rather, the core lies in the trade-offs to solve the right business domain.
To prove the importance of this architectural trade-off, you can refer to the classic technical analysis: Why Uber Engineering Switched from Postgres to MySQL. Even though modern PostgreSQL versions have been significantly upgraded and optimized to reduce the load on the garbage collection process, at the core architectural level, the root MVCC element that causes Write Amplification still fundamentally exists. As long as a record is not updated directly In-place, the system will continue to spend I/O duplicating physical rows.
In summary:
- If your application leans towards static data or one-way appending (Append-only), requires complex analytical queries, polymorphic data structures (JSONB, PostGIS, Array), and the number of records subjected to In-place updates is not disastrous, PostgreSQL remains the undisputed all-in-one powerhouse.
- Conversely, if the core use case of the project is Write-heavy with the frequency of data overwrite (UPDATE) on a finite number of records repeating thousands of times per second, the architecture of MySQL (InnoDB) will protect the server hardware far more perfectly and keep the I/O latency graph absolutely stable.
Hopefully, this deep dive provides you with a clearer understanding of these two prominent databases, empowering you to make informed architectural decisions in real-world applications or ace your next system design interview.
Top comments (0)