Database Maintenance Tasks

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Consider this query joining orders with customers
SELECT c.name, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'USA'
  AND o.order_date >= '2024-01-01'
  AND o.status = 'completed';
 
-- Statistics provide these estimates:
-- customers table: 1,000,000 rows
--   country column: 200 distinct values
--   'USA' appears in ~40% of rows (histogram data)
--   → Estimated 400,000 rows after country filter
 
-- orders table: 50,000,000 rows
--   order_date: 10% of orders are from 2024
--   status: 5 distinct values, 'completed' = 60%
--   → Estimated 3,000,000 rows after date + status filter
 
-- Join estimate:
-- With avg 50 orders per customer
-- Final estimate: ~1,200,000 result rows
 
-- Without statistics:
-- Optimizer might assume 10% selectivity for each predicate
-- Leading to vastly different (and wrong) estimates

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- View histogram statistics in PostgreSQL
SELECT 
    attname AS column_name,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    histogram_bounds
FROM pg_stats
WHERE tablename = 'orders'
  AND attname = 'status';
 
-- Example output:
-- column_name: status
-- n_distinct: 5
-- most_common_vals: {completed,pending,shipped,cancelled,refunded}
-- most_common_freqs: {0.45,0.25,0.15,0.10,0.05}
-- histogram_bounds: NULL (frequency histogram used instead)
 
-- For high-cardinality columns, histogram_bounds shows bucket boundaries:
SELECT 
    attname,
    array_length(histogram_bounds, 1) AS num_buckets,
    histogram_bounds[1:5] AS first_5_bounds,
    histogram_bounds[96:100] AS last_5_bounds
FROM pg_stats
WHERE tablename = 'orders'
  AND attname = 'order_date';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- PostgreSQL: ANALYZE command collects statistics
 
-- Analyze entire database (samples by default)
ANALYZE;
 
-- Analyze specific table
ANALYZE orders;
 
-- Analyze specific columns
ANALYZE orders (customer_id, order_date, status);
 
-- Verbose mode shows what was collected
ANALYZE VERBOSE orders;
-- Output includes: pages sampled, rows sampled, 
-- which columns got histograms, etc.
 
-- Control sampling target (number of histogram buckets)
ALTER TABLE orders ALTER COLUMN customer_id 
    SET STATISTICS 500;  -- More buckets = higher accuracy
 
-- Then re-analyze to apply
ANALYZE orders (customer_id);
 
-- Check when statistics were last updated
SELECT 
    schemaname,
    relname AS table_name,
    last_analyze,
    last_autoanalyze,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY last_analyze DESC NULLS LAST;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- PostgreSQL autovacuum handles automatic ANALYZE
 
-- View current autovacuum settings
SHOW autovacuum;
SHOW autovacuum_analyze_threshold;
SHOW autovacuum_analyze_scale_factor;
 
-- Default: analyze after 50 + 10% of rows changed
-- For 1M row table: triggers after 100,050 row changes
 
-- Customize per-table for high-churn tables
ALTER TABLE orders SET (
    autovacuum_analyze_threshold = 100,
    autovacuum_analyze_scale_factor = 0.02  -- 2% instead of 10%
);
-- Now analyzes after 100 + 2% = 20,100 changes for 1M rows
 
-- For append-only tables, can be more aggressive
ALTER TABLE event_log SET (
    autovacuum_analyze_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.001  -- 0.1%
);
 
-- Monitor autovacuum activity
SELECT 
    relname,
    n_tup_ins AS inserts,
    n_tup_upd AS updates,
    n_tup_del AS deletes,
    n_mod_since_analyze AS changes_since_analyze,
    last_autoanalyze,
    autoanalyze_count
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > 0
ORDER BY n_mod_since_analyze DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

-- Comprehensive PostgreSQL Statistics Maintenance
 
-- 1. Find tables needing analysis
WITH analysis_candidates AS (
    SELECT 
        schemaname,
        relname,
        n_live_tup,
        n_dead_tup,
        n_mod_since_analyze,
        last_analyze,
        last_autoanalyze,
        GREATEST(last_analyze, last_autoanalyze) AS last_stats,
        CASE 
            WHEN n_live_tup = 0 THEN 0
            ELSE ROUND(100.0 * n_mod_since_analyze / n_live_tup, 2)
        END AS pct_changed
    FROM pg_stat_user_tables
)
SELECT *
FROM analysis_candidates
WHERE pct_changed > 5
   OR last_stats < NOW() - INTERVAL '7 days'
   OR last_stats IS NULL
ORDER BY n_mod_since_analyze DESC;
 
-- 2. Analyze only tables that need it (dynamic SQL in function)
CREATE OR REPLACE FUNCTION maintain_statistics(
    threshold_pct NUMERIC DEFAULT 5.0,
    max_age_days INTEGER DEFAULT 7
) RETURNS TABLE(analyzed_table TEXT, reason TEXT) AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT 
            schemaname,
            relname,
            n_live_tup,
            n_mod_since_analyze,
            GREATEST(last_analyze, last_autoanalyze) AS last_stats
        FROM pg_stat_user_tables
        WHERE (n_live_tup > 0 AND 
               100.0 * n_mod_since_analyze / n_live_tup > threshold_pct)
           OR GREATEST(last_analyze, last_autoanalyze) < 
              NOW() - (max_age_days || ' days')::INTERVAL
           OR (last_analyze IS NULL AND last_autoanalyze IS NULL)
    LOOP
        EXECUTE format('ANALYZE %I.%I', rec.schemaname, rec.relname);
        analyzed_table := rec.schemaname || '.' || rec.relname;
        reason := CASE 
            WHEN rec.last_stats IS NULL THEN 'never analyzed'
            WHEN 100.0 * rec.n_mod_since_analyze / rec.n_live_tup > threshold_pct 
                THEN 'high modification count'
            ELSE 'age exceeded threshold'
        END;
        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;
 
-- Run maintenance
SELECT * FROM maintain_statistics(5.0, 7);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

-- Diagnosing statistics issues in PostgreSQL
 
-- 1. Compare estimated vs actual rows
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders 
WHERE status = 'pending' 
  AND order_date > '2024-01-01';
 
-- Look for rows like:
-- "Seq Scan on orders (actual rows=500000 loops=1)"
--   "Rows Removed by Filter: 1000000"
--   "Estimated rows: 5000"  ← 100x underestimate!
 
-- 2. Check if column has statistics
SELECT 
    attname,
    n_distinct,
    null_frac,
    avg_width,
    most_common_vals IS NOT NULL AS has_mcv,
    histogram_bounds IS NOT NULL AS has_histogram
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('status', 'order_date', 'customer_id');
 
-- 3. Verify histogram accuracy
SELECT 
    attname,
    array_length(histogram_bounds, 1) AS bucket_count,
    histogram_bounds[1] AS min_value,
    histogram_bounds[array_length(histogram_bounds, 1)] AS max_value
FROM pg_stats
WHERE tablename = 'orders'
  AND histogram_bounds IS NOT NULL;
 
-- 4. Check MCV accuracy for specific value
SELECT 
    attname,
    most_common_vals,
    most_common_freqs,
    (SELECT COUNT(*)::float / (SELECT COUNT(*) FROM orders)
     FROM orders WHERE status = 'pending') AS actual_freq
FROM pg_stats
WHERE tablename = 'orders'
  AND attname = 'status';
 
-- 5. Identify correlation issues (index efficiency)
SELECT 
    attname,
    correlation  -- -1 to 1: how sorted is data relative to physical order
FROM pg_stats
WHERE tablename = 'orders';
-- Low correlation on indexed column = index less effective