When to Shard PostgreSQL: Vertical Scaling and Cheaper Fixes First

Which resource is your PostgreSQL database actually running out of?

"The database is slow" describes a symptom. Sharding addresses only some causes, so first identify which resource is saturated. Each limit has its own fix, and most of those fixes are cheaper than splitting the data.

Start with pg_stat_statements, sorted by total execution time. A few query shapes usually account for most of the load. Then sample pg_stat_activity repeatedly and count sessions by wait_event_type. A single snapshot can mislead, but a pattern across many snapshots shows where time goes. Run EXPLAIN (ANALYZE, BUFFERS) on the top offenders to see whether pages come from shared buffers ("hit") or from outside them ("read"). Compare all of this against OS metrics such as CPU steal and iowait, device latency and queue depth from iostat, and memory pressure.

Only the first four can be true capacity limits. Connection exhaustion and lock contention are usually caused by how the application uses the database, and adding nodes will not remove them.

Copy-- 1. Top query shapes by total time (needs pg_stat_statements; column names are for PG 13+).
SELECT left(query, 60) AS query_shape,
       calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       shared_blks_hit,
       shared_blks_read  -- high read vs hit suggests the working set exceeds shared buffers
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 2. One snapshot of where sessions are waiting. Run repeatedly (e.g. psql \watch 1)
--    and aggregate the results; a single sample can mislead.
--    NULL wait_event_type on an active session means it is on CPU.
SELECT state,
       coalesce(wait_event_type, 'CPU (no wait)') AS wait_type,
       wait_event,
       count(*) AS sessions
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3
ORDER BY sessions DESC;

-- 3. Buffer cache hit ratio per database since the last stats reset.
--    A falling ratio points at memory / working set, not CPU.
SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname IS NOT NULL;

-- 4. For a top offender, see hit vs read pages per plan node.
--    Note: ANALYZE executes the statement, so wrap writes in a transaction you roll back.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 50;

Diagnostic queries that rank load by query shape, sample wait events, compute cache hit ratio and inspect buffer usage, so each symptom can be traced to the resource that is saturated.

Who should use this decision process?

This process is for teams that run a single PostgreSQL primary, possibly with read replicas, whose load keeps growing, and who have started asking whether to shard. In practice that means:

• Backend and platform engineers who own the schema and the data access code, and who will carry the cost of any split.
• SREs and DBAs who handle capacity, backups, failover and maintenance windows, and who see the database's limits first.
• Tech leads and engineering managers who must decide whether a sharding project belongs on the roadmap, and when.

It fits multi-tenant B2B applications especially well. Those applications usually have a natural shard key, which makes sharding tempting, but they also usually have plenty of cheaper fixes left.

It is less relevant if your database is small and comfortably provisioned, because there is nothing to decide yet. It is also less relevant if you already run a sharded system. In that case, the diagnostic and query-tuning sections still apply to each shard on its own.

How does this approach work?

The process is an escalation ladder. Each step costs more and is harder to undo than the one before it:

1. Measure. Identify the saturated resource with the diagnostics above: CPU, memory, disk, WAL, connections or contention.
2. Fix usage. Tune queries and indexes, end long transactions and pool connections.
3. Scale vertically. Add RAM, faster storage or more cores to the single primary.
4. Offload and reshape. Add read replicas, partition large tables, archive cold data, move side workloads out and split separate domains.
5. Shard. Do this only when a true single-primary limit remains after the earlier steps.

At each step, apply the cheapest fix that targets the resource you identified, then measure again. Move up the ladder only if the same limit persists. A fix that targets the wrong resource, such as adding replicas when the bottleneck is WAL throughput, shows up quickly in the next round of measurements. You can then change course while the cost is still small.

If you do reach step 5, sharding works like this. The data is divided by a shard key. A routing layer sends each query to the instance that owns that key. Each shard runs as an independent PostgreSQL primary. The next section covers the mechanics and the costs that come with them.

Why is sharding PostgreSQL a one-way door?

