How to Scale a Database: From 1K to 10M Users (2026)

In January 2026, OpenAI published how ChatGPT's database handles 800 million users. You would expect a hundred shards and some exotic custom system. It is one PostgreSQL primary that takes every write, plus about 50 read replicas spread across regions.

That is the whole secret of how to scale a database, and most people get it backwards. You do not scale by user count. You find the specific thing that is slow, fix it with the cheapest tool that works, and only move to the next tool when that one runs out. Indexes first, then a bigger machine, then read replicas, then partitioning. Sharding comes last, and plenty of big apps never get there.

This post walks one e-commerce app through every step, with real SQL and real numbers from Postgres 18.

Key takeaways:

• User count is not database load. Active users and the queries they trigger are what count.
• A single composite index took a "latest 20 orders" query from 120 ms to 0.1 ms in my test.
• Every index you add slows down writes. Four extra indexes made inserts 3x slower.
• Read replicas scale reads but can show stale data. Some reads must always go to the primary.
• Partitioning makes old data cheap to skip and cheap to delete. It does not add write capacity.

Does 10 million users mean your database is in trouble?

Not on its own. Here is the trap.

App A has 1 million users, but only 5,000 of them are active on a given day. App B has 10,000 users, and 8,000 of them are active, clicking around all day.

Which database is under more pressure? App B, easily. App A's number looks scarier on a pitch deck, but most of those users are rows sitting quietly in a table.

User count is not database load. The useful question is: what work is actually reaching the database?

Database load comes from four things:

1. How many operations hit the database per second
2. The mix of reads and writes (writes are more expensive)
3. How large the data is right now
4. How fast the data grows

And here is the part beginners miss. One user click is not one query. When someone taps "Place order", the database does something like this:

1. Insert a row into orders
2. Insert one row per product into order_items
3. Update stock in inventory
4. Read the new order back to show the confirmation page

One tap, four or more operations. Multiply that by your peak checkout traffic and you have your real number.

So if the user count is the wrong thing to watch, what is the right thing?

What is a database bottleneck, really?

A bottleneck is any part of your system that stops it from comfortably meeting its targets. Those targets are usually latency (how fast queries return), throughput, storage, or availability.

In practice, bottlenecks come in two very different flavours, and mixing them up wastes a lot of money.

Flavour 1: The database is healthy, but one query is slow. CPU is fine. Memory is fine. But the "My orders" page takes two seconds. This is almost always a missing or wrong index. Buying a bigger server here is like buying a faster car because you keep taking the wrong road.

Flavour 2: Queries are fine, but the machine is maxed out. CPU sits at 90%, memory is full, disk I/O is saturated. Every query is reasonable. There are just too many of them. Now a bigger machine or more machines actually helps.

How do you tell which one you have? Measure before you touch anything:

-- Which queries eat the most total time? (needs the pg_stat_statements extension)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

If one query dominates, you have Flavour 1. If the top of the list is lots of cheap queries and the CPU graph is flat at the top, you have Flavour 2.

One more thing to check early: connection count. Postgres gives each connection its own process, so a thousand app instances each opening their own connections can choke it before the queries even matter. A connection pooler like PgBouncer is often the very first fix, and it costs almost nothing.

But before any fix, you need to know what your app actually asks the database for.

Step 0: What are your access patterns?

An access pattern describes how your data is read or changed. For each one, you want to know:

• What data is needed
• How it is filtered
• How often it happens
• Whether it reads or writes
• Whether the result must be fresh or can be a few seconds old

That last question sounds minor. It decides your whole replica strategy later, so write it down now.

Here are the four access patterns for our shop:

Access pattern	Read or write	How often	Must be fresh?
Browse products (list and detail pages)	Read	Very high. People browse without buying	No. A few seconds old is fine
Place order	Write (several at once)	Medium	Yes
Update inventory	Write	Every order	Yes
View my recent orders	Read	High. People check it again and again	Right after checkout, yes

Look at "Browse products". If an admin changes a product description and a shopper sees the old text for three seconds, nobody gets hurt. Now look at stock. If one person buys the last two units and another shopper still sees "2 left" and buys them too, you have a real problem.

Access patterns also turn into workload over time. If the product page gets 500 visits a day this month and 50,000 a day next month, that one pattern's read load just grew 100x. The other three might not have moved at all. Scaling decisions should follow that kind of change, not a user milestone.

With the patterns written down, you can design tables that serve them.

Step 1: What schema survives growth?

Five tables are enough for our shop:

• users: id (primary key), email (unique), created_at
• products: id, name, current_price
• inventory: product_id, quantity
• orders: id, user_id (foreign key to users), status, amount, created_at
• order_items: order_id, product_id, quantity, unit_price

The relationships:

