Scaling, Production and Operations

Introduction

What is it? Scaling means increasing a system's capacity as usage grows. Production and operations mean running a database that real users depend on: changing it without breaking it, keeping it healthy as it grows, and deciding what to do with data that keeps piling up.

Why do we need it? A database runs on machines with limited CPU, memory, disk speed, network, and connections. A good schema and good indexes postpone the limit, but they cannot remove it. Sooner or later, traffic or data outgrows one machine. The skill is knowing which tool fits which problem, and in what order to use them. Using the wrong tool (sharding a table that only needed an index) makes a system slower, costlier, and harder to run.

Real-world analogy Think of a restaurant that becomes popular.

• First you fix the kitchen workflow and menu (optimize).
• Then you buy a bigger stove (scale up).
• Then you open a second counter just for takeaway orders (read replicas): the main kitchen cooks, the counter serves copies of what is ready.
• Then you clear the store room of last year's stock (data lifecycle) and organize it by month (partitioning).
• Finally, if one kitchen can no longer cook all the orders, you open branches in different cities and split the customers between them (sharding). Now you must decide who goes to which branch, and a customer's order history is in only one of them.

Business use case An online shop's order history page becomes slow during sales. The team works through the same ladder: fix queries, add the right index, add read replicas for history pages, partition the orders table by month, archive orders older than a year, and only when write volume outgrows a single primary, shard by a carefully chosen key.

Dataset Used

This chapter is about architecture and operations, so there is no new dataset. The examples reuse the orders and payments ideas from earlier days:

• orders(order_id, user_id, created_at, status, total_amount)
• payments(payment_id, order_id, amount, status, created_at)

Small tables and SQL snippets are created where an example needs them.

1. What Does Scaling Mean?

At a basic level, scaling means increasing capacity as usage grows. In the beginning, a product may have:

• fewer users
• less traffic
• smaller datasets
• simpler workloads

So a relatively small machine is enough. As the product grows:

• more people use it
• more requests hit the system
• more data is stored
• more features are added
• more internal tools depend on it

The old capacity is no longer enough, and the system must grow too. That growth is scaling.

Why do databases need scaling?

A database ultimately runs on machines with limited resources. Even with a good schema and well-designed indexes, one machine has an upper limit on:

• CPU
• memory
• disk I/O
• network
• connection count
• background processing capacity

When traffic and usage grow enough, the database will hit a ceiling. That is why scaling becomes necessary.

Why reads usually become the first scaling pain

In many products the first serious scaling problem is not writes, it is reads. Think about how products are actually used. Users read far more than they write:

• people scroll feeds far more than they post
• people browse products far more than they place orders
• people read dashboards far more than they reconfigure systems
• support tools look up information far more than they create it
• users view history pages far more than they create new records

Writes matter a lot, but the volume of reads is often much larger.

Real product intuition. In a social product, users read posts, scroll timelines, open profiles, check notifications, and browse comments. Sometimes they write: make a post, like something, comment, update a profile. But the dominant behaviour is consumption, so the system sees much more read traffic than write traffic. The same pattern appears in many other products.

 Typical traffic mix of a consumer product

 reads   ################################  (most of the traffic)
 writes  ####                              (smaller, but more critical)

Everyone wants reads to be fast

Many parts of a product are read-heavy: history pages, dashboards, support lookups, browsing pages, search-like experiences, account views, reporting screens. Users expect them to be fast, and internal teams do too. Support agents cannot wait long for customer details, business teams cannot wait for dashboards, and users do not want slow product pages. So read performance becomes critical very early.

First optimize, then scale

Before talking about replicas or multiple machines, remember: the first step is not scaling, it is optimization. That means:

• improve schema design
• improve queries
• add the right indexes
• remove obvious inefficiencies
• fix bad access patterns

Only after this should you conclude that one machine is still not enough. Scaling a badly designed system only multiplies the mess. So the order is: optimize first, then scale if necessary. (Days 35 to 37 were about exactly this first step.)

One machine always has limits

You can scale vertically (scale up) for some time: a larger CPU, more memory, a faster disk, a better network. But that also hits a ceiling. There is no infinite database machine. When vertical scaling reaches its limit, we must distribute the load. For read-heavy systems, the first natural step is to scale reads separately from writes.

Protect the primary

If one database is your source of truth, you do not want it overwhelmed by read traffic. It is also responsible for important write operations. If too many reads compete for resources on the same machine:

• writes may become slower
• lock contention may increase
• latency may rise
• critical user journeys may suffer

So we want to protect the primary from read traffic as much as possible. That does not mean the primary never serves reads. It means the full read load should not land there.

Writes are usually more sensitive than many reads

In many systems, writes are critical user actions:

• placing an order
• making a payment
• updating a profile
• saving important settings
• creating a post
• completing a workflow

These are often more sensitive than "a user saw slightly older read data for a moment". So in many systems it is acceptable for some reads to be slightly delayed, as long as critical writes stay correct and reliable. That trade-off is the foundation of read scaling.

2. Scaling Reads: Primary + Read Replicas

The standard read-scaling pattern is one primary with multiple replicas.

• The primary is the main source of truth. It handles writes.
• The replicas are copies of the data, used mainly to serve reads.

So instead of sending all traffic to one database, we separate responsibilities:

 writes  =>  primary
 reads   =>  replicas

What is the primary?

The primary is the database instance where write operations happen first. It is the authoritative version of the data. When we say "source of truth", this is what we mean. If a row is inserted, updated, or deleted, the real change happens first on the primary. That is why it must be protected carefully.

What are replicas?

Replicas are copies of the primary's data, used mainly to serve read traffic. If the application has many users reading pages, browsing history, opening dashboards, and doing support lookups, those requests can be routed to replicas instead of the primary. Replicas are a workload-distribution tool for reads. They do not remove the need for a primary. They reduce the burden on it.

How the traffic flow changes

                     +----------------+
   write ----------> |    PRIMARY     | <---- source of truth
                     +-------+--------+
   App server                | changes are sent outward
   (routes each              v
    request)         +-------+--------+--------------+
                     |                |              |
                +----v----+     +-----v---+    +-----v---+
   read ------> | REPLICA |     | REPLICA |    | REPLICA |  <---- reads spread here
                +---------+     +---------+    +---------+

1. The user sends a request to the app server.
2. If it is a write, the server sends it to the primary.
3. If it is a read, the server sends it to one of the replicas.

Read traffic is spread across many copies instead of hammering one DB. As a result, read capacity increases, primary pressure decreases, and the write path stays more protected.

How do replicas stay updated?

1. The primary commits the write first.
2. The change is sent outward.
3. Replicas replay (apply) that change.
4. Replicas eventually catch up.

Replicas are not independent databases inventing their own truth. They continuously receive and replay changes from the primary.

(In MySQL, the primary writes its changes to the binary log. Each replica copies that log, then a separate thread applies the changes. By default this is asynchronous: the primary does not wait for the replicas.)

Replica lag

