PostgreSQL Configuration

Configuration Areas

shared_buffers = 256MB

max_connections = 100

log_statement = 'all'

ssl = on

🔹 Memory Configuration

Memory settings control how PostgreSQL uses RAM for caching and operations. Proper memory configuration dramatically improves query performance by reducing disk I/O. Allocate memory based on available RAM and workload characteristics for optimal results.

# Shared memory for all connections
shared_buffers = 256MB          # 25% of RAM

# Memory per operation (sort, hash)
work_mem = 4MB                  # Per operation

# Maintenance operations memory
maintenance_work_mem = 64MB     # For VACUUM, CREATE INDEX

# Total cache size hint
effective_cache_size = 1GB      # OS + PostgreSQL cache

🔹 Connection Settings

Connection parameters control how many clients can connect simultaneously. Each connection consumes memory and system resources, so balance availability with resource limits. Consider connection pooling for applications with many concurrent users.

# Maximum concurrent connections
max_connections = 100

# Reserved connections for superusers
superuser_reserved_connections = 3

# Connection timeout
authentication_timeout = 60s

# Idle connection timeout
idle_in_transaction_session_timeout = 300000  # 5 minutes

SELECT count(*) FROM pg_stat_activity;

🔹 Logging Configuration

Logging settings determine what information PostgreSQL records about database activity. Comprehensive logging aids troubleshooting and security auditing but increases disk usage. Configure logging to balance detail with performance and storage requirements.

# Where to log
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'

# What to log
log_statement = 'all'           # none, ddl, mod, all
log_duration = on
log_connections = on
log_disconnections = on

# Slow query logging
log_min_duration_statement = 1000  # Log queries > 1 second

SELECT * FROM pg_stat_statements;

🔹 Performance Tuning

Performance settings optimize query execution and resource utilization. These parameters affect query planning, parallel processing, and disk operations. Tune based on workload patterns and hardware capabilities for maximum throughput.

# Query planner settings
random_page_cost = 1.1          # SSD: 1.1, HDD: 4.0
effective_io_concurrency = 200  # SSD: 200, HDD: 2

# Parallel query settings
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 8

# Checkpoint settings
checkpoint_completion_target = 0.9
wal_buffers = 16MB

EXPLAIN ANALYZE SELECT * FROM table;

🔹 Security Configuration

Security settings protect your database from unauthorized access and attacks. Configure authentication methods, SSL encryption, and network access controls. Strong security configuration is essential for protecting sensitive data and maintaining compliance.

# SSL/TLS encryption
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

# Password encryption
password_encryption = scram-sha-256

# Network security
listen_addresses = 'localhost'  # or specific IP
port = 5432

# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       all                       scram-sha-256
host    all       all       127.0.0.1/32    scram-sha-256
host    all       all       ::1/128         scram-sha-256

🔹 Viewing Configuration

Check current configuration values to understand database settings and troubleshoot issues. PostgreSQL provides multiple ways to inspect configuration including SQL commands and system views. Regular configuration reviews ensure settings remain appropriate.

-- Show all settings
SHOW all;

-- Show specific setting
SHOW shared_buffers;

-- Query configuration table
SELECT name, setting, unit, context 
FROM pg_settings 
WHERE name LIKE '%mem%';

-- Show configuration file location
SHOW config_file;

 name          | setting | unit | context
---------------+---------+------+---------
 work_mem      | 4096    | kB   | user
 shared_buffers| 16384   | 8kB  | postmaster

🔹 Changing Configuration

Modify settings temporarily for sessions or permanently in configuration files. Session-level changes affect only current connection while file changes persist across restarts. Some settings require database restart while others apply immediately with reload.

-- Change for current session
SET work_mem = '64MB';

-- Change for current transaction
SET LOCAL work_mem = '64MB';

-- Change globally (requires superuser)
ALTER SYSTEM SET shared_buffers = '512MB';

-- Reload configuration (no restart needed)
SELECT pg_reload_conf();

-- View pending changes
SELECT * FROM pg_file_settings;