This resume is also available in English.

6. Deploying the system5 / 5 · All columns

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.

  • 資料庫
  • Sharding
  • Go
  • PostgreSQL
In this article
  1. What this article solves
  2. Step 1: Diagnose before you decide to split
  3. Step 2: Hot and cold data — move the rarely used data out of the way first
  4. Step 3: Vertical splits — by business domain, or by column
  5. Step 4: Horizontal sharding — shard keys, routing and globally unique IDs
  6. Step 5: What breaks after sharding
  7. Step 6: Routing in middleware, or in the application
  8. Step 7: Migrating without downtime
  9. Step 8: Operations — monitoring, backups, schema changes and capacity planning
  10. Common pitfalls
  11. Key takeaways
  12. 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

MetricHow to measure it (PostgreSQL)What it tells you
Slow and most expensive querieslog_min_duration_statement, pg_stat_statementsWhich queries the time goes to; a few queries, or everything
Table and index sizespg_total_relation_size, pg_indexes_sizeWhich tables are growing; whether indexes are bigger than the data
Row counts and growth ratepg_stat_user_tables.n_live_tup, recorded dailyAt the current rate, how long until the next threshold
Queries per second, read/writeDeltas of calls in pg_stat_statements, application metricsWhether the bottleneck is reads or writes; whether replicas would help
p99 latencyApplication 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_tablesWhether the frequently used data still fits in memory
Connections and waitspg_stat_activity, pool wait metricsWhether the database is slow, or the application is queueing for a connection
Replication lagpg_stat_replicationWhether 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:

  1. 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”.
  2. 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”).
  3. 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”).
  4. Split reads and writes. When reads dominate, send the reads that can tolerate a little staleness to read replicas, and handle replication lag properly.
  5. 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.
  6. 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”:

SignalWhat the problem really isThe matching approach
One table is huge but mostly old, rarely read data; backups, schema changes and clean-up keep taking longerData volume, not loadHot/cold separation (step 2)
Tables from different domains compete for resources; one service's batch job slows another; teams wait on each otherData from different domains tied togetherVertical split (step 3)
The hot data alone no longer fits in one host's memory; write throughput has hit the limit of a single primaryThe write and capacity ceiling of a single primaryHorizontal 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.

order service Picks the tier by the order's time last 3 months older orders Hot: live primary Last 3 months of orders orders, monthly partitions Cache absorbs repeat reads Busy, needs low latency Warm: archive database Finished orders, 3–24 months orders_archive, read-only Cheaper machine and disks Only order history reads it Cold: object storage Older than 24 months Parquet files by month Scanned by a query engine Reports, audits, retention Archive job (daily) Batches; delete after verify Export job (monthly) Export, check, then delete Further right is cheaper, slower and rarely read; the live hot path only touches the left tier

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 that id alone is unique; uniqueness of id has to come from how it's generated (the ID generator in step 4). Likewise, order_items can no longer have a foreign key to orders(id). Common answers are to store created_at in order_items too 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 as pg_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 the DETACH ... CONCURRENTLY below 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 with ALTER 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 100000 has 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 NOTHING means 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 pending order 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 context is 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, Upsert skips what already exists, Count passes, and Delete removes 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 Pause between 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_items must move too. The example only moves orders; in practice the archive's Upsert writes the order and its items in one transaction, and the live Delete deletes 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 singleflight so 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.

Vertical split: by domain One PostgreSQL Every service's tables together order orders payment payments inventory stock Each service owns its database Fewer kinds of tables, same data size No cross-DB joins or transactions → use APIs, events, Saga / Outbox Horizontal sharding: by key order database orders is too big Shard 1 orders Shard 2 orders Shard N orders Same schema, different users Less data and fewer writes each Queries without the key ask all → pick the key, add index tables

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 payment reconciliation batch no longer slows down order placement in order; 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 keyOperations on a single shardOperations that cross shardsHot-spot risk
user_idPlacing an order (one transaction), my orders, a single order (when the ID embeds the shard)Site-wide queries by merchant, by product, by timeLow; a few heavy buyers have limited impact
order_idA single orderMy orders (must ask every shard)Lowest, writes spread most evenly
merchant_idEvery query in the merchant back officeMy 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 schemeHow it worksAdvantagesDrawbacks
Hash modulohash(key) % N, where N is the number of databasesSimplest, even distributionWhen N changes, almost all data has to move
Consistent hashingKeys and nodes sit on a hash ring; a key belongs to the first node clockwiseAdding or removing a node moves only some dataNeeds virtual nodes to be even; the moved range is hard to control
RangeSplit by key range, e.g. IDs 0–9,999,999 on shard 1Range queries are easyIncreasing keys send all writes to the last shard
Directory (lookup table)A table records which shard each key is onMost flexible; any key can be moved on its ownEvery route needs a lookup; the directory itself must be highly available
Fixed logical shards + maphash(key) % 1024 gives a logical shard, then a small map is consultedMoves happen per logical shard at a controlled costThe 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.