The update from primary to replicas is not always instant. There may be a small delay between "the primary has committed the latest change" and "the replica has fully applied it". That delay is called replica lag.

 time --->

 T0      write committed on the PRIMARY         price = 500
 T0+50ms read from a REPLICA                    price = 400   <-- stale (old) value
 T0+200ms replica finishes applying the change  price = 500
 T0+300ms read from a REPLICA                   price = 500   <-- up to date

So if you read from a replica right after a write, the replica may still show older data. Replica-based scaling therefore gives you higher read capacity, but with the possibility of slightly stale reads.

Why slightly delayed reads are often acceptable

In many product scenarios, a tiny delay in some reads is fine:

• a feed showing a new post a little later
• a dashboard refreshing a bit later
• a history page reflecting the newest write after a short lag
• non-critical browsing pages not showing the latest change instantly

But for critical paths that require the freshest possible state, reading from the primary may still be necessary. Not every read should go to replicas blindly. Some reads are "replica-safe", and others are "must-read-from-primary".

Some reads still need the primary

Some reads cannot tolerate stale data, for example:

• the user just completed a critical action and expects immediate confirmation
• the flow depends on the newest possible state
• business correctness is more important than load distribution

The application can route those particular reads to the primary. So the real production model is not "all reads always go to replicas". It is:

 most reads           -> replicas
 some critical reads  -> primary

Common ways to handle "read your own writes" (extra)

Pros of scaling reads with replicas

1. More read capacity. If one DB can handle only so many reads, five replicas can collectively handle much more. Reads scale without forcing everything through one primary.

2. Lower pressure on the primary. Replicas absorb much of the read load, leaving the primary more room for writes, transaction processing, background DB work, and critical fresh reads.

Cons of scaling reads with replicas

1. Stale reads are possible. Replicas may be slightly behind. This is the most important trade-off.

2. More infrastructure. Replicas are more machines to provision, monitor, maintain, and pay for. Scaling reads is not free capacity.

3. More failure modes. More replicas means more things that can go wrong:

• a replica falls behind
• a replica crashes
• replica health becomes inconsistent
• routing logic may fail
• regional issues may appear

4. The primary still has to feed replicas. The primary must produce and send the change stream that keeps replicas updated. Replicas reduce read pressure, but they are still part of a system the primary supports.

A replica is not free capacity

This deserves to be said strongly. It is capacity with trade-offs. When you choose replicas, you accept:

• replica lag
• extra infrastructure
• more failure handling
• more routing complexity
• more operational maintenance

So do not jump to replicas too early just because one read query feels slow. First optimize. Then scale. And when you scale, understand the trade-offs.

A practical progression

1. Fix schema and query problems.
2. Add the right indexes.
3. Scale vertically if useful.
4. When one machine is still not enough, scale reads with replicas.
5. Route critical fresh reads carefully.
6. Accept that replicas bring lag and complexity.

That is far more mature than "traffic is high, let's add replicas".

Regional read scaling

Replicas can also be placed geographically. If users exist across regions, replicas can be placed closer to them. The application can route a user's request to a nearby read replica instead of a distant central DB. So replicas help with more read throughput, and with better read latency by region. This is a major benefit in global systems.

Why this suits consumer-heavy products

The model fits products where users mostly consume data: browse, scroll, view pages, open dashboards, search history, inspect records, perform support lookups. In such systems, read replicas are often one of the first practical horizontal-scaling tools.

Replicas are not the whole story

Even with replicas, you usually still need other techniques:

• caching
• connection pooling
• workload isolation
• careful routing
• query optimization
• better schema design

A system with replicas but poor query patterns can still struggle badly.

Operating replicas in MySQL (extra)

-- how far behind is this replica?  (MySQL 8.0.22 and later)
SHOW REPLICA STATUS\G
--   look at: Seconds_Behind_Source

-- make a replica read-only so nobody writes to it by mistake
SET GLOBAL read_only = 1;
SET GLOBAL super_read_only = 1;

(Before 8.0.22 the statement is SHOW SLAVE STATUS and the column is Seconds_Behind_Master.)

Two important facts for operations:

• A replica is not a backup. It copies mistakes too: if someone runs a wrong DELETE on the primary, the replicas delete the rows as well. You still need real backups, and you should test restoring them.
• A replica can be promoted to primary if the primary fails, which helps availability. But with asynchronous replication, changes the replica had not yet received can be lost, so promotion needs a plan.

3. Sharding

So far we have two scaling ideas:

• when reads become the bottleneck, scale with read replicas
• when a single database machine is under pressure, first try better schema, queries, indexes, and workload distribution

The next question: what happens when even that is not enough for writes? Read replicas help with read traffic, but they do not solve the core problem of a single writer eventually hitting its limits. That is where sharding enters.

Why read replicas are not enough for write scaling

Reads are easy to distribute: one source of truth, many copies. Writes are different. They cannot be scaled in the same "copy and distribute" way, because:

• there must be a clear source of truth
• storage keeps growing
• write throughput on one machine has a limit
• maintenance on one huge database becomes riskier over time

If the system reaches a point where write traffic is very high, storage grows aggressively, and one primary is too large or too busy, then adding replicas is not enough. You need to split the actual data. That is what sharding does.

Why do we need sharding?

At the highest level: one machine has limits. Even the largest machine is limited in storage, write throughput, maintenance window size, and operational risk. If your product keeps growing, one primary eventually stops being a comfortable long-term design. The question is no longer "can I optimize queries more?" but "can I keep all this data and all this write load on one machine forever?" For very large systems, the answer is often no.

Sharding means splitting data across multiple databases

With replicas, every replica contains the same logical data. With sharding, that is no longer true:

• not every database has all rows
• each database stores only a slice of the data

Instead of "one main DB has everything, and replicas copy it", we now say "multiple databases each own different parts of the dataset". That is a major architectural shift.

 Replication (copies)                     Sharding (slices)

 PRIMARY: all rows                        SHARD 1: rows A
    |--> replica: all rows                SHARD 2: rows B
    |--> replica: all rows                SHARD 3: rows C
    |--> replica: all rows                (no shard has everything)

Before sharding, do the simpler things

Sharding is powerful but expensive and complex. First do the cheaper improvements:

• optimize schema and queries
• add the right indexes
• distribute read workload using replicas
• scale vertically if possible
• reduce wasteful workloads

Only then say, "now we need to split the data itself". Sharding is not the first optimization. It is a later-stage scaling move.

A practical use case: Amazon-style orders

A company starts small in the USA, then expands to India, Europe, and later many regions. The orders table becomes enormous: billions of rows, writes day and night, activity from many countries. Even after tuning and read replicas, one writer may not be enough, because:

• the orders table keeps growing
• write pressure is non-stop
• storage pressure grows
• maintenance on one giant primary becomes scary

What sharding looks like in practice

Originally one database had the full orders table, every order from every country. When we shard, we do not copy the full table into several databases. Instead:

• shard 1 gets some orders
• shard 2 gets some other orders
• shard 3 gets yet another set of orders

Each shard becomes responsible for its own slice. This spreads storage, write load, read load, and operational burden across several database instances.

Example: sharding orders by country

If the orders table has a country column, one simple strategy is:

• US orders go to shard 1
• India orders go to shard 2
• EU orders go to shard 3

 Application / routing layer
        |
        +-- country = 'US' ----> SHARD 1   (only US orders)
        +-- country = 'IN' ----> SHARD 2   (only India orders)
        +-- country = 'EU' ----> SHARD 3   (only EU orders)

 Each shard has an orders table, with different contents.

No single database carries all order rows anymore.

What this achieves

The system no longer relies on one giant orders database for all write traffic:

• US write traffic hits one shard
• India write traffic hits another shard
• EU write traffic hits another shard

Write load is no longer concentrated in one place, and storage is distributed too. Sharding gives more storage runway and more write runway.

Sharding vs replication

A simple way to remember it: replicas copy, shards divide.

Partitioning vs sharding

Partitioning usually happens inside a single database instance. It is often managed by the database engine itself and used to improve query efficiency when data is large but still on one server. It is:

• within one DB instance
• simpler operationally
• still one database server
• usually easier for joins and coordination

Sharding happens across multiple database instances. It is a much larger architectural move:

• across multiple DB servers
• much higher complexity
• requires routing logic
• cross-shard operations become harder
• offers much bigger horizontal scalability

Partitioning is often earlier and simpler. Sharding is later and heavier.

Use partitioning when

• data still fits within one server
• but queries are slow because they scan too much data
• and you want better efficiency without moving to multiple DB instances

Use sharding when

• one server is no longer enough
• storage or write load has outgrown the strongest single-machine path
• true horizontal scale is needed

Partitioning improves organization inside one DB. Sharding changes the whole architecture. (Section 6 covers partitioning in detail.)

Pros of sharding

1. More storage runway. One database no longer holds everything, so total capacity increases significantly.

2. More write runway. Write traffic is distributed across shards. One node no longer handles every write. This is one of the biggest reasons sharding exists.

3. One node no longer carries everything. One overloaded node is no longer responsible for the whole system's scale, which reduces concentration of pressure.

4. Better fault isolation. If one shard has a problem, it may affect only part of the data or one segment of traffic, not the whole world.

Cons of sharding

Sharding brings serious costs.

1. Routing logic becomes your responsibility. The application or routing layer must know which shard to query, which shard to write to, and how to find the right shard for a given request. That logic must be designed, implemented, tested, and maintained.

2. Rebalancing is painful. If one shard becomes too large or too hot, you may need to move data again: repartitioning, migration, downtime or careful live migration, more routing changes, lots of operational work. Sharding is not a one-time decision. It keeps affecting future operations.

3. Cross-shard queries become painful. If related data lives on different shards, queries that were easy become complicated. For example, user data in one shard and order data in another: you can no longer rely on a simple local join. You may need multiple queries, application-side joins, orchestration logic, more latency, and more failure cases. Two related gotchas (extra): a UNIQUE constraint cannot be enforced across shards by one ordinary index, and a transaction that touches two shards is much harder than one inside a single database.

4. Operational complexity increases a lot. You must manage shard health, routing, migrations, skew detection, rebalancing, per-shard failures, schema coordination across shards, and maintenance windows across a distributed topology. Sharding looks cool on a whiteboard, but it comes with real operational burden.

What is a shard key?

A shard key is the field (or set of fields) whose value decides which shard a row belongs to. It is one of the most important design choices in a sharded system, because it decides how data and traffic spread across shards.

Examples:

• country
• user_id
• (country, state)
• first character of some user identifier
• hashed user id

All are possible, depending on the system.

A good shard key should...

1. Distribute data and traffic evenly. You do not want one shard overloaded while others sit idle.
2. Match common query patterns. If your most common queries naturally include the shard key, routing is easier and more efficient.
3. Avoid hot shards. A hot shard gets far more traffic than the others, which defeats the purpose of sharding.
4. Remain stable over time. Changing shard logic later is expensive, so the initial choice matters a lot.

Example of a bad shard key

A product has 85% of its traffic from India and 15% from the US. Someone picks country as the shard key:

• India => shard 1
• US => shard 2

This is bad, because shard 1 becomes a hot shard. Most of the data and traffic pile onto one shard while the other stays underused.

 shard 1 (India)  ################################################  85%
 shard 2 (US)     ########                                          15%

 shard 1 behaves like the old overloaded primary

The problem is not just "we chose a key". It is that we chose a key that did not distribute traffic evenly.

What is shard skew?

Shard skew happens when some shards receive too much data or traffic compared to others. It is dangerous, because the overloaded shard becomes the new bottleneck. Even though the system is "sharded", one shard can behave like the old overloaded primary. A bad shard key can ruin the benefit of sharding.

Why changing the shard key later is expensive

If the shard key turns out wrong, fixing it is painful, because by then:

• huge amounts of data are already distributed
• routing logic depends on the current key
• rebalancing means real migration work
• the system may need downtime or complicated live transfer logic

So choose with data distribution, traffic distribution, query patterns, and future scale in mind.

Common sharding strategies (extra)

Why adding shards hurts with a simple hash (extra). Take shard = user_id % N.

Going from 4 shards to 5 moves 5 of these 8 users (ids 4 to 8). Over the ids 1 to 20, 16 of the 20 (80%) change shard. A common remedy is to create many more logical shards than servers from the start (for example 1024 logical shards on a few servers), so adding a server moves whole logical shards instead of re-hashing every row.

Sharding is usually planned before the ceiling hits

Do not wait until the system completely breaks. Once the largest comfortable machine is nearly exhausted, it may be too late to do the migration calmly. Sharding takes design, planning, data movement, routing updates, testing, and operational coordination. Teams usually anticipate the need before the ceiling is reached, which is why analytics, monitoring, and forward planning matter.

4. Distributed IDs

What are distributed IDs?

Distributed IDs are identifiers generated in a distributed system so that they stay unique across:

• many machines
• many application servers
• many services
• many shards
• many regions

and they do this without depending on a single database counter. In essence: "even though the system is split across many places, every created row or event still gets a globally unique identifier."

Why the topic comes right after sharding. With one database, AUTO_INCREMENT hands out 1, 2, 3, ... Naively starting the same AUTO_INCREMENT sequence on every shard produces collisions when data is combined. Configured offsets/increments or a shard prefix can avoid collisions, although distributed ID schemes often provide better portability and routing properties. So sharded systems need IDs that are unique without asking one central counter.

Where distributed IDs are commonly needed

• sharded databases
• microservices
• multi-region systems
• event systems

In these architectures, multiple independent actors create records at the same time. Relying on one central numeric counter can be impractical, slow, or impossible. Instead of "ask one central DB for the next id", we "generate IDs in a distributed-safe way".

What makes a good distributed ID?

