Configuration Tuning

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

-- POSTGRESQL MEMORY CONFIGURATION
 
-- View current memory settings
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW effective_cache_size;
 
-- ═══════════════════════════════════════════════════════════════
-- SHARED_BUFFERS: Main buffer cache for table and index data
-- ═══════════════════════════════════════════════════════════════
-- Recommendation: 25% of total RAM (but no more than 8-16GB)
-- Beyond 8-16GB, PostgreSQL relies on OS cache
-- Example for 64GB server:
-- shared_buffers = '16GB'
 
-- Why not more? PostgreSQL double-buffers with OS page cache
-- Large shared_buffers can increase checkpoint time
 
-- ═══════════════════════════════════════════════════════════════
-- WORK_MEM: Memory per operation (sorts, hashes, bitmap operations)
-- ═══════════════════════════════════════════════════════════════
-- Caution: Setting is PER OPERATION, not per query or session
-- A complex query might use work_mem 10+ times
-- Formula: (Total RAM - shared_buffers) / (max_connections * 5)
 
-- For 64GB RAM, 16GB shared_buffers, 100 connections:
-- (64GB - 16GB) / (100 * 5) = ~96MB per operation
-- work_mem = '64MB'  -- Conservative starting point
 
-- Monitor for spills to disk (indicates work_mem too small):
SELECT query, temp_blks_read, temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC;
 
-- ═══════════════════════════════════════════════════════════════
-- MAINTENANCE_WORK_MEM: Memory for maintenance operations
-- ═══════════════════════════════════════════════════════════════
-- Used by: VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY
-- Can be much larger than work_mem (only one operation at a time)
-- Recommendation: 1-2GB for most systems
-- maintenance_work_mem = '2GB'
 
-- ═══════════════════════════════════════════════════════════════
-- EFFECTIVE_CACHE_SIZE: Planner hint for disk cache available
-- ═══════════════════════════════════════════════════════════════
-- Not an allocation, just tells planner what to expect
-- Recommendation: 50-75% of total RAM
-- effective_cache_size = '48GB'  -- For 64GB server
 
-- ═══════════════════════════════════════════════════════════════
-- Sample postgresql.conf for 64GB RAM server
-- ═══════════════════════════════════════════════════════════════
/*
# Memory Configuration
shared_buffers = '16GB'
work_mem = '64MB'
maintenance_work_mem = '2GB'
effective_cache_size = '48GB'
 
# Huge pages (Linux only, reduces memory overhead)
huge_pages = try
 
# Hash table sizing
hash_mem_multiplier = 2.0  -- PostgreSQL 13+
*/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

-- POSTGRESQL CONNECTION CONFIGURATION
 
-- View current settings
SHOW max_connections;
SELECT count(*) as current_connections FROM pg_stat_activity;
 
-- ═══════════════════════════════════════════════════════════════
-- MAX_CONNECTIONS: Hard limit on connections
-- ═══════════════════════════════════════════════════════════════
-- Formula: CPU cores × 4 (for typical OLTP)
-- Or calculated with memory:
--   (Available RAM - shared_buffers) / (work_mem × queries_per_connection)
-- Default 100 is often too low for modern applications
 
-- For 16-core server with 64GB RAM:
-- max_connections = 200
 
-- IMPORTANT: Each connection reserves ~10MB minimum
-- 1000 connections = 10GB just for connection overhead
 
-- ═══════════════════════════════════════════════════════════════
-- SUPERUSER_RESERVED_CONNECTIONS: Reserved for admin access
-- ═══════════════════════════════════════════════════════════════
-- When max_connections is reached, superusers can still connect
-- superuser_reserved_connections = 3  (default, usually fine)
 
-- ═══════════════════════════════════════════════════════════════
-- CONNECTION POOLING: PgBouncer (external pooler)
-- ═══════════════════════════════════════════════════════════════
-- PgBouncer sits between application and PostgreSQL
-- Maintains small pool of actual connections
-- Application connects to PgBouncer, which multiplexes
 
/* pgbouncer.ini example:
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
# Pool mode: session, transaction, or statement
pool_mode = transaction  # Best for most OLTP workloads
 
max_client_conn = 1000   # Application connections to PgBouncer
default_pool_size = 25   # Connections per database/user pair
min_pool_size = 5        # Minimum idle connections
reserve_pool_size = 5    # Extra connections for burst
*/
 
-- Monitor connection usage
SELECT 
    datname as database,
    usename as user,
    state,
    count(*) as connections
FROM pg_stat_activity
GROUP BY datname, usename, state
ORDER BY connections DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

-- POSTGRESQL WAL CONFIGURATION
 
-- View current settings
SHOW wal_level;
SHOW wal_buffers;
SHOW checkpoint_completion_target;
SHOW max_wal_size;
 
-- ═══════════════════════════════════════════════════════════════
-- SYNCHRONOUS_COMMIT: Durability vs Performance trade-off
-- ═══════════════════════════════════════════════════════════════
/*
Values:
- on (default): Wait for WAL flush to disk
- off: Don't wait for WAL flush (risk: lose last ~600ms on crash)
- local: Fsync locally, don't wait for replicas
- remote_write: Wait for replica to receive (not flush)
- remote_apply: Wait for replica to apply
*/
 