Sharding splits one logical database across several independent PostgreSQL primaries. Each row belongs to exactly one shard, chosen by a shard key such as tenant_id or account_id. A routing layer, either in the application or in a proxy, maps each key to a shard and sends queries there. Each shard is a full PostgreSQL instance with its own WAL, replicas, vacuum and connection limits.

The routing layer is cheap. The real cost is that every future feature has to account for the split:

• Cross-shard queries. A join or aggregate across keys becomes a fan-out to every shard, followed by merging results in application code. Reporting, search and admin screens are hit hardest.
• Transactions. PostgreSQL guarantees atomicity only within one instance. A write that touches two shards needs two-phase commit, a saga with compensating steps, or a product decision to accept partial failure.
• Rebalancing and hot shards. One large tenant can overload its shard. Moving that tenant means copying data live, then switching routing with nothing lost or written twice.
• Schema migrations. Every migration runs N times, and it can fail partway, which leaves shards on different schema versions. Application code has to tolerate that mixed state.
• Operations. You now have N backups, N restore drills, and on-call alerts multiplied by N.
• The shard key. If you choose the key badly, fixing it means rewriting every row and every query path.

These costs never go away, which is why the cheaper fixes in the next sections are worth trying first.

Copy                  +-----------------------------+
                  |         Application         |
                  +--------------+--------------+
                                 |
                                 v
                  +-----------------------------+
                  |        Routing layer        |
                  |     (app code or proxy)     |
                  |     shard key -> shard      |
                  +--------------+--------------+
                                 |
          +----------------------+----------------------+
          |                      |                      |
          v                      v                      v
+-------------------+  +-------------------+  +-------------------+
| Shard 1 primary   |  | Shard 2 primary   |  | Shard N primary   |
| keys: tenants A-F |  | keys: tenants G-M |  | keys: tenants T-Z |
| own WAL, vacuum,  |  | own WAL, vacuum,  |  | own WAL, vacuum,  |
| conn limits       |  | conn limits       |  | conn limits       |
+---------+---------+  +---------+---------+  +---------+---------+
          |                      |                      |
          v                      v                      v
   +-------------+        +-------------+        +-------------+
   |  replicas   |        |  replicas   |        |  replicas   |
   |  + backups  |        |  + backups  |        |  + backups  |
   +-------------+        +-------------+        +-------------+
Query on one key:     routed to exactly one shard
Query across keys:    fan-out to all N shards, results merged in app code
Write across shards:  no atomicity across instances; needs 2PC or a saga

A routing layer maps each shard key to one of N independent PostgreSQL primaries, each with its own WAL, vacuum, connection limits, replicas and backups, so cross-key queries fan out to every shard and cross-shard writes lose single-instance atomicity.

Which cheap fixes remove most database bottlenecks?

Start with pg_stat_statements and sort by total execution time, not mean time. A 2 ms query called 50,000 times a minute costs more than a 3-second report that runs hourly. The top few entries usually account for most of the load. Fixing them is a code review, not a migration.

Query and schema wins

• Missing indexes. Look for sequential scans on large tables in hot queries, then add indexes with CREATE INDEX CONCURRENTLY so writes are not blocked. Also check for unused or duplicate indexes. Each one slows every write and competes for cache.
• Bloated indexes. Rebuild them concurrently. A smaller index fits in memory more easily.
• ORM N+1 patterns. A page that issues one query per row turns one round trip into hundreds. Eager loading or a single join usually fixes it.
• Unbounded queries. Add LIMIT and keyset pagination. Endpoints that return "all rows" get slower as the table grows.
• Long transactions. Check pg_stat_activity for sessions that are long-running or idle in transaction. Vacuum cannot remove dead rows that any open snapshot might still need, so a single forgotten transaction can bloat every busy table.

Connection management

PostgreSQL runs one backend process per connection. Thousands of mostly idle app connections cost memory and add contention. An external pooler in transaction mode lets many clients share a small set of server connections. The tradeoff is that it breaks anything tied to a session: SET variables, temporary tables, session-level advisory locks, and some prepared-statement patterns.