1. Global uniqueness. If multiple machines, services, or shards generate IDs independently, collisions must still be extremely unlikely or impossible by design. Globally unique, not just unique inside one machine.

2. Fast generation. ID generation sits on hot write paths. It must be cheap, so it does not slow inserts, events, or critical user flows.

3. Preferably sortable by time. Not always mandatory, but useful. If IDs are roughly ordered by creation time:

• logs are easier to reason about
• newest records are easier to inspect
• inserts behave better with clustered storage
• time-based debugging is easier

4. Small enough for storage and indexes. From Day 37: primary keys and indexed values drive storage. If the ID is too large, then clustered index entries get larger, secondary indexes get larger, pages hold fewer rows, B+ trees grow faster, and cache efficiency drops. A good distributed ID is compact enough to be practical.

5. Hard to guess. If IDs are too predictable, people may infer business volume, order frequency, timing patterns, or internal growth. For example, if IDs are purely sequential and externally visible, someone can estimate how many orders you create. So in many systems IDs should not be trivially guessable. (This is not always the top priority. And an ID that is hard to guess is still not a security secret.)

Widely used families

Teams usually do not reinvent this. The common families are:

• UUID
• Snowflake-style IDs
• ULID

Each balances the goals differently.

1. UUID

UUID stands for Universally Unique Identifier. It is a 128-bit identifier designed to be unique across systems, so it works in distributed environments without a single central counter. The idea is not that a collision is mathematically impossible, but that the generation scheme makes collisions so unlikely that UUIDs are practical for global uniqueness.

UUID is a family with several versions:

Example format

 550e8400-e29b-41d4-a716-446655440000
 |------| |--| |--| |--| |----------|
   8      4    4    4       12        <- 8-4-4-4-12 hex digits

• It is a hexadecimal representation of a 128-bit value: 32 hex digits, each digit is 4 bits.
• The real UUID underneath is binary data. The string is a human-readable encoding.
• Hex uses 0 to 9 and a to f.
• The dashes are for readability only. They are not part of the identity.

Pros of UUID

1. Easy to generate anywhere. No central counter. Any machine or service can generate UUIDs independently.
2. Globally unique. This is why UUIDs are so appealing in distributed systems.
3. Operationally convenient. Widely supported in libraries, frameworks, languages, and databases, so adoption is easy.

Cons of UUID

1. Large. 128 bits is big compared to compact numeric IDs. More storage overhead.
2. Not very index friendly in many cases. With a random UUID such as UUIDv4, insert order is scattered, which hurts clustered-index locality.
3. Can fragment clustered storage. With a random-looking primary key, new rows land all over the key space instead of near the end, causing more page splits and fragmentation-like behaviour.

UUID is convenient and safe, but not always the best choice for database locality.

Storing UUIDs in MySQL (extra)

