Database Maintenance Routines

Database maintenance is critical for ensuring the long-term performance, reliability, and integrity

of your PostgreSQL databases. Regular maintenance prevents performance degradation, reduces the risk

of data corruption, and helps maintain optimal query execution times as your data grows.

In this lesson, we'll explore essential PostgreSQL maintenance tasks, best practices for scheduling them,

and automation strategies to ensure your databases remain in peak condition with minimal manual intervention.

Without regular maintenance, PostgreSQL databases can experience:

Gradually declining query performance

Inefficient query planning due to outdated statistics

Increasing storage usage from table and index bloat

Higher risk of transaction ID wraparound

Potential data corruption or loss scenarios

Unexpected downtime during critical operations

VACUUM Operations

Statistics Maintenance

Index Maintenance

Maintenance Automation

VACUUM is one of PostgreSQL's most important maintenance operations. It reclaims storage occupied by dead tuples,

updates visibility maps, and prevents transaction ID wraparound. Understanding how and when to run different types

of VACUUM operations is essential for database administrators.

How VACUUM Works

When rows in PostgreSQL are deleted or updated (which creates a new row version), the old versions are

marked as dead tuples but aren't immediately removed from storage. These dead tuples continue to occupy

space until a VACUUM operation processes them.

VACUUM Process

Mark dead rows

— Identifies tuples that are no longer visible to any active transaction

Update free space map

— Records which pages have space available for reuse

Update visibility map

— Tracks which pages contain only visible tuples

Make space available for reuse

— Space is marked as available but files don't shrink

Freeze old row versions

— Prevents transaction ID wraparound

PostgreSQL offers several VACUUM variants with different characteristics and purposes:

VACUUM Type

What it Does

When to Use

Performance Impact

Basic cleanup of dead tuples; space is marked as available for reuse

Regular maintenance; can run concurrently with normal operations

Low to moderate; doesn't lock tables for long periods

VACUUM FULL

Rewrites the entire table, actually shrinking the file on disk; more aggressive cleanup

When table has significant bloat and you need to reclaim disk space

High; requires exclusive lock (blocks all operations) on the table

VACUUM ANALYZE

Combines VACUUM with statistics collection for query planner

After bulk data changes or when query plans seem suboptimal

Moderate; includes additional analysis overhead

VACUUM FREEZE

Aggressively freezes old tuples to prevent transaction ID wraparound

When approaching transaction ID wraparound; preventative maintenance

Moderate to high; more intensive than regular VACUUM

Automated daemon that runs VACUUM and ANALYZE based on table activity

Always enabled for continuous, automatic maintenance

Low to moderate; tunable to minimize impact

VACUUM Best Practices

VACUUM Command Syntax and Options

The VACUUM command has several forms and options:

The most important options include:

— Reclaims more space but requires an exclusive lock

— Marks tuples as "frozen" to prevent transaction ID wraparound

— Updates statistics for the query planner

— Provides detailed progress information

DISABLE_PAGE_SKIPPING

— Examines all pages even those marked as all-visible

Regular VACUUM Recommendations

Let autovacuum handle routine maintenance

— For most cases, a properly configured

autovacuum daemon will handle day-to-day maintenance needs

Adjust autovacuum parameters based on workload

— Default settings may

not be optimal for large or write-heavy databases

Run manual VACUUM ANALYZE after bulk operations

— After large INSERT, UPDATE

or DELETE operations to immediately clean up and update statistics

Schedule VACUUM FREEZE during low-traffic periods

— Run it periodically

to prevent transaction ID wraparound issues

Use VACUUM FULL sparingly

— Reserve for significant table bloat, and only

during maintenance windows when exclusive locks are acceptable

Monitor transaction ID consumption

pg_stat_activity

to

check the transaction age and prevent wraparound issues

Transaction ID Wraparound

PostgreSQL uses 32-bit transaction IDs, which can wrap around after approximately 4 billion

transactions. When this happens, tuples could become "invisible" to queries, effectively causing

data loss. Regular VACUUM FREEZE operations prevent this issue by marking old row versions as

"frozen" (visible to all future transactions) when they're older than

vacuum_freeze_min_age

transactions.

Sample VACUUM Maintenance Script

Autovacuum Configuration

Autovacuum is PostgreSQL's built-in automated maintenance system that runs VACUUM and ANALYZE

operations based on table activity. Properly configuring autovacuum is one of the most important

tasks for a database administrator.

Key Autovacuum Parameters

Description

Recommendation

Enables or disables autovacuum

Keep enabled

autovacuum_max_workers

Maximum number of concurrent autovacuum worker processes

Scale with CPU cores (cores ÷ 4)

autovacuum_naptime

Time between autovacuum runs

30s-2min depending on write volume

autovacuum_vacuum_threshold

Minimum number of updated/deleted tuples before vacuum

50-1000 based on table size

autovacuum_analyze_threshold

Minimum number of inserted/updated/deleted tuples before analyze

50-1000 based on table size

autovacuum_vacuum_scale_factor

Percentage of table that must be changed before vacuum

0.05-0.1 for large tables

autovacuum_analyze_scale_factor

Percentage of table that must be changed before analyze

0.05 for large tables

autovacuum_freeze_max_age

Age at which to vacuum a table to prevent wraparound

200 million

Keep default or increase slightly

autovacuum_vacuum_cost_delay

Cost-based vacuum delay in milliseconds

2-10ms for faster cleanup

autovacuum_vacuum_cost_limit

Cost limit for autovacuum, controlling I/O impact

1000-2000 for modern hardware

You can also configure autovacuum parameters at the table level for specific high-traffic tables:

Tuning for Different Table Sizes

The default autovacuum settings are designed for medium-sized tables. As tables grow larger,

scale_factor becomes problematic because it's percentage-based:

Small tables

(under 10,000 rows) — Default settings work well

Medium tables

(10,000-1M rows) — Lower scale_factor to 0.1

Large tables

(1M-10M rows) — Lower scale_factor to 0.05 and increase thresholds

Very large tables

(>10M rows) — Set scale_factor to 0 and use absolute thresholds

For example, a 100 million row table with the default 0.2 scale_factor would need 20 million

row changes before autovacuum triggers, which is excessive.

1-- Basic VACUUM on a single table
2VACUUM table_name;
3
4-- VACUUM with ANALYZE to update statistics
5VACUUM ANALYZE table_name;
6
7-- VACUUM FULL to reclaim space (exclusive lock required)
8VACUUM FULL table_name;
9
10-- VACUUM specific columns only
11VACUUM (column1, column2) table_name;
12
13-- VACUUM with FREEZE option
14VACUUM (FREEZE) table_name;
15
16-- VACUUM with verbose output
17VACUUM (VERBOSE) table_name;
18
19-- VACUUM all tables in the current database
20VACUUM;
21
22-- Combine options
23VACUUM (VERBOSE, ANALYZE, FREEZE) table_name;

1#!/bin/bash
2# PostgreSQL maintenance script for a production database
3
4echo "Starting PostgreSQL maintenance at $(date)"
5
6# Run VACUUM ANALYZE on all tables
7echo "Running VACUUM ANALYZE on all tables..."
8psql -U postgres -d production_db -c "VACUUM ANALYZE;"
9
10# Run VACUUM FREEZE on tables nearing wraparound
11echo "Running VACUUM FREEZE on tables at risk for wraparound..."
12psql -U postgres -d production_db <<EOF
13DO \$\$
14DECLARE
15  r RECORD;
16BEGIN
17  FOR r IN
18    SELECT schemaname, relname
19    FROM pg_stat_user_tables
20    JOIN pg_class c ON relname = c.relname
21    WHERE age(c.relfrozenxid) > 1000000000 -- 1 billion transactions
22  LOOP
23    RAISE NOTICE 'Freezing table %.%', r.schemaname, r.relname;
24    EXECUTE 'VACUUM (FREEZE, VERBOSE) ' || quote_ident(r.schemaname) || '.' || quote_ident(r.relname);
25  END LOOP;
26END \$\$;
27EOF
28
29# Run VACUUM FULL on bloated tables during maintenance window
30echo "Running VACUUM FULL on bloated tables..."
31psql -U postgres -d production_db <<EOF
32DO \$\$
33DECLARE
34  r RECORD;
35BEGIN
36  -- Find tables with significant bloat (over 30% bloated)
37  FOR r IN
38    SELECT schemaname, tablename
39    FROM pgstattuple_approx('public.large_table')
40    WHERE dead_tuple_percent > 30
41  LOOP
42    RAISE NOTICE 'Running VACUUM FULL on %.%', r.schemaname, r.tablename;
43    EXECUTE 'VACUUM FULL ' || quote_ident(r.schemaname) || '.' || quote_ident(r.tablename);
44  END LOOP;
45END \$\$;
46EOF
47
48echo "PostgreSQL maintenance completed at $(date)"

1-- Example: Setting table-specific autovacuum parameters
2ALTER TABLE large_transactional_table SET (
3  autovacuum_vacuum_threshold = 1000,
4  autovacuum_vacuum_scale_factor = 0.05,
5  autovacuum_vacuum_cost_delay = 5,
6  autovacuum_vacuum_cost_limit = 1000
7);
8
9-- For very large tables, sometimes it's better to use a fixed threshold
10-- rather than a percentage (scale_factor = 0)
11ALTER TABLE massive_append_only_table SET (
12  autovacuum_vacuum_threshold = 10000,
13  autovacuum_vacuum_scale_factor = 0.0,
14  autovacuum_freeze_min_age = 100000000
15);
16
17-- For tables that rarely change
18ALTER TABLE reference_data_table SET (
19  autovacuum_vacuum_threshold = 500,
20  autovacuum_vacuum_scale_factor = 0.2
21);

PostgreSQL's query planner relies on up-to-date statistics about your tables and indexes to generate

efficient query plans. Regular statistics collection ensures optimal query performance and helps avoid

execution plan regressions.

PostgreSQL Statistics Collection

PostgreSQL's statistics collection process gathers information about table contents, including

column value distributions, frequent values, histograms, and correlation coefficients. This

information helps the query planner estimate the selectivity of conditions and the cost of

different execution strategies.

What Statistics are Collected?

— Total number of rows in a table

Column statistics

— Distribution of values, including:

Most common values and their frequencies

Histograms showing distribution of values

Distinct value count

NULL value frequency

Correlation coefficients

— Statistical correlation between physical row order and logical order

Extended statistics

— Multi-column correlations and dependencies (PostgreSQL 10+)

Table and index sizes

— Physical storage requirements

Index statistics

— Information about index entries and their distribution

ANALYZE Command Syntax and Options

The ANALYZE command collects statistics used by the query planner:

While the ANALYZE command is relatively simple, the key is knowing when and how often to run it.

When to Update Statistics

After bulk data changes

— Run ANALYZE after large INSERT, UPDATE, DELETE, or COPY operations

When execution plans change unexpectedly

— If queries suddenly perform poorly

After index creation or modification

— To ensure the planner knows about the new index

When adding or altering database constraints

— Especially foreign keys or unique constraints

Before running complex reporting queries