All of these fixes are measured in days of work, and each one can be reverted.

Copy-- 1. Rank statements by total time, not mean time.
-- Column names are for PostgreSQL 13+; older versions use total_time / mean_time.
SELECT
  calls,
  round(total_exec_time::numeric, 0) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct_of_total,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 2. Find sessions holding transactions open. These can pin backend_xmin
-- and stop vacuum from removing dead rows.
SELECT
  pid,
  usename,
  application_name,
  state,
  now() - xact_start AS xact_age,
  backend_xmin,
  left(query, 60) AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state IN ('idle in transaction', 'idle in transaction (aborted)', 'active')
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

-- 3. Add a missing index without blocking writes.
-- Cannot run inside a transaction block. If it fails, it leaves an INVALID index to drop.
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_created_at_idx
  ON orders (customer_id, created_at);

-- 4. Guardrail: end sessions that sit idle inside a transaction too long.
-- Example only; pick a timeout that suits your workload.
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

Diagnostic SQL that ranks queries by total execution time in pg_stat_statements, finds long-open transactions in pg_stat_activity that can block vacuum, adds an index concurrently, and caps idle-in-transaction sessions for an app role.

How far can vertical scaling take a single PostgreSQL instance?

Vertical scaling is the next step, and it is a legitimate strategy rather than a stall. Each resource buys something specific, and each stops paying off at a different point.

Every resize has an operational cost. You promote a resized replica or restart the instance, then run degraded until the buffer cache warms up again. You also need a maintenance window, or a well-rehearsed failover, each time you move up a size.

The cost curve steepens near the largest instance sizes, and there is a hard ceiling at the top. Risk also concentrates in one machine. A bigger instance takes longer to replay WAL after a crash, and its base backups and restores take longer too. That can break your recovery time objective while query latency still looks fine.

Vertical scaling buys time. Decide in advance what that time is for.

What are the alternatives to sharding for a growing monolith database?

Before you shard, work through these options. They are listed roughly in order of cost.

Read replicas suit reporting, search indexing and background jobs that can tolerate stale data. Replicas lag the primary, so any request that must see its own writes has to read from the primary. That routing logic lives in the application. Long queries on a replica also cause problems. With hot_standby_feedback off, WAL replay can cancel those queries. With it on, the primary keeps dead rows around for the replica, and its tables bloat. Replicas add no write capacity, because every write still lands on one machine.

Declarative partitioning works well for time-series and append-heavy tables. Vacuum and reindex run per partition. Partition pruning skips irrelevant data. Dropping a partition removes old rows without a mass DELETE. The limits: planning cost grows with partition count, and primary keys and unique constraints must include the partition key. The table still lives on one machine.

Archiving or deleting cold data shrinks indexes, vacuum work and backups.

Moving queues, sessions, caches and analytics out of the OLTP database removes churn that it handles poorly.

Splitting separate domains into their own databases gives each one its own hardware. You lose joins and transactions that span those databases.

