Columns › 6. Deploying the system › Part 25
Hot and Cold Data, Sharding and Splitting Databases: What to Do When the Data Grows
冷熱資料與分庫分表:資料長大之後怎麼拆
When the data outgrows one database, measure first, use the cheap fixes in order, and only then split. This article covers hot and cold data, time partitioning and archiving, vertical splits, shard keys and routing, IDs with embedded shard bits, cross-shard queries, zero-downtime migration and operations.
In this article
- What this article solves
- Step 1: Diagnose before you decide to split
- Step 2: Hot and cold data — move the rarely used data out of the way first
- Step 3: Vertical splits — by business domain, or by column
- Step 4: Horizontal sharding — shard keys, routing and globally unique IDs
- Step 5: What breaks after sharding
- Step 6: Routing in middleware, or in the application
- Step 7: Migrating without downtime
- Step 8: Operations — monitoring, backups, schema changes and capacity planning
- Common pitfalls
- Key takeaways
- Next
The previous article, “Adding Indexes Safely: Indexing Large Tables Without an Outage”, dealt with the most common and cheapest fix for “the table is big and the queries are slow”: find the missing indexes and add them without locking the table. The one before it, “From Monolith to Microservices: Evolving the Architecture at 100K, 1M and 10M Scale”, took the architectural view all the way up to sharding, read models and multiple regions at the 10M scale. That article covered “splitting a big table across several databases” in a single subsection; this one expands that step in full: once the data has really grown, how to decide whether to split, what to split first, how to split it, and what it costs you afterwards.
What this article solves
A few years after an online shop goes live, the orders table is usually the biggest table in the whole system. Its growth has nothing to do with the number of users and everything to do with time: even if the number of orders per day stays flat, the table grows every day. The result is a series of problems that surface slowly: backups go from minutes to hours, adding a column needs a maintenance window, indexes grow too large to fit in memory, and now and then a single query without the right conditions pins the primary's CPU.
“Split the database and shard the tables” is often the first answer proposed for these problems, but it is also the most expensive answer and the hardest to undo. This article works through them step by step, in the order you should actually do things:
- Diagnose first: which numbers to measure, which cheap fixes exist, and which signals mean you really do need to split.
- Separate hot and cold data: most old data is almost never read, so move it out of the way first — time-based partitioning, an archive database, object storage, plus the matching caching and API behaviour.
- Vertical splits: put the tables of different business domains in different databases, or move rarely used columns out of a wide table.
- Horizontal sharding: choose a shard key, choose a routing scheme, use logical shards to make later scaling cheap, generate globally unique order IDs that carry the shard, and write the routing package in Go.
- What breaks after sharding: cross-shard queries, pagination and sorting, aggregates, unique constraints, cross-shard transactions, joins and reporting, and how to handle each.
- Whether routing belongs in middleware or in the application.
- Migrating without downtime: full copy, CDC catch-up, verification, read switch, write switch, rollback plan, and resharding later.
- Operations: per-shard monitoring, backups, schema changes and capacity planning.
The example continues the series' online shop, example.com/shop: the four services api-gateway, order, payment and inventory, with PostgreSQL as the database, and SQL written in PostgreSQL syntax; MySQL is mentioned only where the differences matter.
Step 1: Diagnose before you decide to split
“The database is slow” is not a problem you can fix directly. What is slow might be one query, one table, one kind of load (reads or writes) or one time of day, and each calls for a completely different fix. So the first step is always measurement, and you need to measure enough to answer “what exactly is big, what is slow and what is busy”.
Which numbers to measure
| Metric | How to measure it (PostgreSQL) | What it tells you |
|---|---|---|
| Slow and most expensive queries | log_min_duration_statement, pg_stat_statements | Which queries the time goes to; a few queries, or everything |
| Table and index sizes | pg_total_relation_size, pg_indexes_size | Which tables are growing; whether indexes are bigger than the data |
| Row counts and growth rate | pg_stat_user_tables.n_live_tup, recorded daily | At the current rate, how long until the next threshold |
| Queries per second, read/write | Deltas of calls in pg_stat_statements, application metrics | Whether the bottleneck is reads or writes; whether replicas would help |
| p99 latency | Application latency histograms (“Observability in Go: Logs, Metrics and Tracing”) | The slowness users actually feel; averages hide the problem |
| Cache hit ratio (buffer pool) | heap_blks_hit and heap_blks_read in pg_statio_user_tables | Whether the frequently used data still fits in memory |
| Connections and waits | pg_stat_activity, pool wait metrics | Whether the database is slow, or the application is queueing for a connection |
| Replication lag | pg_stat_replication | Whether the read replicas can take on more reads |
| The data's “age distribution” | Row counts per month, and how old the data being read is (next step) | How much hot/cold separation could remove |
A few practical queries:
-- The 10 largest tables: row count, total size (including indexes and TOAST), index size, scan types
SELECT relname,
n_live_tup,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_indexes_size(relid)) AS index_size,
seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
-- Buffer hit ratio for tables: a clear long-term drop means the frequently used data no longer fits in memory
SELECT sum(heap_blks_hit)::numeric
/ nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS heap_hit_ratio
FROM pg_statio_user_tables;
-- The queries with the most total time (needs the pg_stat_statements extension; column names are from PostgreSQL 13 on)
SELECT calls,
round(total_exec_time) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- The age distribution of orders: how many per month
SELECT date_trunc('month', created_at) AS month, count(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;Tip: Record these numbers over time, not just once when something breaks. The growth rate matters more than the current size: a 200 GB table growing 2% a month and a 50 GB table doubling every month call for completely different urgency.
Do the cheap fixes first, in order
Once you have measured, most cases of “the database can't keep up” can be solved with the fixes below, each far cheaper than splitting the database. The order is roughly from lowest to highest cost:
- Fix queries and add indexes. The top few entries of the slow query list usually account for most of the load. Missing indexes, full table scans without conditions, N+1 queries and
SELECT *pulling back large columns nobody needs are places where changing a few lines gives a several-fold improvement. For how to add indexes safely on large tables, see the previous article, “Adding Indexes Safely: Indexing Large Tables Without an Outage”. - Tune the connection pool. A lot of “the database is slow” is really the application queueing for connections, or so many connections that the database is busy switching between them. Pool sizes have to be worked out together with the replica count (“database/sql, Transactions and Connection Pools in Go”).
- Add a cache. When a small set of popular data is read over and over, cache-aside removes most of those reads outright (the 100K stage of “From Monolith to Microservices”).
- Split reads and writes. When reads dominate, send the reads that can tolerate a little staleness to read replicas, and handle replication lag properly.
- Scale vertically. A bigger machine, faster disks, more memory. It doesn't sound “architectural” enough, but it needs no code changes, and the resources a single database host can offer today are considerable. When splitting the database would take months, upgrading the hardware to buy time is often the most rational choice.
- Separate hot and cold data. Move rarely read old data out of the main table (step 2). This changes how the data is stored, but it stays within the same database or the same service, and its complexity is far lower than sharding.
Which signals justify splitting
Only if you have done all of the above and still see the signals below is it worth seriously considering a split. The key point: different signals call for different kinds of split, so don't lump them together as one “split the database and shard the tables”:
| Signal | What the problem really is | The matching approach |
|---|---|---|
| One table is huge but mostly old, rarely read data; backups, schema changes and clean-up keep taking longer | Data volume, not load | Hot/cold separation (step 2) |
| Tables from different domains compete for resources; one service's batch job slows another; teams wait on each other | Data from different domains tied together | Vertical split (step 3) |
| The hot data alone no longer fits in one host's memory; write throughput has hit the limit of a single primary | The write and capacity ceiling of a single primary | Horizontal sharding (step 4 onwards) |
The last row has the highest bar. Read pressure can almost always be handled with caches and read replicas; what actually forces you to shard is usually writes: every write has to land on the primary, and no number of replicas can share that load.
Step 2: Hot and cold data — move the rarely used data out of the way first
Define hot and cold by access pattern
“Hot data” is not a synonym for “new data”; it is data that is read and written often and needs low latency. “Cold data” is data that is rarely accessed, can be a little slower, but still has to be kept. For orders, the access pattern is usually very concentrated:
- The first few days after an order: payment, shipping and status checks, with frequent reads and writes.
- A few weeks to a few months: the occasional look, return requests, customer service lookups.
- After several months: almost only users browsing their order history, annual reports, audits and legal retention.
Don't guess; measure. The most direct way is to record, wherever orders are read, “how old the order being read is”, as a histogram:
// Excerpt: internal/order/service.go
// Record the age (in days) of every order read; the distribution on the dashboard shows where to draw the hot/cold line.
age := time.Since(o.CreatedAt).Hours() / 24
s.metrics.OrderReadAgeDays.Observe(age)After a week or two you might see something like “98% of reads are of orders from the last 90 days”. That is your hot/cold line: orders within 90 days are hot, and the rest can move somewhere cheaper and slower. How long data stays online usually also has to cover business rules such as the return period and the reconciliation cycle; take whichever of the two is longer.
Figure: The order service picks a tier by the order's time. Every day the archive job moves finished old orders to the archive database in batches; older data is exported to object storage once a month. The live hot path only touches the leftmost tier.
Approach 1: Partition the table by time
“From Monolith to Microservices” showed orders partitioned by a hash of user_id, which was about splitting one table into several equal parts. Separating hot and cold data instead calls for range partitioning by time: one partition per month, new data always lands in the newest few partitions, and old partitions no longer change, so you can treat them like separate files.
-- order database: the orders table with one partition per month of created_at
CREATE TABLE orders (
id BIGINT NOT NULL, -- generated by the application (step 4), not a sequence
user_id BIGINT NOT NULL,
total BIGINT NOT NULL, -- in cents
status TEXT NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'cancelled')),
paid_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (id, created_at) -- the primary key must include the partition key
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026_10 PARTITION OF orders
FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00');
CREATE TABLE orders_2026_11 PARTITION OF orders
FOR VALUES FROM ('2026-11-01 00:00:00+00') TO ('2026-12-01 00:00:00+00');
CREATE TABLE orders_2026_12 PARTITION OF orders
FOR VALUES FROM ('2026-12-01 00:00:00+00') TO ('2027-01-01 00:00:00+00');
-- Indexes created on the parent table are created on every partition automatically
CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC);
CREATE INDEX orders_created_id_idx ON orders (created_at, id);
-- A query with a time condition scans only the relevant partitions (partition pruning)
EXPLAIN SELECT id, total, status
FROM orders
WHERE user_id = 42
AND created_at >= '2026-11-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 20;The EXPLAIN output only mentions orders_2026_11 and later partitions; the October partition is never even opened. This is partition pruning: when the query's conditions include the partition key, the planner can rule out partitions that cannot contain matching rows. Conversely, a query without a time condition scans every partition, so queries like “my orders” should default to a time range (say, the last 90 days), with a separate way to look up older orders.
Partitioned tables come with a few rules and traps you must know:
- Primary keys and unique indexes must include the partition key. That's why the primary key is
(id, created_at), and the database cannot guarantee thatidalone is unique; uniqueness ofidhas to come from how it's generated (the ID generator in step 4). Likewise,order_itemscan no longer have a foreign key toorders(id). Common answers are to storecreated_atinorder_itemstoo and partition it the same way, or to drop the foreign key and enforce it in the application. - Rows with no matching partition fail to insert. If December's partition hasn't been created, the first order on 1 December gets the error
no partition of relation "orders" found for row. You need a scheduled job that creates the next few months' partitions ahead of time (the PostgreSQL ecosystem commonly automates this with an extension such aspg_partman), plus an alert on “fewer than N future partitions left”. - A default partition (
DEFAULT) cuts both ways. It catches rows with no matching partition and avoids failed writes, but whenever you later create a matching partition, PostgreSQL has to scan the default partition to make sure no conflicting rows exist, and theDETACH ... CONCURRENTLYbelow cannot be used while a default partition exists. The safer approach is to have no default partition and rely on alerts to make sure partitions are always created ahead of time.
Cleaning up old data changes from “DELETE tens of millions of rows” to “detach one partition”:
-- Detach an old partition without blocking live reads and writes (PostgreSQL 14 and later;
-- cannot run inside a transaction block, and cannot be used on a table that has a default partition)
ALTER TABLE orders DETACH PARTITION orders_2026_10 CONCURRENTLY;
-- Once detached it is an ordinary table: export it to the archive or object storage, check the counts, then drop it
DROP TABLE orders_2026_10;Deleting lots of data with DELETE produces a large amount of WAL, bloats indexes and tables, and slows replication; detaching and dropping a partition is an almost instant file operation. That alone makes time partitioning the best-value tool for “a table that only ever grows”.
Tip: MySQL also supports
PARTITION BY RANGE, and old data is cleaned up withALTER TABLE ... DROP PARTITION; the idea is the same. The differences: MySQL partitioned tables also require every unique key to include the partitioning columns, and partitioned InnoDB tables don't support foreign keys.
Approach 2: An archive database and an archive job
Partitioning keeps cold data in the same database, just in different partitions; the data still takes up the primary's disk, backup time and restore time. The next step is to move finished old orders to a separate archive database: its machine can be much smaller, its disks much cheaper, it needs no read replicas, and the live primary keeps only the hot data.
The archive table has the same structure as the live one, plus a time of archiving:
-- Archive database (a separate PostgreSQL)
CREATE TABLE orders_archive (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
total BIGINT NOT NULL,
status TEXT NOT NULL,
paid_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL,
archived_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX orders_archive_user_created_idx ON orders_archive (user_id, created_at DESC);Moving the data looks like just “copy it over and delete the original”, but the job runs against the production primary and has to meet several conditions at once:
- Batches: move only a few hundred to a few thousand rows at a time and rest between batches, to avoid long transactions, lock contention and spikes in replication lag.
- Keyset pagination, not
OFFSET:OFFSET 100000has to read and discard the first hundred thousand rows, and gets slower the further you go; keyset pagination remembers the(created_at, id)of the previous batch's last row and starts the next batch after it, so every batch costs the same. - Idempotence: the job can be interrupted at any step (a deploy, a crash, a timeout). Writing to the archive with
ON CONFLICT DO NOTHINGmeans rerunning the same batch neither duplicates rows nor fails. - Verify, then delete: after writing to the archive, first confirm every row is there, and only then delete from the live table; if verification fails, stop. Better not to move than to lose data.
- Only move finished orders: a
pendingorder may still be updated even if it's old (say, a stuck payment flow), so it must not be moved. The delete checks the status once more as well. - Cancellable: when the
contextis cancelled (for example, the service is shutting down), finish the current batch and stop.
The SQL the archive job runs:
-- Live.Batch: keyset pagination, oldest first by (created_at, id)
SELECT id, user_id, total, status, created_at
FROM orders
WHERE created_at < $1
AND status IN ('paid', 'cancelled')
AND (created_at, id) > ($2, $3)
ORDER BY created_at, id
LIMIT $4;
-- Archive.Upsert (archive database): skip rows that already exist, so reruns are safe
INSERT INTO orders_archive (id, user_id, total, status, paid_at, created_at)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (id) DO NOTHING;
-- Archive.Count (archive database): confirm the whole batch was written
SELECT count(*) FROM orders_archive WHERE id = ANY($1);
-- Live.Delete: check the status again and delete only finished orders
DELETE FROM orders WHERE id = ANY($1) AND status IN ('paid', 'cancelled');(created_at, id) > ($2, $3) is a PostgreSQL row comparison, equivalent to “created_at is greater, or created_at is equal and id is greater”; with an index on (created_at, id) it jumps straight to the end of the previous batch. Using created_at alone as the cursor is wrong: of two orders created in the same millisecond, one can end the previous batch and the other gets skipped. = ANY($1) takes an array parameter; with a driver such as pgx you can pass a Go []int64 directly.
The Go side abstracts “how to access the database” into two small interfaces, so the archiving flow itself depends on no driver, and the tests need no real database. internal/archive/archive.go:
// Package archive moves finished orders past their retention period from the live database to an archive database, in batches.
package archive
import (
"context"
"errors"
"fmt"
"time"
)
// Order is one order to move (in practice its order_items move with it).
type Order struct {
ID int64
UserID int64
Total int64 // in cents
Status string
CreatedAt time.Time
}
// Cursor is the keyset pagination position: the (created_at, id) of the previous batch's last row.
type Cursor struct {
CreatedAt time.Time
ID int64
}
// Live is the live orders database.
type Live interface {
// Batch returns, oldest first by (created_at, id), at most limit finished orders
// that sort after `after` and were created before `before`.
Batch(ctx context.Context, before time.Time, after Cursor, limit int) ([]Order, error)
// Delete deletes those of the given IDs that are still finished and returns how many it deleted.
Delete(ctx context.Context, ids []int64) (int64, error)
}
// Archive is the archive database.
type Archive interface {
// Upsert writes orders and skips IDs that already exist, so rerunning a batch is safe.
Upsert(ctx context.Context, orders []Order) error
// Count returns how many of the given IDs the archive database already has.
Count(ctx context.Context, ids []int64) (int, error)
}
// ErrVerify means the archive row count did not match, so the batch is not deleted.
var ErrVerify = errors.New("archive: verification failed")
// Archiver handles each batch as: read → write to archive → verify → only then delete from live.
type Archiver struct {
Live Live
Archive Archive
BatchSize int
Pause time.Duration // rest between batches to let replication and disk I/O catch up
}
// Stats is the result of one run.
type Stats struct {
Batches int
Moved int64
}
// Run moves finished orders created before `before` until done, on error or when ctx is cancelled.
// Stopping midway is fine: a half-moved batch is written again (skipped) and deleted on the next run.
func (a *Archiver) Run(ctx context.Context, before time.Time) (Stats, error) {
var st Stats
var cur Cursor
for {
batch, err := a.Live.Batch(ctx, before, cur, a.BatchSize)
if err != nil {
return st, fmt.Errorf("archive: read batch: %w", err)
}
if len(batch) == 0 {
return st, nil
}
ids := make([]int64, len(batch))
for i, o := range batch {
ids[i] = o.ID
}
if err := a.Archive.Upsert(ctx, batch); err != nil {
return st, fmt.Errorf("archive: write batch: %w", err)
}
n, err := a.Archive.Count(ctx, ids)
if err != nil {
return st, fmt.Errorf("archive: verify batch: %w", err)
}
if n != len(ids) {
return st, fmt.Errorf("%w: %d of %d orders found in archive", ErrVerify, n, len(ids))
}
deleted, err := a.Live.Delete(ctx, ids)
if err != nil {
return st, fmt.Errorf("archive: delete batch: %w", err)
}
st.Batches++
st.Moved += deleted
last := batch[len(batch)-1]
cur = Cursor{CreatedAt: last.CreatedAt, ID: last.ID}
if len(batch) < a.BatchSize {
return st, nil
}
select {
case <-ctx.Done():
return st, ctx.Err()
case <-time.After(a.Pause):
}
}
}The key points of this code:
- Each batch goes “read → write to archive → verify → delete”. Any failing step returns an error straight away and never moves on to the next. The worst case is an interruption after writing to the archive and before deleting: on the next run the same batch is read again,
Upsertskips what already exists,Countpasses, andDeleteremoves them — the same result as if nothing had been interrupted. - Verification only compares counts. That is the bare minimum; for especially important data you can also compare a checksum per batch (step 7 has an example).
- The
Pausebetween batches lets read replicas catch up and gives live requests room to breathe. Tune the batch size and pause by replication lag: if you see lag rising, make them smaller. order_itemsmust move too. The example only movesorders; in practice the archive'sUpsertwrites the order and its items in one transaction, and the liveDeletedeletes the items and then the order in one transaction.
If the archive table and the live table are in the same database (for example, you only want to move old data to another tablespace), PostgreSQL can do the move in a single statement, with the delete and the insert in one transaction and no separate verification:
WITH moved AS (
DELETE FROM orders
WHERE id IN (
SELECT id FROM orders
WHERE created_at < '2026-11-01 00:00:00+00'
AND status IN ('paid', 'cancelled')
ORDER BY created_at, id
LIMIT 1000
)
RETURNING id, user_id, total, status, paid_at, created_at
)
INSERT INTO orders_archive (id, user_id, total, status, paid_at, created_at)
SELECT id, user_id, total, status, paid_at, created_at FROM moved
ON CONFLICT (id) DO NOTHING;The tests use an in-memory fake database that implements both Live and Archive and can simulate “a crash halfway through a move” or “the archive silently dropping a row”. internal/archive/archive_test.go:
package archive
import (
"context"
"errors"
"slices"
"sync"
"testing"
"time"
)
// store plays both the live and the archive database, all in memory.
type store struct {
mu sync.Mutex
live map[int64]Order
archived map[int64]Order
failDeletes int // how many upcoming Delete calls fail (simulates a crash mid-move)
dropOne bool // Upsert silently drops one row (simulates a faulty archive write)
onDelete func() // called after every successful Delete
}
func newStore(orders ...Order) *store {
s := &store{live: map[int64]Order{}, archived: map[int64]Order{}}
for _, o := range orders {
s.live[o.ID] = o
}
return s
}
func finished(o Order) bool { return o.Status == "paid" || o.Status == "cancelled" }
func (s *store) Batch(ctx context.Context, before time.Time, after Cursor, limit int) ([]Order, error) {
if err := ctx.Err(); err != nil {
return nil, err
}
s.mu.Lock()
defer s.mu.Unlock()
var out []Order
for _, o := range s.live {
afterCursor := o.CreatedAt.After(after.CreatedAt) ||
(o.CreatedAt.Equal(after.CreatedAt) && o.ID > after.ID)
if finished(o) && o.CreatedAt.Before(before) && afterCursor {
out = append(out, o)
}
}
slices.SortFunc(out, func(a, b Order) int {
if c := a.CreatedAt.Compare(b.CreatedAt); c != 0 {
return c
}
return int(a.ID - b.ID)
})
return out[:min(limit, len(out))], nil
}
func (s *store) Delete(ctx context.Context, ids []int64) (int64, error) {
s.mu.Lock()
if s.failDeletes > 0 {
s.failDeletes--
s.mu.Unlock()
return 0, errors.New("connection reset")
}
var n int64
for _, id := range ids {
if o, ok := s.live[id]; ok && finished(o) {
delete(s.live, id)
n++
}
}
hook := s.onDelete
s.mu.Unlock()
if hook != nil {
hook()
}
return n, nil
}
func (s *store) Upsert(ctx context.Context, orders []Order) error {
s.mu.Lock()
defer s.mu.Unlock()
for i, o := range orders {
if s.dropOne && i == 0 {
continue
}
if _, ok := s.archived[o.ID]; !ok {
s.archived[o.ID] = o
}
}
return nil
}
func (s *store) Count(ctx context.Context, ids []int64) (int, error) {
s.mu.Lock()
defer s.mu.Unlock()
n := 0
for _, id := range ids {
if _, ok := s.archived[id]; ok {
n++
}
}
return n, nil
}
var (
cutoff = time.Date(2026, 5, 1, 0, 0, 0, 0, time.UTC)
old = cutoff.AddDate(0, -6, 0)
)
// fixture: 25 old finished orders, 2 old pending ones and 3 new ones.
// Every two orders share a created_at, to check the keyset cursor skips none created at the same time.
func fixture() *store {
var orders []Order
for i := range 25 {
status := "paid"
if i%5 == 0 {
status = "cancelled"
}
orders = append(orders, Order{ID: int64(100 + i), UserID: 42, Total: 1999, Status: status,
CreatedAt: old.Add(time.Duration(i/2) * time.Hour)})
}
orders = append(orders,
Order{ID: 200, Status: "pending", CreatedAt: old},
Order{ID: 201, Status: "pending", CreatedAt: old.Add(time.Hour)},
Order{ID: 300, Status: "paid", CreatedAt: cutoff},
Order{ID: 301, Status: "paid", CreatedAt: cutoff.Add(time.Hour)},
Order{ID: 302, Status: "pending", CreatedAt: cutoff.Add(2 * time.Hour)},
)
return newStore(orders...)
}
func TestRunMovesOnlyOldFinishedOrders(t *testing.T) {
s := fixture()
a := &Archiver{Live: s, Archive: s, BatchSize: 10}
st, err := a.Run(t.Context(), cutoff)
if err != nil {
t.Fatal(err)
}
if st.Moved != 25 || st.Batches != 3 {
t.Fatalf("stats = %+v, want 25 moved in 3 batches", st)
}
if len(s.archived) != 25 || len(s.live) != 5 {
t.Fatalf("archive %d, live %d", len(s.archived), len(s.live))
}
for _, id := range []int64{200, 201, 300, 301, 302} {
if _, ok := s.live[id]; !ok {
t.Errorf("order %d must stay live", id)
}
}
}
func TestRerunAfterCrashIsSafe(t *testing.T) {
s := fixture()
s.failDeletes = 1 // "crash" after the first batch is archived but before it is deleted
a := &Archiver{Live: s, Archive: s, BatchSize: 10}
if _, err := a.Run(t.Context(), cutoff); err == nil {
t.Fatal("expected the first run to fail")
}
if len(s.archived) != 10 || len(s.live) != 30 {
t.Fatalf("after crash: archive %d, live %d", len(s.archived), len(s.live))
}
st, err := a.Run(t.Context(), cutoff)
if err != nil {
t.Fatal(err)
}
if st.Moved != 25 || len(s.archived) != 25 || len(s.live) != 5 {
t.Fatalf("after rerun: %+v, archive %d, live %d", st, len(s.archived), len(s.live))
}
}
func TestVerifyFailureDeletesNothing(t *testing.T) {
s := fixture()
s.dropOne = true
a := &Archiver{Live: s, Archive: s, BatchSize: 10}
_, err := a.Run(t.Context(), cutoff)
if !errors.Is(err, ErrVerify) {
t.Fatalf("got %v, want ErrVerify", err)
}
if len(s.live) != 30 {
t.Fatalf("live has %d orders, want all 30", len(s.live))
}
}
func TestRunStopsWhenCancelled(t *testing.T) {
s := fixture()
ctx, cancel := context.WithCancel(t.Context())
s.onDelete = cancel // cancel once the first batch is deleted
a := &Archiver{Live: s, Archive: s, BatchSize: 10, Pause: time.Hour}
done := make(chan struct{})
var st Stats
var err error
go func() {
st, err = a.Run(ctx, cutoff)
close(done)
}()
select {
case <-done:
case <-time.After(5 * time.Second):
t.Fatal("Run did not stop after cancel")
}
if !errors.Is(err, context.Canceled) || st.Moved != 10 {
t.Fatalf("got %+v, %v", st, err)
}
}fixture deliberately gives every two orders the same created_at: if the cursor only remembered the time, the second would be skipped, and TestRunMovesOnlyOldFinishedOrders would catch it. TestRerunAfterCrashIsSafe checks the most important property — rerunning after an interruption gives the same result as one uninterrupted run, with nothing lost and nothing duplicated.
Approach 3: Cold storage — object storage and a query engine
Leave the archive database alone for a few more years and it becomes another big database that almost nobody reads. Older data can go one tier further down: export it every month to a columnar file format such as Parquet, store it in object storage (an S3-compatible storage service), and partition it by paths such as year=2024/month=03/. Columnar formats compress well and only read the columns you ask for.
Queries on cold data no longer go through the live API; they go to a query engine that can read Parquet directly (for example DuckDB, Trino, or a similar service from a cloud provider), for reports, audits or the occasional customer service lookup. The export follows the same spirit as the archive job: export the whole month → check row counts and amount totals → only delete the source once everything matches, and record each export's file location, row count and checksum.
Tip: How long data “must be kept” and how long it “may be kept” are legal and business questions, not technical ones. Accounting records usually have a statutory retention period; personal data may be subject to deletion requests, and files in cold storage must also be findable and handled. Settle the retention policy for orders with your legal team when you design the hot/cold tiers, and write it down.
Add a cache in front of the hot data
After separating hot and cold data, the live primary holds only hot data, and caching works even better: hot data is what a cache is for in the first place. Order caching continues the cache-aside pattern from “From Monolith to Microservices”, with a few key points:
- Cache only hot data. Don't put order history or archived data into the cache, or the cold data will push the hot data out.
- Add random jitter to the TTL, so large numbers of keys don't expire in the same second.
- Protect against stampedes: the moment a popular key expires, hundreds of requests go to the database at once. Use
singleflightso that each key is fetched from the source only once; the details are in the next article, “Concurrency Pitfalls: From Data Races to Overselling”, under “Cache stampedes and the thundering herd”. - Delete the cache entry after a write, rather than updating it, so that concurrent writes can't leave the cache stuck on an old value.
The API has to change too
Hot/cold separation isn't only a database matter; the API also has to let users and the front end know that the data now comes in two kinds, and three things need to change with it.
Lists query only hot data by default. GET /orders returns the last 90 days of orders; older orders go through a separate, explicit endpoint such as GET /orders/archived, whose response can be slower, and the front end can show “searching order history”.
Single-order lookups decide where to look from the ID. The order ID from step 4 embeds the creation time, so shard.TimeOf(orderID) tells you how old an order is without querying any table:
// Excerpt: internal/order/service.go
// Old orders are looked up in the archive first; orders just past the cutoff that the archive job hasn't moved yet are found in the live database.
func (s *Service) Get(ctx context.Context, userID, orderID int64) (Order, error) {
if shard.TimeOf(orderID).Before(s.archiveCutoff()) {
o, err := s.archive.Get(ctx, userID, orderID)
if !errors.Is(err, ErrNotFound) {
return o, err
}
}
return s.live.Get(ctx, userID, orderID)
}The archive job could move this order right between the two lookups, so that neither side finds it; when live.Get also returns not found, checking the archive once more closes that gap.
Paginate with a keyset, not OFFSET. For the same reason as the archive job: the deeper a user pages, the slower OFFSET gets. The API returns an opaque cursor (the last row's created_at and id, encoded), and the next page is queried with it:
-- The next page of "my orders": the 20 orders older than the cursor
SELECT id, total, status, created_at
FROM orders
WHERE user_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 20;With an index on (user_id, created_at DESC, id DESC), every page costs the same. The price of keyset pagination is that you can't “jump straight to page 37”, but scrolling interfaces such as order lists and feeds never needed that anyway.
Step 3: Vertical splits — by business domain, or by column
Splitting databases by business domain
A vertical split means “putting different tables in different databases”. In a microservices setting it is really each service owning its own database: orders and order_items belong to the order service; payments and refunds to the payment service; stock and reservations to the inventory service. “From Monolith to Microservices” already did this at the 1M stage; here we look at its effects and costs again from the data's point of view.
Figure: A vertical split leaves fewer tables in each database, but each table holds as much data as before; horizontal sharding spreads one table across several shards with the same structure, each holding only some of the users.
The benefits of a vertical split:
- Resource isolation. The
paymentreconciliation batch no longer slows down order placement inorder; each database can be sized for its own load. - Clear ownership. Only one service can write to a database, and schema changes need only one team's decision.
- Far simpler than sharding. Inside each database you still have a complete relational database: transactions, joins and unique constraints all work as usual.
The cost is that joins and transactions across databases disappear. “Place the order and deduct stock at the same time” can no longer be one database transaction; it becomes a stock reservation plus a Saga, with events published reliably through an Outbox (“Distributed Transactions: Sagas and the Outbox Pattern”). “Show the payment status in the order list” can no longer join payments; either call the payment service, or keep a copy of the payment status on the order side, updated by events (“Message Queues and Event-Driven Design”).
The limit of a vertical split is just as clear: it reduces “how many kinds of tables one database has”, not “how big a table is”. After the split, orders in the order database is exactly as big as before; if the problem is that table itself, you need hot/cold separation or horizontal sharding.
Splitting a table by column
Another kind of vertical split happens within one table: move wide, rarely used or very large columns to another table, and keep only the narrow columns that are queried and updated often in the main table. A common case for orders is the shipping address, notes and invoice details: the list page never needs them; only the order detail page does.
-- Keep the main orders table narrow; columns only the detail page needs go to order_details, one to one
CREATE TABLE order_details (
order_id BIGINT PRIMARY KEY, -- matches orders.id
shipping_address JSONB NOT NULL,
note TEXT,
invoice_key TEXT -- where the invoice PDF is in object storage; files are not stored in the database
);The effect is that each row of the main table gets smaller, the same memory holds more rows, scans and indexes get faster, and the frequently updated status no longer drags a long list of unrelated columns along. PostgreSQL automatically moves very large values out of line into TOAST, so the impact of “a column is large” on its own is smaller than you might think; the main reason to split is different access patterns: one group of columns is used by almost every request, the other by almost none. Files (images, PDFs) should never go into the database in any case; put them in object storage and store only their location.
Step 4: Horizontal sharding — shard keys, routing and globally unique IDs
Systems that still aren't enough after all this usually have the problem that “the hot data itself is too big and there are too many writes”. Horizontal sharding spreads the data of one table across several databases (shards) by a shard key; every shard has the same structure and holds part of the data.
Choosing the shard key
The shard key is the most important decision in a sharding design, and the hardest to change. The method: list your most important and most frequent queries and transactions, and see which key makes almost all of them land on a single shard.
| Shard key | Operations on a single shard | Operations that cross shards | Hot-spot risk |
|---|---|---|---|
user_id | Placing an order (one transaction), my orders, a single order (when the ID embeds the shard) | Site-wide queries by merchant, by product, by time | Low; a few heavy buyers have limited impact |
order_id | A single order | My orders (must ask every shard) | Lowest, writes spread most evenly |
merchant_id | Every query in the merchant back office | My orders (one user buys from many merchants) | High; a big merchant's sale lands on one shard |
For an online shop's orders, user_id is usually the best choice: placing an order involves only one user, “my orders” queries one shard, and merchant back-office queries are far fewer than buyers' queries and can be handled with the index tables of step 5. Once the shard key is chosen, all of a user's related data must be sharded by the same key: order_items and order_details follow their order, so an order's data never ends up scattered across shards.
When choosing the key, also ask: can the key's value change? (A user ID never changes; which merchant something belongs to might.) Is its distribution even enough? Is there a single value so big that one shard can't hold it? (An enterprise account placing a hundred thousand orders a day may be too big even on a shard of its own.)
Choosing a routing scheme
With the shard key chosen, you still have to decide “which shard a given key value goes to”:
| Routing scheme | How it works | Advantages | Drawbacks |
|---|---|---|---|
| Hash modulo | hash(key) % N, where N is the number of databases | Simplest, even distribution | When N changes, almost all data has to move |
| Consistent hashing | Keys and nodes sit on a hash ring; a key belongs to the first node clockwise | Adding or removing a node moves only some data | Needs virtual nodes to be even; the moved range is hard to control |
| Range | Split by key range, e.g. IDs 0–9,999,999 on shard 1 | Range queries are easy | Increasing keys send all writes to the last shard |
| Directory (lookup table) | A table records which shard each key is on | Most flexible; any key can be moved on its own | Every route needs a lookup; the directory itself must be highly available |
| Fixed logical shards + map | hash(key) % 1024 gives a logical shard, then a small map is consulted | Moves happen per logical shard at a controlled cost | The number of logical shards has to be fixed from the start |
The last one is a compromise between the others, and it's what “From Monolith to Microservices” used: the hash targets not the physical databases but a fixed number of logical shards (say 1024), and a small map of just 1024 rows maps logical shards to physical databases.
Figure: A user_id hashes to one of 1024 logical shards, and the map points each logical shard to a physical database; scaling out only changes the map. The lowest 10 bits of an order ID are its logical shard, so the order ID alone is enough to route.
How do you choose the number of logical shards? A few principles:
- Far more than the number of physical databases you might ever need. 1024 logical shards can take you from 2 hosts to several hundred, and each move touches only a small fraction of them.
- A power of two, so it fits into bits of the ID (
id & 1023). - A logical shard should ideally be a unit you can move as a whole. A common approach is to give each logical shard its own schema in the physical database (for example
shard_0042.orders), or at least to have a column in every table that can filter by logical shard; a move can then copy and verify one logical shard at a time. 1024 schemas spread over 2 databases at the start looks like a lot, but each schema is small, and in return every future expansion is just “move a few schemas and change the map”.
Globally unique order IDs
After sharding, the database's auto-increment sequence can no longer be used: every shard would start counting from 1 and the IDs would collide. There are several common ways to get globally unique IDs:
| Approach | Properties | What to watch out for |
|---|---|---|
| Per-shard sequences with offset and step | Shard 1 produces 1, 5, 9…, shard 2 produces 2, 6, 10… | Hard to adjust when the number of shards changes |
| UUID v4 | Fully random, no coordination | 128 bits, unordered; inserts scatter across the B-tree index |
| UUID v7, ULID | Time first, randomness after; roughly ordered by time | 128 bits; carries no shard information |
| Snowflake-style (64 bits) | Time + machine ID + sequence; roughly increasing, fits in a BIGINT | Depends on the clock; machine IDs must never collide |
Here we follow the Snowflake idea, but put the logical shard in the lowest 10 bits: an order ID consists of the time in milliseconds (41 bits), the generator ID (6 bits), a sequence within the millisecond (6 bits) and the logical shard (10 bits). Such IDs have three advantages: they increase roughly over time, so inserts into the B-tree index concentrate at its end; id & 1023 is the logical shard, so a request that only has the order ID (a payment callback, a customer service lookup) can route directly without a lookup; and the high bits are the time, so TimeOf can tell how old an order is, which the hot/cold routing above uses.
The clock is the biggest risk with IDs like these. A server's clock can be stepped back by NTP, and if the generator doesn't handle that, it could issue duplicate IDs in “the same millisecond”. The implementation below waits for the clock to catch up after a small step back, and returns an error after a large one; it never issues an ID that might be a duplicate. The generator ID must be unique among all replicas running at the same time, for example the ordinal of a Kubernetes StatefulSet, or an ID leased from a coordination service at startup.
Tip: A 64-bit ID exceeds the range of integers JavaScript can represent exactly (2^53). Written as a number in JSON, the ID the browser reads gets rounded to a different number. Always output order IDs as strings in the API. Also, IDs that increase over time reveal “how many orders you take per day”; if that's sensitive, you can offer a separate public ID that doesn't reveal order.
The routing package in Go
The routing package lives in internal/shard: computing logical shards, the map, a Router that finds connection pools through the map, and IDGen for generating IDs. internal/shard/shard.go:
// Package shard routes orders to databases:
// user_id → logical shard (a fixed 1024) → map → connection pool of a physical database.
package shard
import (
"database/sql"
"encoding/binary"
"errors"
"fmt"
"hash/fnv"
"sync/atomic"
)
// LogicalShards is fixed from day one: scaling out only moves logical shards and changes the map.
const LogicalShards = 1024
// shardBits is the number of low bits in an order ID that hold the logical shard: 2^10 = 1024.
const shardBits = 10
// ErrUnknownShard means a shard number is out of range.
var ErrUnknownShard = errors.New("shard: unknown shard")
// Of returns the logical shard that a user's data belongs to.
func Of(userID int64) int {
var b [8]byte
binary.BigEndian.PutUint64(b[:], uint64(userID))
h := fnv.New32a()
h.Write(b[:])
return int(h.Sum32() % LogicalShards)
}
// OfOrder recovers the logical shard from the low 10 bits of an order ID, with no lookup.
func OfOrder(orderID int64) int {
return int(orderID & (LogicalShards - 1))
}
// Range says logical shards [From, To) live on the physical database named DB.
type Range struct {
From, To int
DB string
}
// Map is one version of the map: each logical shard points to one physical database.
type Map struct {
Version int
owner [LogicalShards]string
}
// NewMap checks that ranges cover every logical shard exactly once: no overlaps, no gaps.
func NewMap(version int, ranges []Range) (*Map, error) {
m := &Map{Version: version}
for _, r := range ranges {
if r.From < 0 || r.To > LogicalShards || r.From >= r.To || r.DB == "" {
return nil, fmt.Errorf("shard: invalid range %+v", r)
}
for s := r.From; s < r.To; s++ {
if m.owner[s] != "" {
return nil, fmt.Errorf("shard: logical shard %d assigned twice", s)
}
m.owner[s] = r.DB
}
}
for s, db := range m.owner {
if db == "" {
return nil, fmt.Errorf("shard: logical shard %d has no database", s)
}
}
return m, nil
}
// Owner returns the name of the physical database that holds a logical shard.
func (m *Map) Owner(shardID int) string { return m.owner[shardID] }
// Router finds connection pools through the current map; the whole map can be swapped at run time when shards move.
type Router struct {
current atomic.Pointer[Map]
pools map[string]*sql.DB // physical database name → pool, built at startup and read-only afterwards
}
// NewRouter creates a Router from the first map and the pools of every physical database.
func NewRouter(m *Map, pools map[string]*sql.DB) (*Router, error) {
r := &Router{pools: pools}
if err := r.Swap(m); err != nil {
return nil, err
}
return r, nil
}
// Swap installs a new version of the map. Every database it names must have a pool, and versions only move forward.
func (r *Router) Swap(m *Map) error {
for s := range LogicalShards {
if _, ok := r.pools[m.owner[s]]; !ok {
return fmt.Errorf("shard: no pool for database %q", m.owner[s])
}
}
for {
old := r.current.Load()
if old != nil && m.Version <= old.Version {
return fmt.Errorf("shard: map version %d is not newer than %d", m.Version, old.Version)
}
if r.current.CompareAndSwap(old, m) {
return nil
}
}
}
// Version returns the map version in use, handy for logs and metrics.
func (r *Router) Version() int { return r.current.Load().Version }
// DB returns the connection pool of the database that holds a logical shard.
func (r *Router) DB(shardID int) (*sql.DB, error) {
if shardID < 0 || shardID >= LogicalShards {
return nil, fmt.Errorf("%w: %d", ErrUnknownShard, shardID)
}
return r.pools[r.current.Load().owner[shardID]], nil
}
// ForUser returns the connection pool that holds a user's data.
func (r *Router) ForUser(userID int64) (*sql.DB, error) { return r.DB(Of(userID)) }
// ForOrder finds the connection pool from the order ID alone.
func (r *Router) ForOrder(orderID int64) (*sql.DB, error) { return r.DB(OfOrder(orderID)) }internal/shard/id.go:
package shard
import (
"errors"
"fmt"
"sync"
"time"
)
// An order ID is a positive int64 whose 63 bits are, from high to low:
//
// [41 bits: milliseconds since epoch][6 bits: generator][6 bits: sequence within the ms][10 bits: logical shard]
//
// Time is in the high bits, so IDs roughly increase over time; the shard is in the low bits, so OfOrder only takes the low 10 bits.
const (
seqBits = 6
workerBits = 6
seqShift = shardBits
workerShift = shardBits + seqBits
timeShift = shardBits + seqBits + workerBits
maxSeq = 1<<seqBits - 1
MaxWorker = 1<<workerBits - 1
maxMillis = 1<<41 - 1
// maxBackward is how far the clock may step back and still be waited out; beyond that we return an error rather than risk a duplicate ID.
maxBackward = 10 * time.Millisecond
)
// epoch is when IDs start counting; 41 bits of milliseconds last about 69 years. It must never change once live.
var epoch = time.Date(2026, 1, 1, 0, 0, 0, 0, time.UTC)
// ErrClockBackwards means the system clock stepped back too far (for example, adjusted by hand).
var ErrClockBackwards = errors.New("shard: clock moved backwards")
// IDGen generates order IDs with the logical shard embedded. Every replica running at the same time needs a different worker.
type IDGen struct {
mu sync.Mutex
worker int64
lastMS int64
seq int64
now func() time.Time // replaced by a fake clock in tests
sleep func(time.Duration) // likewise
}
// NewIDGen creates a generator; worker must be in 0..MaxWorker and unique among replicas.
func NewIDGen(worker int) (*IDGen, error) {
if worker < 0 || worker > MaxWorker {
return nil, fmt.Errorf("shard: worker %d out of range 0..%d", worker, MaxWorker)
}
return &IDGen{worker: int64(worker), lastMS: -1, now: time.Now, sleep: time.Sleep}, nil
}
// Next generates a new order ID for logical shard shardID.
func (g *IDGen) Next(shardID int) (int64, error) {
if shardID < 0 || shardID >= LogicalShards {
return 0, fmt.Errorf("%w: %d", ErrUnknownShard, shardID)
}
g.mu.Lock()
defer g.mu.Unlock()
for {
ms := g.now().Sub(epoch).Milliseconds()
switch {
case ms < 0 || ms > maxMillis:
return 0, fmt.Errorf("shard: time %d ms since epoch out of range", ms)
case ms > g.lastMS:
g.lastMS, g.seq = ms, 0
case ms == g.lastMS && g.seq < maxSeq:
g.seq++
case ms == g.lastMS:
// This millisecond's sequence is used up; wait for the next one.
g.sleep(time.Millisecond)
continue
default:
// The clock stepped back (for example, an NTP correction): wait for a small step, refuse a large one.
back := time.Duration(g.lastMS-ms) * time.Millisecond
if back > maxBackward {
return 0, fmt.Errorf("%w by %v", ErrClockBackwards, back)
}
g.sleep(back)
continue
}
return g.lastMS<<timeShift | g.worker<<workerShift | g.seq<<seqShift | int64(shardID), nil
}
}
// TimeOf recovers the creation time (to the millisecond) from an order ID, which tells whether the order may be archived.
func TimeOf(orderID int64) time.Time {
return epoch.Add(time.Duration(orderID>>timeShift) * time.Millisecond)
}Compared with the sketch in “From Monolith to Microservices”, this version adds what production use needs:
- The map is checked for completeness when it is built: every logical shard must have exactly one physical database. A bad configuration fails at startup, not when some user places an order.
- The map has a version number and can be replaced atomically as a whole at run time.
atomic.Pointermakes reads lock-free, andCompareAndSwapmakes sure two concurrent swaps can't make the version go backwards. When shards move, a coordinator publishes the new version; once every replica has switched, new requests go to the new database. - All connection pools are built at startup. Routing only looks up a map and never opens connections on the request path; a new map that names a database without a pool is rejected.
IDGenprotects its sequence with a mutex: at most 64 IDs per millisecond per generator, after which it waits for the next millisecond;nowandsleepcan be swapped for a fake clock in tests.
The tests are all pure logic and need no database. internal/shard/shard_test.go:
package shard
import (
"database/sql"
"errors"
"sync"
"testing"
"time"
)
func fourDBs(t *testing.T) *Map {
t.Helper()
m, err := NewMap(1, []Range{
{0, 256, "orders-db-1"},
{256, 512, "orders-db-2"},
{512, 768, "orders-db-3"},
{768, 1024, "orders-db-4"},
})
if err != nil {
t.Fatal(err)
}
return m
}
// The tests need no real connections: they only check that the same *sql.DB comes back.
func pools(names ...string) map[string]*sql.DB {
p := map[string]*sql.DB{}
for _, n := range names {
p[n] = new(sql.DB)
}
return p
}
func TestUsersSpreadEvenly(t *testing.T) {
m := fourDBs(t)
perDB := map[string]int{}
perShard := make([]int, LogicalShards)
for id := int64(1); id <= 100_000; id++ {
s := Of(id)
perShard[s]++
perDB[m.Owner(s)]++
}
for s, n := range perShard {
if n == 0 {
t.Errorf("logical shard %d got no users", s)
}
}
for db, n := range perDB {
if n < 23_750 || n > 26_250 { // expect 25,000, allow ±5%
t.Errorf("%s got %d users", db, n)
}
}
}
func TestNewMapRejectsBadRanges(t *testing.T) {
tests := map[string][]Range{
"gap": {{0, 512, "a"}, {513, 1024, "b"}},
"overlap": {{0, 600, "a"}, {512, 1024, "b"}},
"out of range": {{0, 1025, "a"}},
"empty name": {{0, 1024, ""}},
}
for name, ranges := range tests {
if _, err := NewMap(1, ranges); err == nil {
t.Errorf("%s: expected an error", name)
}
}
}
func TestRouterFollowsSwappedMap(t *testing.T) {
p := pools("orders-db-1", "orders-db-2", "orders-db-3", "orders-db-4", "orders-db-5")
r, err := NewRouter(fourDBs(t), p)
if err != nil {
t.Fatal(err)
}
var user int64 = 42
s := Of(user)
before, _ := r.ForUser(user)
if before != p[fourDBs(t).Owner(s)] {
t.Fatal("ForUser returned the wrong pool")
}
// Move the logical shard holding user 42 to orders-db-5.
var ranges []Range
if s > 0 {
ranges = append(ranges, Range{0, s, "orders-db-1"})
}
ranges = append(ranges, Range{s, s + 1, "orders-db-5"})
if s+1 < LogicalShards {
ranges = append(ranges, Range{s + 1, LogicalShards, "orders-db-2"})
}
v2, err := NewMap(2, ranges)
if err != nil {
t.Fatal(err)
}
if err := r.Swap(v2); err != nil {
t.Fatal(err)
}
after, _ := r.ForUser(user)
if after != p["orders-db-5"] || r.Version() != 2 {
t.Fatalf("after swap: got version %d", r.Version())
}
if err := r.Swap(fourDBs(t)); err == nil {
t.Error("an older map version must be rejected")
}
missing, _ := NewMap(3, []Range{{0, 1024, "orders-db-9"}})
if err := r.Swap(missing); err == nil {
t.Error("a map naming a database without a pool must be rejected")
}
if _, err := r.DB(LogicalShards); !errors.Is(err, ErrUnknownShard) {
t.Errorf("DB(1024): got %v", err)
}
}
func TestIDCarriesShardAndTime(t *testing.T) {
g, err := NewIDGen(3)
if err != nil {
t.Fatal(err)
}
r, _ := NewRouter(fourDBs(t), pools("orders-db-1", "orders-db-2", "orders-db-3", "orders-db-4"))
var user int64 = 42
start := time.Now().Truncate(time.Millisecond)
id, err := g.Next(Of(user))
if err != nil {
t.Fatal(err)
}
if id <= 0 {
t.Fatalf("id %d must be positive", id)
}
if OfOrder(id) != Of(user) {
t.Fatalf("OfOrder(%d) = %d, want %d", id, OfOrder(id), Of(user))
}
byOrder, _ := r.ForOrder(id)
byUser, _ := r.ForUser(user)
if byOrder != byUser {
t.Fatal("an order must live on its user's database")
}
if got := TimeOf(id); got.Before(start) || got.After(time.Now()) {
t.Fatalf("TimeOf = %v, want about %v", got, start)
}
}
func TestIDsAreUniqueAcrossGoroutines(t *testing.T) {
g, _ := NewIDGen(1)
const workers, each = 8, 2000
ids := make(chan int64, workers*each)
var wg sync.WaitGroup
for w := range workers {
wg.Go(func() {
for i := range each {
id, err := g.Next((w*each + i) % LogicalShards)
if err != nil {
t.Error(err)
return
}
ids <- id
}
})
}
wg.Wait()
close(ids)
seen := map[int64]bool{}
for id := range ids {
if seen[id] {
t.Fatalf("duplicate id %d", id)
}
seen[id] = true
}
if len(seen) != workers*each {
t.Fatalf("got %d ids", len(seen))
}
}
// fakeClock is only used in single-goroutine tests: sleep just moves the time forward.
type fakeClock struct{ t time.Time }
func (c *fakeClock) now() time.Time { return c.t }
func (c *fakeClock) sleep(d time.Duration) { c.t = c.t.Add(d) }
func (c *fakeClock) install(g *IDGen) { g.now, g.sleep = c.now, c.sleep }
func (c *fakeClock) back(d time.Duration) { c.t = c.t.Add(-d) }
func TestSequenceOverflowWaitsForNextMillisecond(t *testing.T) {
c := &fakeClock{t: epoch.Add(time.Hour)}
g, _ := NewIDGen(0)
c.install(g)
var last int64
for i := range maxSeq + 2 { // one more than fits: the 65th must wait for the next millisecond
id, err := g.Next(7)
if err != nil {
t.Fatal(err)
}
if id <= last {
t.Fatalf("id %d not increasing (call %d)", id, i)
}
last = id
}
if got := TimeOf(last); !got.Equal(epoch.Add(time.Hour + time.Millisecond)) {
t.Fatalf("last id time = %v", got)
}
}
func TestClockBackwards(t *testing.T) {
c := &fakeClock{t: epoch.Add(time.Hour)}
g, _ := NewIDGen(0)
c.install(g)
first, _ := g.Next(1)
c.back(5 * time.Millisecond) // small step back: wait and carry on
second, err := g.Next(1)
if err != nil || second <= first {
t.Fatalf("small step back: id %d after %d, err %v", second, first, err)
}
c.back(time.Second) // large step back: refuse
if _, err := g.Next(1); !errors.Is(err, ErrClockBackwards) {
t.Fatalf("big step back: got %v", err)
}
}Run them with go test -race ./...: TestIDsAreUniqueAcrossGoroutines has 8 goroutines request IDs at the same time, checking uniqueness and data races together; the two fake-clock tests reproduce “sequence exhausted” and “clock stepped back”, two situations that are rare in production but produce duplicate IDs the moment they happen.
Step 5: What breaks after sharding
Sharding turns one database into several, and much of what the database used to do for you now has to be done by the application. Below are the most common items and how to handle each:
| What used to be a given | After sharding | How to handle it |
|---|---|---|
| Queries without the shard key | Must ask every shard (scatter-gather) | Index tables or a search engine, maintained by CDC or events |
| Pagination and sorting across all data | Each shard sorts its own part, which must be merged | Limit the depth, use keysets, or hand it to a read model |
Aggregates such as COUNT and SUM | One per shard, then summed; slow and inconsistent | A data warehouse, or counter tables maintained by events |
| Global unique constraints | Only unique within a shard | A uniqueness table keyed by that column, or the owning service enforces it |
| Cross-shard transactions | No single database transaction | Avoid them with the shard key; if unavoidable, use a Saga |
| Joins | Data on different shards can't be joined | Denormalise; copy small tables to every shard |
| Reporting | Querying the live shards directly drags them down | CDC into a data warehouse |
Queries without the shard key
With sharding by user_id, “all orders of a merchant” lacks the shard key. The most direct approach is to query every shard at once and merge the results (scatter-gather). With few shards and infrequent queries that's acceptable, but its cost grows with the number of shards, and if any one shard is slow, the whole query is slow.
A better approach is to maintain an index keyed by merchant for this query: CDC reads every shard's transaction log and turns order changes into events, and a consumer updates a table such as merchant_orders (merchant_id, created_at, order_id) (which can itself be sharded by merchant_id). A query first reads the index to get order IDs, and goes back to the shards by ID when it needs the full data — since the ID embeds the shard, this step needs no map lookup. Full-text search and reporting follow the same pattern, just with a search engine and a data warehouse as destinations.
Figure: Queries that carry a user_id go straight to one shard; other queries are answered by data that CDC maintains, going back to the shard by order_id when the latest state is needed.
All of this derived data lags the shards by a few seconds. The interface has to accept “a new order shows up in the merchant back office a few seconds later”; operations that need the latest state (for example, confirming before shipping that an order hasn't been cancelled) always read from the shard.
Pagination, sorting and aggregates across shards
After sharding, “the 20 newest orders site-wide” means taking the newest 20 from every shard, merging and sorting them in the application, and keeping the top 20. The first page is fine; page 100 needs 2,000 rows from every shard — the cost of deep pagination multiplies with the number of shards. The answers are to limit how deep users can page, switch to keyset cursors (one position per shard), or simply hand such queries to a read model.
Aggregates such as COUNT and SUM are similar: computing one per shard and adding them up works, but it's slow, and since each shard computes at a different moment, the result isn't a snapshot of a single instant. Operational reports go to the data warehouse; counts the interface must show live (such as “orders awaiting shipment”) come from a counter table maintained by events.
Unique constraints
After sharding, UNIQUE (email) can only guarantee “unique within one shard”. If users is sharded by user_id, users on two different shards can still register the same email. The answer is a separate uniqueness table keyed by that column: user_emails (email PRIMARY KEY, user_id), sharded by email; registration inserts into this table first and creates the user only if that succeeds. The two writes are on different shards, so design it as a retryable, compensatable flow (reserve first, then create, and release on failure). Rules such as “a coupon can only be used once” work the same way: key the coupon's usage records by the coupon code and let the service that owns them enforce it.
Cross-shard transactions
The best way to handle them is to avoid them through the shard key: shard by user_id, and an order, its items and its Outbox events are all on the same shard, so placing an order is still a local transaction. Operations that really must span shards (say, a transfer between two users) are handled like cross-service transactions: a Saga with compensations, with events published through an Outbox (“Distributed Transactions: Sagas and the Outbox Pattern”). Two-phase commit works in theory, but it makes every participating shard wait for the slowest one, and most online systems avoid it.
Joins and reporting
After sharding you can't join across shards. The usual answer is to denormalise: store the product name and unit price at the time of purchase directly on the order (which is the right thing to do anyway, since products may later be renamed or repriced); copy small, rarely changing reference data (currencies, regions, shipping rate bands) to every shard. Reporting always goes through CDC into a data warehouse; never run large queries on the live shards.
Step 6: Routing in middleware, or in the application
Shard routing can live in two places:
| Approach | Examples | Advantages | Costs |
|---|---|---|---|
| Routing inside the application (a library) | This article's internal/shard | No extra network hop or component; routing rules are fully readable and testable | Every service that accesses the data must use the same code; other languages need a rewrite |
| Database middleware (proxy or distributed layer) | Vitess (MySQL), Citus (a PostgreSQL extension), Apache ShardingSphere | The application talks to what looks like one database; tools support jobs such as resharding | One more component to run and keep highly available; behaviour and limits take time to learn; one more layer to debug |
A few neutral observations:
- Vitess is a sharding system designed for MySQL; applications connect through its gateway, and it handles routing and the resharding workflow.
- Citus is a PostgreSQL extension: once a table is declared distributed, a coordinator node dispatches queries to the worker nodes.
- Apache ShardingSphere offers modes that embed it as a library (in the Java ecosystem) or run it as a proxy.
- MySQL proxies such as ProxySQL mainly handle connection pooling, read/write splitting and rule-based routing; on their own they are not a complete sharding solution.
Each product differs in the SQL it supports, how it behaves with cross-shard queries and transactions, and how it reshards, and all of that changes between versions; before adopting one, go by its official documentation and test it with your own queries.
How to choose? If only one or two services need sharding and the sharding logic is simple (one key, one map), routing inside the application is usually the easiest to understand and debug — it's a package of a few hundred lines with tests. If many services in many languages need to reach the same sharded data, or you want mature tooling for resharding, the value of middleware outweighs its operating cost. Either way, choosing the shard key and handling cross-shard queries are still your design work; no tool decides them for you.
Step 7: Migrating without downtime
Whether you're splitting a single database into shards or later moving some logical shards to a new database, it has to happen while the system keeps serving. The process shares the spirit of the safe service extraction in “From Monolith to Microservices”: a series of small steps, each of which can be rolled back.
Figure: Seven steps from the full copy to cleaning up the old data. Before step 6 switches writes, a problem at any step just means stopping or switching reads back; after it, rollback relies on reverse sync.
Double writes, or CDC
There are two ways to keep the new shards up to date with changes in the old database:
- Double writes in the application: every write to the old database also writes a copy to the new one. It looks simple, but the two writes aren't in one transaction; any failure of either, or concurrent writes applied in a different order, leaves the two sides inconsistent, and nobody will know. “From Monolith to Microservices” already listed it as something to avoid.
- Full copy + CDC: take a snapshot at a consistent point in time and copy it to the new shards; at the same time, record that point's position in the transaction log, and from there use CDC to apply later changes to the new shards in order. PostgreSQL's logical replication follows exactly this model: creating a replication slot can give you a consistent snapshot and its starting position together. Changes are applied in log order, so there's none of the ordering trouble of double writes.
Verification
Once CDC has caught up, and before you switch any traffic, prove that both sides hold the same data. Compare row counts and checksums per range, where a range can be one month of one logical shard:
-- Run once on the old database and once on the new one, then compare the results
SELECT count(*) AS row_count,
md5(string_agg(id::text || ':' || user_id || ':' || total || ':' || status,
',' ORDER BY id)) AS checksum
FROM orders
WHERE (id & 1023) = $1 -- logical shard (the low 10 bits of the ID)
AND created_at >= $2
AND created_at < $3;Keep the ranges small, so that a mismatch can be located quickly and only that range copied again. Repeat verification while CDC keeps running: recent ranges may briefly disagree because changes are still in flight, which is fine as long as they converge soon after; older ranges must match exactly.
Switching reads, switching writes
- Shadow reads: read requests query both sides, respond with the old database's result, and log any mismatch.
- Switch reads by percentage: 1%, 10%, 100%; if there's a problem, set the percentage back to 0. Writes still go to the old database, and CDC keeps the new one updated.
- Switch writes: this is the only risky step. For the logical shards being moved, briefly freeze writes (a few seconds): write requests temporarily get a retryable error, CDC applies the last changes, and then a new version of the map is published so writes go to the new database. Because the switch happens per logical shard, each one affects only a small fraction of users, and everyone else notices nothing.
- Reverse sync: for a while after the switch, sync the new database's changes back to the old one. If the new database has problems, you can swap the map back to the previous version, and no data is lost.
- Clean up: after watching for at least one full business cycle, stop the reverse sync and delete the moved data from the old database.
Write down each step's completion criteria and rollback method in advance, for example “switch reads only after 24 hours of verification without a mismatch” or “if the error rate rises within 15 minutes of switching writes, swap back to the old map”. Retries during the write freeze rely on the write path being idempotent (“Idempotency and Distributed Locks”): the same request arriving once before and once after the switch never creates a duplicate order.
Resharding later
This is where logical shards pay off most clearly: when data keeps growing and you need to go from 4 hosts to 8, you don't recompute the hash of a single row; you just move half of each database's logical shards to the new databases, taking each logical shard through the process above, and finally update the map. Had you started with hash(user_id) % 4, changing to % 8 would relocate about half the data, with no way to switch over in batches — which is why the number of logical shards has to be fixed from the start, and far larger than the number of physical databases.
Step 8: Operations — monitoring, backups, schema changes and capacity planning
Monitor every shard
After sharding, “the database CPU” is no longer one number but N. Monitoring has to answer: is any shard especially busy?
- QPS, p99 latency, CPU, connections, disk usage and replication lag for every physical database, on the same chart for comparison. Label metrics by physical database; record the logical shard number in logs and traces only, not as a metric label, or 1024 values will blow up the number of series.
- Find the logical shards with the most traffic: count requests per logical shard in the routing layer and report the top few regularly.
- Handling hot shards: if a few logical shards are especially busy, move them to a database of their own; if a single key (say, one enormous enterprise account) is the busy one, logical shards can't help, and it needs a design of its own, such as subdividing that account's data or rate limiting it at the entrance.
Backups and restores
Each shard is backed up and restored to a point in time (PITR) on its own, just like a single database. The difference: there's no common consistent point in time across shards. Each shard's backup captures its own instant, and restoring all of them to “the same second” doesn't guarantee that data across shards is mutually consistent (a cross-shard Saga might be halfway through, for example). So regularly rehearse restoring a single shard, and have a reconciliation procedure ready for after a restore: use events or logs to complete cross-shard flows. Back up the map itself too; it is what makes all the data findable.
Schema changes
The same schema change has to be applied to every shard, and it can't happen on all of them at the same instant. The principles:
- Code must cope with both the old and the new schema at the same time. Use the order “expand, migrate, contract”: add the new column first (nullable), deploy code that reads and writes both shapes, backfill the data, and only then remove the old column. At any moment some shards may have changed and others not yet.
- Apply changes to each shard in turn with the same migration tool, record each shard's current version, and stop on failure rather than carrying on to the next shard.
- Change large tables online. Many PostgreSQL schema changes (adding a nullable column with no default, for example) only touch the system catalogue and finish quickly; build indexes with
CREATE INDEX CONCURRENTLY(“Adding Indexes Safely”). The MySQL ecosystem commonly uses online schema change tools such as gh-ost and pt-online-schema-change, which copy into a new table and then switch over.
Capacity planning: a worked example
Below is one calculation with assumed numbers, to show the way of thinking; real numbers must always come from measuring and load testing your own system.
Assumptions: 1 million orders a day; one order, with its items, Outbox event and indexes, takes about 2 KB; the peak orders per second are 10 times the daily average; each order produces about 10 write statements on the primary (order, items, Outbox, status updates and so on).
- Average orders per second: 1,000,000 ÷ 86,400 ≈ 12; the peak is about 120, which is about 1,200 write statements per second.
- New data per day: 1,000,000 × 2 KB ≈ 2 GB; about 730 GB a year.
- Keeping only 90 days of hot data: about 180 GB.
Conclusion: at this scale you don't need sharding. A thousand or so simple writes per second is not a strain for a reasonably sized PostgreSQL primary; the real problem is 730 GB a year that keeps accumulating, and hot/cold separation keeps the primary steady at around 180 GB.
A different set of assumptions: 20 million orders a day, everything else the same.
- The peak is about 2,300 orders, about 23,000 write statements per second.
- About 40 GB of new data a day; 90 days of hot data is about 3.6 TB.
- Suppose load testing shows that one primary sustains about 5,000 write statements per second while keeping p99 latency acceptable. Then you need at least 23,000 ÷ 5,000 ≈ 5 hosts; keeping about 50% headroom for sales and failover, take 8.
- 1024 logical shards ÷ 8 hosts = 128 logical shards per host; about 3.6 TB ÷ 8 = 450 GB of hot data per host.
Only now does sharding have a clear justification, and the numbers also tell you that scaling beyond 8 hosts means moving half of each host's logical shards. The value of capacity planning isn't precision; it's writing the assumptions down, correcting them with real measurements, and starting on the next step before the numbers approach a threshold (for example, utilisation above 60%).
Common pitfalls
- Deciding to shard before measuring. The slowness might be just a few queries missing indexes, or a misconfigured connection pool. Look at
pg_stat_statementsand table sizes before talking about splitting. - Confusing hot/cold separation with sharding. A huge table that's mostly old data has a data-volume problem, not a load problem; time partitioning and archiving solve it without sharding.
- Cleaning up large amounts of old data with
DELETE. Deleting tens of millions of rows at once produces a lot of WAL, slows replication and bloats the table. Partition by time and detach old partitions instead. - Forgetting to create partitions ahead of time. When the new month arrives, every write fails. Schedule the creation of future partitions and alert on how many remain.
- An archive job that isn't idempotent, or deletes before writing. One interruption and data is lost. Write to the archive, verify, then delete, and make reruns safe.
- Paginating large tables with
OFFSET. It gets slower the further you go, for the archive job and the API alike. Use keyset pagination on(created_at, id). - Using only time as the cursor. Rows with the same timestamp get skipped. The cursor must include a unique column.
- Choosing the shard key for a few queries. If the most frequent queries and transactions have to cross shards, sharding loses its point. List the queries and choose the key from the data.
- Routing with
hash % number of physical databases. Adding one database moves almost all the data. Use a fixed number of logical shards plus a map. - Keeping the database's auto-increment sequence after sharding. IDs on different shards collide. Use globally unique IDs and handle the clock stepping back.
- Writing 64-bit IDs as numbers in JSON. The browser rounds them to different numbers. Use strings.
- Migrating with double writes in the application. The two sides will drift apart sooner or later, and nobody will know. Use a full copy plus CDC, and verify range by range.
- Switching writes without a rollback plan. Every step needs completion criteria and a rollback method; keep reverse sync running after the switch.
- Watching only aggregate metrics. Averages hide a single hot shard. Look at each shard separately and find the logical shards with the most traffic.
Key takeaways
- Measure first: slow queries, table and index sizes, growth rate, read/write ratio, p99, buffer hit ratio and the data's age distribution. Then use the cheap fixes in order: fix queries and indexes, connection pools, caching, read replicas, vertical scaling, hot/cold separation.
- Different signals call for different splits: lots of data but mostly old → hot/cold separation; domains interfering with each other → vertical split; hot data and writes beyond a single primary → horizontal sharding.
- Define hot and cold by access pattern, and measure it. Range partitioning by time lets queries scan only the relevant partitions and cleans up old data by detaching partitions; the archive job works in batches, uses keyset pagination, writes idempotently, verifies before deleting and can be cancelled; older data is exported to object storage.
- Cache the hot data (TTL with jitter, stampede protection, delete after write); the API queries only hot data by default, order history goes through an explicit endpoint, and pagination uses keysets.
- A vertical split gives each service its own database, at the cost of turning cross-database joins and transactions into APIs, events, Sagas and Outboxes; a column split moves rarely used columns out of the main table according to access patterns.
- Choose the shard key from the most frequent queries and transactions — usually
user_idfor an online shop's orders; route with a fixed number of logical shards plus a versioned map; embed the logical shard and the time in order IDs, and handle the clock stepping back and duplicate generator IDs. - After sharding, queries without the shard key, pagination, aggregates, unique constraints, transactions, joins and reporting all need redesigning: indexes and data warehouses maintained by CDC, uniqueness tables, avoiding cross-shard transactions through the shard key, Sagas, denormalisation.
- Routing inside the application is simple and transparent; middleware suits many services, many languages and the need for mature tooling; go by the official documentation for each product's behaviour.
- Migrating without downtime: a full copy plus CDC, range-by-range comparison of counts and checksums, shadow reads, switching reads by percentage, switching writes after a brief freeze, reverse sync, and cleanup once confirmed. Logical shards make later resharding a move rather than a recomputation.
- Operations: monitor each shard separately and find hot spots, back up each shard and rehearse restores, apply schema changes one shard at a time in expand-and-contract order, and write down your capacity planning assumptions and correct them with measurements.
Next
Once the data is split, more things are running at the same time: more shards, more replicas, more background jobs, plus the retries added so that switchovers don't fail. Every extra actor reading and writing the same data is another chance for someone to cut in — two requests deducting stock for the same product at once, the archive job and a user changing the same order at the same time, requests flooding the database the instant a cache entry expires, an event applied twice during a migration. The next article, “Concurrency Pitfalls: From Data Races to Overselling”, begins the “Pitfalls in practice” part, going from data races in Go programs all the way to overselling and hot rows in the database, reproducing these problems one by one and fixing them.