Auto VACUUM in Postgres

Introduction

How to handle data changes

Postgres uses the MVCC mechanism, meaning when data changes occur, Postgres still retains old data versions and INSERTs new data

• DELETE: when deleting rows, Postgres only adds a flag to that row called Dead Tuple indicating that this row is no longer used
  • This means that row still exists on disk and is not completely deleted, so it will need cleaning up
  • When querying, it will be automatically ignored and will not return results
• INSERT: when adding a row
  • Postgres will search for empty space in existing pages to INSERT new rows
  • Or create a new page, if empty pages are exhausted
  • Or override data of Dead Tuple rows according to the Free Space Map
• UPDATE: when an UPDATE is needed, it will DELETE the old row and INSERT a row with new data
• TRUNCATE is an operation belonging to the DDL (Data Definition Language) group, not DML like DELETE.
• Instead of marking Dead Tuple for each row, Postgres will completely delete the table's data file on disk and create a new empty replacement file
• As a result, no Dead Tuple is created and disk space is immediately released to the OS without waiting for VACUUM to run.

Operations inside a Transaction

• This is one of the operations working in PostgreSQL that very easily creates Dead Tuples
• If running only a single statement, it is also a Transaction that will be automatically Committed, or if you manually start a Transaction and Commit successfully, it will process as mentioned above
• But if a Transaction runs with an error or you actively ROLLBACK, it can still create Dead Tuples and still require VACUUM to clean up.
• INSERT: Postgres actually writes data to disk
  • When the Transaction is ROLLBACKed, that row becomes a Dead Tuple
• UPDATE: Postgres will not update in-place directly on the old row
  • Instead, it only updates information on that row to know that it is about to be deleted
  • After that, it INSERTs the new row version
  • When the Transaction is ROLLBACKed, the old row remains a Live Tuple because it was not COMMITted, while the newly created row becomes a Dead Tuple
• DELETE: Postgres only marks rows about to be deleted with the corresponding Transaction ID
  • When ROLLBACK occurs, the Transaction is ABORTED and the old row remains a Live Tuple
  • No Dead Tuples are created at all
• TRUNCATE: When used, Postgres does not delete the old data file yet, but only creates a new empty file
  • If ROLLBACKed, it only needs to delete the newly created file
  • No extra Dead Tuples are created

Auto Vacuum

• Postgres uses the MVCC mechanism, so when you UPDATE or DELETE, old data does not disappear immediately but turns into Dead Tuples
• PostgreSQL has a background process called Autovacuum Daemon, which runs by default every 1 minute to check tables and determine if cleanup is needed.
• It will automatically trigger VACUUM on a table when the number of dead tuples (deleted/modified rows) exceeds a certain threshold.
• This threshold is calculated using the formula:
  • Trigger threshold = {autovacuum_vacuum_threshold} + ({autovacuum_vacuum_scale_factor} x {Total rows of table})
  • Default parameters in Postgres:
    • autovacuum_vacuum_threshold: 50 rows
    • autovacuum_vacuum_scale_factor: 0.2 (equivalent to 20%).
• Example: A table with 1,000,000 rows will have a trigger threshold of: 50 + (0.2 x 1,000,000) = 200,050 rows. When the table accumulates more than 200,050 dead tuples (from DELETE or UPDATE commands), Auto-vacuum will automatically be triggered to work on this table.

Auto Analyze

• The background process Auto-analyze is part of the Autovacuum daemon and will automatically trigger ANALYZE on a table to update pg_statistic when the number of modified rows (including INSERT, UPDATE, DELETE) exceeds a certain threshold.
• Formula for calculating the Auto-analyze threshold:
  • {Trigger threshold} = {autovacuum_analyze_threshold} + ({autovacuum_analyze_scale_factor} x {Total rows})
  • Default parameters in Postgres:
    • autovacuum_analyze_threshold: 50 rows
    • autovacuum_analyze_scale_factor: 0.1 (equivalent to 10%)
• Example: A table with 100,000 rows has an automatic Auto-analyze trigger threshold when 50 + (0.1 x 100,000) = 10,050 rows are modified.

Table Bloat

• Table Bloat is a phenomenon where the disk space occupied by a table (or Index) expands abnormally compared to the actual size of Live Tuples contained inside it.
• Dead Tuple is the primary cause of Table Bloat.
• Example: A table containing only 100,000 real rows of data (actual size is only about 50 MB).
  • However, the storage file for this table on the hard disk reaches up to 5 GB
  • That 4,950 MB difference is Bloat (wasted space).

