Performance Metrics Overview:
┌─────────────────────────────────────────────────────────────┐
│                    DATABASE PERFORMANCE                      │
├─────────────┬─────────────┬─────────────┬──────────────────┤
│   Query     │   Index     │   Table     │   Connection     │
│   Metrics   │   Metrics   │   Metrics   │   Metrics        │
├─────────────┼─────────────┼─────────────┼──────────────────┤
│ • Exec Time │ • Usage %   │ • Size      │ • Pool Usage     │
│ • Rows Read │ • Hit Ratio │ • Bloat     │ • Wait Time      │
│ • Scan Type │ • Unused    │ • Dead Rows │ • Active Count   │
│ • Plan Cost │ • Duplicate │ • Growth    │ • Idle Count     │
└─────────────┴─────────────┴─────────────┴──────────────────┘

-- Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries > 1 second
SELECT pg_reload_conf();

-- Find slowest queries using pg_stat_statements
SELECT
    ROUND((total_exec_time / 1000)::numeric, 2) as total_seconds,
    calls,
    ROUND((mean_exec_time)::numeric, 2) as avg_ms,
    ROUND((max_exec_time)::numeric, 2) as max_ms,
    rows,
    SUBSTRING(query, 1, 100) as query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Query performance schema
SELECT
    DIGEST_TEXT,
    COUNT_STAR as exec_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 3) as total_sec,
    ROUND(AVG_TIMER_WAIT/1000000000, 3) as avg_ms,
    SUM_ROWS_EXAMINED as rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- PostgreSQL EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2024-01-01';

-- Key metrics to watch:
-- 1. Actual Time vs Planning Time
-- 2. Rows (estimated vs actual)
-- 3. Scan type (Seq Scan vs Index Scan)
-- 4. Buffers (shared hit vs read)

-- Tables with high sequential scan ratio
SELECT
    schemaname,
    relname as table_name,
    seq_scan,
    idx_scan,
    CASE
        WHEN (seq_scan + idx_scan) = 0 THEN 0
        ELSE ROUND(100.0 * seq_scan / (seq_scan + idx_scan), 2)
    END as seq_scan_percentage,
    n_live_tup as row_count
FROM pg_stat_user_tables
WHERE n_live_tup > 1000  -- Tables with meaningful data
ORDER BY seq_scan_percentage DESC, seq_scan DESC
LIMIT 20;

-- Index usage and effectiveness
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan as times_used,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Unused indexes (candidates for removal)
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%_pkey'
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Overall cache hit ratio (should be > 99%)
SELECT
    'index hit ratio' as metric,
    ROUND(
        100.0 * sum(idx_blks_hit) /
        NULLIF(sum(idx_blks_hit + idx_blks_read), 0),
        2
    ) as percentage
FROM pg_statio_user_indexes

UNION ALL

SELECT
    'table hit ratio',
    ROUND(
        100.0 * sum(heap_blks_hit) /
        NULLIF(sum(heap_blks_hit + heap_blks_read), 0),
        2
    )
FROM pg_statio_user_tables;

-- Find potentially duplicate indexes
SELECT
    pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
    (array_agg(idx))[1] as idx1,
    (array_agg(idx))[2] as idx2,
    (array_agg(idx))[3] as idx3
FROM (
    SELECT
        indexrelid::regclass as idx,
        indrelid,
        indkey,
        coalesce(indexprs::text, ''),
        coalesce(indpred::text, '')
    FROM pg_index
) sub
GROUP BY indrelid, indkey, coalesce, coalesce
HAVING count(*) > 1
ORDER BY sum(pg_relation_size(idx)) DESC;

-- Simplified bloat estimation
SELECT
    current_database(),
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
    n_dead_tup as dead_rows,
    n_live_tup as live_rows,
    CASE
        WHEN n_live_tup > 0
        THEN ROUND(100.0 * n_dead_tup / n_live_tup, 2)
        ELSE 0
    END as dead_ratio_percent,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Create a size tracking table
CREATE TABLE IF NOT EXISTS db_size_history (
    recorded_at TIMESTAMP DEFAULT NOW(),
    table_name TEXT,
    total_size BIGINT,
    row_count BIGINT
);

-- Record current sizes (run periodically)
INSERT INTO db_size_history (table_name, total_size, row_count)
SELECT
    schemaname || '.' || relname,
    pg_total_relation_size(schemaname || '.' || relname),
    n_live_tup
FROM pg_stat_user_tables;

-- Analyze growth trends
SELECT
    table_name,
    MIN(recorded_at) as first_record,
    MAX(recorded_at) as last_record,
    MIN(total_size) as initial_size,
    MAX(total_size) as current_size,
    pg_size_pretty((MAX(total_size) - MIN(total_size))::bigint) as growth
FROM db_size_history
GROUP BY table_name
HAVING MAX(total_size) > MIN(total_size)
ORDER BY (MAX(total_size) - MIN(total_size)) DESC;

-- Current connection status
SELECT
    state,
    COUNT(*) as connection_count,
    COUNT(*) FILTER (WHERE state = 'active') as active,
    COUNT(*) FILTER (WHERE state = 'idle') as idle,
    COUNT(*) FILTER (WHERE state = 'idle in transaction') as idle_in_txn
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY state;

-- Long-running queries
SELECT
    pid,
    now() - pg_stat_activity.query_start as duration,
    state,
    SUBSTRING(query, 1, 100) as query_preview
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state = 'active';

-- Performance Health Score Calculator
WITH metrics AS (
    -- Index scan ratio
    SELECT
        'index_scan_ratio' as metric,
        COALESCE(
            ROUND(100.0 * SUM(idx_scan) / NULLIF(SUM(idx_scan + seq_scan), 0), 2),
            100
        ) as value
    FROM pg_stat_user_tables

    UNION ALL

    -- Cache hit ratio
    SELECT
        'cache_hit_ratio',
        COALESCE(
            ROUND(100.0 * sum(heap_blks_hit) /
                NULLIF(sum(heap_blks_hit + heap_blks_read), 0), 2),
            100
        )
    FROM pg_statio_user_tables

    UNION ALL

    -- Tables with primary keys percentage
    SELECT
        'tables_with_pk_ratio',
        ROUND(100.0 * COUNT(*) FILTER (
            WHERE EXISTS (
                SELECT 1 FROM information_schema.table_constraints tc
                WHERE tc.table_name = t.table_name
                AND tc.constraint_type = 'PRIMARY KEY'
            )
        ) / COUNT(*), 2)
    FROM information_schema.tables t
    WHERE table_schema = 'public'
    AND table_type = 'BASE TABLE'
)
SELECT
    metric,
    value,
    CASE
        WHEN value >= 90 THEN 'Excellent'
        WHEN value >= 70 THEN 'Good'
        WHEN value >= 50 THEN 'Fair'
        ELSE 'Poor'
    END as rating
FROM metrics;

Monitoring Frequency:
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│  Real-time:                                                 │
│  └── Active connections, running queries, locks            │
│                                                             │
│  Hourly:                                                    │
│  └── Slow query log analysis, cache hit ratios             │
│                                                             │
│  Daily:                                                     │
│  └── Index usage stats, table sizes, bloat levels          │
│                                                             │
│  Weekly:                                                    │
│  └── Unused indexes, growth trends, capacity planning      │
│                                                             │
│  Monthly:                                                   │
│  └── Full performance audit, index optimization review     │
│                                                             │
└─────────────────────────────────────────────────────────────┘