— Especially if the data has changed since the last analysis

On a scheduled basis

— As part of regular maintenance routines

Statistics Storage

PostgreSQL stores statistics in the

pg_statistic

catalog, though this is not directly

readable in a useful format. You can view the interpreted statistics through the

view,

which presents the information in a more user-friendly form. Run

SELECT * FROM pg_stats WHERE tablename = 'your_table';

to see the statistical information used by the planner.

Statistics Best Practices

Statistics Collection Recommendations

Rely on autovacuum for routine statistics updates

— Configure it appropriately for your workload

Manually run ANALYZE after significant data changes

— Don't wait for autovacuum after bulk operations

Adjust statistics target for important columns

— Use ALTER TABLE ... ALTER COLUMN SET STATISTICS

Create extended statistics for correlated columns

— Especially for columns used together in WHERE clauses

Monitor stale statistics

n_mod_since_analyze

pg_stat_user_tables

Schedule full ANALYZE operations

— Weekly or monthly, depending on your data volatility

Adjusting Statistics Collection Depth

For important query columns, you can increase the statistics collection detail:

The default statistics target of 100 is often too low for large tables or columns with many distinct values.

Increasing it improves estimation accuracy but makes ANALYZE take longer and use more memory.

Query Planning Issues

Poor or outdated statistics can cause serious query performance problems:

Incorrect join order

— The planner may choose a suboptimal join sequence

Wrong index selection

— The planner might use the wrong index or none at all

Inappropriate join methods

— Hash joins might be chosen when nested loops would be better

Poor cardinality estimation

— Underestimating or overestimating result sizes

If you notice sudden query performance degradation, outdated statistics are often the culprit.

Monitoring Statistics Health

Use these queries to identify tables that might need statistics updates:

Monitoring Statistics-Related Issues

Regularly monitor your database for signs of statistics-related problems to proactively

address issues before they affect application performance.

Key Statistics Monitoring Metrics

Execution plan changes

— Unexpected changes in query execution plans

Query time fluctuations

— Sudden increases in query execution times

Row estimation accuracy

— Difference between estimated and actual row counts

Statistics age

— Time since last ANALYZE on heavily-used tables

Data modification rates

— High rates indicate need for more frequent statistics updates

pg_stat_statements

extension to track query performance and detect

potential statistics issues:

The Cost of Statistics Collection

While up-to-date statistics are crucial, collecting them is not free:

I/O impact

— ANALYZE needs to read a sample of the table

— Computing histograms and other statistics is CPU-intensive

Memory usage

— Higher statistics targets increase memory requirements

Lock contention

— Although ANALYZE uses only a read lock, it still adds to contention

Balance the frequency of statistics updates with their overhead, especially on busy systems.

1-- Basic ANALYZE on a single table
2ANALYZE table_name;
3
4-- ANALYZE specific columns only
5ANALYZE table_name(column1, column2);
6
7-- ANALYZE with verbose output
8ANALYZE VERBOSE table_name;
9
10-- ANALYZE all tables in the current database
11ANALYZE;
12
13-- Combined with VACUUM
14VACUUM ANALYZE table_name;

1-- Increase statistics target for a specific column (default is 100)
2ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
3
4-- Check current statistics targets
5SELECT
6    tablename,
7    attname,
8    attstattarget
9FROM pg_stats
10JOIN pg_attribute ON attname = pg_stats.attname
11WHERE tablename = 'orders';
12
13-- Create extended statistics for correlated columns (PostgreSQL 10+)
14CREATE STATISTICS order_stats (dependencies) ON
15    customer_id, order_status, order_date
16FROM orders;
17
18-- View extended statistics
19SELECT * FROM pg_statistic_ext;

1-- Tables with outdated statistics (high modification count since last ANALYZE)
2SELECT
3    schemaname,
4    relname,
5    last_analyze,
6    last_autoanalyze,
7    n_mod_since_analyze,
8    n_live_tup,
9    ROUND(n_mod_since_analyze * 100.0 / GREATEST(n_live_tup, 1), 2) AS pct_modified
10FROM pg_stat_user_tables
11WHERE n_mod_since_analyze > 0
12ORDER BY pct_modified DESC;
13
14-- Tables that have never been analyzed
15SELECT
16    schemaname,
17    relname,
18    n_live_tup
19FROM pg_stat_user_tables
20WHERE last_analyze IS NULL AND last_autoanalyze IS NULL
21ORDER BY n_live_tup DESC;
22
23-- Tables with stale statistics (analyzed more than 7 days ago)
24SELECT
25    schemaname,
26    relname,
27    n_live_tup,
28    last_analyze,
29    last_autoanalyze,
30    GREATEST(last_analyze, last_autoanalyze) as last_stats_update,
31    NOW() - GREATEST(last_analyze, last_autoanalyze) as stats_age
32FROM pg_stat_user_tables
33WHERE GREATEST(last_analyze, last_autoanalyze) < NOW() - INTERVAL '7 days'
34  AND n_live_tup > 1000
35ORDER BY stats_age DESC;

1-- Enable pg_stat_statements if not already enabled
2CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
3
4-- Find queries with large estimation errors
5SELECT
6    substring(query, 1, 100) as query_sample,
7    calls,
8    rows,
9    mean_exec_time,
10    (rows/calls) as actual_rows,
11    (SELECT CASE WHEN explain_plan LIKE '%rows=% width=%'
12               THEN regexp_replace(
13                    substring(
14                        explain_plan,
15                        'rows=([0-9]+) width=([0-9]+)'
16                    ),
17                    'rows=([0-9]+) width=([0-9]+)',
18                    '\\1'
19                )::numeric
20               ELSE NULL
21               END
22     FROM pg_stat_statements_explain(queryid) LIMIT 1) as estimated_rows,
23    query_id
24FROM pg_stat_statements
25WHERE calls > 100
26ORDER BY mean_exec_time DESC
27LIMIT 20;
28
29-- Reset statistics to start fresh
30SELECT pg_stat_statements_reset();

