VACUUM - Tabloyu Bloke Etmez, Disk Alanını da Geri Vermez

Giriş

Açıklaması şöyle. VACUUM komutu bir tablo üzerinde vakumlama işlemini elle başlatır. 

PostgreSQL provides two types of vacuums: manual and auto.

VACUUM Komutları Neden Lazım?

Açıklaması şöyle. PostgreSQL silinmesi istenen satırları hemen silmiyor. Hemen silinmemesiyle ilgili olarak MVCC yazısına da bakabilirsiniz. Bu yüzden bu satırları ara ara VACUMM ile silmek gerekiyor.

When your Java application executes a DELETE or UPDATE statement against a PostgreSQL database, a deleted record is not removed immediately nor is an existing record updated in its place. Instead, the deleted record is marked as a dead tuple and will remain in storage. The updated record is, in fact, a brand new record that PostgreSQL inserts by copying the previous version of the record and updating requested columns. The previous version of that updated record is considered deleted and, as with the DELETE operation, marked as a dead tuple.

There is a good reason why the database engine keeps old versions of the deleted and updated records in its storage. For starters, your application can run a bunch of transactions against PostgreSQL in parallel. Some of those transactions do start earlier than others. But if a transaction deletes a record that still might be of interest to a few transactions started earlier, then the record needs to be kept in the database (at least until the point in time when all earlier started transactions finish). This is how PostgreSQL implements MVCC (multi-version concurrency protocol).

It’s clear that PostgreSQL can’t and doesn’t want to keep the dead tuples forever. This is why the database has its own garbage collection process called vacuuming. There are two types of VACUUM — the plain one and the full one. The plain VACUUM works in parallel with your application workloads and doesn’t block your queries. This type of vacuuming marks the space occupied by dead tuples as free, making it available for new data that your app will add to the same table later. The plain VACUUM doesn’t return the space to the operating system so that it can be reused by other tables or 3rd party applications (except in some corner cases when a page includes only dead tuples and the page is in the end of a table).

VACUUM ve VACUUM FULL Farkı Nedir?

Açıklaması şöyle. Yani VACUUM tablonun kullandığı disk alanını işletim sistemine geri vermez ama tabloları da bloke etmez. VACUUM FULL ise disk alanını geri verir ama tabloyu bloke eder.

VACUUM

The VACUUM process removes DEAD tuples for future usage, but it does not return the space to the operating system.

Therefore, if you perform a bulk data deletion or updates, you might be using too much storage due to space occupied by these DEAD tuples. 

VACUUM FULL

The VACUUM FULL process returns the space to the operating system, ... It does the following tasks.

1. VACUUM FULL process obtains an exclusive lock on the table.

2. It creates a new empty storage table file.

3. Copy the live tuples to the new table storage.

4. Removes the old table file and frees the storage.

5. It rebuilds all associated table indexes, updates the system catalogs and statistics.

VACUUM şeklen şöyle

Örnek

İsmi SampleTable olan tabloyu vakumlamak için şöyle yaparız

VACUUM SampleTable;

Örnek

Şöyle yaparız

VACUUM ANALYZE table_name;