Database Capacity Planning

-- Database size and growth
SELECT
    pg_database_size(current_database()) AS db_size,
    pg_size_pretty(pg_database_size(current_database())) AS db_size_pretty;

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

-- Connection usage
SELECT
    count(*) AS current_connections,
    current_setting('max_connections')::int AS max_connections,
    ROUND(count(*)::numeric / current_setting('max_connections')::int * 100, 1) AS pct_used;

-- Monthly growth rate
SELECT
    date_trunc('month', created_at) AS month,
    COUNT(*) AS rows_added,
    pg_size_pretty(SUM(pg_column_size(*)) OVER (ORDER BY date_trunc('month', created_at))) AS cumulative_size
FROM orders
WHERE created_at > NOW() - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

-- Days until disk full (simple projection)
SELECT
    current_size AS current_gb,
    free_space AS free_gb,
    daily_growth_gb,
    ROUND(free_space / daily_growth_gb) AS days_until_full
FROM (
    SELECT
        pg_database_size(current_database()) / (1024*1024*1024) AS current_size,
        -- Replace with actual free disk space
        50 AS free_space,
        -- Calculate daily growth from last 30 days
        (pg_database_size(current_database()) - LAG(pg_database_size(current_database()))
            OVER (ORDER BY current_date)) / (1024*1024*1024) AS daily_growth_gb
) calc;

-- Calculate connection budget
-- max_connections = application_servers * connections_per_server + reserved

-- Example:
-- 10 application servers
-- 25 connections each (via PgBouncer)
-- 3 reserved for superuser
-- Total: 10 * 25 + 3 = 253

-- Verify current allocation
SHOW max_connections;           -- 253
SHOW superuser_reserved_connections;  -- 3

-- Check PgBouncer pool configuration
-- default_pool_size: 25
-- max_client_conn: 1000
-- Actual DB connections: number_of_databases * default_pool_size

-- Cache hit ratio (should be > 99%)
SELECT
    sum(blks_hit) AS cache_hits,
    sum(blks_read) AS disk_reads,
    ROUND(100.0 * sum(blks_hit) / GREATEST(sum(blks_hit) + sum(blks_read), 1), 2) AS hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

-- Work mem pressure: temporary files indicate insufficient work_mem
SELECT
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database
WHERE datname = current_database();

-- Check for OOM risk: total memory usage vs available
SHOW shared_buffers;   -- PostgreSQL cache
SHOW work_mem;         -- Per operation, multiply by concurrent operations
SHOW maintenance_work_mem;  -- For VACUUM, CREATE INDEX