Scaling PostgreSQL: Connections, Replicas, Partitioning, and the Limits of One Primary

Scaling PostgreSQL: Connections, Replicas, Partitioning, and the Limits of One Primary

PostgreSQL can scale much further than many applications need before sharding becomes necessary. The usual path is to first make queries cheaper, keep connection counts under control, scale the primary vertically, move suitable reads to replicas, partition genuinely large tables, and separate analytical workloads.

Only after one write-owning PostgreSQL primary becomes the demonstrated limit should you seriously consider application-level sharding or a distributed PostgreSQL architecture.

As of September 2026, PostgreSQL 18.6 is the current supported stable release line update, while PostgreSQL 19 is still in beta. This article uses PostgreSQL 18's current production documentation rather than development-only PostgreSQL 19 behavior.

Start with query work, not node count

Before changing topology, find out what the server is spending time on.

Useful evidence includes:

• slow statements
• actual execution plans
• CPU saturation
• storage latency and I/O
• lock waits
• connection count
• WAL generation
• checkpoint pressure
• autovacuum health
• replication lag

pg_stat_statements is one of the most useful PostgreSQL extensions for this job because it aggregates planning and execution statistics by normalized statement.

A scaling project should begin with questions such as:

Which statements consume the most total execution time?
Which queries run most often?
Which queries generate the most WAL?
Which indexes are never used?
Which tables accumulate dead tuples fastest?

A missing index or pathological query should not become a sharding project.

PostgreSQL connections are not free

PostgreSQL uses a server process for each client connection. The max_connections setting limits concurrent connections, and current PostgreSQL documentation notes that increasing it also increases resources allocated by the server, including shared memory.

A common failure pattern is:

100 application instances
x 50 pooled connections each
= 5,000 possible database connections

That does not mean the database can productively execute 5,000 transactions at once.

Too much concurrency can increase:

• memory usage
• context switching
• lock contention
• cache churn
• latency variance

Use bounded application pools and, when architecture requires many short-lived client connections, evaluate a PostgreSQL-aware external pooler such as PgBouncer.

The objective is not “maximum connections.” It is enough active database work to saturate useful resources without overwhelming them.

Scale the primary vertically while it remains simple

Giving PostgreSQL more CPU, RAM, and faster storage preserves the simplest architecture:

application
    |
    v
single PostgreSQL primary

Advantages include:

• normal transactions stay local
• joins stay local
• no shard router
• one authoritative write owner
• simpler backup and failover procedures

Vertical scaling is often the correct answer while hardware cost and recovery time remain acceptable.

More RAM can be particularly valuable when the active table/index working set is competing for cache and repeated storage reads are the bottleneck.

Indexes and plans determine how well extra hardware is used

Scaling PostgreSQL without fixing access paths wastes capacity.

For each hot query, inspect EXPLAIN (ANALYZE, BUFFERS) in an appropriate non-destructive environment and ask:

• Is PostgreSQL scanning far more rows than it returns?
• Is an index selective enough?
• Is a sort spilling to temporary storage?
• Are cardinality estimates badly wrong?
• Would a compound or partial index better match the predicate?

Also remove redundant indexes carefully. Every additional index can improve some reads while adding write, WAL, storage, and maintenance cost.

Read replicas scale database reads, not authoritative writes

PostgreSQL physical streaming replication can maintain standby servers by continuously sending WAL records from the primary.

A common topology is:

                 primary
                /       \
          standby A   standby B

Hot standbys can serve read-only queries, which helps when the primary is constrained by read CPU, I/O, or connection pressure.

Good replica candidates include:

• public catalog reads
• reporting
• stale-tolerant dashboards
• background read jobs

But PostgreSQL streaming replication is asynchronous by default. A transaction can commit on the primary before it is visible on a standby.

Therefore, do not automatically route every SELECT to replicas.

Keep consistency-sensitive reads on the primary when they must immediately observe:

• a just-created row
• authorization changes
• account state
• inventory or reservation decisions

Read scaling comes with a freshness contract.

Monitor replay position, not just “replica is connected”

PostgreSQL exposes replication progress through views such as pg_stat_replication.

Useful positions include:

• WAL sent
• WAL written on standby
• WAL flushed
• WAL replayed

