Database Maintenance

This guide covers database cleanup, retention policies, and optimization for Milvaion.

Why Maintenance Matters

Without cleanup, the JobOccurrences table grows continuously:

Executions	Approximate Size
100,000	~250 MB
1,000,000	~2.5 GB
10,000,000	~25 GB

Each occurrence stores:

• Status and timestamps
• JSONB logs (can be several KB each)
• Exception details
• Status change history

---

Automated Cleanup Jobs

Milvaion includes built-in cleanup jobs that run automatically via SQL Worker. For detailed information about SQL Worker configuration and usage, see see Maintenance Worker documentation.

---

Manual SQL Cleanup

For emergency cleanup, run SQL directly:

-- Delete completed occurrences older than 7 days
DELETE FROM "JobOccurrences"
WHERE "Status" = 2
  AND "EndTime" < NOW() - INTERVAL '7 days';

-- Check how many would be deleted (dry run)
SELECT COUNT(*) FROM "JobOccurrences"
WHERE "Status" = 2
  AND "EndTime" < NOW() - INTERVAL '7 days';

-- Delete all occurrences older than 30 days (any status)
DELETE FROM "JobOccurrences"
WHERE "EndTime" < NOW() - INTERVAL '30 days';

-- Reclaim disk space after large delete
VACUUM FULL "JobOccurrences";

Warning

VACUUM FULL locks the table. For large tables, use regular VACUUM instead.

Batch Deletion (Large Tables)

For tables with millions of rows, delete in batches:

-- Delete in batches of 10,000
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    LOOP
        DELETE FROM "JobOccurrences"
        WHERE "Id" IN (
            SELECT "Id" FROM "JobOccurrences"
            WHERE "Status" = 2
              AND "EndTime" < NOW() - INTERVAL '7 days'
            LIMIT 10000
        );
        
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        
        IF deleted_count = 0 THEN
            EXIT;
        END IF;
        
        -- Commit and pause to reduce load
        COMMIT;
        PERFORM pg_sleep(1);
    END LOOP;
END $$;

---

Failed Occurrences Cleanup

Failed occurrences in the FailedOccurrences table require manual review. Clean resolved ones:

-- Delete resolved failed occurrences older than 30 days
DELETE FROM "FailedOccurrences"
WHERE "Resolved" = true
  AND "ResolvedAt" < NOW() - INTERVAL '30 days';

-- Delete old unresolved failures (after 180 days, probably stale)
DELETE FROM "FailedOccurrences"
WHERE "Resolved" = false
  AND "FailedAt" < NOW() - INTERVAL '180 days';

---

Table Partitioning (Advanced)

For very high volumes (>1M occurrences/month), consider partitioning:

Create Partitioned Table

-- Create partitioned table by month
CREATE TABLE "JobOccurrences_new" (
    "Id" UUID PRIMARY KEY,
    "JobId" UUID NOT NULL,
    "Status" INTEGER NOT NULL,
    "CreatedAt" TIMESTAMP NOT NULL,
    "EndTime" TIMESTAMP,
    -- ... other columns
) PARTITION BY RANGE ("CreatedAt");

-- Create monthly partitions
CREATE TABLE "JobOccurrences_2025_01" 
PARTITION OF "JobOccurrences_new"
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE "JobOccurrences_2025_02" 
PARTITION OF "JobOccurrences_new"
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

Drop Old Partitions

-- Much faster than DELETE for large datasets
DROP TABLE "JobOccurrences_2024_01";

---

Indexing

Required Indexes

Milvaion creates these automatically:

-- Job ID lookup
CREATE INDEX IX_JobOccurrences_JobId ON "JobOccurrences" ("JobId");

-- Status filtering
CREATE INDEX IX_JobOccurrences_Status ON "JobOccurrences" ("Status");

-- Date range queries
CREATE INDEX IX_JobOccurrences_CreatedAt ON "JobOccurrences" ("CreatedAt" DESC);

-- Cleanup queries
CREATE INDEX IX_JobOccurrences_Status_EndTime 
ON "JobOccurrences" ("Status", "EndTime");

Execution Log Indexes

