# Remove the table-level autovacuum override and restore default autovacuum behavior.
psql --port=5432 --username=postgres -d creditcards -c "
ALTER TABLE consumer.kyc RESET (
    autovacuum_enabled
);
"

# Remove the table-level autovacuum override and restore default autovacuum behavior.
psql --port=5432 --username=postgres -d creditcards -c "
ALTER TABLE consumer.kyc RESET (
    autovacuum_enabled
);
"

# Verify that no table-level autovacuum override exists.
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    relname,
    reloptions
FROM pg_class
WHERE relname = 'kyc';
"

# Verify that no table-level autovacuum override exists.
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    relname,
    reloptions
FROM pg_class
WHERE relname = 'kyc';
"

The table no longer has a custom autovacuum setting and will inherit the cluster-wide autovacuum configuration.

# Display all autovacuum-related configuration parameters.
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    name,
    setting,
    unit,
    source
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;
"

# Display all autovacuum-related configuration parameters.
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    name,
    setting,
    unit,
    source
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;
"

PostgreSQL ships with autovacuum enabled by default. These parameters determine when maintenance occurs, how frequently workers run, and when VACUUM or ANALYZE operations are triggered. We will explore each of these settings throughout this chapter.

5.2 Architecture Overview

A simple diagram :

                    Autovacuum Launcher
                              │
                              │
                 Wakes up every autovacuum_naptime
                           (Default: 60s)
                              │
                              ▼
            Scans statistics and identifies tables
                   requiring maintenance
                              │
                              ▼
         ┌─────────────────────────────────────┐
         │     Autovacuum Worker Pool          │
         │     (Default: 3 Workers)            │
         └─────────────────────────────────────┘
                 │           │           │
                 ▼           ▼           ▼
             Worker 1    Worker 2    Worker 3
                 │           │           │
                 ▼           ▼           ▼
          VACUUM/ANALYZE VACUUM/ANALYZE VACUUM/ANALYZE
                 │           │           │
                 └──────► Database Tables ◄──────┘

• PostgreSQL uses a launcher process and one or more worker processes.
• The launcher does not vacuum tables itself.
• The launcher identifies tables requiring maintenance.
• Workers perform the actual VACUUM and ANALYZE operations.

# Display autovacuum-related background processes.
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    pid,
    backend_type,
    state
FROM pg_stat_activity
WHERE backend_type LIKE 'autovacuum%';
"

# Display autovacuum-related background processes.
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    pid,
    backend_type,
    state
FROM pg_stat_activity
WHERE backend_type LIKE 'autovacuum%';
"

Depending on activity you may also see:

 pid  |      backend_type      | state
------+------------------------+-------
 7944 | autovacuum launcher    |
 5148 | autovacuum worker      | active

Launcher Process

The launcher is a dedicated PostgreSQL background process responsible for:

• waking up periodically
• checking table statistics
• identifying tables requiring maintenance
• assigning work to available workers

The launcher never performs VACUUM itself.

Its job is scheduling.

# Display how frequently the launcher wakes up.
psql --port=5432 --username=postgres -d creditcards -c "
SHOW autovacuum_naptime;
"

# Display how frequently the launcher wakes up.
psql --port=5432 --username=postgres -d creditcards -c "
SHOW autovacuum_naptime;
"

By default, the launcher wakes up every 60 seconds and evaluates whether any tables require maintenance.

# Display the autovacuum launcher process.
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    pid,
    backend_type
FROM pg_stat_activity
WHERE backend_type = 'autovacuum launcher';
"

# Display the autovacuum launcher process.
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    pid,
    backend_type
FROM pg_stat_activity
WHERE backend_type = 'autovacuum launcher';
"

Worker Processes

Worker processes perform the actual maintenance work:

• VACUUM
• ANALYZE
• FREEZE operations

Workers are created by the launcher when maintenance is required.