Use BINARY(16) with UUID_TO_BIN(uuid, 1). The 1 flag rearranges the time parts of a version 1 UUID so that values sort roughly by time. (MySQL's own UUID() function generates version 1 UUIDs.)

2. Snowflake-style IDs

These come from the well-known Twitter Snowflake approach. A Snowflake-style ID is a numeric ID built from several pieces:

 ID  =  time  +  machine  +  sequence

This makes it possible to generate unique IDs across many machines while keeping them roughly ordered by creation time.

Why they are attractive. They solve several problems at once: global uniqueness, distributed generation, time ordering, and compact numeric representation.

The classic 64-bit structure

 | 1 bit  |      41 bits       |   10 bits    |   12 bits    |
 | unused |     timestamp      |  machine id  |  sequence    |
 |  sign  |  (when)            |  (where)     | (which one)  |

• 1 unused/sign bit
• 41 bits for the timestamp
• 10 bits for the machine ID
• 12 bits for the sequence number

The exact split varies by implementation, but this is the classic mental model:

• the timestamp says when
• the machine ID says where
• the sequence says which number within that time slice

This lets many nodes generate IDs independently without colliding.

What the bit sizes allow (extra)

A tiny worked example (extra). Suppose the timestamp part is 1, the machine id is 5, and the sequence is 3:

SELECT (1 << 22) | (5 << 12) | 3 AS snowflake_id;

The timestamp is shifted left by 22 bits (12 + 10), the machine by 12 bits, and the sequence takes the lowest 12 bits. To decode:

SELECT 4214787 >> 22          AS ts,        -- 1
       (4214787 >> 12) & 1023 AS machine,   -- 5
       4214787 & 4095         AS seq;       -- 3

A real ID has a large timestamp, so real values are much bigger, but they still fit in a BIGINT.

Pros of Snowflake-style IDs

1. Globally unique when configured correctly. Machines can generate unique IDs independently.
2. Sortable by creation time. A big operational and database advantage.
3. More compact than UUID. Around 64 bits, much smaller than 128-bit UUIDs. Good for storage and indexes.
4. Better clustered-index behaviour. IDs tend to increase with time, so inserts are append-like, friendlier for B+ tree locality.

Cons of Snowflake-style IDs

1. Clock problems can cause trouble. Time is part of the ID, so clock skew or clock rollback can create problems if the implementation is not careful.
2. Leaks creation time. The ID reveals approximate creation time. Sometimes acceptable, sometimes not.
3. More complex to implement. Compared to "call a UUID library", it needs careful implementation and operations (for example, giving every generator a unique machine id).

3. ULID

ULID stands for Universally Unique Lexicographically Sortable Identifier. It is a 128-bit ID designed to be unique, easier to sort by time, and more orderly than common random UUID usage. A ULID combines:

• a timestamp part
• a random part

(In the usual layout: a 48-bit millisecond timestamp plus 80 random bits, written as a 26-character string. Sorting the strings alphabetically also sorts them by time.)

Why ULID exists. It is for teams that want something easier than building Snowflake infrastructure, more time-ordered than a typical random UUID, friendlier for logs and operational inspection, and often nicer to work with as strings. It is a compromise.

Pros of ULID

1. Sortable by time. One of its main attractions.
2. Better for logs and human inspection. Time ordering and a readable format make debugging and log analysis nicer.
3. More index-friendly than purely random UUIDs. The timestamp component makes insertion behaviour better than UUIDv4-style randomness.

Cons of ULID

1. String storage overhead. ULIDs are often handled as strings, which costs more storage than compact numeric IDs.
2. Not as compact as Snowflake numeric IDs. A 64-bit numeric ID is still usually smaller.

ULID is nicer than random UUID in some ways, but not the most compact.

Comparing the three (extra)

5. Zero-Downtime Schema Changes

As products evolve, the schema must evolve too. New requirements keep coming:

• add a new column
• add a new table
• relax a constraint
• introduce a new read/write flow
• migrate a data format
• split data differently

At small scale, schema changes feel easy. At scale they become dangerous, because:

• tables are large
• traffic is live
• reads and writes are continuous
• old app versions may still be running
• cached clients may still call old APIs

So the goal is not just "change the schema". The goal is "change the schema without breaking production traffic."

What does "downtime" mean?

Downtime means a part of your service, or the entire service, becomes unavailable for a period of time. It does not always mean the whole app is dead. Sometimes only a specific capability is unavailable. In a banking system during maintenance, you may still log in and view balances, but money transfers are disabled. So downtime can be full or partial.

Why schema changes cause downtime

A schema change touches the contract between the database, backend code, old app versions, new app versions, existing data, and new incoming writes. Examples of risky changes:

• add a column
• rename a column
• change a type
• make a nullable field non-null
• split one field into multiple fields
• change primary key logic

These can break live writes, live reads, old code paths, cached clients, and background jobs. Downtime is one option, but where possible the better option is near-zero or zero-downtime migration.

What does zero-downtime actually mean?

It does not mean "magic happens and nothing changes". It means:

• the app keeps serving traffic
• critical reads and writes keep working
• old and new versions of the application can both function during the migration window
• backward compatibility is preserved

Backward compatibility is everything.

If the old backend or app still expects the old contract and you suddenly remove it, production breaks. Zero-downtime migration is about carefully evolving the contract without breaking the old path immediately.

The safe pattern: Expand, Backfill, Switch, Contract

 1. EXPAND    add the new schema in a compatible way; old path keeps working
      |
 2. BACKFILL  fill old rows into the new structure, slowly, in batches
      |
 3. SWITCH    new writes populate the new field; reads start using it
      |
 4. CONTRACT  only when everything is stable: remove the old path

1. Expand. Add the new schema in a compatible way:

• add a new nullable column
• add a new table
• add a new index
• introduce new code paths without removing old ones

Keep the old path working. Do not delete old behaviour immediately.

2. Backfill. After the new schema exists, fill old data gradually into the new structure. Existing rows were created before the new schema existed, so they may have NULL in the new column, missing derived values, or an outdated shape. Backfill means: read old rows, compute the new representation, write it back gradually. This step is usually the most dangerous one (more below).

3. Switch. Once the new schema exists and backfill is sufficiently complete, slowly switch reads and writes to the new path:

• new writes start populating the new field
• reads begin reading the new field
• new app logic starts depending on the new structure

You usually do not flip everything blindly in one second.

4. Contract. Only after everything is stable do you remove the old path: stop writing the old column, stop reading the old format, remove old code, and finally drop old schema pieces. This is the last step, not the first.

A simple example: adding payment_mode

The payments table did not have a payment_mode column. Now the product requires that users see the payment mode in payment history. How do we add it safely?

Step 1: Expand. Add a new nullable column:

ALTER TABLE payments
  ADD COLUMN payment_mode VARCHAR(20) NULL,
  ALGORITHM=INSTANT;

Why nullable? Older code paths may still insert rows without this field. If you make it NOT NULL immediately, old code may start failing. So: add the column and keep old code compatible.

(ALGORITHM=INSTANT, available in MySQL 8.0.12 and later for this kind of change, changes only metadata, so it is very fast. If MySQL cannot do the change this way, the statement fails instead of quietly doing a heavy table copy, which is what you want in production.)

Step 2: Backfill. All old rows now have payment_mode = NULL. That is not enough. Fill old payment rows with the correct value. For example, older rows may all be cash_on_delivery, or you can derive the value from another historical source. Do it in small ranges:

UPDATE payments
SET payment_mode = 'cash_on_delivery'
WHERE payment_id >  100000
  AND payment_id <= 101000          -- next range each time: 101000-102000, and so on
  AND payment_mode IS NULL;

Step 3: Switch. Update the newer write path so every new payment writes payment_mode directly. Update read paths so payment history reads this field (for example, falling back to the old logic when it is still NULL). At this point:

• old data is being backfilled
• new data is written correctly
• read APIs are becoming aware of the new schema

Step 4: Contract. Once old rows are backfilled, new writes are stable, and old clients no longer depend on the old path, you can clean up. If the column should become mandatory, first check:

SELECT COUNT(*) FROM payments WHERE payment_mode IS NULL;   -- must be 0

and only then run ALTER TABLE payments MODIFY payment_mode VARCHAR(20) NOT NULL;.

 time --->

 schema:     [ old ]--[ + nullable column ]----------------------------[ NOT NULL ]
 backfill:              [ batch..batch..batch..batch ]
 app code:   [ v1: old path ]--[ v2: writes both, reads new with fallback ]--[ v3: new only ]
 old clients:  still working the whole time ------------------------------> retired

Why backfills are the real danger

Many people think the danger is adding the column, writing the migration script, or changing the code. The real danger is often the backfill. Backfills touch many rows, so they create:

• lots of writes
• more I/O
• more locking pressure
• more replication lag
• more disk activity
• more cache churn
• possible latency spikes for production traffic

Adding the column may be easy, but backfilling billions of rows is not. That is why large companies say a migration took days or weeks: not because adding the schema took weeks, but because the safe backfill did.

How backfills should be done

Backfills should be:

• rate-limited
• done in batches
• done in the background
• monitored carefully
• paused if production health degrades

You do not blast the full table at once. You do something like: process 1,000 rows, wait, process the next 1,000, monitor DB pressure, continue.

How long can that take? Suppose each batch is 1,000 rows followed by a half-second pause, which is about 2,000 rows per second:

At very large scale, it can take days or weeks. That is okay. A slow safe rollout is better than a fast outage. Watch replica lag while you do it, and pause if the replicas fall behind.

Why old and new app versions both matter

This is often missed. Imagine backend v1 exists, app clients still use v1, backend v2 is rolling out, and the schema is changing underneath. If you suddenly remove an old field or old API path, old clients may break immediately. Zero-downtime migration requires old and new versions to coexist for some time:

• old clients should still work
• new clients should also work
• old schema expectations should not instantly disappear

Backward-compatible design is the foundation of zero-downtime schema change.

Which schema changes are usually safer?

Many changes can be done with near-zero downtime, especially when they are additive and backward-compatible:

• adding a nullable column
• adding a new table
• adding an index with MySQL online DDL where the operation supports it
• relaxing certain constraints

They are safer because they do not immediately invalidate the old contract.

Which changes are riskier?

• dropping a column
• renaming a column
• changing a column type
• making a nullable column suddenly NOT NULL
• splitting one column into multiple columns
• changing primary key design

These often break assumptions in app code, old clients, old data, queries, and background jobs. They need much more careful choreography, and sometimes real downtime.

Two production gotchas (extra)

• Metadata locks. An ALTER TABLE needs a brief exclusive lock on the table. If a long-running transaction is holding a lock, the ALTER waits, and every new query on that table queues up behind the waiting ALTER. One forgotten open transaction can freeze a busy table. Check for long transactions before running a migration, and set a short lock wait timeout.
• Big-table tools. For changes that MySQL cannot do online, teams use online schema-change tools such as gh-ost or pt-online-schema-change. They copy the table in the background and swap at the end.

When is downtime still acceptable?

Downtime is acceptable in some cases:

• low-traffic windows
• early-stage products
• internal tools
• small systems where user impact is limited

If you have a young product with very low production usage, a short maintenance window may be perfectly reasonable. But if you are a cloud provider, or promise very high uptime, downtime is much more expensive and much less acceptable. That is why large platforms invest heavily in compatibility-driven migration strategies.

6. Partitioning and Data Lifecycle

Till now we have seen how indexes help reads, how replicas scale reads, how sharding helps when one writer is not enough, and how schema changes must be handled carefully. One more practical question remains:

What if the data still fits on one database server, but queries are getting slower because the table has become too large?

That is where partitioning comes in. And once we understand it, we need something equally important: data lifecycle. Not all data should stay in the hot production database forever.

Partition vs partitioning

• A partition is one physical slice of a table.
• Partitioning is the process of breaking one logical table into many such physical slices.

Suppose we have an orders table (order_id, user_id, created_at, status, total_amount) that stores 5 years of order history. Think practically: when users open the orders page, are they usually checking orders from 5 years ago? No. Most users check the last 30 days, last 3 months, last 6 months, maybe last year. Very old data is queried much less often. But if the whole table remains one giant table, then even though B+ trees are efficient, the database still operates over a very large structure holding 5 years of data. Partitioning helps here.

Partition orders by time

A very common strategy for transactional tables is to partition by date or month:

• January 2026 data in one partition
• February 2026 data in another
• March 2026 data in another
• and so on

Logically it is still SELECT * FROM orders. Internally the database knows "Jan data lives here, Feb data lives here, Mar data lives here", which makes certain queries much more efficient.

 ONE logical table: orders

 +-------------+-------------+-------------+-------------+-----
 | p2026_01    | p2026_02    | p2026_03    | p2026_04    | ...    <- physical partitions
 | Jan orders  | Feb orders  | Mar orders  | Apr orders  |
 +-------------+-------------+-------------+-------------+-----

How this looks in MySQL (extra)

CREATE TABLE orders_p (
  order_id     BIGINT NOT NULL,
  user_id      BIGINT NOT NULL,
  created_at   DATETIME NOT NULL,
  status       VARCHAR(20) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, created_at)        -- must include the partition column
)
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2026_01 VALUES LESS THAN ('2026-02-01'),
  PARTITION p2026_02 VALUES LESS THAN ('2026-03-01'),
  PARTITION p2026_03 VALUES LESS THAN ('2026-04-01'),
  PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);