• One user places many orders (one to many, through orders.user_id)
• One order holds many products, and one product shows up in many orders. That is many to many, and order_items is the table in the middle that connects them.
• Each product has one inventory row

[Image: Diagram of the five tables (users, orders, order_items, products, inventory) with arrows showing one-to-many and many-to-many links]

Why store the price twice?

Spot the two price columns? products.current_price and order_items.unit_price. This is not duplication. It is history.

Say a pair of headphones cost ₹1,500 in January 2026. In August the price goes up to ₹3,000. A customer who bought them in January opens their order history. They should see ₹1,500, the price they actually paid. If you only store current_price and join to it, their old order suddenly shows ₹3,000. Invoices break, refunds get calculated wrong, and support tickets pile up.

So current_price answers "what does it cost now?" and unit_price answers "what did they pay back then?"

Why checkout needs a transaction

Placing an order touches three tables. If the server crashes after inserting the order but before updating inventory, you have an order with no stock deducted. A transaction makes it all or nothing:

BEGIN;

INSERT INTO orders (user_id, status, amount, created_at)
VALUES (42, 'paid', 3000, now())
RETURNING id;  -- say it returns 9001

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (9001, 101, 2, 1500);

UPDATE inventory
SET quantity = quantity - 2
WHERE product_id = 101
  AND quantity >= 2;   -- this line prevents overselling

COMMIT;

That AND quantity >= 2 is doing a lot of work. Without it, two shoppers buying the last two units at the same moment can both succeed, and your stock goes to -2. With it, the second UPDATE matches zero rows because the first one already took the stock. Your app sees "0 rows updated", runs ROLLBACK, and tells the second shopper it is sold out. This is the version that belongs in production.

A quick note on what transactions are for. Transactions keep your data correct. Indexes keep your reads fast. You never switch from one to the other. You need both.

The schema is solid. Then the orders table gets big, and one page starts crawling.

Step 2: Why is "show my latest 20 orders" slow?

Every shop has this query:

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

It looks harmless. On a small table, it is. So I tested it at scale instead of guessing.

The setup: Postgres 18.3 (running as PGlite on a laptop), an orders table with 2 million rows across 50,000 users and three years of dates, plus one heavy user with 5,000 orders. Every timing below is the best of three EXPLAIN ANALYZE runs. Your absolute numbers on a real server will differ. The gaps between them are what matter.

Index on orders	Time	What Postgres did
None	119.9 ms	Read all 2,000,000 rows, threw away 1,999,963 of them, sorted the rest
(user_id)	2.6 ms	Found this user's 5,037 rows, then sorted all of them to get 20
(user_id, created_at DESC)	0.10 ms	Walked straight to the 20 newest rows and stopped. No sort at all

That is roughly 1,200x faster from one line of SQL:

CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);

This is a composite index: an index on more than one column. It stores entries sorted by user_id first, then by created_at newest first inside each user. So "user 42's newest 20 orders" are sitting next to each other at the top of user 42's section, already in order.

Does LIMIT 20 make Postgres read only 20 rows?

No. This is a very common misunderstanding. LIMIT 20 controls how many rows come back to you. It says nothing about how much work Postgres does to find them.

Look at the "None" row in the table. The query had LIMIT 20 and still scanned 2 million rows, because Postgres cannot know which 20 are the newest until it has seen all of them. LIMIT only becomes cheap when an index already hands the rows over in the right order.

Does the column order in a composite index matter?

A lot. I flipped the index to (created_at DESC, user_id) and ran the same query for a normal user with 35 orders:

Index	Time	Pages read
(created_at DESC, user_id) (wrong order)	27.9 ms	5,166
(user_id, created_at DESC) (right order)	0.05 ms	24

Same two columns, about 570x slower. With created_at first, Postgres walks every order on the site from newest to oldest and checks each one for user_id = 7, until it has found 20. For a user who orders rarely, that means wading through a huge part of the table.

The rule of thumb: put the column you filter by with = first, and the column you sort or range over second.

What do indexes cost?

Indexes are not free, and this is where people overdo it.

Storage. In my test, that one composite index took 60 MB next to a 115 MB table. That is more than half the size of the data, for a single index.

Slower writes. Every INSERT, UPDATE, and DELETE has to update every index on the table too. I inserted 200,000 orders into a table with only a primary key, then into one with four extra indexes:

Table	Median insert time (5 runs)
Primary key only	719 ms
Primary key + 4 indexes	2,227 ms

Three times slower writes. On a checkout-heavy app, that matters.

Index the queries you actually run, not the columns you might one day filter on.

Indexes fix slow queries. But what happens when the queries are all fast and the machine is still drowning?

Step 3: When should you vertically scale a database?