# Display the maximum number of autovacuum workers.
psql --port=5432 --username=postgres -d creditcards -c "
SHOW autovacuum_max_workers;
"

# Display the maximum number of autovacuum workers.
psql --port=5432 --username=postgres -d creditcards -c "
SHOW autovacuum_max_workers;
"

By default, PostgreSQL can run up to three autovacuum workers simultaneously.

Key Takeaways

• Autovacuum consists of a launcher and worker processes.
• The launcher identifies tables requiring maintenance.
• Workers perform VACUUM and ANALYZE operations.
• autovacuum_naptime controls how often the launcher checks tables.
• autovacuum_max_workers controls how many workers can run simultaneously.

5.3 Maintenance Triggers and Statistics Refresh

Autovacuum does not continuously vacuum every table in the database.

Instead, PostgreSQL periodically evaluates whether a table requires maintenance by checking two independent triggers:

Understanding these triggers is important because they determine when PostgreSQL decides to perform maintenance automatically.

5.3.1 Verify Current Autovacuum Settings

Before demonstrating the trigger calculations, let’s review the current configuration.

# Verify the current autovaccum settings
psql --port=5432 --username=postgres -d creditcards -x -c "
SELECT
    current_setting('autovacuum') AS autovacuum,
    current_setting('autovacuum_vacuum_threshold') AS vacuum_threshold,
    current_setting('autovacuum_vacuum_scale_factor') AS vacuum_scale_factor,
    current_setting('autovacuum_analyze_threshold') AS analyze_threshold,
    current_setting('autovacuum_analyze_scale_factor') AS analyze_scale_factor,
    current_setting('autovacuum_naptime') AS autovacuum_naptime;
"

# Verify the current autovaccum settings
psql --port=5432 --username=postgres -d creditcards -x -c "
SELECT
    current_setting('autovacuum') AS autovacuum,
    current_setting('autovacuum_vacuum_threshold') AS vacuum_threshold,
    current_setting('autovacuum_vacuum_scale_factor') AS vacuum_scale_factor,
    current_setting('autovacuum_analyze_threshold') AS analyze_threshold,
    current_setting('autovacuum_analyze_scale_factor') AS analyze_scale_factor,
    current_setting('autovacuum_naptime') AS autovacuum_naptime;
"

-[ RECORD 1 ]--------+-----
autovacuum           | on
vacuum_threshold     | 50
vacuum_scale_factor  | 0.2
analyze_threshold    | 50
analyze_scale_factor | 0.1
autovacuum_naptime   | 1min

5.3.2 Understanding the Trigger Formulas

PostgreSQL uses separate formulas for AUTOANALYZE and AUTOVACUUM.

AUTOANALYZE Trigger

Analyze Trigger = 
autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor × Number of Rows)

Default values:

autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.10

AUTOVACUUM Trigger

Vacuum Trigger =
autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × Number of Rows)

Default values:

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.20

Notice that the default ANALYZE scale factor is 10%, while the default VACUUM scale factor is 20%.

This means AUTOANALYZE typically becomes eligible before AUTOVACUUM.

5.3.3 Example Trigger Calculations

The following table shows how PostgreSQL calculates maintenance thresholds as table size increases.

As tables become larger, PostgreSQL allows more changes before maintenance is triggered.

5.3.4 Capture Baseline

# Capture the baseline:
psql --port=5432 --username=postgres -d creditcards -x -c "
SELECT
    schemaname,
    relname,
    n_live_tup AS total_rows,
    n_dead_tup AS dead_rows,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname='kyc'
AND schemaname='consumer';
"

# Capture the baseline:
psql --port=5432 --username=postgres -d creditcards -x -c "
SELECT
    schemaname,
    relname,
    n_live_tup AS total_rows,
    n_dead_tup AS dead_rows,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname='kyc'
AND schemaname='consumer';
"

