At What Specific User Count Should You Shard Your PostgreSQL Database?

Every senior backend engineer has heard the knee-jerk reaction in architecture reviews: "What happens when we get to a million users?" It is the wrong question. A million users consuming a read-only news feed generates a vastly different load profile than a million users executing high-frequency financial trades. Focusing on raw user count leads to premature optimization, while the actual bottlenecks—disk I/O saturation and memory pressure—are ignored.

If you are looking for a specific user count, you are looking for a shortcut that does not exist. However, there are specific, objective metrics derived from PostgreSQL internals that tell you exactly when vertical scaling has run its course. We need to stop looking at the user registration table and start looking at pg_stat_activity.

The Working Set Exceeds Available RAM

The first concrete signal that you have crossed the threshold from "optimizable monolith" to "necessary sharding" is the relationship between your active data set and your server's RAM. PostgreSQL relies heavily on the OS page cache for performance. When the "hot" data—the rows and indexes frequently accessed by your application—fits comfortably in memory, query latencies remain low and predictable.

The danger zone begins when your active working set exceeds 1.5 times the available RAM.

For example, if you are running a memory-optimized instance with 128 GB of RAM, but your pg_stat_user_tables indicates that combined index and table scans are regularly accessing more than 192 GB of data within a 24-hour window, you will start to see disk I/O wait times spike. Once the buffer cache hit ratio drops consistently below 99%, the cost of fetching data from disk (even NVMe) introduces latency variance that vertical CPU upgrades cannot fix. At this point, adding more users will degrade performance linearly.

The 200 Millisecond P95 Latency Wall

Latency is the canary in the coal mine, but you must measure it aggressively. We are not interested in average latency; we care about the tail. Specifically, the P95 and P99 response times for your primary, database-bound endpoints.

You should consider sharding when your P95 query latency for simple primary key lookups consistently exceeds 200 milliseconds, provided you have already optimized your indexes.

Why 200ms? Once you cross this threshold, the cumulative latency on the client side— compounded by network hops and application logic— often breaches the 500ms mark, which is the threshold where users perceive an interface as "sluggish." If you have exhausted EXPLAIN ANALYZE optimizations, tweaked random_page_cost for your SSDs, and scaled your CPU to handle parallel queries, but the P95 remains stuck above 200ms during peak load, you are likely contending on internal locks or I/O bandwidth that only sharding can distribute.

Connection Pool Saturation and Throttling

PostgreSQL is process-based. Each connection requires a distinct memory overhead and CPU context switching. While tools like PgBouncer help manage transaction pooling, the database engine itself still has limits. You know you have hit a scaling wall when your connection pooler begins queuing transactions for longer than 10 milliseconds consistently during your peak traffic window.

This usually correlates to sustaining roughly 2,000 to 3,000 concurrent transactions per second (TPS) on a single, large PostgreSQL instance, depending heavily on the complexity of the write workload.

If you see your max_connections being throttled, or if PgBouncer's cl_waiting metric remains non-zero for extended periods, you have run out of concurrency capacity. You cannot simply increase max_connections indefinitely; doing so will induce OOM (Out of Memory) kills or thrashing. Distributing these connections across shards is the only way to reclaim linear throughput growth.

Write-Ahead Log (WAL) Volume Constraints

A frequently overlooked metric is the volume of the Write-Ahead Log. High write throughput changes the equation significantly. Even if your read latency is acceptable, if you are generating more than 50 GB of WAL data per day, you face two severe risks: replication lag and backup complexity.

When your replicas cannot apply the WAL stream fast enough to keep up with the primary, they fall behind, rendering them useless for read scaling or failover. If your replica lag regularly exceeds 5 seconds, you have a write bottleneck. Sharding effectively partitions the WAL stream. Instead of one serial log of 50 GB, you might have five shards each handling 10 GB, which your replicas can process comfortably.

Architectural Prerequisites Before You Cut

Do not interpret these metrics as a green light to hire a distributed systems consultant and start sharding tomorrow. Sharding introduces complexity in application code, foreign key relationships, and transactions. Before you undertake this, you must ensure your architecture is capable of handling the eventual consistency and cross-shard query patterns required.

Often, engineers reach for sharding when they actually need read optimization. If your bottleneck is read traffic, implementing CQRS Explained: Separating Reads and Writes for Scalability might solve your problem without the operational nightmare of sharding. Additionally, if your monolith is tightly coupled, physically splitting the database will be a disaster. Reviewing how we approached boundaries in From Monolith to Modular Monolith: How We Reduced Deployment Time from 2 Hours to 10 Minutes is a prerequisite step to ensure your data access layers are logically separated before they are physically separated.

The Principle of Least Privilege and Rollback Strategies

Sharding is not a "set and forget" migration; it is a live surgery on your production data. As per strict architectural governance, the migration strategy must include a robust rollback plan that does not rely on "restoring from backup."

You should implement a "dual-write" phase where the application writes to both the legacy monolithic database and the new shard. To enforce the principle of least privilege, the service account used for the migration must only have INSERT and SELECT permissions on the target tables, with no DROP or TRUNCATE rights. This prevents a catastrophic logic error in the migration script from wiping out the new shard.

Furthermore, your verification jobs—processes that compare data integrity between the old and new systems—must run under a distinct, read-only role. If the checksums fail, the rollback strategy is simply to cut traffic back to the monolith. You must be able to execute this failover in under 60 seconds via a configuration toggle (e.g., a feature flag in your service discovery layer) without requiring a code deploy.

The Verdict on Volume

Returning to the original question: there is no specific user count. However, based on the metrics above, we can reverse-engineer a proxy. If you have a write-heavy application (like an IoT platform or transactional ledger) and you are processing more than 10,000 writes per second, or if you have a read-heavy application with a hot dataset larger than 500 GB, you are effectively past the point of safety for a single PostgreSQL instance.

Sharding is a response to hard limits on physics—memory bandwidth, disk IOPS, and CPU context switching. When iowait consumes more than 15% of your CPU cycles, or when your autovacuum processes cannot keep up with dead tuple cleanup, you have your answer. The decision is not about how many users you have; it is about how much pain your current hardware is willing to endure before it breaks.