Scaling PostgreSQL 
When to Tune, When to Replicate, When to Shard 

PostgreSQL will take a growing SaaS much further than most teams expect — single instances comfortably serve tens of thousands of customers and terabytes of data when they're run well. Yet the instinct when the database gets slow is to reach for the most dramatic, most expensive option first: 'we need to shard.' That is almost always the wrong move, and it's the one that introduces years of operational complexity to solve a problem a $40 index would have fixed. Scaling Postgres is an order of operations. Do the cheap, reversible things first; reach for the irreversible, complex ones only when you've genuinely exhausted the rest. Here's the order we follow.

First, find out what's actually slow

You cannot scale what you haven't measured, and the bottleneck is rarely where intuition points. Turn on pg_stat_statements to find your most expensive queries by total time, enable slow-query logging, and use EXPLAIN ANALYZE on the offenders to see whether they're doing sequential scans, bad joins, or fetching far more rows than they return. Nine times out of ten the 'we've outgrown Postgres' crisis is three missing indexes and one N+1 query pattern from the ORM. Profile first — the most common scaling mistake is solving a problem you don't have while ignoring the one you do.

Tune and index — the cheapest, biggest wins

• Add the missing indexes. The single most common fix: a query filtering or joining on an unindexed column. Add B-tree indexes for equality and range, composite indexes matching your query's filter order, and partial indexes for queries that always include the same WHERE clause. Build them with CREATE INDEX CONCURRENTLY so you don't lock the table.
• Fix N+1 queries from the ORM. One query that loads 500 parents and then fires 500 child queries will melt a database no amount of hardware saves. Eager-load instead.
• Right-size the config. Defaults are conservative; tune shared_buffers, work_mem, and effective_cache_size to your instance. A correctly configured mid-size instance often outperforms a misconfigured large one.
• Vacuum and analyze. Bloat and stale statistics quietly wreck the query planner. Make sure autovacuum is keeping up on your high-churn tables.

Pool your connections before you scale hardware

PostgreSQL uses a process per connection, and each one carries real memory overhead, so a few hundred direct connections from an autoscaling app fleet will exhaust the database long before CPU or disk is the limit. This surprises teams constantly: the database isn't 'out of capacity,' it's out of connection slots. Put a pooler — PgBouncer or the equivalent — in front of it in transaction-pooling mode, and a handful of real database connections can serve thousands of application clients. Connection pooling is one of the highest-leverage, lowest-effort changes available, and it's frequently the actual fix for a database that 'falls over under load.' Do this before you spend a dollar on a bigger instance.

Scale reads with replicas

Most SaaS workloads are read-heavy — far more queries read data than write it. Once a single primary is genuinely saturated on reads despite good indexes and pooling, add one or more read replicas and route read-only traffic to them, keeping writes on the primary. This scales the dominant side of your workload without touching your schema or application logic much. The one thing to design around is replication lag: replicas are eventually consistent, usually milliseconds behind, so route anything that must read-its-own-write (a user viewing the record they just saved) to the primary, and send dashboards, reports, and search to the replicas. Vertical scaling — simply running a bigger instance — is also legitimate and underrated here; modern cloud Postgres scales to very large instances, and buying a bigger box is often cheaper than the engineering time of any horizontal scheme.

Partition big tables before you shard them

When a single table grows into the hundreds of millions or billions of rows — events, logs, time-series, audit trails — and queries or maintenance slow down, native table partitioning splits it into smaller physical chunks (by date range or by key) while it still looks like one table to your application. Queries that filter on the partition key only scan the relevant partitions, and you can drop old data by detaching a whole partition instead of running a massive DELETE. Partitioning buys most of the benefit people think they need sharding for, with a fraction of the complexity, because everything still lives in one database you can join and transact across. Exhaust this before considering sharding.

When sharding is finally the answer

Sharding — splitting data across multiple independent Postgres instances — is the last resort, justified only when a single primary genuinely cannot handle your write throughput or your dataset no longer fits one machine, and you've already done indexing, pooling, replicas, and partitioning. It is the most powerful lever and by far the most expensive: cross-shard queries and joins become hard or impossible, transactions no longer span all your data, every shard needs its own backups and failover, and rebalancing is a project. For multi-tenant SaaS the natural shard key is the tenant — which dovetails with the isolation models we cover in our guide to multi-tenant architecture — but even then, design the migration as expand-and-contract so you can move tenants without downtime. Shard because you've proven you must, not because the dataset feels big.

How Infiniti Tech Partners scales databases

We start where the wins are cheapest: profile the real bottleneck, fix the indexes and N+1 queries, pool the connections, tune the config — and only then design replicas, partitioning, or, if it's genuinely warranted, a sharding strategy with a zero-downtime migration path. The goal is the simplest architecture that holds your growth, not the most impressive one. If your Postgres is buckling under load and 'we need to shard' is on the table, start a conversation before you commit to the hard road.