A PostgreSQL database that has handled production traffic for years starts missing its latency targets.
The immediate suggestions are predictable:
- Add read replicas.
- Put Redis in front of the database.
- Partition the largest tables.
- Start planning for sharding.
Any of those might eventually be the right decision.
But choosing one before understanding the bottleneck is how a manageable performance problem becomes an expensive architecture migration.
The first question isn't "How do we scale PostgreSQL?" It's "What is PostgreSQL actually waiting on?"
The scenario
Imagine a commerce API running on a single PostgreSQL primary.
The application has been stable for three years. During the evening traffic peak, checkout latency rises sharply, but outside that window everything looks normal.
The team suspects that the database has outgrown its architecture.
One engineer proposes a read replica. Another suggests Redis. Someone else wants to partition the orders table.
Before changing the architecture, I'd want answers to five questions.
1. Is the database actually the bottleneck?
A slow checkout endpoint doesn't automatically mean PostgreSQL is overloaded.
Start with application tracing.
Break the request into:
- Time spent waiting for a database connection
- Time executing SQL
- Time spent on external services
- Application processing time
- Time blocked by retries or downstream dependencies
If most of the latency comes from waiting for an available connection, adding a read replica may not solve the underlying problem.
If the slowest SQL statements account for most of the delay, query analysis becomes the priority.
The distinction matters because different bottlenecks require different fixes.
2. What changes during peak traffic?
Compare a healthy period against the evening peak.
Look at:
| Signal | What it might reveal |
|---|---|
| CPU utilization | Expensive queries or insufficient compute |
| Disk read latency | I/O pressure or inefficient access patterns |
| Active connections | Pool exhaustion or excessive concurrency |
| Lock waits | Transactions blocking one another |
| Slow query frequency | A workload that becomes expensive at scale |
| Cache hit ratio | More reads reaching disk than expected |
No single metric is enough.
High CPU might indicate insufficient capacity, but it could also indicate a missing index or an inefficient query plan.
Similarly, high connection counts might be a symptom of slow transactions rather than the original cause.
3. Are a few queries responsible for most of the pain?
Before considering sharding, identify the queries consuming the most execution time.
PostgreSQL's pg_stat_statements extension is useful here, if it's already enabled.
For example:
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
The exact columns and availability depend on your PostgreSQL version and configuration.
This helps answer an important question:
Are we dealing with a broad capacity limit, or a small number of expensive queries?
Suppose a checkout-related query becomes slower as the orders table grows.
An EXPLAIN (ANALYZE, BUFFERS) investigation might reveal a sequential scan, inefficient join, or unexpected query plan.
Run potentially expensive diagnostic queries carefully, ideally against a representative non-production environment or with appropriate safeguards.
A targeted index or query rewrite could be a much smaller intervention than introducing another database component.
4. Would a read replica actually help?
Read replicas are useful when read traffic is a substantial part of the primary's workload and those reads can tolerate replication lag.
They are less useful when the bottleneck is:
- Write contention
- Long-running transactions
- Checkout updates that require immediate consistency
- Inefficient SQL that remains inefficient on a replica
A replica also introduces operational decisions:
- Which requests can safely use it?
- How will the application handle replication lag?
- What happens when the replica is unavailable?
- How will read routing and failover work?
A replica isn't free capacity without trade-offs.
The same principle applies to Redis. Caching can reduce repeated reads, but it introduces invalidation rules, stale-data risks, and additional failure modes.
5. What's the smallest safe change?
Once the bottleneck is understood, prioritize changes by expected impact, reversibility, and operational risk.
My typical decision sequence would be:
First: Diagnose. Identify the limiting resource, expensive queries, contention, or connection pressure.
Second: Fix obvious inefficiencies. Improve queries, indexes, transaction boundaries, or pool configuration where evidence supports it.
Third: Validate capacity. Determine whether vertical scaling or workload isolation provides sufficient headroom.
Fourth: Introduce new components only when justified. Consider read replicas, caching, partitioning, or sharding when the simpler options no longer meet the requirements.
This isn't a universal ordering. For example, an immediate capacity increase may be the safest short-term mitigation during an active incident.
The important part is to distinguish stabilizing the system now from designing the architecture for the next year.
The architecture decision I would defend
For this scenario, I would not approve sharding based solely on increased checkout latency.
I'd first establish:
- Which part of checkout became slower
- Which PostgreSQL resources are saturated
- Whether query and transaction improvements can restore headroom
- How much additional capacity the proposed change provides
- What new failure modes the change introduces
If the evidence points to expensive queries, I'd fix those first.
If read-heavy reporting traffic is competing with checkout, moving suitable reporting queries to a replica might be justified.
If the primary is fundamentally constrained by write throughput, we'd need a different discussion.
Architecture decisions should follow the bottleneck, not the popularity of the solution.
Final thought
Senior-level system design isn't just knowing when to use Redis, replicas, or sharding.
It's knowing when not to use them.
The strongest architecture decision is often the smallest change that restores reliability while preserving the option to evolve later.
This article explores a scenario from my book, System Design Under Pressure: 30 Real-World Architecture Problems Every Senior Software Engineer Should Know How to Solve. The book works through production architecture decisions using a repeatable diagnostic and trade-off framework.
Top comments (1)
On step 3, the "if it's already enabled" part matters: CREATE EXTENSION alone isn't enough, pg_stat_statements has to be in shared_preload_libraries, and that needs a restart. Worth setting up before the evening peak, not during it. And on anything older than PostgreSQL 13 the column is total_time, not total_exec_time.