PostgreSQL for the Oracle DBA, Part 6: Tuples, MVCC, and Two Views

After our first two weeks of ensuring Grant and I didn’t burn down SQLServerCentral figuring out how Steve Jones has kept the pace he has for so long, (quite an impressive feat, I think we’d both agree!) I’m back to working with my comparisons and building more knowledge in PostgreSQL.  What caught my attention this week was the simple concept of a row (or tuple) which might seem universal in relational systems, after all, data is data no matter the platform, right? But under the hood, the way databases store, manage, and control visibility of that data can differ drastically. This is especially true when comparing PostgreSQL with how Oracle manages rows.

Both systems implement MVCC, aka Multi-Version Concurrency Control which allows a database to enable high concurrency while ensuring consistency. But the mechanisms, storage structures, and performance trade-offs they use are surprisingly different.

Let’s unpack what a tuple means in PostgreSQL 17 and how it contrasts with a row in Oracle, followed by a deeper dive into each system’s approach to MVCC.

PostgreSQL: The Tuple-Centric, an Append-Only Model

In PostgreSQL, (focusing on version 17 for this post) a tuple is a single row stored in a heap file. Each tuple is physically independent and contains not only user data but also metadata used for MVCC visibility, such as:

• xmin: the transaction ID that created the tuple
• xmax: the transaction ID that deleted or updated it
• ctid: the tuple’s physical location (block#, offset#)
• Flags and null bitmap

When a row is updated, PostgreSQL doesn’t modify it in-place. Instead, it will:

1. Create a new version of the tuple with the updated data.
2. Mark the old tuple as expired by setting its xmax.
3. Finally, leave the old version in place until vacuumed.

This append-only update strategy ensures readers never block writers and vice versa. However, it also means bloat that will need to be dealt with at some point.  In other words, more dead tuples that must be cleaned up by PostgreSQL’s VACUUM or autovacuum process.

Oracle: In-Place Rows and Undo Magic

In contrast, Oracle, (using the simplest, classic instance or a PDB architecture) uses a more traditional model where a row in Oracle is updated in-place. To maintain MVCC, Oracle generates undo records that store the before image of the row which was changed.

What happens during an update is basically as follows:

1. The row is changed in-place in the data block.
2. An undo record is written to the UNDO tablespace.
3. Transactions querying a past state of the row can reconstruct it using the UNDO data if needed.

Oracle’s MVCC relies on System Change Numbers (SCNs) instead of transaction IDs. Each operation is tagged with an SCN, and Oracle uses these to determine whether a row is visible to a transaction based on its snapshot.

This model has a major advantage: no need for periodic vacuuming. The cleanup of old data happens automatically via UNDO and space management mechanisms.

Key Differences Between PostgreSQL 17 and Oracle

Feature	PostgreSQL 17	Oracle
Update Strategy	Append-only (creates new tuples)	In-place updates
MVCC Mechanism	Tuple versioning via xmin/xmax	Undo-based rollback using SCNs
Visibility Control	Transaction IDs and snapshots	SCN-based snapshots
Old Data Cleanup	Manual (VACUUM/Autovacuum)	Automatic (Undo retention)
Concurrency Handling	Readers never block writers (tuple isolation)	Readers reconstruct old versions from UNDO
Storage Implication	Risk of bloat if vacuum delayed	Space-efficient, but UNDO tablespace sometimes must be managed
Row Identifiers	ctid (physical location)	ROWID (block, slot, object reference)
Index Maintenance	Indexes must be updated for every new tuple	Less frequent due to in-place updates

 

Practical Considerations

• PostgreSQL’s model is elegant in simplicity and excellent for analytical workloads where immutability and snapshot isolation shine. But it requires diligent maintenance via autovacuum, especially under high write loads.
• Oracle’s undo-based MVCC is space-efficient and stable for high-throughput OLTP systems, but concurrency and lock issues can arise.

Summary

Both PostgreSQL 17 and Oracle are mature, powerful relational databases, but the tuple is one aspect where their philosophies diverge.

PostgreSQL views each change as a new truth, preserving history until someone cleans it up. Oracle rewrites reality but keeps a short-term diary just in case someone needs to look back.

Understanding these underlying mechanics is critical to our learning path and the bigger picture around data consistency, recovery planning, and even optimization strategy. Appreciating how each system handles MVCC at the tuple level gives you a clearer picture of why they behave the way they do.