-[ RECORD 1 ]----+------------------------------
schemaname       | consumer
relname          | kyc
total_rows       | 20000
dead_rows        | 0
last_vacuum      |                               <-- Manually run via 'VACUUM;'
last_autovacuum  | 2026-05-30 11:57:00.717263+00 <-- Last automatic VACUUM
last_analyze     |                               <-- Manually run via 'ANALYZE;'
last_autoanalyze | 2026-05-30 11:57:02.462312+00 <-- Last automatic ANALYZE

Note down the last_autovacuum and last_autoanalyze timings.

5.3.5 Calculate Maintenance Thresholds for consumer.kyc

Using the formulas above:

AUTOANALYZE

autovacuum_analyze_threshold +  (autovacuum_analyze_scale_factor × Number of Rows)
= 50 + (0.10 × 20,000)
= 50 + 2,000
= 2,050

AUTOANALYZE becomes eligible after approximately 2,050 changed rows.

AUTOVACUUM

autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × Number of Rows)
= 50 + (0.20 × 20,000)
= 50 + 4,000
= 4,050

AUTOVACUUM becomes eligible after approximately 4,050 dead tuples.

At this point we know:

5.3.6 Demo — Trigger AUTOANALYZE

To demonstrate AUTOANALYZE, delete approximately 15% of the table.

# Delete 3k records - 15% of the table entries. 
psql --port=5432 --username=postgres -d creditcards -c "
DELETE FROM consumer.kyc
WHERE kyc_id IN (
    SELECT kyc_id
    FROM consumer.kyc
    ORDER BY kyc_id
    LIMIT 3000
);
"

# Delete 3k records - 15% of the table entries. 
psql --port=5432 --username=postgres -d creditcards -c "
DELETE FROM consumer.kyc
WHERE kyc_id IN (
    SELECT kyc_id
    FROM consumer.kyc
    ORDER BY kyc_id
    LIMIT 3000
);
"

DELETE 3000

Compare against the trigger values:

AUTOANALYZE Trigger = 2,050
Dead Tuples         = 3,000

3,000 > 2,050

AUTOANALYZE becomes eligible.

After 1 min of time, please check the baseline : to see autoanalyze has been triggered.

# Capture the baseline:
psql --port=5432 --username=postgres -d creditcards -x -c "
SELECT
    schemaname,
    relname,
    n_live_tup AS total_rows,
    n_dead_tup AS dead_rows,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname='kyc'
AND schemaname='consumer';
"

# Capture the baseline:
psql --port=5432 --username=postgres -d creditcards -x -c "
SELECT
    schemaname,
    relname,
    n_live_tup AS total_rows,
    n_dead_tup AS dead_rows,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname='kyc'
AND schemaname='consumer';
"

-[ RECORD 1 ]----+------------------------------
schemaname       | consumer
relname          | kyc
total_rows       | 17000
dead_rows        | 3000
last_vacuum      |
last_autovacuum  | 2026-05-30 11:57:00.717263+00
last_analyze     |
last_autoanalyze | 2026-05-30 16:47:27.203331+00 <-- Last automatic ANALYZE

However: autovaccum is not trigerred because , the trigger has still not met the condition.

AUTOVACUUM Trigger = 4,050
Dead Tuples        = 3,000

Result:

3,000 < 4,050

AUTOVACUUM has not yet reached its trigger point.

What This Demonstrates

The table has changed enough to require refreshed statistics, but not enough dead tuples to require vacuum cleanup.

This explains why AUTOANALYZE typically runs more frequently than AUTOVACUUM.

AUTOANALYZE outcome: Updates planner statistics (pg_statistic) so PostgreSQL can choose more accurate execution plans.

5.3.6 Demo — Trigger AUTOVACUUM

After the first delete, approximately 17,000 live rows remain.
The vacuum threshold is recalculated using the current table size.

Recalculate the Vacuum Trigger

Rows Remaining = 17,000

autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × Number of Rows)
= 50 + (0.20 × 17,000)
= 50 + 3,400
= 3,450