-- For less critical data, gain performance:
SET synchronous_commit = off;  -- Session level
-- ALTER DATABASE mydb SET synchronous_commit = off;  -- Database level
 
-- ═══════════════════════════════════════════════════════════════
-- WAL_BUFFERS: Shared memory for WAL before disk write
-- ═══════════════════════════════════════════════════════════════
-- Default: 1/32 of shared_buffers, max 16MB
-- Usually auto-calculation is fine
-- wal_buffers = -1  # Auto-tune (default in PG 9.1+)
 
-- For very high write volume:
-- wal_buffers = '64MB'
 
-- ═══════════════════════════════════════════════════════════════
-- CHECKPOINT CONFIGURATION
-- ═══════════════════════════════════════════════════════════════
-- Checkpoints flush all dirty buffers to disk
-- More frequent = faster recovery, more I/O interruption
-- Less frequent = slower recovery, smoother I/O
 
-- Size-based trigger (primary control)
-- max_wal_size = '4GB'   # Trigger checkpoint when WAL reaches this
-- min_wal_size = '2GB'   # Keep at least this much WAL
 
-- Time-based trigger (secondary)
-- checkpoint_timeout = '15min'  # Maximum time between checkpoints
 
-- Spread I/O over checkpoint interval
-- checkpoint_completion_target = 0.9  # Use 90% of interval for checkpoint
 
-- ═══════════════════════════════════════════════════════════════
-- FULL_PAGE_WRITES: Protection against partial page writes
-- ═══════════════════════════════════════════════════════════════
-- Default ON (recommended)
-- Writes full page image after each checkpoint
-- Can be disabled if filesystem guarantees atomic 8KB writes
-- full_page_writes = on
 
-- ═══════════════════════════════════════════════════════════════
-- Sample configuration for write-heavy workload
-- ═══════════════════════════════════════════════════════════════
/*
# WAL Configuration
wal_level = replica
wal_buffers = 64MB
wal_writer_delay = 200ms
commit_delay = 100  # Group commits (busy systems)
commit_siblings = 5
 
# Checkpoints
max_wal_size = 8GB
min_wal_size = 4GB
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9
 
# Consider for less critical data
# synchronous_commit = off
*/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

-- POSTGRESQL QUERY EXECUTION CONFIGURATION
 
-- ═══════════════════════════════════════════════════════════════
-- PARALLELISM SETTINGS
-- ═══════════════════════════════════════════════════════════════
 
-- Maximum workers per gather (parallel query)
-- max_parallel_workers_per_gather = 4
-- Recommendation: CPU cores / 2 for OLTP; 
--                 CPU cores - 2 for analytical
 
-- Total parallel workers across all queries
-- max_parallel_workers = 8
-- Should not exceed max_worker_processes
 
-- Total background workers
-- max_worker_processes = 8
-- Includes all worker types (maintenance, replication, parallel)
 
-- Thresholds for parallel plans
-- min_parallel_table_scan_size = '8MB'
-- min_parallel_index_scan_size = '512kB'
-- Lower values = more parallelism
 
-- Parallel cost estimation
-- parallel_setup_cost = 1000
-- parallel_tuple_cost = 0.1
-- Lower values = optimizer prefers parallel plans
 
-- ═══════════════════════════════════════════════════════════════
-- OPTIMIZER COST PARAMETERS
-- ═══════════════════════════════════════════════════════════════
 
-- Random page cost relative to sequential (4.0 default)
-- For SSD: random_page_cost = 1.1 to 1.5
-- For spinning disk: random_page_cost = 4.0
 
-- CPU cost parameters (rarely need adjustment)
-- cpu_tuple_cost = 0.01
-- cpu_index_tuple_cost = 0.005
-- cpu_operator_cost = 0.0025
 
-- ═══════════════════════════════════════════════════════════════
-- TIMEOUTS AND LIMITS
-- ═══════════════════════════════════════════════════════════════
 
-- Statement timeout (kill long queries)
SET statement_timeout = '30s';  -- Session level
-- ALTER DATABASE mydb SET statement_timeout = '60s';
 
-- Lock acquisition timeout
SET lock_timeout = '5s';
 
-- Idle transaction timeout (PG 14+)
SET idle_in_transaction_session_timeout = '5min';
 
-- ═══════════════════════════════════════════════════════════════
-- PLANNER CONFIGURATION
-- ═══════════════════════════════════════════════════════════════
 
-- Join collapse limit (tables to consider for join reordering)
-- join_collapse_limit = 8  (default)
-- Higher = better plans for complex joins, longer planning time
 
-- FROM clause subquery collapse
-- from_collapse_limit = 8
 
-- Enable/disable specific plan types (debugging only)
-- enable_seqscan = on
-- enable_indexscan = on
-- enable_hashjoin = on
-- enable_mergejoin = on
 
-- ═══════════════════════════════════════════════════════════════
-- SAMPLE OPTIMIZED CONFIGURATION
-- ═══════════════════════════════════════════════════════════════
/*
# Parallelism (16-core server)
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 2
 
# Cost adjustments for SSD
random_page_cost = 1.1
effective_io_concurrency = 200
 
# Timeouts
statement_timeout = '60s'
lock_timeout = '5s'
idle_in_transaction_session_timeout = '10min'
*/