Harmful Effects

• Wasteful hard disk space by wasting storage resources on unused data
• Impacting query performance:
  • When running Seq Scan, Postgres must read the entire Heap file from disk into RAM (Buffer Cache), including Dead Tuples instead of reading only actual data.
  • As a result, disk and RAM must work harder rather than focusing on processing primary data
• When Table Bloat occurs, indexes related to that table suffer from Index Bloat as well, making data lookups on Index require more operations

Detail

You can use this query to view information about Live Tuple and Dead Tuple in a table

SELECT 
    relname AS table_name,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    ROUND(
        (n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0)) * 100, 2
    ) AS dead_pct_ratio,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'test';

You can see that if a new table has just been INSERTed with data, there are only Live Tuples and no Dead Tuples

Next, check using the following queries

-- Statement 1
UPDATE test SET col_smallint = 1 WHERE col_smallint = 1
DELETE FROM test WHERE col_smallint = 1

-- Transaction 2
BEGIN;
INSERT INTO test (col_smallint) VALUES (500);
ROLLBACK;

-- Statement 3
TRUNCATE TABLE TEST RESTART IDENTITY CASCADE;

• Statement 1: Simply using UPDATE/DELETE will create Dead Tuples
• Transaction 2: When using INSERT inside a Transaction that is ROLLBACKed, it also creates Dead Tuples

Statement 3: After TRUNCATE, it will delete all Live Tuples and Dead Tuples

Auto Vacuum/Analyze & Table Bloat

-- Query 1
SELECT name, setting, unit 
FROM pg_settings 
WHERE name IN (
    'autovacuum_vacuum_threshold',
    'autovacuum_vacuum_scale_factor',
    'autovacuum_analyze_threshold',
    'autovacuum_analyze_scale_factor'
);

-- Query 2
SELECT relname, reloptions 
FROM pg_class 
WHERE reloptions IS NOT NULL 
  AND relname = 'test';

-- Query 3
SELECT 
    schemaname,
    relname,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    (current_setting('autovacuum_vacuum_threshold')::bigint + 
    (current_setting('autovacuum_vacuum_scale_factor')::numeric * n_live_tup))::bigint AS vacuum_threshold,
    CASE 
        WHEN n_dead_tup >= (current_setting('autovacuum_vacuum_threshold')::bigint + 
                           (current_setting('autovacuum_vacuum_scale_factor')::numeric * n_live_tup))
        THEN 'NEED VACUUM' 
        ELSE 'OK' 
    END AS vacuum_status,
    
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC;

-- Query 4
SELECT 
    table_len AS total_bytes,                 
    tuple_count AS live_tuples,               
    dead_tuple_count AS dead_tuples,          
    round(dead_tuple_len * 100.0 / table_len, 2) AS dead_tuple_percent,
    round(free_space * 100.0 / table_len, 2) AS free_space_percent
FROM pgstattuple('test');

Query 1: View configuration parameters for threshold and scale_factor of vacuum and analyze of the Database

Query 2: View custom configuration per table in case that table overrides specific settings. If the returned result is null, that table is using the system-wide (Global) configuration.

Query 3: To view actual thresholds and current table status, using this query will pre-calculate actual trigger thresholds based on current rows of each table and compare with actual dead_tuples or modifications to know which table is about to be / currently being processed by Autovacuum

Query 4: Use this query to check if there are Table bloat issues

• Pay attention to the details of dead_tuple_percent and free_space_percent
• If dead_tuple_percent is too high, it means Auto Vacuum is not operating effectively and has not reclaimed Dead Tuples well
• Regarding free_space_percent
  • If high (such as above 20%), it means that inside pages (8KB), there is a lot of empty space not containing actual data (usually caused by DELETE/UPDATE of old rows, where Autovacuum cleaned up Dead Tuples later but could not return disk capacity to OS), causing the following issues:
    • Disk capacity waste: Table occupies a much larger space than actual data.
    • Reduced query performance (Slow I/O & RAM): When querying (for example Seq Scan), PostgreSQL still has to read all disk pages (including empty portions) into RAM (shared_buffers). Reading "half-empty" pages wastes RAM and increases disk I/O.
    • Associated index bloat: Indexes on this table will also bloat.
  • If low (below 20%), it means pages are almost completely filled with Live Tuples. Disk capacity is being used very efficiently, optimized for RAM memory and disk I/O

Happy coding!

See more articles here.