The new AUTOVACUUM trigger becomes:

3,450 dead tuples

At this stage the table already contains:

3,000 dead tuples

Delete an additional 500 rows.

# Delete 500 records 
psql --port=5432 --username=postgres -d creditcards -c "
DELETE FROM consumer.kyc
WHERE kyc_id IN (
    SELECT kyc_id
    FROM consumer.kyc
    ORDER BY kyc_id
    LIMIT 500
);
"

# Delete 500 records 
psql --port=5432 --username=postgres -d creditcards -c "
DELETE FROM consumer.kyc
WHERE kyc_id IN (
    SELECT kyc_id
    FROM consumer.kyc
    ORDER BY kyc_id
    LIMIT 500
);
"

Total dead tuples now become approximately:

3,000 + 500
= 3,500 dead tuples

Compare against the recalculated vacuum threshold:

3,500 > 3,450

AUTOVACUUM now becomes eligible.

# Capture the baseline:
psql --port=5432 --username=postgres -d creditcards -x -c "
SELECT
    schemaname,
    relname,
    n_live_tup AS total_rows,
    n_dead_tup AS dead_rows,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname='kyc'
AND schemaname='consumer';
"

# Capture the baseline:
psql --port=5432 --username=postgres -d creditcards -x -c "
SELECT
    schemaname,
    relname,
    n_live_tup AS total_rows,
    n_dead_tup AS dead_rows,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname='kyc'
AND schemaname='consumer';
"

-[ RECORD 1 ]----+------------------------------
schemaname       | consumer
relname          | kyc
total_rows       | 14057
dead_rows        | 0
last_vacuum      |
last_autovacuum  | 2026-05-30 17:52:28.392799+00 <-- Last automatic VACUUM
last_analyze     |
last_autoanalyze | 2026-05-30 16:47:27.203331+00

Key Takeaways

✅ PostgreSQL maintains separate triggers for AUTOANALYZE and AUTOVACUUM.

✅ AUTOANALYZE uses a default scale factor of 10%.

✅ AUTOVACUUM uses a default scale factor of 20%.

✅ On a 20,000-row table, AUTOANALYZE becomes eligible at approximately 2,050 row changes.

✅ After deleting 3,000 rows, AUTOANALYZE becomes eligible.

✅ After the table shrinks to 17,000 rows, the vacuum trigger drops to approximately 3,450 dead tuples.

✅ Deleting an additional 500 rows increases dead tuples to approximately 3,450, causing AUTOVACUUM to become eligible.

This demonstrates why PostgreSQL often refreshes statistics before performing dead tuple cleanup and how maintenance thresholds dynamically adjust as table size changes.

5.4 Monitoring Autovacuum Health

Autovacuum is not something you should just assume is working in the background.

That evidence comes from PostgreSQL’s statistics views and logs. These tell you whether dead tuples are being cleaned, whether autovacuum is running on time, whether planner statistics are fresh, and whether anything is silently going wrong.

Key Takeaways

✅ pg_stat_user_tables shows whether tables are accumulating dead tuples.
✅ pg_stat_progress_vacuum shows vacuum activity while it is happening.
✅ last_autovacuum and last_autoanalyze help you spot stale maintenance.
✅ Logs can confirm whether autovacuum is running, how long it took, and what it cleaned.
⚠️ Rising dead tuples with old timestamps usually mean something is off.
❌ If autovacuum is blocked or delayed too long, bloat starts building up.

5.4.1 Monitoring Autovacuum Activity

# Connect to the database you want to monitor.
psql --port=5432 --username=postgres --dbname=<database_name>

# Connect to the database you want to monitor.
psql --port=5432 --username=postgres --dbname=<database_name>

replace the port 5432 and replace the <database_name> – accordingly and use below sql commands in respective database in psql prompt

-- Show autovacuum and autoanalyze status for all user tables in the current database.
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n_dead_tup DESC;