Indexes are crucial for query performance, but they require regular maintenance to remain efficient.

Over time, indexes can become fragmented, bloated, or even corrupted, leading to performance degradation.

Index Bloat and Fragmentation

Index bloat occurs when indexes contain dead tuples or unused space, similar to table bloat.

As data is modified, indexes become less efficiently organized, leading to increased size and

decreased performance.

Causes of Index Bloat

High update/delete rates

— Frequent modifications lead to dead index entries

Transaction aborts

— Aborted transactions can leave behind unused space

Insufficient maintenance

— Infrequent VACUUM and REINDEX operations

Non-fillfactor tuning

— Default fillfactor may not be optimal for write-heavy workloads

Sequential inserts with concurrent reads

— Can cause page splits and fragmentation

Detecting Index Bloat

Use these queries to identify bloated indexes in your database:

Signs of problematic index bloat include:

Bloat percentage above 30%

Index size much larger than expected based on table size

Decreasing index scan performance over time

Increasingly frequent sequential scans on indexed tables

Index Maintenance Operations

What it Does

When to Use

Lock Requirements

REINDEX INDEX

Rebuilds an index from scratch

For heavily bloated or corrupted indexes

Exclusive lock on the index

REINDEX TABLE

Rebuilds all indexes on a table

After bulk updates or to fix all indexes

Exclusive lock on the table

REINDEX CONCURRENTLY

Rebuilds index without exclusive locks (PG 12+)

In production systems during normal operation

ShareUpdateExclusiveLock (allows reads and writes)

DROP/CREATE INDEX

Drops and recreates an index

To change index properties (e.g., fillfactor)

AccessExclusiveLock during index creation

CREATE INDEX CONCURRENTLY

Creates a new index without blocking operations

When replacing an index in production

ShareUpdateExclusiveLock (allows reads and writes)

Index Corruption

Rarely, indexes can become corrupted due to hardware failures, bugs, or interrupted operations.

Symptoms include wrong query results, errors like "index contains corrupted page," or crashes.

If you suspect index corruption, use

to rebuild the affected index immediately.

Index Maintenance Best Practices

Regular Index Maintenance Recommendations

Monitor index usage and bloat

— Regularly check for unused or bloated indexes

Schedule REINDEX operations

— Plan for regular rebuilding of critical indexes

Use REINDEX CONCURRENTLY for production

— Minimize impact on active systems

Adjust fillfactor for write-heavy indexes

— Lower values reduce future fragmentation

Drop unused indexes

— Remove indexes that aren't being used to reduce overhead

Consider index type

— B-tree, hash, GIN, or BRIN have different maintenance needs

Fillfactor Optimization

Fillfactor determines how full PostgreSQL will fill index pages. The default is 90%, but for heavily updated

indexes, a lower value can reduce fragmentation:

Identifying Unused and Redundant Indexes

Unused indexes waste space and slow down writes without providing any benefit:

When to Keep Unused Indexes

Before dropping unused indexes, consider these factors:

Enforce constraints

— Some indexes enforce unique or exclusion constraints

Seasonal workloads

— Some indexes may only be used during specific operations

Reporting queries

— Indexes used for monthly reports won't show frequent scans

Emergency scenarios

— Some indexes might be for emergency troubleshooting

Recent creation

— New indexes may not have had time to be used yet

Always validate with application owners before dropping indexes.

Index Maintenance Automation

Automating index maintenance ensures it happens regularly without manual intervention. Here are

some approaches to index maintenance automation:

Sample Index Maintenance Script

Using pg_cron for Scheduled Maintenance

The pg_cron extension allows scheduling PostgreSQL commands directly from the database:

Monitoring Index Maintenance

Set up monitoring to track index health metrics over time. Key metrics to monitor include:

Index size growth over time

Index bloat percentage

Index scan vs. sequential scan ratios

Time spent on index scans

Success/failure of scheduled index maintenance

Integrate these metrics into your database monitoring dashboards for visibility.

1-- Estimate index bloat (basic query)
2SELECT
3    schemaname,
4    tablename,
5    reltuples::numeric AS rows,
6    relpages::numeric AS pages,
7    otta AS expected_pages,
8    ROUND(CASE WHEN otta=0 THEN 0.0 ELSE relpages/otta::numeric END,1) AS bloat_ratio,
9    CASE WHEN relpages > otta THEN (relpages-otta)::numeric*bs ELSE 0 END AS bloat_size,
10    CASE WHEN relpages > otta THEN 100 * (relpages-otta)::numeric / relpages ELSE 0 END AS bloat_percentage
11FROM (
12    SELECT
13        schemaname,
14        tablename,
15        bs,
16        reltuples,
17        relpages,
18        CEIL((reltuples*4)/(bs-20::float)) AS otta
19    FROM (
20        SELECT
21            n.nspname AS schemaname,
22            c.relname AS tablename,
23            (SELECT current_setting('block_size')::numeric) AS bs,
24            c.reltuples,
25            c.relpages
26        FROM pg_class c
27        JOIN pg_namespace n ON n.oid = c.relnamespace
28        WHERE c.relkind = 'i'
29        AND n.nspname NOT IN ('pg_catalog', 'information_schema')
30    ) AS foo
31) AS foo2
32WHERE relpages > 10  -- Only show tables with at least 10 pages
33ORDER BY bloat_percentage DESC
34LIMIT 20;
35
36-- More accurate index bloat detection (using pgstattuple extension)
37CREATE EXTENSION IF NOT EXISTS pgstattuple;
38
39-- Check specific index
40SELECT * FROM pgstattuple('index_name');
41
42-- Get bloat for all indexes in a schema
43SELECT
44    schemaname,
45    indexrelname,
46    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
47    (SELECT avg_leaf_density FROM pgstatindex(indexrelid)) as density,
48    (SELECT (100 - avg_leaf_density) FROM pgstatindex(indexrelid)) as bloat_percentage
49FROM pg_stat_user_indexes
50WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
51ORDER BY bloat_percentage DESC
52LIMIT 20;