Two MySQL rules to know:

• Every unique key, including the primary key, must contain the partitioning column. That is why the primary key is (order_id, created_at). A consequence: the database no longer guarantees that order_id alone is unique, so the id itself should come from a safe generator (see section 4).
• A partitioned InnoDB table cannot have foreign keys, and cannot be referenced by one.

Why this helps

Suppose a query asks for orders from January to March 2026:

SELECT order_id, status, total_amount
FROM orders_p
WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-04-01';

With monthly partitions, the database can focus only on the relevant partitions. Instead of thinking about the full 5 years of storage, it limits work to smaller slices. This is called partition pruning, and EXPLAIN shows it in the partitions column (here it would list p2026_01,p2026_02,p2026_03).

The benefit is not that partitioning magically makes everything fast. The benefit is that it can reduce the amount of data the database needs to consider for certain query patterns. That is why partitioning must match the real access pattern.

A feel for the numbers (illustrative). With 5 years of monthly partitions there are 60 slices. A "last 30 days" predicate may let MySQL prune to 1 or 2 partitions, about 3% of the partitions. That does not promise a 3% runtime or work estimate: partition sizes, indexes, selectivity, caching, and the chosen plan still determine cost.

Partitioning does not make all queries fast

This is the most important warning. Suppose someone asks: give me all orders from the last 5 years for this user. The database may still have to touch many or all partitions. Partitioning helps most when queries naturally align with the partitioning rule:

How to think about it internally

A normal InnoDB table has one clustered-index B+ tree for the table data and secondary-index B+ trees for the secondary indexes. When the table is partitioned, each partition behaves almost like its own smaller physical table. So you get one logical table, many physical partitions, and index structures maintained per partition. Instead of one massive storage structure, the table is physically split into smaller pieces.

What happens on insert?

When a new row is inserted, the database first works out which partition the row belongs to. If the table is partitioned by month and the order date is February 2026, the row goes into the February 2026 partition. Inserts are routed by the partition rule.

What happens on search?

It depends on the query.

• Query aligns with the partition rule (for example, orders from January to March 2026): the database focuses on only those partitions. Efficient.
• Query does not align well (for example, all orders ever placed by a user): the database may need to search across many partitions.

Partitioning vs sharding

This is a very frequently asked interview question. The distinction must be crystal clear.

Partitioning = one DB, many internal slices. Sharding = many DBs, data spread across them.

When to use partitioning instead of sharding

• data still fits on one server
• but query performance is degrading because the table is too large
• and many queries naturally filter by a dimension like date or range

Use sharding when

• one machine is no longer enough
• the storage ceiling is being reached
• the write load cannot be handled by one database instance
• horizontal scale is necessary

Partitioning often comes before sharding on the escalation path.

The second big topic: data lifecycle

Partitioning becomes even more useful when you think about data lifecycle. Data lifecycle is about:

• how long data stays in hot production storage
• when it moves to an archive
• when it is deleted
• or when it is anonymized

This is a real production concern. Not all data should live forever in your main transactional database. If you keep everything forever in hot storage:

• storage cost grows
• scans grow
• maintenance becomes harder
• performance suffers
• scaling pressure increases faster

So lifecycle management is essential.

Hot, warm, and cold data

A useful mental model is three buckets.

1. Hot data is recently and frequently queried, and used in active production flows. Examples: last 30 days of orders, recent payments, active support tickets, current user sessions. It should stay in the main hot production database.

2. Warm data is older, still needed occasionally, not hit often but still useful. Examples: 3 to 12 months of order history, recently closed tickets, moderately old user activity. It may still stay close to the operational system, but does not need to be treated as the hottest data.

3. Cold data is rarely queried, mostly kept for audit, compliance, reporting, or edge-case access. Examples: 2-year-old or 5-year-old records, old logs, old transactional trails required only for compliance, stale inactive accounts. It usually should not live forever in the hottest production path unless there is a strong reason.

 HOT  (last ~30-90 days)   -> main production database, fast storage
 WARM (months)             -> still close to production (smaller tier or replica)
 COLD (years)              -> archive: cheaper storage, warehouse, object storage
 EXPIRED                   -> deleted or anonymized, per retention policy

(The day ranges are illustrative. Your product decides them.)

How platforms usually handle old data

There are three broad actions.

1. Keep recent data in the main database. This is the hot path. The most important, most frequently accessed data stays in the production system.

2. Archive older data somewhere cheaper. Move it to archive tables, a data warehouse, cheaper object storage, or historical storage systems. The goal: keep it available if needed, but do not let it keep hurting the hot system.

