PostgreSQL Visibility Map Explained

The visibility map in PostgreSQL is a crucial mechanism that tracks whether table pages contain only tuples (rows) visible to all transactions. This tracking is essential for enabling efficient Index Only Scans, potentially eliminating the need to consult the heap (table) during query execution.

Why Visibility Map Matters

The visibility map serves two primary purposes:

Enables Index Only Scans by confirming tuple visibility without heap access
Prevents unnecessary heap lookups when all tuples are known to be visible

When PostgreSQL can't rely on the visibility map (because it's stale), it falls back to regular Index Scans, which are typically slower due to required heap access.

How Visibility Map Works

Maintenance Process

The visibility map is maintained through two primary methods:

Automatic updates via autovacuum
Manual updates using the VACUUM command

When a page is vacuumed, PostgreSQL checks if:

All tuples on the page are visible to all transactions
No deleted or recently updated rows require additional checks

If these conditions are met, the page is marked as "all-visible" in the visibility map.

Common Scenarios That Stale the Map

Row modifications:
- Updates
- Deletes
- New inserts
System issues:
- Disabled or delayed autovacuum
- High-traffic tables with frequent changes

VACUUM Operations

Basic VACUUM

-- Vacuum a specific table
VACUUM employees;

-- Vacuum all tables
VACUUM;

VACUUM FULL

-- Complete table rewrite
VACUUM FULL employees;

Note: VACUUM FULL locks the table and requires significant resources. Use with caution in production environments.

VACUUM with ANALYZE

-- Update visibility map and gather statistics
VACUUM ANALYZE employees;

Optimization Best Practices

1. Autovacuum Configuration

Check autovacuum status:

-- Check if autovacuum is enabled
-- Returns 'on' if enabled, 'off' if disabled
SHOW autovacuum;

Optimize autovacuum settings in postgresql.conf:

# Increase processing limit
# Controls how much work autovacuum can do before sleeping
# Higher values mean more aggressive cleaning but more system load
# Default is typically 200
autovacuum_vacuum_cost_limit = 2000

# Reduce processing delay
# Time autovacuum sleeps between operations
# Lower values mean more frequent cleaning but higher system load
# Default is typically 20ms
autovacuum_vacuum_cost_delay = 10ms

# Optional: Control when autovacuum triggers
# Triggers when table size + dead tuples exceeds this percentage
# Default is 0.2 (20%)
autovacuum_vacuum_scale_factor = 0.1

# Optional: Minimum number of dead tuples before vacuum
# Default is 50
autovacuum_vacuum_threshold = 100

2. Monitoring and Maintenance

Regular maintenance tasks:

Monitor autovacuum activity:

-- Check last vacuum and autovacuum times for all user tables
-- relname: table name
-- last_vacuum: last manual vacuum time
-- last_autovacuum: last automatic vacuum time
-- n_dead_tup: current number of dead tuples
-- autovacuum_count: number of times autovacuumed
SELECT
    relname,
    last_vacuum,
    last_autovacuum,
    n_dead_tup,
    autovacuum_count
FROM pg_stat_user_tables;

-- Check currently running vacuum operations
SELECT pid, datname, usename, query
FROM pg_stat_activity
WHERE query LIKE '%vacuum%';

-- Monitor vacuum progress (PostgreSQL 9.6+)
SELECT * FROM pg_stat_progress_vacuum;

Schedule manual VACUUM during off-peak hours:

-- Add random delay to prevent multiple vacuums starting simultaneously
-- pg_sleep takes seconds as argument (random 0-5 minutes)
SELECT pg_sleep(random() * 300);

-- Vacuum and analyze table
-- ANALYZE updates statistics used by query planner
-- VERBOSE provides detailed progress information
VACUUM ANALYZE VERBOSE high_traffic_table;

-- Optional: Set statement timeout for vacuum
-- Prevents vacuum from running too long
SET statement_timeout = '2h';
VACUUM ANALYZE high_traffic_table;
RESET statement_timeout;

3. Table Optimization Strategies

Batch updates and deletes when possible
Regular cleanup of dead tuples
Monitor table bloat
- Table bloat in PostgreSQL refers to the wasted space within a table or index caused by the accumulation of dead tuples (rows that are no longer needed but still occupy storage). This typically occurs as a result of frequent UPDATE and DELETE operations because PostgreSQL uses a Multi-Version Concurrency Control (MVCC) approach. Instead of modifying or deleting rows in-place, PostgreSQL creates new versions of rows for updates and marks old versions as dead.
- Over time, if these dead tuples are not cleaned up, they lead to bloat, increasing the size of the table and degrading performance.

Impact on Query Performance

Without Updated Visibility Map

EXPLAIN ANALYZE
SELECT indexed_column FROM table WHERE condition;

-- Output might show:
Index Scan using idx_name on table
  Heap Blocks: actual=100
  -- Heap Blocks > 0 indicates PostgreSQL needed to check the heap (table)
  -- to verify tuple visibility because visibility map was not up to date

With Updated Visibility Map

-- After VACUUM
EXPLAIN ANALYZE
SELECT indexed_column FROM table WHERE condition;

-- Output might show:
Index Only Scan using idx_name on table
  Heap Fetches: 0
  -- Heap Fetches = 0 indicates:
  -- 1. All required data was found in the index
  -- 2. Visibility map confirms all matching tuples are visible
  -- 3. No need to check the heap (table) for visibility
  -- This is the optimal scenario, enabled by an up-to-date visibility map

Note: Zero heap fetches means PostgreSQL was able to satisfy the query entirely from the index without checking the table data. This is only possible when:
The visibility map confirms all relevant pages are "all-visible"
The query only requests columns included in the index
No recent modifications (updates/deletes) have occurred on the relevant pages

Conclusion

An up-to-date visibility map is crucial for:

Enabling efficient Index Only Scans
Reducing I/O operations
Improving query performance

Regular maintenance through proper VACUUM operations ensures optimal database performance. Whether through automated processes or manual intervention, keeping the visibility map current should be a priority in your PostgreSQL maintenance strategy.