1-- Set fillfactor for a new index
2CREATE INDEX idx_customer_modified
3ON customers(last_modified)
4WITH (fillfactor=70);
5
6-- Change fillfactor for an existing index (requires rebuild)
7DROP INDEX idx_customer_modified;
8CREATE INDEX idx_customer_modified
9ON customers(last_modified)
10WITH (fillfactor=70);
11
12-- Or use REINDEX with new storage parameters (PostgreSQL 12+)
13ALTER INDEX idx_customer_modified SET (fillfactor = 70);
14REINDEX INDEX CONCURRENTLY idx_customer_modified;
15
16-- Recommended fillfactors based on workload:
17-- Read-heavy tables: 90-100%
18-- Mixed workload: 80-90%
19-- Write-heavy tables: 70-80%
20-- Extremely write-heavy: 60-70%

1-- Find unused indexes
2SELECT
3    schemaname,
4    relname as table_name,
5    indexrelname as index_name,
6    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
7    idx_scan as scans_count
8FROM pg_stat_user_indexes
9WHERE idx_scan = 0      -- No scans performed
10  AND indexrelname NOT LIKE 'pg_%'  -- Exclude system indexes
11  AND indexrelname NOT IN (        -- Exclude primary keys and unique constraints
12        SELECT conname
13        FROM pg_constraint
14        WHERE contype IN ('p', 'u')
15    )
16ORDER BY pg_relation_size(indexrelid) DESC;
17
18-- Find redundant indexes (similar indexes on same columns)
19SELECT
20    indrelid::regclass AS table_name,
21    array_agg(indexrelid::regclass ORDER BY idx_scan DESC) AS indexes,
22    array_agg(idx_scan ORDER BY idx_scan DESC) AS scans
23FROM (
24    SELECT
25        idx.indexrelid,
26        idx.indrelid,
27        idx.indkey,
28        idx.indpred IS NOT NULL AS has_predicate,
29        idx.indexprs IS NOT NULL AS has_expression,
30        stats.idx_scan
31    FROM pg_index idx
32    JOIN pg_stat_user_indexes stats
33        ON idx.indexrelid = stats.indexrelid
34    WHERE idx.indrelid::regclass::text NOT LIKE 'pg\\_%'
35) sub
36GROUP BY indrelid, indkey, has_predicate, has_expression
37HAVING count(*) > 1
38ORDER BY max(idx_scan) DESC, sum(idx_scan) DESC;

1#!/bin/bash
2# PostgreSQL index maintenance script
3
4echo "Starting PostgreSQL index maintenance at $(date)"
5
6# Database connection parameters
7DB_NAME="production_db"
8DB_USER="postgres"
9
10# Rebuild heavily bloated indexes
11echo "Rebuilding bloated indexes..."
12psql -U $DB_USER -d $DB_NAME <<EOF
13DO \$\$
14DECLARE
15    r RECORD;
16BEGIN
17    FOR r IN
18        -- Find bloated indexes (simplified query)
19        SELECT schemaname, indexrelname
20        FROM pg_stat_user_indexes s
21        JOIN pg_index i ON s.indexrelid = i.indexrelid
22        JOIN pg_class c ON i.indexrelid = c.oid
23        WHERE s.idx_scan > 100  -- Only rebuild actively used indexes
24          AND c.relpages > 10   -- Only indexes of a certain size
25          -- Skip unique indexes and primary keys due to higher impact
26          AND NOT i.indisunique
27          -- Estimate bloat using available system statistics
28          -- (in production, use more accurate query from earlier examples)
29          AND c.relpages > c.reltuples::numeric / (bs-20)::numeric * 8
30    LOOP
31        RAISE NOTICE 'Reindexing %.%...', r.schemaname, r.indexrelname;
32        -- Use CONCURRENTLY for minimal impact on production
33        EXECUTE 'REINDEX INDEX CONCURRENTLY ' ||
34                quote_ident(r.schemaname) || '.' ||
35                quote_ident(r.indexrelname);
36    END LOOP;
37END \$\$;
38EOF
39
40# Review unused indexes
41echo "Identifying unused indexes..."
42psql -U $DB_USER -d $DB_NAME -c "
43SELECT
44    schemaname,
45    relname as table_name,
46    indexrelname as index_name,
47    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
48    idx_scan as scans_count
49FROM pg_stat_user_indexes
50WHERE idx_scan < 10
51  AND indexrelid NOT IN (
52      SELECT indexrelid FROM pg_constraint
53      WHERE contype IN ('p','u')
54  )
55ORDER BY pg_relation_size(indexrelid) DESC
56LIMIT 10;"
57
58echo "PostgreSQL index maintenance completed at $(date)"