3. Delete or anonymize very old data. Some data eventually becomes unnecessary for the active product. Depending on business and legal requirements, you may permanently delete it or anonymize it, based on retention policy, privacy policy, and compliance obligations.

Why partitioning helps lifecycle so much

Suppose orders is partitioned by month, so each month is already a separate physical slice. Then you decide: keep the last 6 months hot, archive older months, delete very old partitions after retention expires. Because the data is partitioned by time, lifecycle operations become much easier. Instead of deleting millions of old rows one by one, you operate at the partition level. This is one of the strongest practical reasons why time-based partitioning is so common for large historical tables.

Lifecycle operations in MySQL (extra)

-- add the next month's partition (when using the catch-all partition)
ALTER TABLE orders_p REORGANIZE PARTITION pmax INTO (
  PARTITION p2026_04 VALUES LESS THAN ('2026-05-01'),
  PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);

-- move an old month out into its own table (archive it)
ALTER TABLE orders_p EXCHANGE PARTITION p2026_01 WITH TABLE orders_archive_2026_01;

-- remove an old month that has passed its retention period (fast, no row-by-row delete)
ALTER TABLE orders_p DROP PARTITION p2026_01;

(EXCHANGE PARTITION needs a standalone table with the same structure. DROP PARTITION permanently deletes the data in that partition, so archive first if you still need it.)

Why row-by-row delete is dangerous

Deleting old data row by row from a huge table is painful, because it can create:

• many row locks
• large transactions
• heavy undo/redo work
• more I/O
• operational instability

That is expensive and risky. A better approach is usually:

• batch delete slowly, or
• where supported and appropriate, operate via partition lifecycle management

-- batch delete: small pieces, repeat until 0 rows are deleted, pause between rounds
DELETE FROM orders
WHERE created_at < '2025-01-01'
LIMIT 1000;

That is why partitioning and lifecycle strategy are tightly connected.

A product example

A user signed up years ago, bought something once, and has not returned for a very long time. You must decide:

• should the full active profile remain in hot storage?
• should their heavy historical activity stay in the production DB?
• should some parts move to an archive?
• should some data be deleted?

A common practical pattern: keep only what is needed in hot storage, move rarely used heavy historical data elsewhere, and eventually delete or anonymize it based on retention and privacy policy. This is how storage is optimized before blindly jumping to sharding.

Delete vs anonymize

• Delete: the data is removed fully.
• Anonymize: keep the business or compliance record, but remove personally identifying fields.

For example, an order may need to exist for accounting or audit, but the user's identity must be removed or masked. Anonymization is useful when some record must remain but personal identity should not. It is often driven by privacy rules and product policy.

A sketch (extra)

UPDATE users
SET full_name = 'Deleted User',
    email     = CONCAT('deleted_', user_id, '@example.invalid')   -- still unique
WHERE user_id = 4;

The orders of that user stay for accounting, but they no longer point to a real identity. What exactly you must delete or keep depends on your legal requirements.

What decides the data lifecycle policy?

There is no universal number like "delete after X months". It depends on:

1. Business value of the data. Is it still useful to the product?
2. Access frequency. Is it still queried often?
3. Legal and compliance requirements. Do you need to retain it for audit or regulation?
4. Privacy policy. Are you allowed to keep it? Must you anonymize or erase it on request?
5. Storage and operational cost. Is keeping it in hot production storage worth the cost?

These factors decide whether data stays hot, moves warm, becomes cold, is archived, or is deleted.

7. Putting It Together

The scaling ladder

 1. Fix schema, queries, indexes          cheap, always first
        |
 2. Reduce waste: cache, pool connections, isolate heavy jobs
        |
 3. Scale up (bigger machine)              simple, limited
        |
 4. Read replicas                          more read capacity; accept lag
        |
 5. Partition big tables + data lifecycle  smaller hot data on one server
        |
 6. Shard                                  more write and storage runway; most complex

This is a guide, not a law. Steps 4 and 5 solve different problems and can come in either order. The point is that each step costs more complexity than the one before it, so you earn the right to use it by proving the earlier steps are not enough.

Which tool for which problem

An operations checklist (extra)

How the whole series connects (extra)

Deep Dive

Internal behaviour of replication. The primary records changes in its binary log. Each replica copies that log and applies the changes in order (with some parallelism). Because this happens after the primary has committed, replicas are slightly behind by design. Heavy writes on the primary (like a big backfill) make the replicas lag more.

Consistency trade-offs. A system with replicas is not strongly consistent for reads from replicas. It is eventually consistent: given time, replicas catch up. You choose per query whether stale reads are acceptable.

