Maintenance

Routine maintenance tasks to keep your Mantis database healthy and performant.

Routine Maintenance

Autovacuum

PostgreSQL autovacuum handles most maintenance automatically:

# postgresql.conf
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1

Monitor Autovacuum

-- Check autovacuum activity
SELECT relname, last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;

-- Tables needing vacuum
SELECT schemaname, relname, n_dead_tup,
       n_live_tup, round(n_dead_tup * 100.0 / NULLIF(n_live_tup, 0), 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Manual Vacuum

For immediate maintenance:

-- Vacuum specific table
VACUUM (VERBOSE) deployment_history;

-- Vacuum and analyze
VACUUM ANALYZE deployment_history;

-- Full vacuum (requires exclusive lock)
VACUUM FULL deployment_history;

Index Maintenance

Check Index Health

-- Index usage statistics
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Unused indexes (candidates for removal)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pk_%'
ORDER BY schemaname, tablename;

-- Index sizes
SELECT indexrelname as index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Reindex

Rebuild corrupted or bloated indexes:

-- Reindex single index
REINDEX INDEX idx_dh_status;

-- Reindex table
REINDEX TABLE deployment_history;

-- Reindex concurrently (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_dh_status;

Index Bloat Detection

-- Check for bloated indexes
SELECT
    current_database() AS db,
    schemaname,
    tablename,
    indexrelname AS index_name,
    pg_size_pretty(index_size) AS index_size,
    pg_size_pretty(index_size - expected_size) AS bloat,
    round((index_size - expected_size) * 100.0 / index_size, 2) AS bloat_pct
FROM (
    SELECT
        schemaname,
        tablename,
        indexrelname,
        pg_relation_size(indexrelid) AS index_size,
        (avg_leaf_density / 90.0) * pg_relation_size(indexrelid) AS expected_size
    FROM pg_stat_user_indexes
    JOIN pg_index USING (indexrelid)
    JOIN pg_class ON indexrelid = pg_class.oid
    CROSS JOIN LATERAL (
        SELECT (100.0 - COALESCE(avg_leaf_density, 90.0)) AS avg_leaf_density
        FROM pg_stats
        WHERE tablename = pg_class.relname
        LIMIT 1
    ) s
    WHERE pg_relation_size(indexrelid) > 10485760  -- > 10MB
) t
WHERE index_size > expected_size * 1.3  -- > 30% bloat
ORDER BY bloat DESC;

Table Maintenance

Check Table Statistics

-- Table sizes
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) as total_size,
       pg_size_pretty(pg_relation_size(relid)) as table_size,
       pg_size_pretty(pg_indexes_size(relid)) as index_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Row counts
SELECT relname, n_live_tup as row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

Table Bloat

-- Estimate table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
    pg_size_pretty(
        pg_total_relation_size(schemaname || '.' || tablename) -
        pg_relation_size(schemaname || '.' || tablename)
    ) as bloat_estimate
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

Reduce Bloat

-- Option 1: VACUUM FULL (locks table)
VACUUM FULL tablename;

-- Option 2: pg_repack (online, no locks)
-- Install extension first
CREATE EXTENSION pg_repack;

-- Repack table
SELECT pg_repack.repack_table('public.deployment_history');

Monitoring Queries

Slow Queries

-- Enable query logging
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 1 second
SELECT pg_reload_conf();

-- Find slow queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 20;

Lock Monitoring

-- Current locks
SELECT pid, relation::regclass, mode, granted
FROM pg_locks
WHERE relation IS NOT NULL
ORDER BY relation;

-- Blocked queries
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

Connection Monitoring

-- Connection summary
SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'mantis'
GROUP BY state;

-- Active queries
SELECT pid, usename, application_name,
       now() - query_start as query_duration,
       state, query
FROM pg_stat_activity
WHERE datname = 'mantis'
AND state = 'active'
ORDER BY query_start;

Scheduled Maintenance

Daily Tasks

Create maintenance script:

#!/bin/bash
# Analyze tables
psql -U mantis -d mantis -c "ANALYZE;"

# Check for bloat
psql -U mantis -d mantis -c "
SELECT relname, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;"

Weekly Tasks

#!/bin/bash
# Reindex concurrently
psql -U mantis -d mantis -c "
REINDEX INDEX CONCURRENTLY idx_dh_status;
REINDEX INDEX CONCURRENTLY idx_execution_steps_deployment_id;"

# Vacuum verbose
psql -U mantis -d mantis -c "VACUUM VERBOSE;"

Schedule with Cron

# Daily at 3 AM
0 3 * * * mantis /opt/mantis/scripts/daily-maintenance.sh >> /var/log/mantis/maintenance.log 2>&1

# Weekly on Sunday at 4 AM
0 4 * * 0 mantis /opt/mantis/scripts/weekly-maintenance.sh >> /var/log/mantis/maintenance.log 2>&1

Data Retention

Deployment History

Clean up old deployment data:

-- Delete deployment data older than 90 days
DELETE FROM deployment_logs
WHERE deployment_id IN (
    SELECT id FROM deployment_history
    WHERE created_at < NOW() - INTERVAL '90 days'
);

DELETE FROM execution_steps
WHERE deployment_id IN (
    SELECT id FROM deployment_history
    WHERE created_at < NOW() - INTERVAL '90 days'
);

DELETE FROM deployment_history
WHERE created_at < NOW() - INTERVAL '90 days';

-- Vacuum after large deletes
VACUUM ANALYZE deployment_history, execution_steps, deployment_logs;

Audit Logs

-- Archive old audit logs
CREATE TABLE audit_log_entries_archive AS
SELECT * FROM audit_log_entries
WHERE occurred_at < NOW() - INTERVAL '365 days';

DELETE FROM audit_log_entries
WHERE occurred_at < NOW() - INTERVAL '365 days';

VACUUM ANALYZE audit_log_entries;

Automated Retention Script

#!/bin/bash
RETENTION_DAYS=${RETENTION_DAYS:-90}

psql -U mantis -d mantis <<EOF
BEGIN;

-- Delete old deployment data
DELETE FROM deployment_logs
WHERE deployment_id IN (
    SELECT id FROM deployment_history
    WHERE created_at < NOW() - INTERVAL '${RETENTION_DAYS} days'
);

DELETE FROM execution_steps
WHERE deployment_id IN (
    SELECT id FROM deployment_history
    WHERE created_at < NOW() - INTERVAL '${RETENTION_DAYS} days'
);

DELETE FROM deployment_history
WHERE created_at < NOW() - INTERVAL '${RETENTION_DAYS} days';

COMMIT;

VACUUM ANALYZE;
EOF

Performance Tuning

Query Optimization

-- Analyze query plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM deployment_history
WHERE tenant_id = '...' AND status = 'success'
ORDER BY created_at DESC
LIMIT 50;

-- Check for sequential scans
SELECT relname, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC;

Missing Indexes

-- Find queries that might need indexes
SELECT schemaname, tablename, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch,
       seq_tup_read / NULLIF(seq_scan, 0) as avg_seq_tup
FROM pg_stat_user_tables
WHERE seq_scan > 100
AND seq_tup_read / NULLIF(seq_scan, 0) > 1000
ORDER BY seq_tup_read DESC;

Statistics Update

-- Update table statistics
ANALYZE deployment_history;

-- Increase statistics target for frequently queried columns
ALTER TABLE deployment_history ALTER COLUMN status SET STATISTICS 500;
ANALYZE deployment_history;

Health Checks

Automated Health Check Script

#!/bin/bash
# Connection test
if ! psql -U mantis -d mantis -c "SELECT 1" > /dev/null 2>&1; then
    echo "ERROR: Cannot connect to database"
    exit 1
fi

# Check for long-running queries
LONG_QUERIES=$(psql -U mantis -d mantis -t -c "
SELECT count(*) FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 minutes'")

if [ "$LONG_QUERIES" -gt 0 ]; then
    echo "WARNING: $LONG_QUERIES long-running queries detected"
fi

# Check for high bloat
BLOATED_TABLES=$(psql -U mantis -d mantis -t -c "
SELECT count(*) FROM pg_stat_user_tables
WHERE n_dead_tup > 100000")

if [ "$BLOATED_TABLES" -gt 0 ]; then
    echo "WARNING: $BLOATED_TABLES tables with high dead tuple count"
fi

# Check connection count
CONNECTIONS=$(psql -U mantis -d mantis -t -c "
SELECT count(*) FROM pg_stat_activity WHERE datname = 'mantis'")
MAX_CONN=$(psql -U mantis -d mantis -t -c "SHOW max_connections")

if [ "$CONNECTIONS" -gt $((MAX_CONN * 80 / 100)) ]; then
    echo "WARNING: Connection usage above 80% ($CONNECTIONS/$MAX_CONN)"
fi

echo "Database health check completed"

Next Steps

PostgreSQL Setup - Initial setup
Connection Pooling - Pool management
Backup & Recovery - Backup procedures