1-- Install pg_cron extension
2CREATE EXTENSION pg_cron;
3
4-- Schedule weekly maintenance for specific indexes
5SELECT cron.schedule(
6    'weekly_index_maintenance',
7    '0 2 * * 0',  -- Run at 2:00 AM every Sunday
8    $$
9    -- Rebuild heavily used indexes on orders table
10    REINDEX INDEX CONCURRENTLY orders_customer_id_idx;
11    REINDEX INDEX CONCURRENTLY orders_status_idx;
12    REINDEX INDEX CONCURRENTLY orders_date_idx;
13
14    -- Update statistics after reindexing
15    ANALYZE orders;
16    $$
17);
18
19-- Schedule monthly review of unused indexes
20SELECT cron.schedule(
21    'monthly_unused_index_review',
22    '0 3 1 * *',  -- Run at 3:00 AM on the 1st day of each month
23    $$
24    -- Output to server log
25    DO $do$
26    DECLARE
27        r RECORD;
28    BEGIN
29        FOR r IN
30            SELECT
31                schemaname,
32                relname as table_name,
33                indexrelname as index_name,
34                pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
35                idx_scan
36            FROM pg_stat_user_indexes
37            WHERE idx_scan = 0
38              AND indexrelid NOT IN (
39                  SELECT indexrelid FROM pg_index i
40                  JOIN pg_constraint c ON (c.conindid = i.indexrelid)
41                  WHERE c.contype IN ('p', 'u')
42              )
43            ORDER BY pg_relation_size(indexrelid) DESC
44            LIMIT 10
45        LOOP
46            RAISE NOTICE 'Unused index: %.% (%) - size: %',
47                r.schemaname, r.index_name, r.table_name, r.index_size;
48        END LOOP;
49    END $do$;
50    $$
51);

A comprehensive database maintenance strategy requires automation to ensure tasks are performed

consistently and reliably. This section explores different approaches to automating PostgreSQL

maintenance tasks and creating a maintenance schedule tailored to your database needs.

Maintenance Schedule Planning

Effective maintenance requires a well-structured schedule that balances database health with

operational impact. Different tasks should be scheduled at appropriate frequencies based on

their importance and resource requirements.

Recommended Maintenance Schedule by Task

Maintenance Task

Impact Level

Routine VACUUM

Daily (via autovacuum)

Continuous (background)

Daily (via autovacuum)

Continuous (background)

VACUUM FREEZE

Off-peak hours

Monthly (as needed)

Medium (CONCURRENTLY)

Off-peak hours

VACUUM FULL

Quarterly (as needed)

High (exclusive locks)

Maintenance window

Database backups

Daily/Hourly

Low-Medium

Staggered throughout day

Unused index review

None (review only)

Business hours

Log rotation

Off-peak hours

Stats reset

Weekly/Monthly

After collecting reports

Full system check

Off-peak hours

Adjusting for Database Size and Activity

The maintenance schedule should be adjusted based on:

Database size

— Larger databases need more frequent maintenance

Write volume

— High-write systems need more aggressive cleanup

Business hours

— Schedule intensive tasks during lowest usage periods

Critical periods

— Avoid maintenance during business-critical times

Hardware capabilities

— More powerful servers can handle more frequent maintenance

Automation Tools and Approaches

Several approaches exist for automating PostgreSQL maintenance tasks, each with its own

advantages and use cases. Choose the approach that best fits your environment and requirements.

Using pg_cron Extension

pg_cron is a PostgreSQL extension that adds job scheduling capabilities directly

within the database, similar to cron in Unix systems:

pg_cron advantages include:

Jobs run inside the database, no external dependencies

Can be managed directly via SQL

Runs with database permissions

Built-in job history and logging

Using System Cron Jobs

Traditional system cron jobs can execute maintenance scripts on a schedule:

Example daily maintenance script:

Specialized Automation Tools

Several specialized tools exist to manage PostgreSQL maintenance:

Popular PostgreSQL Maintenance Tools

pgAdmin Scheduled Tasks

— pgAdmin's built-in job scheduler for routine maintenance

pg_partman

— Extension for automated partition management and maintenance

pgBackRest

— Backup solution with scheduling and retention policies

— Tool for removing bloat from tables and indexes with minimal locks

pg_stat_monitor

— Enhanced monitoring extension with built-in maintenance functionality

Patroni/Repmgr

— High availability solutions with maintenance capabilities

Postgres Operator

— Kubernetes operators for automated PostgreSQL management

Example using pg_repack for zero-downtime table maintenance:

Cloud and Managed Service Automation

Cloud and managed PostgreSQL services often include built-in maintenance capabilities:

Managed PostgreSQL Maintenance Features

AWS RDS/Aurora

Maintenance windows for automatic updates

Parameter group configurations for autovacuum

Automated minor version upgrades

Automated backups with retention policies

Azure Database for PostgreSQL

Scheduled maintenance windows

Automated database optimizations

Performance recommendations

Intelligent query tuning

Google Cloud SQL

Maintenance window configuration

Automatic storage increases

Automated backups and point-in-time recovery

Automatic instance repair

Example AWS RDS Parameter Group settings for maintenance:

Maintenance Automation Best Practices

Stagger tasks

— Don't schedule all maintenance simultaneously to avoid resource contention

Monitor automation

— Set up alerts for failed maintenance jobs

Log everything

— Maintain detailed logs of all maintenance activities

Test thoroughly

— Test automation scripts in staging before production

Document runbooks

— Create clear documentation for manual intervention

Review regularly

— Periodically review and adjust your maintenance strategy

Enforce timeouts

— Ensure maintenance tasks can't run indefinitely

Comprehensive Maintenance Plan Example

Here's an example of a comprehensive PostgreSQL maintenance plan for a medium-sized production database:

Maintenance Reporting

Generate regular maintenance reports to track database health and identify trends:

Weekly health reports

— Summary of maintenance actions and database metrics

Monthly trend analysis

— Growth rates, performance changes, and maintenance impact

Quarterly review documents

— Comprehensive assessment with recommendations

These reports help demonstrate the value of regular maintenance to stakeholders and

provide documentation for compliance requirements.

1-- Install pg_cron
2CREATE EXTENSION pg_cron;
3
4-- Grant usage to specific role if needed
5GRANT USAGE ON SCHEMA cron TO maintenance_role;
6
7-- Schedule database-wide ANALYZE every day at 1 AM
8SELECT cron.schedule('daily-analyze', '0 1 * * *', 'ANALYZE VERBOSE;');
9
10-- Schedule VACUUM on specific tables
11SELECT cron.schedule('weekly-vacuum-large-tables', '0 2 * * 0', $$
12    VACUUM (VERBOSE, ANALYZE) large_table_1;
13    VACUUM (VERBOSE, ANALYZE) large_table_2;
14    VACUUM (VERBOSE, ANALYZE) large_table_3;
15$$);
16
17-- Schedule index maintenance for critical tables
18SELECT cron.schedule('monthly-reindex-critical', '0 3 1 * *', $$
19    REINDEX INDEX CONCURRENTLY idx_critical_1;
20    REINDEX INDEX CONCURRENTLY idx_critical_2;
21    ANALYZE critical_table;
22$$);
23
24-- List all scheduled jobs
25SELECT * FROM cron.job;
26
27-- Remove a scheduled job
28SELECT cron.unschedule(job_id);

1# Example crontab entries
2
3# Run daily vacuum at 1:00 AM
40 1 * * * /path/to/scripts/daily_vacuum.sh >> /var/log/postgres/vacuum.log 2>&1
5
6# Run weekly statistics update at 2:00 AM on Sundays
70 2 * * 0 /path/to/scripts/weekly_analyze.sh >> /var/log/postgres/analyze.log 2>&1
8
9# Run monthly index maintenance at 3:00 AM on the first day of each month
100 3 1 * * /path/to/scripts/monthly_index_maint.sh >> /var/log/postgres/index_maint.log 2>&1
11
12# Run quarterly full vacuum during maintenance window (Sat 1 AM)
130 1 1 1,4,7,10 6 /path/to/scripts/quarterly_vacuum_full.sh >> /var/log/postgres/vacuum_full.log 2>&1

1#!/bin/bash
2# daily_vacuum.sh - Daily PostgreSQL maintenance
3
4LOG_FILE="/var/log/postgres/vacuum_$(date +\%Y\%m\%d).log"
5DB_NAME="production_db"
6DB_USER="postgres"
7
8echo "=== Starting daily PostgreSQL maintenance at $(date) ===" | tee -a $LOG_FILE
9
10# Run VACUUM ANALYZE on all tables
11echo "Running VACUUM ANALYZE..." | tee -a $LOG_FILE
12psql -U $DB_USER -d $DB_NAME -c "VACUUM ANALYZE;" >> $LOG_FILE 2>&1
13
14# Check for tables approaching transaction ID wraparound
15echo "Checking for tables needing FREEZE..." | tee -a $LOG_FILE
16psql -U $DB_USER -d $DB_NAME -c "
17SELECT schemaname, relname, age(relfrozenxid) as xid_age,
18       round(100*(age(relfrozenxid)/2000000000::numeric),2) as pct_towards_wraparound
19FROM pg_class c
20JOIN pg_namespace n on n.oid = c.relnamespace
21WHERE relkind IN ('r', 'm')
22  AND schemaname NOT IN ('pg_catalog', 'information_schema')
23  AND age(relfrozenxid) > 500000000
24ORDER BY age(relfrozenxid) DESC
25LIMIT 20;" >> $LOG_FILE 2>&1
26
27# Run VACUUM FREEZE on tables approaching wraparound
28echo "Running VACUUM FREEZE where needed..." | tee -a $LOG_FILE
29psql -U $DB_USER -d $DB_NAME <<EOF >> $LOG_FILE 2>&1
30DO \$\$
31DECLARE
32    r RECORD;
33BEGIN
34    FOR r IN
35        SELECT schemaname, relname
36        FROM pg_class c
37        JOIN pg_namespace n on n.oid = c.relnamespace
38        WHERE relkind IN ('r', 'm')
39          AND schemaname NOT IN ('pg_catalog', 'information_schema')
40          AND age(relfrozenxid) > 1000000000
41    LOOP
42        RAISE NOTICE 'Freezing %.%...', r.schemaname, r.relname;
43        EXECUTE 'VACUUM (FREEZE, VERBOSE) ' ||
44                quote_ident(r.schemaname) || '.' ||
45                quote_ident(r.relname);
46    END LOOP;
47END \$\$;
48EOF
49
50echo "=== Daily PostgreSQL maintenance completed at $(date) ===" | tee -a $LOG_FILE

1# Install pg_repack
2sudo apt-get install pg-repack
3
4# Basic usage for a single table
5pg_repack -t orders -d production_db
6
7# Full database repacking
8pg_repack -d production_db
9
10# Only repack tables with significant bloat
11pg_repack -d production_db --tablespace=new_tablespace --no-order \
12  --min-size=10MB --min-bloat-ratio=30 --no-superuser-check
13
14# Schedule with cron (example)
150 2 * * 0 /usr/bin/pg_repack -d production_db --no-superuser-check \
16  --min-size=50MB --min-bloat-ratio=40 >> /var/log/pg_repack.log 2>&1