NULL handling (in this chapter's context).

Performance considerations

• Replicas spread read load; they do not speed up an individual slow query.
• A shard key that matches the most common query avoids cross-shard queries.
• Backfills and bulk deletes should be batched and throttled.
• Time-ordered ids (Snowflake, UUIDv7, ULID) keep clustered inserts append-like.
• Partitioning pays off only when queries include the partition column.
• Every extra node (replica, shard) adds monitoring, upgrades, and failure cases.

Database behaviour.

• The replication commands (SHOW REPLICA STATUS) and the partitioning syntax shown here are MySQL-specific. Other databases have the same ideas with different names and syntax.
• PostgreSQL also supports declarative partitioning and read replicas. Sharding is usually done by the application or by extensions, in most databases.
• Some databases (for example Google Spanner or CockroachDB) shard automatically, which moves much of the routing and rebalancing work away from the application.

Replication vs partitioning vs sharding (summary)

Real-world Use Cases

• Read replicas for history pages, dashboards, support tools, and reporting, with critical reads kept on the primary.
• Regional replicas so users in different regions get low-latency reads.
• Sharded order or event stores at very large scale, with a carefully chosen shard key.
• Distributed IDs for orders, events, and messages created by many services at once.
• Zero-downtime migrations such as adding payment_mode to a live payments table.
• Monthly partitions for orders, logs, and payments, with old months archived or dropped.
• Retention jobs that archive old rows to cheaper storage and anonymize inactive accounts.
• Backfills run in the background for days, throttled by replica lag.

Review Tips

Interviewers commonly ask:

• "What is the first step when a database becomes slow?" — Optimize first: queries, indexes, schema. Then scale.
• "Why do reads usually become the first scaling problem?" — Most products are read-heavy (feeds, dashboards, history, lookups).
• "What are read replicas, and what is replica lag?" — Copies of the primary used for reads. Lag is the delay before a replica applies the latest change.
• "Can you read your own write from a replica?" — Not always. Route critical or just-written reads to the primary, or check lag.
• "Replication vs sharding?" — Replicas copy (reads). Shards divide (storage and writes).
• "Partitioning vs sharding?" — Partitioning: one DB, many internal slices. Sharding: many DBs, data spread.
• "What makes a good shard key?" — Even distribution, matches query patterns, avoids hot shards, stays stable.
• "What is shard skew?" — One shard gets far more data or traffic than the others. For example, 85% of traffic from one country.
• "Why do sharded systems need distributed IDs?" — Per-shard counters would collide.
• "UUID vs Snowflake vs ULID?" — UUID: easy, 128-bit, random versions hurt index locality. Snowflake: compact, time-ordered, needs coordination and careful clocks. ULID: time-ordered, 128-bit, string-friendly.
• "How do you add a column to a huge live table safely?" — Expand (nullable), backfill in batches, switch reads and writes, contract later.
• "Why are backfills dangerous?" — They create a lot of writes, locking, I/O, and replication lag.
• "Does partitioning always make queries faster?" — No. Only queries that include the partition column (pruning).
• "How do you delete five-year-old data from a huge table?" — Not with one giant delete. Drop partitions, or batch deletes slowly.
• "Delete vs anonymize?" — Delete removes the data. Anonymize keeps the record but removes identity.

Common Mistakes to Avoid

Mistake 1: Scaling before optimizing

Why it happens: Adding machines feels like a quick answer.

Result: a bad schema and bad queries, multiplied across more machines.

Correct: Fix queries, indexes, and schema first.

Mistake 2: Treating replicas as free capacity

Why it happens: More machines look like more speed.

Result: lag, stale reads, more failure modes, more cost.

Correct: Accept the trade-offs on purpose, and monitor lag.

Mistake 3: Sending every read to replicas

Why it happens: "Replicas are for reads."

Result: a user saves something and immediately sees the old value.

Correct: Route critical and just-written reads to the primary.

Mistake 4: Using replicas as backups

Why it happens: "The data is already copied."

Result: a wrong DELETE or DROP replicates to every copy.

Correct: Take real backups and test restores.

Mistake 5: Sharding too early

Why it happens: It sounds like the "real" way to scale.

Result: huge complexity for a problem an index or a replica would have solved.

Correct: Exhaust the cheaper steps first.

Mistake 6: Sharding too late

Why it happens: "We will do it when it breaks."

Result: a rushed, risky migration on a system already at its limit.

Correct: Plan ahead using growth numbers and monitoring.

Mistake 7: Picking a skewed shard key

Why it happens: country looks natural.

Result: one hot shard behaves like the old primary.

Correct: Choose a key that spreads data and traffic evenly and matches the main queries.

Mistake 8: Ignoring cross-shard queries

Why it happens: Joins worked before.

Result: application-side joins, more latency, more failure cases.

Correct: Design the shard key so most queries stay on one shard.

Mistake 9: Using identical AUTO_INCREMENT sequences across shards

Why it happens: It is the default.

Result: the same id can appear on different shards.

Correct: Use distributed ids, configured offsets/increments, or a shard-prefixed scheme, and enforce the intended global identity rule.

Mistake 10: Using random UUIDv4 as the primary key of a huge clustered table

Why it happens: It is the easiest UUID.

Result: scattered inserts, page splits, bigger indexes.

Correct: Use time-ordered ids (Snowflake, UUIDv7, ULID), and store UUIDs as BINARY(16).

Mistake 11: Reusing a machine id, or ignoring clock problems, in Snowflake-style ids

Why it happens: Configuration is copied between servers.

Result: duplicate ids, or ids that go backward after a clock change.

Correct: Give each generator a unique machine id and handle clock rollback.

Mistake 12: Adding a NOT NULL column (or dropping or renaming one) in a single step

Why it happens: It is one line of SQL.

Result: old code breaks.

Correct: Expand, backfill, switch, contract.

Mistake 13: Backfilling a huge table with one giant UPDATE

Why it happens: It is the simplest code.

Result: long locks, huge transactions, replica lag.

Correct: Small batches, throttled, monitored, pausable.

Mistake 14: Running an ALTER TABLE with a long transaction open

Why it happens: Nobody checked.

Result: the ALTER waits for the metadata lock, and all new queries queue behind it.

Correct: Check for long transactions first and set a short lock wait timeout.

Mistake 15: Partitioning by a column the queries do not use

Why it happens: Date seemed like the obvious choice.

Result: queries still touch every partition.

Correct: Partition by the column most queries filter on.

Mistake 16: Forgetting the partition key rules in MySQL

Why it happens: The table worked before.

Result: error because the primary key or a unique key does not include the partition column, and foreign keys are not allowed.

Correct: Include the partition column in every unique key, and plan without foreign keys.

Mistake 17: Keeping everything forever in the hot database

Why it happens: Storage feels cheap.

Result: bigger scans, slower maintenance, earlier scaling pressure.

Correct: Define hot, warm, and cold data, and a retention policy.

Mistake 18: Deleting old data row by row in one huge transaction

Why it happens: DELETE ... WHERE created_at < ... looks simple.

Result: locks, undo growth, instability.

Correct: Drop partitions, or delete in small batches.

Key Takeaways

• Scaling means increasing capacity as usage grows. One machine always has limits on CPU, memory, disk I/O, network, and connections.
• Reads are usually the first pain, because most products are read-heavy.
• Optimize first (schema, queries, indexes), then scale. Scaling a bad design only multiplies the mess.
• Vertical scaling (a bigger machine) helps, but it has a ceiling.
• Protect the primary. Writes are usually more sensitive than many reads.
• Primary + read replicas: writes go to the primary, most reads go to replicas. Replicas replay the primary's changes.
• Replica lag means replica reads can be slightly stale. Some reads must still go to the primary.
• A replica is not free capacity and it is not a backup. It costs infrastructure, adds failure modes, and the primary still feeds it.
• Replicas can also be placed near users to cut read latency.
• Replicas scale reads. They do not solve write or storage limits. That is what sharding is for.
• Sharding splits the data itself across databases: replicas copy, shards divide. It gives more storage and write runway, at the price of routing logic, painful rebalancing, cross-shard queries, and heavy operations.
• The shard key decides everything. A good one distributes evenly, matches queries, avoids hot shards, and stays stable. A bad one causes shard skew and is expensive to change.
• Plan sharding before the ceiling hits.
• Sharded and multi-service systems need distributed IDs: unique, fast, preferably time-sortable, compact, and hard to guess.
• UUID: easy and unique but 128-bit, and random versions hurt index locality. Snowflake-style: 64-bit, time-ordered (timestamp + machine + sequence), needs careful clocks and machine ids. ULID: 128-bit, time-ordered, string-friendly.
• Zero-downtime schema change means backward compatibility. Use expand, backfill, switch, contract.
• The backfill is the real danger: do it in throttled, monitored batches, and expect it to take days at large scale.
• Additive changes are safer. Drops, renames, type changes, and sudden NOT NULL are riskier.
• Partitioning splits one logical table into physical slices inside one database. It helps when queries match the partition column, and does nothing for queries that do not.
• Partitioning = one DB, many slices. Sharding = many DBs.
• Data lifecycle: keep hot data in the main database, archive warm and cold data, and delete or anonymize expired data. Partitions make this easy: drop or exchange a partition instead of deleting row by row.
• The policy depends on business value, access frequency, legal and compliance rules, privacy policy, and cost.