Lesson 7.7: Deployment and Maintenance

Initial Setup Script

-- Run this to set up production database

BEGIN;

-- Core extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Create all tables (use scripts from lessons above)

-- Set up partitioning automation (requires pg_partman)
CREATE EXTENSION IF NOT EXISTS pg_partman;

SELECT create_parent('public.model_predictions', 'created_at', 'native', 'monthly');

-- Configure auto-partition creation
UPDATE part_config
SET premake = 3,  -- Create 3 months in advance
    retention_keep_table = false,
    retention_keep_index = false,
    retention = '6 months'  -- Drop partitions older than 6 months
WHERE parent_table = 'public.model_predictions';

COMMIT;

Maintenance Tasks

-- Daily cron job
DO $$
BEGIN
    -- Refresh materialized views
    REFRESH MATERIALIZED VIEW CONCURRENTLY feature_stats;
    REFRESH MATERIALIZED VIEW CONCURRENTLY model_performance_daily;

    -- Update user features
    PERFORM refresh_user_features();

    -- Vacuum analyze for statistics
    VACUUM ANALYZE;

    -- Check for bloat
    -- REINDEX if needed
END $$;

Production Checklist

• All tables have appropriate indexes
• Time-series tables partitioned
• Soft deletes implemented where needed
• Materialized views refreshing automatically
• pg_stat_statements enabled for monitoring
• Connection pooling configured (PgBouncer)
• Read replicas for analytics
• Backup and recovery tested
• Monitoring dashboard deployed

Key Takeaways

• Extensions (vector, pg_stat_statements, pg_partman) enable advanced features
• pg_partman automates partition creation and retention
• Premake parameter creates future partitions in advance
• Retention policies automatically drop old partitions
• Daily maintenance refreshes materialized views and updates statistics
• VACUUM ANALYZE keeps statistics fresh for query planner
• Connection pooling (PgBouncer) manages connection overhead
• Production checklist ensures system readiness before launch
• Read replicas offload analytics queries from primary database