When you are in Flavour 2: queries are already tuned, but CPU, memory, or disk I/O stays pinned under normal traffic.

Vertical scaling means keeping the exact same setup, one Postgres server, and giving it a bigger machine. More CPU cores, more RAM, faster SSDs.

It helps because:

• More CPU cores means more queries can run at the same time
• More RAM means more of your data and indexes stay in memory, so fewer slow trips to disk
• Faster storage (higher IOPS) means the trips that do reach disk come back sooner

And the best part: your application code does not change at all. Same connection string. That is why it is usually the right second move.

The tradeoffs:

1. Cost. On AWS RDS, each step up within an instance family roughly doubles the hourly price, because you get double the CPU and RAM. The bill grows at the same speed as the hardware.
2. A hard ceiling. The biggest single machines top out at a few hundred vCPUs and a few terabytes of RAM. After that, there is nowhere left to go up.
3. Still one machine. It is still a single point of failure, and resizing usually means a short failover.

A warning for system design interviews. If the interviewer describes an internal admin tool used by 200 employees and you answer "I would put it on a 512 GB RAM instance", expect the follow-up: "Why is the company paying for that?" Knowing the term is not enough. You have to justify it for the load in front of you.

Now say your app keeps growing, and the pressure is mostly reads.

Step 4: How do read replicas work, and when do they lie to you?

Most apps read far more than they write. Our shop is a good example: people browse products and check their orders all day, but they place orders far less often.

So you keep one primary that takes every write, and you add read replicas. These are copies of the same database that serve read traffic.

Here is how it flows:

1. All writes go to the primary
2. The primary streams every change to the replicas
3. Replicas answer the read queries you choose to send them

[Image: Diagram showing the app sending writes to the primary, the primary replicating to Replica A and Replica B, and read queries fanning out to the replicas]

One thing to be clear about: replicas are not separate databases with different data. It is one dataset, copied. That is exactly the shape OpenAI runs at 800 million users: one writer, dozens of readers.

What is replica lag, and why does it matter?

Copying takes time. Usually milliseconds, sometimes seconds when the primary is busy. That delay is replica lag.

For most reads, lag does not matter. Product pages are fine being two seconds behind.

Now picture this. A user places an order and gets sent straight to "My orders". That read goes to a replica that is 500 ms behind. Their brand new order is not there. They panic, refresh, maybe order again. Now you have a duplicate order and a support ticket.

This is the difference between a stale read (fine to be slightly old) and a fresh read (must reflect what just happened). Your access pattern table from Step 0 already told you which is which.

How do you route reads so they stay fresh?

A simple approach is read-your-own-writes: after a user writes something, send their reads to the primary for a few seconds.

import { Pool } from "pg";

const primary = new Pool({ connectionString: process.env.PRIMARY_URL });
const replicas = [
  new Pool({ connectionString: process.env.REPLICA_A_URL }),
  new Pool({ connectionString: process.env.REPLICA_B_URL }),
];

const STICKY_MS = 5_000; // comfortably above your normal replica lag

// lastWriteAt comes from a cookie or session, so it works across app servers
export function poolFor(lastWriteAt: number | undefined, mustBeFresh = false) {
  const wroteRecently = lastWriteAt !== undefined && Date.now() - lastWriteAt < STICKY_MS;
  if (mustBeFresh || wroteRecently) return primary;
  return replicas[Math.floor(Math.random() * replicas.length)];
}

After checkout, set lastWriteAt in the user's session. For the next five seconds, their reads go to the primary. Everyone else keeps reading from replicas.

The tradeoffs of replicas:

• Your app now decides where each read goes. That is new code and new bugs.
• Lag can grow. Heavy write bursts or a slow replica can push it from milliseconds to seconds.
• Writes are not scaled at all. Every write still lands on one primary. OpenAI's answer was to move their most write-heavy workloads to a separate sharded system, not to shard Postgres.

Reads are handled. But the orders table keeps growing, and almost nobody looks at orders from three years ago.

Step 5: How do you partition a Postgres table by date?

Most order queries only care about recent data: "last 30 days", "this month", "last 90 days". Yet every one of those queries runs against a table holding years of history.

Table partitioning fixes this. Postgres keeps one logical orders table for your app, but stores the rows in smaller physical tables, one per month. Your queries do not change. SELECT ... FROM orders still works.

