Adding a Database Server Doubled Capacity. One Index Multiplied It by 2,600.

Measure first, then climb the ladder.

The API is slow.

The dashboard takes two seconds to load. Someone opens the database graph and sees CPU at 100%.

Then the meeting starts.

"We need a read replica."
"Should we add Redis?"
"Maybe it's time to shard."

All three sound like serious engineering. All three cost money, add moving parts, and stay with you for years.

But nobody in that meeting has asked the first question: what is the database actually doing?

Nikki Siapno's recent Level Up Coding piece on scaling databases lays out a sensible order: measure the bottleneck, reduce the work, optimize queries, then add replicas, and shard last.

So this article puts that order to the test. One table, one slow query, one load test, and real measurements at each step.

The Test

The setup is a small PostgreSQL lab that you can run yourself. The full script is at the end of this article.

• PostgreSQL 16.15 on a 2-core Linux cloud machine
• An orders table with 5 million rows and 100,000 customers
• One common query: the 20 latest orders for a customer
• 8 clients sending that query as fast as they can for 30 seconds
• Each step repeated 3 times, reporting the median

Here is the query:

SELECT id, status, amount, created_at FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 20; 

It looks harmless. You probably have one like it in your code right now.

To keep the comparison fair, the primary database was pinned to one CPU core. The second core ran the load generator, or the replica in the replica test.

The Results

Read that table twice.

The replica worked. Capacity went up about 2.2 times, which is close to what a second core should give.

But one CREATE INDEX on the original server handled about 2,600 times the traffic. It beat the two-server setup by about 1,180 times.

The index took 4.8 seconds to build and used 150 MB of disk.

Step 1: Measure the Bottleneck

Before touching anything, ask PostgreSQL where the time goes.

The pg_stat_statements extension records every query shape, how often it runs, and how long it takes. It must be added to shared_preload_libraries, which needs a restart.

SELECT calls, round(mean_exec_time::numeric, 1) AS avg_ms, round(total_exec_time::numeric / 1000, 1) AS total_seconds, left(query, 70) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; 

Sort by total time, not average time. A 5 ms query that runs a million times hurts more than a 2 second report that runs once a day.

Next, look at the plan of your worst query:

EXPLAIN (ANALYZE, BUFFERS) SELECT id, status, amount, created_at FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 20; 

This is the real output from the test, shortened:

Limit (actual time=224.516..224.520 rows=20) Buffers: shared hit=66670 -> Sort -> Seq Scan on orders Filter: (customer_id = 4242) Rows Removed by Filter: 4999944 Execution Time: 245.630 ms 

Here is the whole problem in one line: Rows Removed by Filter: 4999944.

To find 56 rows for one customer, PostgreSQL read all 5 million. Think of a librarian who walks past every book in the building each time someone asks for one author.

No amount of extra hardware changes what this query is doing.

Different symptoms point to different problems:

These two queries help with the first pass:

-- Tables being read the hard way SELECT relname, seq_scan, seq_tup_read, idx_scan, seq_tup_read / NULLIF(seq_scan, 0) AS rows_per_scan FROM pg_stat_user_tables ORDER BY seq_tup_read DESC LIMIT 10; -- Connection pressure SELECT count(*) FILTER (WHERE state = 'active') AS active, count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn, count(*) AS total, current_setting('max_connections') AS max FROM pg_stat_activity WHERE backend_type = 'client backend'; 

Step 2: The Replica Added Capacity, Not Speed

This is the step many teams take first, so it deserves a fair look.

With a replica, the same 8 clients were split between two servers. Throughput doubled and average latency fell from 2,020 ms to 926 ms.

That looks like a fix. It isn't.

With no load, the query took 246 ms on the primary and 304 ms on the replica. The plan on the replica was identical: same sequential scan, same 4,999,944 rows thrown away.

So where did the latency gain come from?

Queueing. With 8 clients on one core, each request spent most of its time waiting behind other requests. A second server shortened the line. The checkout was just as slow.

A replica shortens the queue. It does not make the work smaller.

The replica also brings bills of its own: another server to pay for and monitor, and one more problem that the next section measures.

Step 3: The Replica Problem Nobody Sees in Testing

A read replica copies changes from the primary a moment after they commit. That moment is called replication lag.

Here is the test. Insert a row on the primary, wait for the commit, then read it from the replica straight away. Do that 2,000 times.

This was the best possible case: both servers on the same machine, no network between them, and almost no load.

98.65% of immediate reads missed the row on an idle system.

The replica was only 0.33 ms behind. That was still slower than the next read.

