PostgreSQL for 1 Million Concurrent Users: Ultimate Tuning Guide

CPU: 64 cores (optimized for database workloads)
RAM: 512GB - 1TB
Storage: NVMe SSDs in RAID configuration
Network: 10Gbps+ with dedicated bandwidth

# /etc/sysctl.conf

# Increase kernel parameters
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_tw_reuse = 1
net.ipv4.ip_local_port_range = "1024 65535"
net.ipv4.tcp_fin_timeout = 15

# File descriptors
fs.file-max = 1000000
fs.nr_open = 1000000

# Shared memory
kernel.shmmax = 274877906944  # 256GB
kernel.shmall = 67108864
kernel.shmmni = 4096

# Apply changes
sysctl -p

# pgbouncer.ini

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 150000
default_pool_size = 100
reserve_pool_size = 5000
reserve_pool_timeout = 3
max_db_connections = 50000
server_reset_query = DISCARD ALL
server_check_delay = 30
server_check_query = select 1
server_fast_close = 1
tcp_keepalive = 1
tcp_keepidle = 5
tcp_keepintvl = 1

# haproxy.cfg

global
    maxconn 1000000
    nbproc 4

defaults
    mode tcp
    timeout connect 5s
    timeout client 24h
    timeout server 24h

frontend pgbouncer_front
    bind *:5432
    default_backend pgbouncer_back

backend pgbouncer_back
    balance roundrobin
    server pgbouncer1 10.0.0.1:6432 check
    server pgbouncer2 10.0.0.2:6432 check
    server pgbouncer3 10.0.0.3:6432 check

# postgresql.conf

# Connection Settings
max_connections = 5000
superuser_reserved_connections = 3

# Memory Settings
shared_buffers = 128GB                  # 25% of RAM
huge_pages = on
work_mem = 16MB                         # Depends on max_connections
maintenance_work_mem = 2GB
effective_cache_size = 384GB            # 75% of RAM
effective_io_concurrency = 200          # For SSDs
random_page_cost = 1.1                  # For SSDs

# Background Writer
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 10.0

# WAL Settings
wal_level = replica
wal_buffers = 16MB
wal_writer_delay = 200ms
wal_writer_flush_after = 1MB
max_wal_size = 16GB
min_wal_size = 4GB

# Query Planning
default_statistics_target = 1000
random_page_cost = 1.1
effective_io_concurrency = 200

# Parallel Query
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

# Transaction Management
max_locks_per_transaction = 128

-- Create sharding function
CREATE OR REPLACE FUNCTION get_shard_id(value bigint)
RETURNS integer AS $$
BEGIN
    RETURN value % 1024; -- 1024 shards
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Create sharded table
CREATE TABLE users_shard (
    id bigserial,
    username text,
    email text,
    created_at timestamp with time zone,
    shard_id int GENERATED ALWAYS AS (get_shard_id(id)) STORED
) PARTITION BY RANGE (shard_id);

-- Create partitions
DO $$
BEGIN
    FOR i IN 0..1023 LOOP
        EXECUTE format(
            'CREATE TABLE users_shard_%s PARTITION OF users_shard
             FOR VALUES FROM (%s) TO (%s)',
            i, i, i + 1
        );
    END LOOP;
END $$;

# primary postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

# replica postgresql.conf
hot_standby = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s
hot_standby_feedback = on

# Example Python code using Redis cache
import redis
from functools import wraps

redis_client = redis.Redis(
    host='localhost',
    port=6379,
    db=0,
    socket_timeout=1,
    socket_connect_timeout=1
)

def cache_query(ttl_seconds=300):
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            cache_key = f"query:{func.__name__}:{str(args)}:{str(kwargs)}"
            result = redis_client.get(cache_key)
            
            if result is not None:
                return result
                
            result = func(*args, **kwargs)
            redis_client.setex(cache_key, ttl_seconds, result)
            return result
        return wrapper
    return decorator

@cache_query(ttl_seconds=300)
def get_user_data(user_id):
    # PostgreSQL query here
    pass

-- Create materialized view for frequently accessed data
CREATE MATERIALIZED VIEW user_stats AS
SELECT 
    date_trunc('hour', created_at) as hour,
    count(*) as user_count,
    count(distinct country) as country_count
FROM users
GROUP BY 1
WITH DATA;

-- Create unique index for faster refresh
CREATE UNIQUE INDEX user_stats_hour_idx ON user_stats (hour);

-- Refresh concurrently
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

-- Monitor active connections
SELECT 
    datname,
    usename,
    state,
    count(*) as connection_count
FROM pg_stat_activity
GROUP BY 1, 2, 3;

-- Monitor connection pooler
SELECT 
    database,
    user,
    cl_active,
    cl_waiting,
    sv_active,
    sv_idle,
    sv_used,
    sv_tested,
    sv_login,
    maxwait
FROM pgbouncer.pools;

-- Monitor query performance
SELECT 
    datname,
    calls,
    total_time,
    rows,
    shared_blks_hit,
    shared_blks_read,
    shared_blks_written
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- Create maintenance function
CREATE OR REPLACE FUNCTION perform_maintenance()
RETURNS void AS $$
BEGIN
    -- Vacuum analyze all tables
    VACUUM (ANALYZE, VERBOSE);
    
    -- Refresh materialized views
    REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;
    
    -- Update table statistics
    ANALYZE VERBOSE;
    
    -- Log maintenance completion
    INSERT INTO maintenance_log (completed_at, status)
    VALUES (now(), 'success');
END;
$$ LANGUAGE plpgsql;

-- Schedule maintenance
SELECT cron.schedule('0 0 * * *', 'SELECT perform_maintenance()');

[Client Applications]
         ↓
[HAProxy Load Balancer]
         ↓
[PgBouncer Pool]
         ↓
[PostgreSQL Primary]
    ↙    ↓    ↘
[Replica] [Replica] [Replica]
    ↓        ↓        ↓
[Redis Cache Layer]