PostgreSQL stores table data in a structure known as a heap file, which is essentially an unordered collection of pages. Each page, typically 8KB in size, contains its own header, line pointers (or an item identifier array), the tuples themselves, and some special space. When a new row is inserted, Postgres finds a page with free space and places the new tuple there. There is no inherent ordering of rows on disk, which is why a sequential scan reads pages as they appear in the file, not in any logical key order.

Each tuple has a header containing visibility information. This includes xmin, the transaction ID that created the tuple, and xmax, the transaction ID that deleted it (if any). When you UPDATE a row, Postgres doesn't modify the tuple in place. Instead, it creates a new version of the tuple and sets the xmax of the old version to the current transaction ID. The old, now-dead tuple remains on the page. This is the core of its MVCC implementation. The primary drawback is the accumulation of dead tuples, leading to table bloat and the necessity of the VACUUM process to reclaim space and prevent transaction ID wraparound failure.

For data that's too large to fit in a single page, PostgreSQL uses a mechanism called TOAST (The Oversized-Attribute Storage Technique). It automatically slices the large data into chunks and stores them in a separate TOAST table, leaving a pointer in the main heap tuple. This keeps the primary table's pages slim and scannable, but it introduces overhead for accessing large fields, as it requires an extra lookup into the TOAST table.

MySQL's InnoDB storage engine takes a fundamentally different approach. Every InnoDB table is stored as a clustered index based on its primary key. This means the data itself is integrated into the leaf nodes of a B+Tree structure. The physical order of rows on disk is the primary key order. This makes primary key lookups extremely fast, as the database can traverse the B+Tree directly to the page containing the row.

When a secondary index exists, its leaf nodes don't point directly to the row's physical location. Instead, they store a copy of the primary key for that row. To retrieve data using a secondary index, InnoDB must first scan the secondary index to find the primary key, and then use that key to traverse the clustered index to find the actual row data. This can result in two B+Tree traversals for a single lookup.

InnoDB's MVCC implementation also differs significantly. Instead of creating new tuple versions on the same page, an UPDATE modifies the row in-place within the clustered index. The previous version of the data is written to a separate area called the undo log. Each row header contains a pointer to the previous version in the undo log, forming a linked list of historical versions. Transactions needing an older view of the data traverse this chain. This avoids the tuple bloat seen in Postgres but introduces its own challenges, such as potential contention on the undo log and the overhead of purging old versions from it.

These architectural differences lead to distinct performance characteristics and maintenance requirements.

In write-heavy, high-throughput environments, these distinctions are critical. For PostgreSQL, frequent UPDATEs can rapidly create dead tuples, causing table bloat that degrades scan performance and inflates storage. Aggressive vacuuming is required to manage this, which itself consumes I/O and CPU resources. Modeling choices that favor INSERT-only patterns over frequent UPDATEs can mitigate this pressure.

For MySQL, write-heavy workloads can strain the undo log system. Long-running transactions can prevent the purge process from cleaning up old row versions, causing the undo log to grow significantly. This consumes disk space and can slow down the creation of new read views. Choosing a short, non-changing primary key is also crucial, as updating a primary key requires physically moving the row, and a large primary key bloats all secondary indexes.