-- Show autovacuum and autoanalyze status for all user tables in the current database.
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n_dead_tup DESC;

-- Show all currently running vacuum workers in the current database.
SELECT
    pid,
    relid::regclass AS table_name,
    phase,
    heap_blks_scanned,
    heap_blks_total
FROM pg_stat_progress_vacuum;

-- Show all currently running vacuum workers in the current database.
SELECT
    pid,
    relid::regclass AS table_name,
    phase,
    heap_blks_scanned,
    heap_blks_total
FROM pg_stat_progress_vacuum;

-- Show the most recently autoanalyzed tables in the current database.
SELECT
    schemaname,
    relname AS table_name,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY last_autoanalyze DESC NULLS LAST;

-- Show the most recently autoanalyzed tables in the current database.
SELECT
    schemaname,
    relname AS table_name,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY last_autoanalyze DESC NULLS LAST;

-- Show the tables with the highest dead tuple counts in the current database.
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Show the tables with the highest dead tuple counts in the current database.
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS live_tuples,
    n_dead_tup AS dead_tuples,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n_dead_tup DESC
LIMIT 20;

5.4.2 Monitoring Current Vacuum Workers

5.4.2.1 Why Monitor Vacuum Workers?

• pg_stat_user_tables shows historical statistics.
• Sometimes you need real-time visibility.
• PostgreSQL provides pg_stat_progress_vacuum for active vacuum operations.

✅ Use this view when you want to know whether VACUUM or AUTOVACUUM is currently running.

5.4.2.2 View Active Vacuum Operations

-- Show all currently running vacuum workers in the current database.
SELECT
    pid,
    relid::regclass AS table_name,
    phase,
    heap_blks_scanned,
    heap_blks_total
FROM pg_stat_progress_vacuum;

-- Show all currently running vacuum workers in the current database.
SELECT
    pid,
    relid::regclass AS table_name,
    phase,
    heap_blks_scanned,
    heap_blks_total
FROM pg_stat_progress_vacuum;

5.4.2.3 Understanding the Output

The information returned by pg_stat_progress_vacuum helps us understand what PostgreSQL is currently doing during a vacuum operation.

Sample Output

 pid  |     table_name     |      phase       | heap_blks_scanned | heap_blks_total
------+--------------------+------------------+-------------------+----------------
 5432 | consumer.kyc       | scanning heap    | 12000             | 17000

Column Descriptions

Example Interpretation

In the example above:

✅ Vacuum worker process 5432 is currently active.

✅ The worker is processing table consumer.kyc.

✅ PostgreSQL is currently in the Scanning Heap phase.

✅ 12,000 of 17,000 table blocks have already been scanned.

This indicates the vacuum operation is actively progressing.

Production Note

⚠️ Large tables may remain visible in this view for several minutes.

⚠️ Small tables may finish so quickly that no rows appear when the query is executed.

5.4.2.4 Understanding Vacuum Phases

Initializing
      ↓
Scanning Heap
      ↓
Vacuuming Indexes
      ↓
Cleaning Up Indexes
      ↓
Truncating Heap
      ↓
Complete

5.5 Configuration and Tuning

💡 There is no one-size-fits-all autovacuum configuration.

Effective autovacuum tuning begins with understanding:

• How frequently tables are updated or deleted
• Which tables experience the highest transaction volume
• How quickly dead tuples accumulate
• Query performance requirements
• Maintenance windows and resource constraints

5.5.1 Where Should Autovacuum Configuration Be Applied?

PostgreSQL allows autovacuum settings to be configured at multiple levels.

In practice, most environments use a combination of:

💡 In large production environments, global settings provide a baseline, while heavily updated tables are typically tuned individually using per-table autovacuum settings.

Recommended Approach

A common operational strategy is:

1. Define sensible cluster-wide defaults.
2. Observe workload patterns.
3. Identify high-churn tables.
4. Apply targeted per-table tuning where needed.