JobOccurrenceLogs is the largest table in the system - one row per log line per execution. These support the occurrence detail view and the log search and summary endpoints described in Monitoring:

-- All lines of one run; also the join path when filtering logs by job
CREATE INDEX IX_JobOccurrenceLogs_OccurrenceId_Timestamp
ON "JobOccurrenceLogs" ("OccurrenceId", "Timestamp");

-- Log search and summary both bound the scan by time first
CREATE INDEX IX_JobOccurrenceLogs_Timestamp
ON "JobOccurrenceLogs" ("Timestamp" DESC);

-- "Only the errors in this window" - the narrowing that has to stay fast during an incident
CREATE INDEX IX_JobOccurrenceLogs_Level_Timestamp
ON "JobOccurrenceLogs" ("Level", "Timestamp" DESC);

Optional: Trigram Index for Message Search

Message search uses ILIKE '%term%', which no B-tree can serve. Without a trigram index the search still works, but only as fast as the time filter makes it - which is why searchTerm should be paired with since.

If you search logs often and can afford the write overhead and disk:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX IX_JobOccurrenceLogs_Message_Trgm
ON "JobOccurrenceLogs" USING gin ("Message" gin_trgm_ops);

Left opt-in because a GIN index on a high-write table costs on every insert, and log inserts are the highest-volume writes Milvaion makes.

Check for Missing Indexes

-- Find slow queries
SELECT query, mean_time, calls
FROM pg_stat_statements
WHERE query LIKE '%JobOccurrences%'
ORDER BY mean_time DESC
LIMIT 10;

-- Check index usage
SELECT 
    indexrelname,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND relname = 'JobOccurrences'
ORDER BY idx_scan DESC;

---

Storage Optimization

JSONB Compression

Logs are stored as JSONB. For large log payloads:

-- Check average log size per occurrence
SELECT 
    AVG(pg_column_size("Logs")) as avg_log_bytes,
    MAX(pg_column_size("Logs")) as max_log_bytes
FROM "JobOccurrences"
WHERE "Logs" IS NOT NULL;

If logs are too large (>10KB average), consider:

1. Reducing log verbosity in jobs
2. Truncating long messages
3. Moving logs to separate table/storage

Vacuum and Analyze

Schedule regular maintenance:

-- Update statistics (helps query planner)
ANALYZE "JobOccurrences";
ANALYZE "ScheduledJobs";

-- Reclaim dead tuple space (non-blocking)
VACUUM "JobOccurrences";

Or configure autovacuum:

# postgresql.conf
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_delay = 10ms

---

Monitoring Storage

Check Table Sizes

SELECT 
    relname as table_name,
    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_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Check Row Counts

SELECT 
    'JobOccurrences' as table_name,
    COUNT(*) as total_rows,
    COUNT(*) FILTER (WHERE "Status" = 2) as completed,
    COUNT(*) FILTER (WHERE "Status" = 3) as failed,
    COUNT(*) FILTER (WHERE "CreatedAt" > NOW() - INTERVAL '7 days') as last_7_days
FROM "JobOccurrences";

Alert on Growth

-- Check if table exceeds threshold
SELECT CASE 
    WHEN pg_total_relation_size('"JobOccurrences"') > 5368709120 -- 5GB
    THEN 'ALERT: JobOccurrences exceeds 5GB'
    ELSE 'OK'
END as status;

---

Backup Considerations

Before Major Cleanup

# Create backup before large delete operations
pg_dump -h localhost -U milvaion -t JobOccurrences MilvaionDb > backup_occurrences.sql

Exclude Large Tables from Daily Backups

# Backup without occurrence data (structure only)
pg_dump -h localhost -U milvaion \
  --exclude-table-data='JobOccurrences' \
  MilvaionDb > backup_without_occurrences.sql

---

Maintenance Checklist

Weekly

• Check JobOccurrences row count
• Verify cleanup jobs ran successfully
• Check disk space usage

Monthly

• Review failed occurrence backlog
• Analyze slow query logs
• Run VACUUM ANALYZE

Quarterly

• Review retention policies
• Archive old data if needed
• Check index health and fragmentation
• Review storage growth trends

---

What's Next?

• Security – Security considerations
• MilvaionUI – UI screenshoots