# Memory Settings
# ===============

# Shared buffers: PostgreSQL's main memory cache
# Start with 25% of available RAM, max 8GB on most systems
shared_buffers = 4GB

# Effective cache size: Estimate of memory available for disk caching
# Set to 75% of total system memory
effective_cache_size = 12GB

# Work memory: Memory for sorts and hash operations per operation
# Be careful - this is per operation, not total
work_mem = 256MB

# Maintenance work memory: Memory for VACUUM, CREATE INDEX, etc.
maintenance_work_mem = 1GB

# Write-Ahead Log (WAL) Settings
# ==============================

# WAL buffers: Memory for WAL data before writing to disk
wal_buffers = 64MB

# Checkpoint settings: Balance between recovery time and I/O load
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
max_wal_size = 4GB

# Query Planner Settings
# ======================

# For SSDs, random reads are nearly as fast as sequential
random_page_cost = 1.1

# Number of concurrent I/O operations the disk can handle
effective_io_concurrency = 200

# Statistics target: Higher = better plans, slower ANALYZE
default_statistics_target = 200

-- Basic explain
EXPLAIN SELECT * FROM posts WHERE author_id = 123;

-- With actual execution stats (runs the query!)
EXPLAIN ANALYZE SELECT * FROM posts WHERE author_id = 123;

-- With buffer usage information
EXPLAIN (ANALYZE, BUFFERS)
SELECT p.*, a.name as author_name
FROM posts p
JOIN authors a ON p.author_id = a.id
WHERE p.status = 'PUBLISHED'
ORDER BY p.published_at DESC
LIMIT 10;

-- Output as JSON for programmatic analysis
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM posts WHERE title ILIKE '%python%';

-- Example output interpretation
/*
Limit  (cost=0.42..12.56 rows=10) (actual time=0.152..0.398 rows=10)
  ->  Index Scan using idx_posts_published on posts
        (cost=0.42..1842.66 rows=15234) (actual time=0.150..0.394 rows=10)
        Filter: (status = 'PUBLISHED')
        Rows Removed by Filter: 2
        Buffers: shared hit=15

Planning Time: 0.234 ms
Execution Time: 0.425 ms
*/

-- Key things to look for:
-- 1. "actual time" vs "cost" - large differences indicate estimation problems
-- 2. "Rows Removed by Filter" - high numbers suggest missing indexes
-- 3. "Buffers: shared hit" vs "read" - reads mean cache misses
-- 4. Sequential Scan on large tables - usually means missing index

-- Composite index for common queries
-- Column order matters: most selective first, or match WHERE clause order
CREATE INDEX idx_posts_status_published
ON posts (status, published_at DESC);

-- Partial index: only index rows you'll actually query
-- Smaller = faster to scan and update
CREATE INDEX idx_posts_published_recent
ON posts (published_at DESC)
WHERE status = 'PUBLISHED' AND published_at > NOW() - INTERVAL '1 year';

-- Covering index: includes all columns needed by query
-- Enables index-only scans (no table access needed)
CREATE INDEX idx_posts_list
ON posts (status, published_at DESC)
INCLUDE (title, excerpt, author_id);

-- Expression index: for computed values
CREATE INDEX idx_users_email_lower
ON users (LOWER(email));
-- Now this query uses the index:
-- SELECT * FROM users WHERE LOWER(email) = 'user@example.com'

-- GIN index for full-text search
CREATE INDEX idx_posts_search
ON posts USING GIN (to_tsvector('english', title || ' ' || content));

-- GIN index for JSONB queries
CREATE INDEX idx_posts_metadata
ON posts USING GIN (metadata);
-- Supports: metadata @> '{"featured": true}'

# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Pool mode: transaction is best for most web apps
pool_mode = transaction

# Pool size per user/database pair
default_pool_size = 20
max_client_conn = 1000
max_db_connections = 50

-- Enable query statistics (requires pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries by total time
SELECT
    round(total_exec_time::numeric, 2) as total_time_ms,
    calls,
    round(mean_exec_time::numeric, 2) as avg_time_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) as pct,
    left(query, 80) as query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Find unused indexes
SELECT
    schemaname || '.' || relname as table,
    indexrelname as index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) as size,
    idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE idx_scan < 50
  AND pg_relation_size(i.indexrelid) > 1024 * 1024  -- > 1MB
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- Table bloat (dead tuples)
SELECT
    schemaname || '.' || relname as table,
    n_live_tup as live_rows,
    n_dead_tup as dead_rows,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) as dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;