This approach avoids unnecessary complexity while allowing critical tables to be maintained more aggressively.

5.5.2 Modular Configuration with 01-autovacuum.conf

PostgreSQL allows configuration settings to be organized across multiple files. In this lab, all autovacuum-related parameters are maintained in a dedicated file named 01-autovacuum.conf.

Using a separate configuration file keeps vacuum settings isolated from other areas such as logging, auditing, replication, and backup configuration. It also makes future tuning, troubleshooting, and change management significantly easier.

For demonstration purposes, the file is initially created as an empty placeholder:

# Create an empty autovacuum configuration file
echo -e "" > /var/lib/pgsql/onboarding/conf.d/01-autovacuum.conf

# Create an empty autovacuum configuration file
echo -e "" > /var/lib/pgsql/onboarding/conf.d/01-autovacuum.conf

5.5.3 Reviewing Current Autovacuum Settings

Before modifying autovacuum behavior, it is useful to understand the current configuration. PostgreSQL exposes all runtime parameters through the pg_settings view, allowing administrators to verify active values and determine where settings originated.

This is particularly helpful in environments that use multiple configuration files, as it confirms whether a parameter is coming from a custom file or from PostgreSQL defaults.

View All Autovacuum Parameters

# Display all autovacuum-related parameters and their active values
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    name,
    setting,
    unit,
    source,
    sourcefile,
    sourceline
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;
"

# Display all autovacuum-related parameters and their active values
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    name,
    setting,
    unit,
    source,
    sourcefile,
    sourceline
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;
"

                 name                  |  setting  | unit | source  | sourcefile | sourceline
---------------------------------------+-----------+------+---------+------------+------------
 autovacuum                            | on        |      | default |            |
 autovacuum_analyze_scale_factor       | 0.1       |      | default |            |
 autovacuum_analyze_threshold          | 50        |      | default |            |
 autovacuum_freeze_max_age             | 200000000 |      | default |            |
 autovacuum_max_workers                | 3         |      | default |            |
 autovacuum_multixact_freeze_max_age   | 400000000 |      | default |            |
 autovacuum_naptime                    | 60        | s    | default |            |
 autovacuum_vacuum_cost_delay          | 2         | ms   | default |            |
 autovacuum_vacuum_cost_limit          | -1        |      | default |            |
 autovacuum_vacuum_insert_scale_factor | 0.2       |      | default |            |
 autovacuum_vacuum_insert_threshold    | 1000      |      | default |            |
 autovacuum_vacuum_scale_factor        | 0.2       |      | default |            |
 autovacuum_vacuum_threshold           | 50        |      | default |            |
 autovacuum_work_mem                   | -1        | kB   | default |            |
(14 rows)

Why This Matters

Whenever configuration changes are made, this query should be executed again to verify that PostgreSQL has successfully loaded the new values.

It also helps identify situations where a parameter is still using the built-in default rather than a custom setting defined in a configuration file.

💡 Always verify active settings from PostgreSQL itself rather than assuming a configuration file has been loaded correctly.

5.5.4 Understanding the Most Important Autovacuum Parameters

PostgreSQL exposes many autovacuum-related settings, but only a handful are commonly reviewed and tuned during day-to-day administration.

The following parameters have the greatest impact on autovacuum behavior:

Key Takeaways

✅ Most production tuning focuses on thresholds and scale factors.

✅ Lower scale factors cause maintenance to occur more frequently.

✅ autovacuum_max_workers becomes important as database size and table count grow.

✅ Always tune based on observed workload patterns rather than fixed formulas.

5.5.5 Building a Lab-Friendly Autovacuum Configuration

The default PostgreSQL settings are designed to work across a wide range of environments. For demonstration purposes, however, we want autovacuum activity to occur more frequently so that its behavior can be observed within a reasonable timeframe.

The following settings provide a lab-friendly configuration while remaining easy to understand.

Update 01-autovacuum.conf