Don't read too much into the idle and load numbers being in that order. The point is that "very low lag" and "no stale reads" are different things. Across a real network, lag will be higher.

Your users meet this as a strange bug:

1. A user updates their profile.
2. The app redirects to the profile page.
3. The page shows the old name.
4. The user refreshes, and now it's correct.

The fix is to read from the primary right after a write. Here is a simple Django router that does this for the rest of a request:

import threading _state = threading.local() class PrimaryAfterWriteRouter: def db_for_read(self, model, **hints): return "default" if getattr(_state, "wrote", False) else "replica" def db_for_write(self, model, **hints): _state.wrote = True return "default" def allow_relation(self, obj1, obj2, **hints): return True def allow_migrate(self, db, app_label, **hints): return db == "default" def primary_after_write_middleware(get_response): def middleware(request): _state.wrote = False try: return get_response(request) finally: _state.wrote = False return middleware 

Add the class to DATABASE_ROUTERS and the middleware to MIDDLEWARE.

This covers one request. In the profile example, the redirect is a second request, so you also need something that carries over, such as a short-lived cookie or session flag that keeps that user on the primary for a few seconds.

Also keep money reads on the primary: balances, stock counts, permissions, and anything a decision depends on.

The Failure This Lab Hit by Accident

The first run of this test never finished.

During the write-heavy stage, the replica fell behind. PostgreSQL's primary keeps only a limited amount of old WAL (its change log), and it recycled the segment the replica still needed. The replica could never catch up, and the primary's log filled with this line every few seconds:

ERROR: requested WAL segment 00000001000000000000004C has already been removed 

Nothing crashed. The replica stayed up and kept answering queries, while its data froze in the past. The test sat waiting for a row that would never arrive until it was stopped.

This is the scariest kind of failure: a healthy-looking server serving old data.

The fix was a replication slot, which makes the primary keep WAL until the replica has received it:

pg_basebackup -h primary -D /data/replica -R -X stream -c fast -C -S lab_slot 

Slots have their own danger. If a replica dies and the slot stays, the primary keeps WAL forever and can fill its disk. Set max_slot_wal_keep_size as a limit, and watch both of these:

SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_held FROM pg_replication_slots; SELECT application_name, state, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS behind, replay_lag FROM pg_stat_replication; 

If a replica is missing from the second query, it has stopped replicating. Alert on that.

Step 4: Cache Only What Repeats

Redis is the next suggestion in most of these meetings, so the lab tested it on the slow, unindexed query.

The code is the standard cache-aside pattern: check Redis, and on a miss, query PostgreSQL and store the result for 5 minutes. One client ran for 60 seconds with three traffic patterns:

The third row is what people imagine when they say "add a cache." The first two rows are what can happen instead.

The cache did not fail. The traffic did not repeat enough. With 100,000 customers and about 5 slow queries a second, most keys were requested once and never seen again before the test ended.

Even in the best case, the misses stayed painful. The 95th percentile was still 219 ms, because every miss ran the same full table scan.

Compare that with the plain index: under 1 ms on average, even with 8 clients at full speed, with no hit rate to worry about and no invalidation to get wrong.

A cache makes a good query cheaper. It is a poor way to hide a bad one.

So before you add one, ask these questions:

• Do the same keys get read again and again?
• How stale can this data be?
• Who invalidates it when the data changes?
• What happens to the database if Redis restarts and every request misses?

That last one is a real risk. OpenAI's write-up, covered below, describes using cache locking and leasing so that only one reader fetches a missed key from PostgreSQL.

Step 5: The Index, and Its Bill

Here is the plan after one index:

CREATE INDEX CONCURRENTLY orders_customer_created ON orders (customer_id, created_at DESC); 

Limit (actual time=0.034..0.205 rows=20) Buffers: shared hit=23 -> Index Scan using orders_customer_created on orders Index Cond: (customer_id = 4242) Execution Time: 0.228 ms 

From 66,670 buffers to 23. From 245.6 ms to 0.23 ms.

The column order matters. customer_id narrows down to one customer, and created_at DESC means the rows are already sorted, so PostgreSQL can stop after 20. The sort step disappears.

Use CONCURRENTLY on a live table. A plain CREATE INDEX blocks writes while it builds. In Django, the safe version looks like this:

from django.contrib.postgres.operations import AddIndexConcurrently from django.db import migrations, models class Migration(migrations.Migration): atomic = False dependencies = [("orders", "0041_previous")] operations = [ AddIndexConcurrently( model_name="order", index=models.Index( fields=["customer_id", "-created_at"], name="orders_customer_created", ), ), ] 

