Optimization Techniques in PostgreSQL

-- Vacuum and analyze
VACUUM ANALYZE users;            -- Clean up and update stats
VACUUM FULL users;               -- Full cleanup (locks table)

-- Table partitioning
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Materialized views
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
  DATE_TRUNC('month', created_at) as month,
  SUM(amount) as total
FROM orders
GROUP BY 1;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

-- DETAILED_TAB:
-- Parallel queries (PostgreSQL 9.6+)
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 100;

-- Force index usage
SET enable_seqscan = OFF;  -- Force index (testing only!)

-- Work memory for sorts
SET work_mem = '256MB';    -- For current session

-- Prepared statements
PREPARE get_user (INT) AS
  SELECT * FROM users WHERE id = $1;
EXECUTE get_user(123);

-- Connection pooling config
ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET shared_buffers = '4GB';
SELECT pg_reload_conf();    -- Apply changes