# Configure lab-friendly autovacuum settings
echo -e "autovacuum = on
autovacuum_naptime = '30s'
autovacuum_max_workers = 5
autovacuum_vacuum_scale_factor = 0.10
autovacuum_vacuum_threshold = 50
autovacuum_analyze_scale_factor = 0.10
autovacuum_analyze_threshold = 50" >> /var/lib/pgsql/onboarding/conf.d/01-autovacuum.conf

# Configure lab-friendly autovacuum settings
echo -e "autovacuum = on
autovacuum_naptime = '30s'
autovacuum_max_workers = 5
autovacuum_vacuum_scale_factor = 0.10
autovacuum_vacuum_threshold = 50
autovacuum_analyze_scale_factor = 0.10
autovacuum_analyze_threshold = 50" >> /var/lib/pgsql/onboarding/conf.d/01-autovacuum.conf

# Reload configuration without restarting PostgreSQL
psql --port=5432 --username=postgres -d postgres -c "
SELECT pg_reload_conf();
"

# Reload configuration without restarting PostgreSQL
psql --port=5432 --username=postgres -d postgres -c "
SELECT pg_reload_conf();
"

Verify Active Settings

# Visualization: show only autovacuum-related parameters, along with the config file and line number they came from
psql --port=5432 --username=postgres -d postgres -c "
SELECT
    name,
    setting,
    unit,
    sourcefile,
    sourceline
FROM pg_settings
WHERE name LIKE 'autovacuum%'
  AND sourcefile IS NOT NULL
ORDER BY sourcefile, sourceline, name;"

# Verify that PostgreSQL loaded the updated values
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    name,
    setting,
    source
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;"

# Visualization: show only autovacuum-related parameters, along with the config file and line number they came from
psql --port=5432 --username=postgres -d postgres -c "
SELECT
    name,
    setting,
    unit,
    sourcefile,
    sourceline
FROM pg_settings
WHERE name LIKE 'autovacuum%'
  AND sourcefile IS NOT NULL
ORDER BY sourcefile, sourceline, name;"

# Verify that PostgreSQL loaded the updated values
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    name,
    setting,
    source
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;"

💡 These values are intended for demonstration purposes. Production systems should be tuned according to actual workload characteristics rather than copied directly from a lab environment.

5.5.6 Per-Table Tuning

While cluster-wide settings provide a sensible baseline, production databases often contain tables with very different workload patterns.

For example, consider the consumer.kyc table in our lab.

In a banking environment, a KYC (Know Your Customer) table may experience:

• Frequent customer profile updates
• Address changes
• Contact information updates
• Compliance status modifications
• Periodic risk assessment updates
• Document verification workflows

As a result, rows may be updated many times throughout their lifecycle, generating a steady stream of dead tuples.

Using the same autovacuum settings for both a heavily updated KYC table and a rarely modified reference table may not be optimal. PostgreSQL allows autovacuum behavior to be adjusted at the individual table level, enabling maintenance to occur more aggressively where needed.

View Existing Table-Level Settings

# Check whether any table-specific autovacuum settings exist
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    relname,
    reloptions
FROM pg_class
WHERE relname = 'kyc';
"

# Check whether any table-specific autovacuum settings exist
psql --port=5432 --username=postgres -d creditcards -c "
SELECT
    relname,
    reloptions
FROM pg_class
WHERE relname = 'kyc';
"

 relname | reloptions
---------+------------
 kyc     |
(1 row)

Configure Table-Specific Autovacuum Settings

The following example reduces the vacuum and analyze scale factors for the consumer.kyc table:

# Apply table-specific autovacuum settings
psql --port=5432 --username=postgres -d creditcards -c "
ALTER TABLE consumer.kyc SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 50,
    autovacuum_analyze_scale_factor = 0.05,
    autovacuum_analyze_threshold = 50
);
"