If a concurrent build fails, it leaves an invalid index behind. Drop it and try again.

Indexes are not free, though. The lab measured insert speed on a 1 million row table with no indexes, 1 index, and 5 indexes:

Five indexes cost about 21% of write speed on this test. These runs were noisy, and the ranges for 0 and 1 index overlapped, so treat the trend as the finding, not the exact percentage.

The cost grows with every index you never use. This query finds the candidates:

SELECT s.relname, s.indexrelname, s.idx_scan, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary ORDER BY pg_relation_size(s.indexrelid) DESC; 

Before you drop one, remember these counts are per server. An index with no scans on the primary may be busy on a replica.

Step 6: Shard Last, and Mean It

This lab did not test sharding. It would need several machines to say anything honest. So here are two real teams that lived at the top of the ladder.

Case study: Figma

Figma's database team describes a stack that grew almost 100x since 2020.

They climbed in order. In 2020 they ran a single Postgres database on AWS's largest physical instance. By the end of 2022 they had caching, read replicas, and about a dozen vertically partitioned databases, with related tables split into their own groups.

Only then did they hit real limits. Some tables reached several terabytes and billions of rows, and the highest-write tables neared the IOPS limit on RDS.

Horizontal sharding started in late 2022. The first table took roughly nine months and shipped in September 2023. Figma called the work "an order of magnitude more complex" than their earlier scaling, and listed what it gave up: cross-shard transactions, foreign keys, and globally unique indexes.

Case study: OpenAI

OpenAI's January 2026 post describes serving 800 million ChatGPT users on PostgreSQL, with one primary handling writes and nearly 50 read replicas.

They did not shard their PostgreSQL deployment. Their reasons: sharding would mean changing hundreds of endpoints and could take months or years, and the workload is mainly reads.

But they were not doing nothing. They optimized queries, reviewed ORM-generated SQL, used PgBouncer, put a cache in front, and moved shardable, write-heavy workloads to systems such as Azure Cosmos DB.

Note what both stories share. Neither team reached for the biggest tool first, and both moved based on a specific limit they had measured.

Signs you might really need sharding

• A write bottleneck on the primary that remains after query and index work
• A single table so large that maintenance like vacuum causes reliability problems
• Storage or IOPS limits on the largest machine you can buy
• Isolating noisy tables into their own database has already been done

Replicas will not help with any of these writes, because every replica has to apply every write too.

And there is a smaller step that people skip. PostgreSQL partitioning splits a big table into pieces on the same server, which helps with time-based data and deleting old rows, with no routing layer at all.

The Ladder

Here is the order, with the question that earns each step:

You can stop at any step. Most systems should.

What This Test Does Not Prove

• 2,600x is not a promise. This query was chosen because it had no index on a big table. It is a best case for indexing. If your query already uses a good index, you will see nothing like this.
• The replica test was a clean case. One extra core, no network, and only reads. Real replicas also have to replay writes.
• One query is not a workload. Real systems have many query shapes competing for the same resources.
• The numbers depend on the machine. This was a small shared cloud machine, and baseline runs ranged from 3.7 to 5.5 requests per second. Trust the size of the gaps, not the decimals.
• Cold caches matter. The replica's 304 ms included reading pages into memory for the first time, while the primary's data was already cached.

What To Do on Monday

1. Turn on pg_stat_statements if it's off, and find your top 10 queries by total time.
2. Run EXPLAIN (ANALYZE, BUFFERS) on each one. Look for a high Rows Removed by Filter.
3. Check for N+1 queries. In Django, select_related and prefetch_related can remove whole groups of queries.
4. Add indexes from real query patterns, using CONCURRENTLY.
5. Drop indexes nobody uses, after checking every replica.
6. Before any new infrastructure, write down the number it should change. "Replica will take p95 from X to Y." If nobody can fill in that sentence, the measuring isn't finished.
7. If you already have replicas, alert on lag and on a replica going missing, and list which reads must stay on the primary.

Common Questions

Should I add an index for every slow query?
No. Start with queries that run often. An index for a report that runs once a month may cost more in write overhead than it saves.

Is high CPU always a reason to scale up?
No. High CPU tells you the database is working hard. The query plan tells you whether that work is necessary. In this test, a missing index was creating almost all of it.

Can a replica ever beat an index?
Yes, when queries are already efficient and you simply have too many of them. That is exactly when replicas earn their cost.

When should I cache instead of optimizing?
When a query is already good but runs constantly on data that rarely changes. A homepage product list is a classic example.

