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

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