# Apply table-specific autovacuum settings
psql --port=5432 --username=postgres -d creditcards -c "
ALTER TABLE consumer.kyc SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 50,
    autovacuum_analyze_scale_factor = 0.05,
    autovacuum_analyze_threshold = 50
);
"

💡 Notice that we use 5% rather than an extremely aggressive value such as 2%.

The objective is not to trigger autovacuum as frequently as possible. The objective is to balance timely cleanup against maintenance overhead.

Lower scale factors result in:

✅ Earlier dead tuple cleanup

✅ More frequent statistics updates

❌ More autovacuum activity

❌ Increased CPU and I/O consumption

Verify Table-Level Settings

# Display table-specific autovacuum settings
psql --port=5432 --username=postgres -d creditcards -c "
SELECT 
    relname, 
    unnest(reloptions) AS individual_setting
FROM pg_class 
WHERE relname = 'kyc';
"

# Display table-specific autovacuum settings
psql --port=5432 --username=postgres -d creditcards -c "
SELECT 
    relname, 
    unnest(reloptions) AS individual_setting
FROM pg_class 
WHERE relname = 'kyc';
"

 relname |          individual_setting
---------+--------------------------------------
 kyc     | autovacuum_vacuum_scale_factor=0.05
 kyc     | autovacuum_vacuum_threshold=50
 kyc     | autovacuum_analyze_scale_factor=0.05
 kyc     | autovacuum_analyze_threshold=50
(4 rows)

Production Considerations

⚠️ Per-table tuning should be driven by observed workload patterns, not by applying aggressive settings everywhere.

A common production approach is:

1. Start with cluster-wide defaults.
2. Monitor dead tuple growth.
3. Identify high-churn tables.
4. Apply targeted tuning where necessary.
5. Re-evaluate based on observed vacuum activity.

This keeps maintenance predictable while avoiding unnecessary overhead on less active tables.

Chapter Summary

At this point we have covered:

• How Autovacuum works
• When VACUUM and ANALYZE are triggered
• How to monitor autovacuum activity
• How to tune autovacuum globally
• How to tune autovacuum at the table level

6.1 Why VACUUM Does More Than Remove Dead Tuples

Most PostgreSQL administrators first encounter VACUUM while dealing with table bloat.

As we learned in previous chapters, VACUUM helps PostgreSQL:

• Remove dead tuples created by UPDATE and DELETE operations
• Reclaim space for future reuse
• Maintain healthy table and index performance
• Support query planning through updated statistics

Because of this, many people think VACUUM is simply a cleanup tool.
In reality, VACUUM has another responsibility that is even more important:

Protecting the database from Transaction ID Wraparound.

• If PostgreSQL cannot properly manage transaction IDs, the database may eventually reach a state where it refuses new write operations to protect data integrity.
• This means VACUUM is not just a performance-maintenance tool.
• It is also a critical mechanism that keeps the database operational.

VACUUM Has Two Jobs

Throughout this chapter, think of VACUUM as performing two separate responsibilities:

A Quick Preview

Every transaction in PostgreSQL receives a unique identifier called a Transaction ID (XID).

Let’s look at the current transaction ID:

# Check the current TXN ID 
psql --port=5432 --username=postgres -d postgres -c "
SELECT txid_current();
"

# Check the current TXN ID 
psql --port=5432 --username=postgres -d postgres -c "
SELECT txid_current();
"

[postgres@sandbox ~]$ # Check the current TXN ID
psql --port=5432 --username=postgres -d postgres -c "
SELECT txid_current();
"
 txid_current
--------------
          950
(1 row)

🔍 Key Observation

• Every transaction consumes a Transaction ID (XID).
• Transaction IDs continuously increase over time.

⚠️ Potential Risk

• If old Transaction IDs are not properly managed, PostgreSQL can eventually stop accepting new write operations.

✅ Why VACUUM Matters

• Removes dead tuples created by MVCC.
• Helps prevent Transaction ID Wraparound.
• Keeps the database healthy and operational.