Scaling PostgreSQL Databases for Millions of Concurrent Users
Deep-dive into PgBouncer connection pooling, declarative table partitioning, B-tree index optimization, and read-replica strategies to achieve ultra-high PostgreSQL throughput.
The Architecture of High-Concurrency PostgreSQL
PostgreSQL is one of the most reliable open-source relational databases in existence. However, its default process-based architecture assigns a dedicated operating system process to every client connection. When concurrent user traffic scales from hundreds to tens of thousands of active connections, this architecture encounters severe memory bloat, CPU cache thrashing, and lock contention.
To engineer PostgreSQL systems capable of handling millions of concurrent requests with sub-10ms response times, enterprise teams must overhaul four foundational layers: connection management, physical table partitioning, indexing topologies, and replication architectures.
1. Eliminating Connection Overhead with PgBouncer
Because each PostgreSQL backend process consumes approximately 5MB to 10MB of RAM, running 5,000 direct client connections can exhaust 50GB of memory purely on connection overhead. Furthermore, operating system context switching between thousands of processes degrades CPU throughput.
The solution is deploying PgBouncer in Transaction Pooling mode directly in front of the database cluster.
[databases]
app_production = host=127.0.0.1 port=5432 dbname=app_production
[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 50
reserve_pool_size = 10
Why Transaction Pooling?
In transaction pooling mode, a server connection is bound to a client connection only for the duration of a single transaction. As soon as the transaction commits or rolls back, the server connection returns to the pool. This allows 10,000 active web clients to be served smoothly by fewer than 100 actual PostgreSQL backend processes.
2. Declarative Table Partitioning for Billion-Row Datasets
When relational tables exceed tens of millions of rows, sequential scans become prohibitively expensive, and B-tree indexes grow too large to fit in RAM. Query execution times degrade as random disk I/O replaces in-memory lookups.
PostgreSQL declarative partitioning physically breaks massive tables into smaller, self-contained sub-tables while maintaining a unified schema interface for application queries.
-- Parent table partitioned by range
CREATE TABLE transactions (
id BIGSERIAL,
user_id UUID NOT NULL,
amount NUMERIC(12, 2) NOT NULL,
status VARCHAR(32) NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
-- Monthly partition slices
CREATE TABLE transactions_2026_09 PARTITION OF transactions
FOR VALUES FROM ('2026-09-01 00:00:00+00') TO ('2026-10-01 00:00:00+00');
CREATE TABLE transactions_2026_10 PARTITION OF transactions
FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00');
With partition pruning enabled (enable_partition_prune = on), PostgreSQL query planner evaluates filter clauses on created_at and reads only the relevant partition, reducing scan volumes by over 90%.
3. Specialized Indexing Topologies
Improper indexing is the number one cause of lock waits and high I/O latency in high-concurrency applications.
Partial Indexes for Asymmetric Datasets
If 98% of orders are marked COMPLETED and only 2% are PENDING, indexing the entire table wastes RAM. A partial index indexes only the active records:
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'PENDING';
Covering Indexes with INCLUDE Clauses
To eliminate expensive heap table fetches, utilize covering indexes that satisfy queries entirely from the B-tree structure:
CREATE INDEX idx_users_lookup
ON users (email)
INCLUDE (id, name, status);
4. Read-Replication and Connection Routing
To distribute analytical workloads and high-throughput read traffic away from the primary writer node, establish physical streaming replication to dedicated read-replicas.
- Primary Node: Dedicated exclusively to transactional writes, updates, and serialized consistency checks.
- Read Replicas: Horizontally scaled behind a load balancer (such as HAProxy or AWS Aurora Reader Endpoints) to handle user lookups, reporting dashboards, and read-only API requests.
- Lag Telemetry: Continuously monitor
pg_stat_replicationto ensure replication lag remains under 50 milliseconds during peak traffic.
Key Takeaways for Engineering Teams
- Never connect client web processes directly to PostgreSQL; always enforce lightweight connection pooling via PgBouncer.
- Partition any table projected to exceed 50 million rows or 100GB in size.
- Keep indexes lean using partial indexes and
INCLUDEclauses to maximize buffer cache hit ratios. - Scale reads horizontally across streaming replicas while protecting the primary writer node.
Looking for Custom Architecture Solutions?
Schedule an architectural discovery session with our senior engineers.
Schedule Consultation