Lesson 5.6: Performance Monitoring

Essential Metrics

-- Database size
SELECT
    pg_size_pretty(pg_database_size(current_database())) AS db_size;

-- Table sizes
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 10;

-- Cache hit ratio
SELECT
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;
-- Goal: > 0.99 (99%)

-- Active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- Long-running queries
SELECT
    pid,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state != 'idle' AND query_start < now() - interval '1 minute'
ORDER BY duration DESC;

Alerts and Thresholds

Set up monitoring:

• Cache hit ratio < 0.95 → Increase shared_buffers
• Active connections > 80% of max_connections → Add connection pooling
• Replication lag > 100MB → Investigate network or load
• Slow queries > 1s → Add indexes or optimize

Module 5 Summary

Key Takeaways

1. Use pg_stat_statements to find slow queries
2. Bulk load with COPY for massive writes (500x faster than INSERTs)
3. Partition time-series tables to manage growth
4. Read replicas scale read throughput horizontally
5. Cache aggressively at multiple layers (Redis, materialized views, app cache)
6. Monitor continuously (cache hit ratio, replication lag, slow queries)

Performance Checklist

• Enabled pg_stat_statements
• Identified top 10 slowest queries
• Optimized with indexes and query rewrites
• Bulk loading uses COPY, not row-by-row INSERTs
• Time-series tables partitioned by date
• Read replicas for analytics and dashboards
• Caching layer (Redis/Memcached) for hot data
• Monitoring dashboards for key metrics

What's Next

Module 6 covers the intersection of AI and SQL:

• How vector search actually works
• pgvector vs dedicated vector databases
• Writing hybrid semantic + structured queries
• Storing AI agent context, memory, and logs

---

Continue to Module 6: AI + SQL - Vectors and Hybrid Systems