1-- postgresql.conf
2
3-- Shared Buffers: 25% of RAM for dedicated database servers
4shared_buffers = '32GB'
5
6-- Effective Cache: Help planner understand available OS cache
7effective_cache_size = '96GB'  -- ~75% of total RAM
8
9-- Work Memory: Per-operation memory for sorts/hashes
10work_mem = '256MB'  -- Increased for complex queries
11
12-- Maintenance Work Memory: For VACUUM, CREATE INDEX, etc.
13maintenance_work_mem = '2GB'
14
15-- WAL Buffers: Write-ahead log buffering
16wal_buffers = '16MB'
17
18-- Checkpoint Settings: Balance between recovery time and I/O
19checkpoint_timeout = '15min'
20checkpoint_completion_target = 0.9
21max_wal_size = '4GB'
22min_wal_size = '1GB'
23

1# pgbouncer.ini
2
3[databases]
4trading_db = host=localhost port=5432 dbname=trading
5
6[pgbouncer]
7# Transaction pooling for stateless requests
8pool_mode = transaction
9
10# Connection limits
11max_client_conn = 1000
12default_pool_size = 25
13reserve_pool_size = 5
14
15# Timeout settings
16query_timeout = 30
17idle_transaction_timeout = 60
18

1-- Enable parallel query execution
2max_parallel_workers_per_gather = 4
3max_parallel_workers = 8
4parallel_setup_cost = 100
5parallel_tuple_cost = 0.01
6

1-- Order lookup by account and date range
2CREATE INDEX idx_orders_account_created ON orders(
3    account_id,
4    created_at DESC
5) INCLUDE (order_type, status, total_amount);
6
7-- Trade lookup by symbol and timestamp
8CREATE INDEX idx_trades_symbol_time ON trades(
9    symbol,
10    executed_at DESC
11) INCLUDE (price, quantity, side);
12
13-- INCLUDE clause (v11+) adds columns to index without making them part of search key
14-- This enables index-only scans for these queries
15

1-- Index only active orders
2CREATE INDEX idx_active_orders ON orders(account_id, created_at)
3WHERE status IN ('pending', 'partially_filled');
4
5-- Index only recent trades (last 90 days)
6CREATE INDEX idx_recent_trades ON trades(symbol, executed_at)
7WHERE executed_at > CURRENT_DATE - INTERVAL '90 days';
8
9-- These partial indexes are much smaller and faster to scan
10

1-- Index on normalized symbol (uppercase)
2CREATE INDEX idx_trades_upper_symbol ON trades(UPPER(symbol));
3
4-- Index on JSON field
5CREATE INDEX idx_orders_metadata_priority ON orders(
6    (metadata->>'priority')
7) WHERE metadata->>'priority' IS NOT NULL;
8
9-- GIN index for JSON containment queries
10CREATE INDEX idx_orders_metadata_gin ON orders USING gin(metadata);
11

1EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
2SELECT 
3    o.order_id,
4    o.account_id,
5    o.total_amount,
6    COUNT(t.trade_id) as trade_count,
7    SUM(t.quantity) as total_quantity
8FROM orders o
9LEFT JOIN trades t ON t.order_id = o.order_id
10WHERE o.created_at >= CURRENT_DATE - INTERVAL '7 days'
11    AND o.status = 'filled'
12GROUP BY o.order_id, o.account_id, o.total_amount
13ORDER BY o.created_at DESC
14LIMIT 100;
15

1-- SLOW: Computing running balance on every query
2SELECT 
3    transaction_id,
4    amount,
5    SUM(amount) OVER (
6        PARTITION BY account_id 
7        ORDER BY created_at
8    ) as running_balance
9FROM transactions
10WHERE account_id = $1
11ORDER BY created_at DESC;
12
13-- FAST: Maintain running balance as a column
14CREATE TABLE account_balances (
15    account_id BIGINT PRIMARY KEY,
16    balance DECIMAL(20, 8) NOT NULL,
17    last_updated TIMESTAMPTZ NOT NULL
18);
19
20-- Update balance in transaction
21BEGIN;
22    INSERT INTO transactions (account_id, amount, ...)
23    VALUES ($1, $2, ...);
24    
25    UPDATE account_balances
26    SET balance = balance + $2,
27        last_updated = NOW()
28    WHERE account_id = $1;
29COMMIT;
30

1-- Calculate price changes efficiently
2WITH price_data AS (
3    SELECT 
4        symbol,
5        price,
6        executed_at,
7        LAG(price) OVER (PARTITION BY symbol ORDER BY executed_at) as prev_price
8    FROM trades
9    WHERE executed_at >= NOW() - INTERVAL '1 hour'
10        AND symbol = ANY($1)  -- Array of symbols
11)
12SELECT 
13    symbol,
14    price,
15    executed_at,
16    CASE 
17        WHEN prev_price IS NOT NULL THEN 
18            ((price - prev_price) / prev_price * 100)
19        ELSE NULL
20    END as price_change_pct
21FROM price_data
22ORDER BY executed_at DESC;
23

