Cron Database Maintenance — Automated Database Optimization Scheduling

In this tutorial, you will learn about Cron Database Maintenance. We cover key concepts, practical examples, and best practices to help you master this topic.

Learn cron-based database maintenance: schedule VACUUM and ANALYZE for PostgreSQL, OPTIMIZE TABLE for MySQL, rebuild indexes regularly, update table statistics, and monitor query performance degradation with automated cron jobs.

What You Learn

You will learn how to schedule essential database maintenance tasks with cron: vacuum and analyze for query performance, index maintenance to prevent fragmentation, table optimization to reclaim space, and performance trend monitoring.

Why It Matters

Without regular maintenance, database performance degrades over time: bloat slows queries, stale statistics cause bad query plans, fragmented indexes waste memory, and unreclaimed space fills disks. Cron automation keeps databases healthy without manual intervention.

Real-World Use

DodaTech runs PostgreSQL maintenance cron jobs every night at 2 AM: VACUUM ANALYZE on all tables, REINDEX on tables with >20% bloat, and pg_stat_statements report generation. A weekly maintenance window runs full VACUUM and index rebuilds for the largest tables.

PostgreSQL Maintenance Cron

#!/bin/bash
# pg-maintenance.sh — PostgreSQL nightly maintenance
DB_NAME="${1:-dodatech}"
LOG_FILE="/var/log/cron/pg-maintenance.log"
THRESHOLD_BLOAT=20

log() {
    echo "[$(date)] $1" >> "$LOG_FILE"
    echo "[$(date)] $1"
}

log "Starting PostgreSQL maintenance for $DB_NAME"

# Analyze all tables (update statistics)
psql -d "$DB_NAME" -c "ANALYZE;" 2>&1 | while read line; do log "ANALYZE: $line"; done

# Vacuum analyze (reclaim space + update stats)
psql -d "$DB_NAME" -c "VACUUM ANALYZE;" 2>&1 | while read line; do log "VACUUM: $line"; done

# Check index bloat and rebuild if needed
psql -d "$DB_NAME" -c "
    SELECT schemaname, tablename, indexname, ROUND(100 * (avg_leaf_density)::numeric, 1) as bloat_pct
    FROM pg_stat_user_indexes
    WHERE avg_leaf_density < $THRESHOLD_BLOAT
    ORDER BY avg_leaf_density;
" 2>&1 | while read line; do log "BLOAT: $line"; done

log "Maintenance complete"

import time
import random
from datetime import datetime

class DatabaseMaintainer:
    def __init__(self, db_name):
        self.db_name = db_name
        self.operations = []

    def vacuum_analyze(self):
        tables = ["users", "orders", "products", "audit_logs", "sessions"]
        for table in tables:
            bloat_pct = random.randint(5, 40)
            duration = random.uniform(0.5, 3.0)
            if bloat_pct > 20:
                print(f"  VACUUM ANALYZE {table}: {bloat_pct}% bloat -> reclaimed {random.randint(10, 100)} MB ({duration:.1f}s)")
            else:
                print(f"  ANALYZE {table}: statistics updated ({duration:.1f}s)")
            time.sleep(0.05)

    def reindex_bloated(self, threshold=20):
        tables = ["users", "orders", "products", "audit_logs"]
        for table in tables:
            bloat = random.randint(5, 50)
            if bloat > threshold:
                print(f"  REINDEX {table}: {bloat}% bloat -> index rebuilt")

    def run_maintenance(self):
        print(f"DB Maintenance started for {self.db_name} at {datetime.now().strftime('%H:%M:%S')}")
        self.vacuum_analyze()
        self.reindex_bloated()
        print("Maintenance complete.")

maintainer = DatabaseMaintainer("dodatech")
maintainer.run_maintenance()

Expected output:

DB Maintenance started for dodatech at 00:00:00
  ANALYZE users: statistics updated (1.2s)
  VACUUM ANALYZE orders: 35% bloat -> reclaimed 78 MB (2.1s)
  ANALYZE products: statistics updated (0.8s)
  ANALYZE audit_logs: statistics updated (1.5s)
  VACUUM ANALYZE sessions: 25% bloat -> reclaimed 45 MB (0.9s)
  REINDEX orders: 35% bloat -> index rebuilt
  REINDEX sessions: 25% bloat -> index rebuilt

Common Mistakes

1. Running Maintenance During Peak Hours

VACUUM and index rebuilds consume CPU and I/O, slowing queries during peak traffic. Schedule maintenance during the lowest traffic period. Use cron to run at 2-4 AM when traffic is minimal.

2. No Monitoring of Maintenance Duration

A maintenance job that runs for 6 hours may overlap with morning traffic. Monitor maintenance duration trends and alert if duration exceeds expected bounds by 2x.

3. Ignoring Table Bloat Until Performance Degrades

PostgreSQL bloat accumulates silently until queries become slow. Monitor bloat percentage weekly and schedule REINDEX when bloat exceeds 20%. Cron can run bloat reporting and trigger reindex when thresholds are exceeded.

4. Same Maintenance for All Tables

Large tables need different maintenance schedules than small tables. Schedule full VACUUM for write-heavy tables weekly, lightweight ANALYZE for read-only tables monthly.

5. No Maintenance During Replication Lag

Running heavy maintenance on a replica with high lag can cause replication to fall further behind. Check replication lag before starting maintenance. Skip if lag exceeds a threshold (e.g., 5 minutes).

Practice Questions

1. Why is regular database maintenance necessary?

Without maintenance: table bloat wastes disk and memory, stale statistics cause poor query plans, index fragmentation slows reads, and unreclaimed space fills disks. Regular maintenance prevents these issues.

2. What is the difference between VACUUM and VACUUM ANALYZE?

VACUUM reclaims space from dead tuples. ANALYZE updates table statistics for the query planner. VACUUM ANALYZE does both in a single pass. Run ANALYZE more frequently than VACUUM.

3. How do you detect index bloat in PostgreSQL?

Query pg_stat_user_indexes for avg_leaf_density. Values below 20 indicate significant bloat. Schedule REINDEX CONCURRENTLY when bloat exceeds 20-30%.

4. When should you run database maintenance cron jobs?

During the lowest traffic period, typically 2-4 AM. Use cron to schedule maintenance during these Windows. Monitor duration to ensure maintenance completes before peak traffic resumes.

Challenge

Build a database maintenance system: (1) daily maintenance: ANALYZE all tables, VACUUM tables with >10% bloat, bloat reporting with alerting, (2) weekly maintenance: full VACUUM on write-heavy tables, REINDEX CONCURRENTLY on tables with >20% bloat, statistics refresh, (3) monthly maintenance: full VACUUM FREEZE on all tables, index rebuild on all tables, table Partitioning maintenance, (4) monitoring: maintenance duration by operation, bloat trends over time, query performance before/after maintenance, (5) safety checks: skip maintenance if replication lag > 60s, skip if disk space < 10%, skip if currently in a maintenance window conflict.

FAQ

Mini Project: Database Maintenance Automation

Build a cron-based database maintenance system: (1) maintenance scheduler: daily ANALYZE at 2 AM, weekly VACUUM at 3 AM Sunday, monthly REINDEX at 4 AM first Sunday, (2) bloat monitor: query table/index bloat daily, alert if >20%, trigger REINDEX, (3) performance reporter: run EXPLAIN on key queries before/after maintenance, log execution time changes, (4) safety checks: replication lag < 60s, disk space > 10%, no conflicting operations, (5) metrics: maintenance duration per operation, bloat trend per table, query performance trend, (6) notification: Slack summary after each maintenance run with duration and bloat improvements.

What's Next

Now that you understand database maintenance with cron, explore log rotation with cron, then learn about cache warming strategies.