user_id shard key FNV hash % 1024 Logical shard 0 to 1023 Map versioned Pool physical DB Map (version 2): logical shard → physical database 0 – 255 256 – 511 512 – 767 768 – 1023 orders-db-1 orders-db-2 orders-db-3 orders-db-4 To scale out, move some logical shards to a new database and publish a new map; nothing is rehashed Order ID (a positive int64, 63 bits) Milliseconds (41 bits) Generator (6) Sequence (6) Logical shard (10) id & 1023 is the logical shard: the order ID alone finds the database, no lookup

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:

ApproachPropertiesWhat to watch out for
Per-shard sequences with offset and stepShard 1 produces 1, 5, 9…, shard 2 produces 2, 6, 10…Hard to adjust when the number of shards changes
UUID v4Fully random, no coordination128 bits, unordered; inserts scatter across the B-tree index
UUID v7, ULIDTime first, randomness after; roughly ordered by time128 bits; carries no shard information
Snowflake-style (64 bits)Time + machine ID + sequence; roughly increasing, fits in a BIGINTDepends 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.Pointer makes reads lock-free, and CompareAndSwap makes 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.
  • IDGen protects its sequence with a mutex: at most 64 IDs per millisecond per generator, after which it waits for the next millisecond; now and sleep can 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 givenAfter shardingHow to handle it
Queries without the shard keyMust ask every shard (scatter-gather)Index tables or a search engine, maintained by CDC or events
Pagination and sorting across all dataEach shard sorts its own part, which must be mergedLimit the depth, use keysets, or hand it to a read model
Aggregates such as COUNT and SUMOne per shard, then summed; slow and inconsistentA data warehouse, or counter tables maintained by events
Global unique constraintsOnly unique within a shardA uniqueness table keyed by that column, or the owning service enforces it
Cross-shard transactionsNo single database transactionAvoid them with the shard key; if unavoidable, use a Saga
JoinsData on different shards can't be joinedDenormalise; copy small tables to every shard
ReportingQuerying the live shards directly drags them downCDC 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.

order service With user_id: one shard only By merchant: the index first, then the shard by order_id orders-db-1 orders-db-2 orders-db-N CDC + event stream reads the WAL Merchant order index merchant_id → order_id Search engine Full-text search for support Data warehouse Reports, cross-shard totals All three lag the shards by seconds; for the latest state, read the shard by order_id

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:

ApproachExamplesAdvantagesCosts
Routing inside the application (a library)This article's internal/shardNo extra network hop or component; routing rules are fully readable and testableEvery 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 ShardingSphereThe application talks to what looks like one database; tools support jobs such as reshardingOne 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.

Step Where reads and writes go 1. Build the new shards, copy all history Reads and writes: old 2. CDC catches up from the copy position Reads and writes: old; new follows 3. Verify ranges: counts and checksums Reads and writes: old 4. Shadow reads: query both, compare Answer from old; new only compared 5. Switch reads by percentage Reads: new; writes: old 6. Freeze writes briefly, catch up, switch Reads and writes: new 7. Reverse sync for a while, then delete Reads and writes: new; old follows Before step 6, stop or switch reads back on any problem; after it, roll back with reverse sync

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

  1. Shadow reads: read requests query both sides, respond with the old database's result, and log any mismatch.
  2. 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.
  3. 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.
  4. 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.
  5. 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_statements and 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_id for 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.