1-- Create partitioned trades table
2CREATE TABLE trades (
3    trade_id BIGSERIAL,
4    order_id BIGINT NOT NULL,
5    symbol VARCHAR(20) NOT NULL,
6    price DECIMAL(20, 8) NOT NULL,
7    quantity DECIMAL(20, 8) NOT NULL,
8    side CHAR(1) NOT NULL,
9    executed_at TIMESTAMPTZ NOT NULL,
10    metadata JSONB
11) PARTITION BY RANGE (executed_at);
12
13-- Create monthly partitions
14CREATE TABLE trades_2024_10 PARTITION OF trades
15    FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');
16
17CREATE TABLE trades_2024_11 PARTITION OF trades
18    FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');
19
20-- Indexes on each partition
21CREATE INDEX ON trades_2024_10(symbol, executed_at);
22CREATE INDEX ON trades_2024_11(symbol, executed_at);
23
24-- Automatically drop old partitions
25DROP TABLE trades_2024_07;
26

1-- GOOD: Query includes partition key
2SELECT * FROM trades
3WHERE executed_at >= '2024-11-01'
4    AND executed_at < '2024-11-02'
5    AND symbol = 'AAPL';
6-- PostgreSQL scans only the November partition
7
8-- BAD: Missing partition key
9SELECT * FROM trades
10WHERE symbol = 'AAPL'
11    AND trade_id > 1000000;
12-- PostgreSQL must scan all partitions
13

1-- Use advisory locks for account operations
2CREATE OR REPLACE FUNCTION debit_account(
3    p_account_id BIGINT,
4    p_amount DECIMAL
5) RETURNS BOOLEAN AS $$
6DECLARE
7    v_current_balance DECIMAL;
8BEGIN
9    -- Acquire advisory lock (account_id as lock key)
10    PERFORM pg_advisory_xact_lock(p_account_id);
11    
12    -- Get current balance
13    SELECT balance INTO v_current_balance
14    FROM account_balances
15    WHERE account_id = p_account_id;
16    
17    -- Check sufficient funds
18    IF v_current_balance < p_amount THEN
19        RETURN FALSE;
20    END IF;
21    
22    -- Debit account
23    UPDATE account_balances
24    SET balance = balance - p_amount,
25        last_updated = NOW()
26    WHERE account_id = p_account_id;
27    
28    RETURN TRUE;
29END;
30$$ LANGUAGE plpgsql;
31

1-- Instead of individual inserts
2INSERT INTO trades (symbol, price, quantity, executed_at)
3VALUES ('AAPL', 150.25, 100, NOW());
4-- Repeat 10,000 times...
5
6-- Use multi-row insert
7INSERT INTO trades (symbol, price, quantity, executed_at)
8VALUES 
9    ('AAPL', 150.25, 100, NOW()),
10    ('GOOGL', 2800.50, 50, NOW()),
11    ('MSFT', 380.75, 200, NOW())
12    -- ... up to a few thousand rows
13ON CONFLICT DO NOTHING;
14
15-- Or use COPY for millions of rows
16COPY trades (symbol, price, quantity, executed_at)
17FROM STDIN WITH (FORMAT CSV);
18

1-- Check table bloat
2SELECT 
3    schemaname,
4    tablename,
5    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
6    n_dead_tup,
7    n_live_tup,
8    ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_pct
9FROM pg_stat_user_tables
10WHERE n_live_tup > 0
11ORDER BY n_dead_tup DESC
12LIMIT 20;
13
14-- Identify slow queries
15SELECT 
16    query,
17    calls,
18    mean_exec_time,
19    max_exec_time,
20    stddev_exec_time,
21    rows
22FROM pg_stat_statements
23WHERE mean_exec_time > 100  -- More than 100ms average
24ORDER BY mean_exec_time DESC
25LIMIT 20;
26
27-- Check index usage
28SELECT 
29    schemaname,
30    tablename,
31    indexname,
32    idx_scan,
33    idx_tup_read,
34    idx_tup_fetch,
35    pg_size_pretty(pg_relation_size(indexrelid)) as size
36FROM pg_stat_user_indexes
37WHERE idx_scan < 100  -- Rarely used indexes
38    AND pg_relation_size(indexrelid) > 1048576  -- Larger than 1MB
39ORDER BY pg_relation_size(indexrelid) DESC;
40

1-- Per-table autovacuum tuning
2ALTER TABLE account_balances SET (
3    autovacuum_vacuum_scale_factor = 0.05,  -- Vacuum at 5% dead tuples
4    autovacuum_analyze_scale_factor = 0.02, -- Analyze at 2% changes
5    autovacuum_vacuum_cost_delay = 10,      -- Reduce I/O impact
6    autovacuum_vacuum_cost_limit = 1000
7);
8
9-- For very active tables, consider manual VACUUM in off-peak hours
10VACUUM (ANALYZE, VERBOSE) account_balances;
11

1-- On primary server
2ALTER SYSTEM SET wal_level = 'replica';
3ALTER SYSTEM SET max_wal_senders = 10;
4ALTER SYSTEM SET wal_keep_size = '1GB';
5
6-- Create replication user
7CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secure_password';
8
9-- On standby server (recovery.conf / postgresql.auto.conf)
10primary_conninfo = 'host=primary_host port=5432 user=replicator password=secure_password'
11hot_standby = on
12

1# Application code: Route queries intelligently
2class DatabaseRouter:
3    def route_query(self, query_type, query_sql):
4        if query_type == 'read' and 'REPORT' in query_sql:
5            return 'replica_connection'
6        return 'primary_connection'
7
8# Ensures transactional queries go to primary
9# while reports use replicas
10