Do managed databases solve this?
They help with backups, replicas and failover. They will not fix a sequential scan on 5 million rows.

Are ORMs the problem?
Not by themselves. But they make it easy to run queries you never see. OpenAI's post specifically calls out reviewing ORM-generated SQL.

Run It Yourself

Run this on a throwaway machine or container with at least 2 cores, and allow about 15 minutes. It creates and deletes its own data in /tmp/scaling-lab.

docker run -it --rm --cpus=2 ubuntu:24.04 bash # inside the container apt-get update apt-get install -y postgresql redis-server python3-psycopg2 python3-redis util-linux 

Save the script below as /tmp/scaling_lab.py, then run it as the postgres user:

chmod a+r /tmp/scaling_lab.py su postgres -s /bin/bash -c \ 'cd /tmp && PATH=/usr/lib/postgresql/16/bin:$PATH python3 /tmp/scaling_lab.py' 

The results are saved to lab_results.json. Change one thing at a time: try 20 million rows, a hotter or colder cache pattern, or a real replica on a second machine.

Click to expand the full lab script (scaling_lab.py)

#!/usr/bin/env python3 """Database scaling lab: test each step of the scaling ladder on one workload. Stages: measure, read replica, cache, index, index write cost, stale reads. Needs PostgreSQL 16 with contrib, Redis, psycopg2, redis-py and 2 CPU cores. Run as a non-root user with the PostgreSQL bin directory on PATH. Use a throwaway machine. It creates and deletes its own data in BASE. """ import json, os, random, re, shutil, statistics as st, subprocess as sp, time import psycopg2, redis BASE = "/tmp/scaling-lab" P, R, RP = 55432, 55433, 56379 ROWS, CUSTOMERS, RUN_S, REPEATS = 5_000_000, 100_000, 30, 3 READ = ("SELECT id, status, amount, created_at FROM orders " "WHERE customer_id = %s ORDER BY created_at DESC LIMIT 20") out = {} def sh(cmd, core=None, check=True): if core is not None: cmd = f"taskset -c {core} {cmd}" r = sp.run(cmd, shell=True, capture_output=True, text=True) if check and r.returncode: raise RuntimeError(cmd + "\n" + r.stdout + r.stderr) return r.stdout def conn(port=P): c = psycopg2.connect(host=BASE, port=port, dbname="postgres") c.autocommit = True return c def q(sql, port=P): c = conn(port); cur = c.cursor(); cur.execute(sql) rows = cur.fetchall() if cur.description else [] c.close() return rows def plan(port=P): rows = q("EXPLAIN (ANALYZE, BUFFERS) " + READ.replace("%s", "4242"), port) return [r[0] for r in rows] def pgbench(port, script, clients, core, seconds=RUN_S): cmd = (f"pgbench -n -h {BASE} -p {port} -f {script} -c {clients} -j 1 " f"-T {seconds} postgres") return sp.Popen(f"taskset -c {core} {cmd}", shell=True, stdout=sp.PIPE, stderr=sp.STDOUT, text=True) def parse(text): tps = float(re.search(r"tps = ([\d.]+)", text).group(1)) lat = float(re.search(r"latency average = ([\d.]+) ms", text).group(1)) return tps, lat def setup(): shutil.rmtree(BASE, ignore_errors=True); os.makedirs(BASE) sh(f"initdb -D {BASE}/p -A trust") with open(f"{BASE}/p/postgresql.confSource：https://dev.to/anas_issath/adding-a-database-server-doubled-capacity-one-index-multiplied-it-by-2600-437g
My favorites

Related posts

Show HN: My compact, bio inspired distributed edge detection algorithm

Hi HN, I've been working on a bio / visual cortex inspired edge detection algorithm. I released a preliminary version a

0Hacker News Show

Anthropic found a way to prove which AI model answered you — but there’s a costly catch

Developers using AI APIs have little way of knowing whether the model answering their requests is actually the one they’

0The New Stack

Low-voltage capacitor combines rapid switching with a trillion cycles without breakdown

A breakthrough has been made toward overcoming both the speed and endurance limitations of low-power memory. A research

0Phys.org Tech

Now available: Retention messages for subscriptions

Reduce cancellations with retention messages that remind subscribers of your subscription's value as they decide whether

0Apple Developer News

What a payment plan really costs: the 20 lines of Python behind a dental financing calculator

When someone is quoted a few thousand dollars for dental work, the question that follows is rarely "can I afford this?"

0dev.to

An AI proposal is not permission: designing reviewable HR actions

A conversational interface can make a workflow easier to start. It should not make a consequential change easier to appl

0dev.to