For application-visible freshness, replay progress matters because a change is not query-visible on the standby until it has been replayed.

Also monitor:

• write/flush/replay lag
• standby CPU and storage latency
• recovery conflicts
• WAL retention
• replication slots

A replica that is online but far behind should not remain in the normal read pool blindly.

Synchronous replication is a durability decision, not a scaling trick

PostgreSQL can require one or more synchronous standbys to acknowledge a write before the primary reports commit success.

That can reduce the acknowledged-data-loss window during primary failure, but it adds network/standby latency to the commit path.

Use synchronous replication because the RPO requires it—not because “more replicas means safer by default.”

A distant synchronous standby can materially increase transaction latency.

Partition large tables when the lifecycle and queries align

PostgreSQL supports declarative range, list, and hash partitioning.

Time-based range partitioning is especially useful for very large event tables:

CREATE TABLE events (
    id bigint,
    occurred_at timestamptz NOT NULL,
    payload jsonb
) PARTITION BY RANGE (occurred_at);

Partitioning can help with:

• pruning irrelevant partitions
• keeping hot indexes smaller
• dropping/detaching old data quickly
• isolating maintenance

PostgreSQL's current documentation emphasizes that partition pruning comes from the partition bounds, not from merely having an index.

Partitioning does not distribute the table across independent write servers. It is still one PostgreSQL cluster unless you add another distribution layer.

Too many partitions can become their own scaling problem

Partitioning is not free metadata.

PostgreSQL documentation warns that excessive partition counts can increase planning time and memory consumption, especially when many sessions touch many partitions.

Avoid designs such as:

one partition per customer

when customer count may grow to hundreds of thousands.

Choose partition boundaries that allow common queries to prune most partitions while keeping partition count operationally reasonable.

Autovacuum is part of PostgreSQL scalability

PostgreSQL MVCC creates old row versions that eventually need cleanup.

If autovacuum falls behind on write-heavy tables, you can see:

• table and index bloat
• more I/O
• worse cache efficiency
• longer scans
• transaction-ID wraparound risk

Scaling a write-heavy PostgreSQL workload therefore includes monitoring:

• dead tuples
• autovacuum frequency/duration
• transaction age
• table growth
• vacuum progress

Do not solve bloat by adding replicas. Replicas reproduce the primary's data state; the underlying maintenance problem remains.

Long-running transactions can hold back cleanup

A transaction that keeps an old snapshot open can prevent PostgreSQL from reclaiming row versions that might still be visible to it.

Examples include:

• forgotten idle-in-transaction sessions
• long reporting transactions
• workers that begin a transaction before slow external work

These can create pressure disproportionate to their request count.

Keep transactional scopes short and monitor pg_stat_activity for unexpectedly old transactions.

WAL generation is a write-scaling signal

Every transactional write can generate WAL used for crash recovery and replication.

High WAL volume affects:

• storage bandwidth
• replica network traffic
• replica replay work
• archive storage
• backup/recovery operations

pg_stat_wal and replication views help identify whether the write path is becoming WAL/storage constrained.

Before considering write sharding, also review whether the application is generating avoidable writes or maintaining too many indexes.

Logical replication solves selective data movement

PostgreSQL logical replication works through publications and subscriptions and can replicate selected tables and changes at the logical row level.

It is useful for:

• migrations between PostgreSQL versions
• moving selected tables to another PostgreSQL instance
• feeding reporting/integration databases
• gradually separating a domain from a monolith

Logical replication begins with an initial snapshot/copy and then continuously applies changes in transactional order for the subscription.

It is a powerful migration/scaling tool, but it is not transparent horizontal sharding of one SQL database.

Logical replication needs schema coordination

Current PostgreSQL documentation notes important restrictions:

• schema/DDL changes are not automatically replicated
• sequence state is not automatically replicated
• publisher and subscriber schemas need compatible management

That means a migration using logical replication needs a deployment plan such as:

prepare compatible subscriber schema
start initial synchronization
stream changes
validate lag/data
apply compatible schema changes deliberately
cut traffic over

Do not assume logical replication means the destination can be forgotten until cutover.

Use CDC to separate OLTP and analytics when needed