Copy-- Partitioned append-heavy table: monthly range partitions on created_at.
CREATE TABLE events (
    id          bigserial,
    created_at  timestamptz NOT NULL,
    account_id  bigint      NOT NULL,
    payload     jsonb,
    -- The primary key must include the partition key.
    -- PRIMARY KEY (id) alone is rejected on a partitioned table.
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_2026_11 PARTITION OF events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- An index on the parent is created on every partition.
-- Vacuum and reindex can then work one partition at a time.
CREATE INDEX ON events (account_id, created_at);

-- Partition pruning: the plan should scan only events_2026_11.
EXPLAIN
SELECT count(*)
FROM events
WHERE created_at >= '2026-11-01' AND created_at < '2026-11-08';

-- Retire a month of data without a mass DELETE, dead tuples or a long vacuum.
-- Detach first if you want to archive the table before dropping it.
ALTER TABLE events DETACH PARTITION events_2026_10;
DROP TABLE events_2026_10;

A monthly range-partitioned table: the primary key includes the partition key, a date-bounded query is pruned to one partition, and old data is removed by detaching and dropping a partition rather than running DELETE.

When should you shard PostgreSQL, and when should you avoid it?

Sharding is justified when the limit is the single primary itself, not how it is being used. Four signals qualify:

• Write volume or WAL generation exceeds what the primary can absorb after checkpoint tuning, batching and index pruning. Replicas cannot help because every write still lands on one node.
• The hot working set no longer fits in memory on any machine you can afford, so cache misses dominate latency.
• Maintenance no longer fits its window. Vacuum, index builds, backups or restores of one huge instance run longer than you can tolerate.
• Recovery time objectives are unmeetable because restoring or re-seeding a replica of that size takes too long.

Sharding is the wrong answer for unexamined slow queries, connection exhaustion, read-heavy load, table bloat, or growth that partitioning or a domain split can absorb. Each of these has a cheaper remedy that does not add cross-shard queries and distributed transactions.

Waiting too long is also a failure. If you shard as an emergency while the primary is saturated, you are backfilling data and changing query routing on a system with no spare headroom, and rollback is hard. A planned migration that starts while you still have capacity margin is much safer.

How can this approach save engineering time?

The savings come from three places.

You avoid solving the wrong problem. Ten minutes with pg_stat_statements and repeated pg_stat_activity samples can show that the bottleneck is one unindexed query or a pile of idle connections. Neither problem is fixed by sharding. Measuring first keeps a team from starting a multi-month project that leaves the real cause in place.

Cheap fixes are small and reversible. An index, a pooler or a partitioned table takes days of work and can be rolled back. A sharding project takes months, touches most data access code, and cannot be undone in practice.

You avoid a permanent tax. After sharding, every schema migration runs on N shards. Every report has to fan out. Every cross-tenant feature needs a distributed-transaction design. Every incident can involve N instances. That cost recurs for the life of the system, so each quarter you put off sharding is a quarter without it.

Illustrative estimate, using assumed inputs rather than measured results. Assume the cheap-fix ladder (query fixes, a pooler, one vertical resize and partitioning the largest table) takes two engineers about three weeks. Assume a sharding migration takes three engineers about six months. Assume ongoing shard-related work then takes about 10% of one engineer's time. Under those assumptions, the cheap fixes cost roughly 6 engineer-weeks, while sharding costs roughly 78 engineer-weeks up front, plus about 5 engineer-weeks a year after that. Plug in your own team's figures. The structure of the comparison matters more than these particular numbers.

Even when sharding does turn out to be necessary, the ladder still saves time. The diagnostics tell you which limit you are actually hitting. The query cleanup leaves less code to rewrite. The readiness work in the next section spreads the hardest part of the migration across normal releases, so you don't have to do it all in one emergency.

How do you prepare a monolith for sharding before you need it?

Shard-readiness is cheap to build gradually and expensive to retrofit under pressure, so start while the database is still comfortable.

Pick the boundary. Most B2B monoliths already have one: tenant_id, account_id or organization_id. The right key is the one that nearly every request, transaction and report is naturally scoped to. If you can't name it, sharding is probably the wrong tool.

Audit what crosses it. List the queries, joins, foreign keys and transactions that touch more than one tenant. Typical offenders are admin dashboards, global uniqueness constraints and background jobs that scan everything. Each one needs a plan: move it to a replica or warehouse, drop it, or redesign it.

Carry the key everywhere. Add tenant_id to child tables, not just the root, even when it looks redundant. Lead composite primary keys and indexes with it, as in (tenant_id, id). Include it in every WHERE clause. Without it, a future split means backfilling columns and rewriting queries on live data.

Route early. Send connections through a small resolver that maps a key to a database. Today it always returns the same one. Later, moving a tenant becomes a configuration change.

Measure progress. Track two numbers from query logs or statement statistics:

• The share of queries filtered by the key.
• The count of cross-key joins.

Watch both trend over releases. Making the codebase obey a constraint costs most of the engineering time in a sharding project. Doing that work incrementally, alongside normal feature development, turns the eventual split into a data migration, not a rewrite.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to when to shard postgresql, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.