Comprendre MVCC et VACUUM

Welcome to understanding PostgreSQL's core! Today, we dive into Multi-Version Concurrency Control (MVCC). It's a fancy term for a simple, powerful idea.

MVCC is how PostgreSQL allows many users or applications to access and modify data at the same time without interfering with each other. Think of it as a traffic controller for your database.

Imagine a database without MVCC. If one user is reading a row, another user trying to update that same row would have to wait. This is called locking, and too much of it can make your database painfully slow.

MVCC solves this by ensuring that readers don't block writers, and writers don't block readers. Everyone gets their own consistent view of the data.

The magic of MVCC lies in how it handles changes. When you UPDATE or DELETE a row in PostgreSQL, the database doesn't immediately overwrite or remove the original data.

Instead, it creates a new version of the row (for updates) or simply marks the existing row as 'deleted' without physically removing it. The old version remains, temporarily.

How does PostgreSQL know which version of a row to show you? Each transaction gets a unique ID. When a row is created, it gets an xmin (creation transaction ID). When it's 'deleted', it gets an xmax (deletion transaction ID).

• Your transaction only sees rows committed *before* it started.
• It ignores rows deleted *after* it started.

This ensures you always see a consistent snapshot of the data.

Let's see a simple example of how an UPDATE creates new row versions:

First, we create a table and insert a product:

Now, when we update the price, PostgreSQL doesn't change the existing row. Instead, it marks the old row version as 'dead' and inserts a brand new row version with the updated price.

The old version is now a 'dead tuple' – it's no longer visible to new transactions but still occupies disk space.

Over time, with many UPDATEs and DELETEs, tables can accumulate a lot of these 'dead tuples'. This leads to table bloat.

Table bloat means your database files are larger than they need to be, consuming more disk space and potentially slowing down queries because more data needs to be read from disk.

This is where the VACUUM command comes in! Its primary job is to clean up these dead tuples. It's like a janitor for your database, tidying up the old, unused versions of data.

VACUUM marks the space occupied by dead tuples as reusable, making it available for new data to be inserted into the table. This prevents continuous table growth and improves performance.

When you run VACUUM, PostgreSQL scans the table, identifies dead tuples, and adds their locations to a 'free space map'. This doesn't immediately shrink the table file on disk, but it ensures that future INSERTs or UPDATEs can reuse that space.

Here's how you'd run a basic VACUUM:

You've just learned about two critical PostgreSQL concepts!

• MVCC enables high concurrency by allowing multiple versions of data.
• UPDATEs and DELETEs create dead tuples.
• Table bloat occurs when these dead tuples accumulate, wasting space.
• The VACUUM command cleans up dead tuples, making their space reusable and preventing bloat.

Understanding these is key to maintaining a healthy and performant PostgreSQL database!