CREATE TABLE orders (
  id         bigserial,
  user_id    int NOT NULL,
  amount     numeric(10,2),
  created_at timestamptz NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_07 PARTITION OF orders
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE orders_2026_08 PARTITION OF orders
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE orders_2026_09 PARTITION OF orders
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

What is partition pruning?

When your query filters on the partition key, Postgres skips every partition that cannot match. This is called partition pruning. Here is the actual plan for WHERE created_at >= '2026-08-01' on the table above:

Append
  ->  Seq Scan on orders_2026_08
  ->  Seq Scan on orders_2026_09

July is not even in the plan. Postgres did not open it.

Now the catch: pruning only works when the query filters on the partition key. I ran WHERE user_id = 5 with no date filter, and the plan listed all three partitions. Partitioning by date does nothing for a query that has no date in it.

[Image: Diagram of one logical orders table split into July, August and September partitions, with the July partition greyed out because the query only asks for August onwards]

The three gotchas nobody warns you about

1. The primary key must include the partition column. My first attempt used a plain id primary key. Postgres refused:

ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: PRIMARY KEY constraint on table "orders" lacks column "created_at"
which is part of the partition key.

That is why the example above uses PRIMARY KEY (id, created_at). Every search for "postgres partitioning primary key" is someone hitting this wall.

2. Future partitions do not create themselves. I inserted an order dated November 2026 and got:

ERROR: no partition of relation "orders" found for row

If nobody creates next month's partition, checkouts start failing at midnight on the 1st. Run a scheduled job (or the pg_partman extension) that creates partitions a few months ahead. If you have never written a cron schedule, this cron expression generator will build the line for you. Something like "every day at 2 AM" is plenty.

3. Old partitions are the cheap way to delete data. Deleting three years of old orders row by row can run for hours and bloat the table. Detaching a partition took 0.8 ms in my test:

ALTER TABLE orders DETACH PARTITION orders_2026_07;
-- archive it, dump it to cold storage, or DROP it

What partitioning does not do

• It does not add a second server. Everything is still on the same Postgres.
• It does not increase write capacity.
• It does not make every query faster. Queries without the partition key can get slightly slower, since Postgres checks every partition.

It is a data organisation tool, not a new machine.

What comes after partitioning?

When a single primary truly cannot take any more writes, you reach the advanced tools:

• Sharding: splitting data across multiple Postgres servers, for example by user_id. It is powerful and painful. Every query now needs to know which shard to ask.
• Caching: putting hot, read-heavy data like product pages in Redis so the database never sees most of those requests.
• Moving workloads out: sending one write-heavy feature to a database built for it. This is what OpenAI did instead of sharding Postgres.
• Backups and disaster recovery: not a scaling tool, but you will want it long before 10 million users.

Here is the lesson from the whole journey in one line. OpenAI serves 800 million users without sharding their main Postgres database. Your app almost certainly does not need to shard yet either. It probably needs one good index.

The database scaling cheat sheet

Save this. It is the whole post in one table.

Symptom	Fix	What it costs you
One query is slow, server is healthy	Composite index that matches the query	Storage and slower writes
Too many connections	Connection pooler (PgBouncer)	Almost nothing
CPU or memory pinned, queries already tuned	Vertical scaling (bigger machine)	Money, and a hard ceiling
Lots of reads, writes are fine	Read replicas	Replica lag, routing logic
Huge table, queries only want recent rows	Partition by date	Partition upkeep, primary key rules
One primary cannot take the writes	Sharding, or move the workload out	Serious complexity

Start at the top. Only move down a row when you have measured that the row above is not enough.

Frequently Asked Questions

What are the main database scaling techniques?

The main database scaling techniques are indexing, connection pooling, vertical scaling (a bigger server), read replicas, table partitioning, caching, and sharding. They go roughly from cheapest to most complex. Most applications should exhaust indexing, vertical scaling and read replicas before considering sharding.

What is the difference between vertical and horizontal scaling in a database?

Vertical scaling means giving your single database server more CPU, RAM, or faster storage. It needs no code changes but has a hard ceiling and higher cost at each step. Horizontal scaling means adding more servers, either as read replicas that copy the data or as shards that split it. It can grow much further but adds complexity to your application.

Is partitioning the same as sharding?

No. Partitioning splits one table into smaller pieces that all live on the same database server, which helps Postgres skip data it does not need. Sharding splits data across multiple separate servers, which is what adds write capacity. Partitioning is a built-in Postgres feature, while sharding usually needs extra tooling or application changes.

How many users can a single PostgreSQL database handle?

There is no fixed user limit, because load depends on active users and the queries they trigger, not the total count. OpenAI publicly runs ChatGPT for around 800 million users on one PostgreSQL primary with about 50 read replicas. With good indexes, pooling and replicas, a single Postgres primary handles far more than most apps will ever need.

What database scaling questions come up in system design interviews?

Common questions include how you would speed up a slow query, when to use read replicas, how to handle stale reads after a write, the tradeoffs of indexes, and when partitioning or sharding makes sense. Interviewers care most about whether you justify each choice with the actual load, not whether you name every technique.

Written by Adithya Guttha, Founder of Art of Code.