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
- Use pg_stat_statements to find slow queries
- Bulk load with COPY for massive writes (500x faster than INSERTs)
- Partition time-series tables to manage growth
- Read replicas scale read throughput horizontally
- Cache aggressively at multiple layers (Redis, materialized views, app cache)
- 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
التالي · الدرس 34 من 45
How Vector Search Actually Works
قراءة 2 دقيقة
أنهيت 33 من 45 درسًا

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