1{
2  "Parameters": [
3    {
4      "ParameterName": "autovacuum",
5      "ParameterValue": "1",
6      "Description": "Starts the autovacuum subprocess."
7    },
8    {
9      "ParameterName": "autovacuum_naptime",
10      "ParameterValue": "60",
11      "Description": "Time between autovacuum runs, in seconds."
12    },
13    {
14      "ParameterName": "autovacuum_vacuum_threshold",
15      "ParameterValue": "50",
16      "Description": "Minimum number of tuple updates/deletes before vacuum."
17    },
18    {
19      "ParameterName": "autovacuum_vacuum_scale_factor",
20      "ParameterValue": "0.1",
21      "Description": "Fraction of table size to add to autovacuum_vacuum_threshold."
22    },
23    {
24      "ParameterName": "autovacuum_vacuum_cost_delay",
25      "ParameterValue": "5",
26      "Description": "Vacuum cost delay for autovacuum, in milliseconds."
27    },
28    {
29      "ParameterName": "autovacuum_vacuum_cost_limit",
30      "ParameterValue": "1000",
31      "Description": "Vacuum cost limit for autovacuum."
32    },
33    {
34      "ParameterName": "maintenance_work_mem",
35      "ParameterValue": "1048576",
36      "Description": "Sets the maximum memory for maintenance operations, in KB."
37    }
38  ]
39}

1# PostgreSQL Maintenance Plan - Production Environment
2
3## Daily Maintenance (Automated)
4
5* **Time**: 1:00 AM - 3:00 AM local time
6* **Frequency**: Daily
7* **Tasks**:
8  * Run VACUUM ANALYZE on all tables (via script)
9  * Check for tables approaching XID wraparound, run VACUUM FREEZE if needed
10  * Run REINDEX CONCURRENTLY on any indexes identified as heavily bloated (>40%)
11  * Collect performance statistics and store historically
12  * Rotate and archive PostgreSQL logs
13  * Verify that automated backups completed successfully
14
15## Weekly Maintenance (Automated)
16
17* **Time**: Sunday, 2:00 AM - 5:00 AM local time
18* **Frequency**: Weekly
19* **Tasks**:
20  * Run VACUUM FREEZE on tables with high XID age (>500M)
21  * Update table and index statistics with increased sampling (ANALYZE VERBOSE)
22  * Rebuild heavily bloated indexes (20-40% bloat) with REINDEX CONCURRENTLY
23  * Analyze query performance data to identify slow queries
24  * Check for unused indexes (no scans in past month)
25  * Reset pg_stat_statements to collect fresh query statistics
26  * Verify replication lag across all replicas
27
28## Monthly Maintenance (Semi-Automated)
29
30* **Time**: First Saturday of month, 1:00 AM - 8:00 AM local time
31* **Frequency**: Monthly
32* **Tasks**:
33  * Run VACUUM FULL on tables with extreme bloat (manual approval required)
34  * Review and clean up unused indexes (manual approval required)
35  * Review table partitioning needs based on growth patterns
36  * Run database consistency checks
37  * Test point-in-time recovery process
38  * Update maintenance scripts and automation as needed
39  * Generate and review capacity planning report
40
41## Quarterly Maintenance (Manual)
42
43* **Time**: Scheduled maintenance window
44* **Frequency**: Every 3 months
45* **Tasks**:
46  * Perform minor version upgrades if available
47  * Review and update PostgreSQL configuration parameters
48  * Test and validate disaster recovery procedures
49  * Clean up old WAL archives and backup files
50  * Review and adjust autovacuum parameters
51  * Perform storage capacity review and planning
52  * Update documentation and runbooks
53
54## Continuous Monitoring
55
56* **PostgreSQL metrics**:
57  * Transaction ID age
58  * Bloat levels
59  * Index usage
60  * Query performance
61  * Replication status
62  * Lock contention
63  * Resource utilization
64
65* **Alerts**:
66  * Autovacuum not running
67  * Tables approaching wraparound
68  * Replication lag exceeding thresholds
69  * Maintenance job failures
70  * High bloat detection
71  * Disk space thresholds

Learn about Key Takeaways.

Regular VACUUM prevents bloat and transaction ID wraparound

Different VACUUM variants serve different purposes (basic, FULL, FREEZE)

Autovacuum should be properly configured for optimal performance

Monitor transaction ID age to prevent database emergencies

Up-to-date statistics are crucial for optimal query performance

Run ANALYZE after significant data changes

Increase statistics targets for important or high-cardinality columns

Use extended statistics for correlated columns

Regularly monitor indexes for bloat and usage patterns

Use REINDEX CONCURRENTLY for minimal operational impact

Adjust fillfactor based on write patterns to reduce fragmentation

Remove unused indexes to improve write performance

Create a comprehensive maintenance schedule based on database needs

Automate routine tasks with pg_cron, system cron, or dedicated tools

Stagger maintenance to avoid resource contention

Monitor automation and verify task completion

PostgreSQL Documentation: Routine Vacuuming

Official documentation on VACUUM operations and maintenance

PostgreSQL Documentation: Statistics Used by the Planner

Detailed explanation of PostgreSQL statistics collection and usage

PostgreSQL Wiki: Index Maintenance

Community-maintained information on index maintenance best practices

Automated PostgreSQL Maintenance Tasks

Extended guides for automating PostgreSQL maintenance

PostgreSQL: Understanding and Fighting Table Bloat

Comprehensive guide to managing and preventing table bloat

Managing Transaction ID Wraparound in PostgreSQL

Detailed explanation of transaction ID wraparound and prevention