A transactional primary should not necessarily serve every analytical scan forever.

When dashboards or event analytics begin competing with customer transactions, a common architecture is:

PostgreSQL primary
      |
logical decoding / CDC
      |
analytical store

This is especially useful when the analytical system has a storage/query model better suited to long scans and aggregations.

Lofingo's separate PostgreSQL + ClickHouse article covers this architecture in depth, so this guide keeps the point focused: scale OLTP by removing workloads that do not need OLTP semantics.

Core PostgreSQL does not transparently shard writes across many primaries

PostgreSQL provides partitioning, physical replication, logical replication, and foreign data access, but a standard PostgreSQL primary still owns its authoritative local writes.

If one primary's write/storage capacity becomes the demonstrated limit, the next step generally requires one of these architectures:

• application-level sharding
• tenant/domain-specific PostgreSQL clusters
• a PostgreSQL-based distributed database/platform
• extensions/products that add distributed placement/routing

That is a major architectural transition because cross-shard transactions, joins, backups, migrations, and failover become more complex.

Do not confuse table partitioning with multi-primary horizontal write scale.

Tenant-based split can be a practical intermediate step

Multi-tenant SaaS systems sometimes outgrow one primary because a small number of large tenants dominate load.

Instead of immediately building generic row-level sharding, you can move tenant groups to separate PostgreSQL clusters:

small tenants      -> cluster A
enterprise tenant1 -> cluster B
enterprise tenant2 -> cluster C

A routing directory maps tenant ownership.

This preserves normal PostgreSQL semantics inside each tenant boundary and can provide strong isolation.

The cost is operational: schema migrations, backups, routing, and observability now span several clusters.

Connection pooling becomes more important as app instances grow

Horizontal application scaling can overwhelm one PostgreSQL server long before query throughput itself is exhausted.

For example, serverless functions or autoscaled containers can create sudden connection storms.

Use:

• bounded client pools
• sensible idle lifetimes
• external pooling when justified
• statement/transaction timeouts
• concurrency limits

Never set max_connections extremely high just to accommodate an unbounded application-side pool without measuring the memory/scheduling consequences.

Separate availability from scale

A standby may exist for HA even if it never serves read traffic.

A read replica may exist for reports but be a poor failover candidate if it intentionally tolerates lag or heavy analytical load.

A logical subscriber may exist only for migration.

Document each replica's role:

standby A -> HA promotion candidate
standby B -> stale-tolerant reads
subscriber C -> migration target

This prevents one database copy from being overloaded with incompatible responsibilities.

What to monitor as PostgreSQL grows

At minimum:

• p50/p95/p99 query latency
• pg_stat_statements total time and call count
• CPU
• storage read/write latency
• active connections and pool wait
• lock waits/deadlocks
• long transactions
• dead tuples/autovacuum activity
• WAL bytes/sec
• checkpoints
• replication replay lag
• replication slot retained WAL
• table/index size growth
• partition count and pruning behavior

Scaling decisions should come from trends in these signals rather than from a generic traffic number.

A practical PostgreSQL scaling sequence

For most applications:

1. Measure hot statements with query statistics.
2. Fix query plans, indexes, and unnecessary work.
3. Bound application connections and pool correctly.
4. Scale CPU, RAM, and storage vertically while economical.
5. Cache repeated hot reads where staleness can be controlled.
6. Add physical replicas for HA and appropriate read offload.
7. Move heavy analytics away from OLTP.
8. Partition genuinely large tables for pruning/retention.
9. Keep autovacuum, WAL, and long transactions healthy.
10. Use logical replication for migrations/selective distribution.
11. Shard or adopt distributed PostgreSQL only when one write owner is the proven bottleneck.

PostgreSQL scales best when each added layer removes a specific bottleneck. Replicas solve read capacity and availability, partitioning solves table lifecycle/locality, logical replication moves data, and sharding solves a fundamentally different one-primary write/storage limit.

Official references

• PostgreSQL 18 — Connection Settings
• PostgreSQL 18 — High Availability and Streaming Replication
• PostgreSQL 18 — Table Partitioning
• PostgreSQL 18 — Logical Replication
• PostgreSQL 18 — pg_stat_statements