Imagine you run a messaging app used by billions of people.
Every day, people upload photos, videos, and text as Status updates. But there is one important rule: a Status should disappear after about 24 hours.
Now imagine you have to clean up billions of expired Statuses every day.
Your first thought might be:
“Easy. Just run a query that deletes everything older than 24 hours.”
Something like:
DELETE FROM statuses
WHERE expires_at < NOW();
Simple, right?
Unfortunately, at WhatsApp's scale, this can be a terrible idea.
A giant delete can put enormous pressure on the database, causing slow queries, high disk usage, replication delays, locks, and even outages.
So how do large systems actually approach this problem?
Let's understand it from the ground up.
1. First: What Is a WhatsApp Status?
A Status is basically a temporary piece of content.
For example:
- Alice posts a photo at 10:00 AM.
- Bob posts a video at 2:00 PM.
- Carol posts some text at 8:00 PM.
After roughly 24 hours, these Statuses are no longer supposed to be visible.
From a database perspective, you might store something like:
| status_id | user_id | content | created_at | expires_at |
|---|---|---|---|---|
| 101 | Alice | photo.jpg | 10:00 | 10:00 next day |
| 102 | Bob | video.mp4 | 14:00 | 14:00 next day |
| 103 | Carol | "Hello!" | 20:00 | 20:00 next day |
The database now has a simple job:
Find expired records and remove them.
But there's a catch.
At massive scale, there can be an enormous number of expired records.
2. Why Can't We Just Delete Everything at Once?
Suppose there are 1 billion expired records.
You might write:
DELETE FROM statuses
WHERE expires_at < NOW();
The database now has to find and delete a huge number of rows.
That can cause several problems.
Problem #1: Huge amount of work
Deleting a row isn't always as simple as removing a line from a file.
The database may need to:
- Locate the row.
- Modify indexes.
- Write transaction information.
- Update storage structures.
- Replicate the changes.
- Eventually reclaim the space.
Multiply that by billions.
That's a lot of work.
3. The Database Has Other Jobs Too
This is perhaps the most important idea for beginners.
Your database isn't sitting around waiting for the cleanup job.
While the cleanup system is deleting old Statuses, users are still:
- uploading new Statuses,
- viewing Statuses,
- sending messages,
- opening chats,
- checking contacts.
The database needs to handle those requests too.
Think of a restaurant.
Imagine a restaurant has one kitchen.
During dinner, hundreds of customers are ordering food.
Now someone says:
“Before serving any more customers, let's completely clean the kitchen.”
The kitchen might become extremely busy cleaning instead of cooking.
A massive database deletion can create a similar problem.
4. The Basic Solution: Delete in Small Batches
Instead of deleting one billion rows in one operation, divide the work into small pieces.
For example:
DELETE FROM statuses
WHERE expires_at < NOW()
LIMIT 10,000;
Then repeat.
Conceptually:
Delete 10,000
↓
Delete 10,000
↓
Delete 10,000
↓
Delete 10,000
↓
...
Now the database gets many small jobs instead of one enormous job.
This is called batch deletion.
5. Why Batches Are Better
Suppose you need to remove 100 million records.
Instead of:
100,000,000 rows
↓
ONE MASSIVE DELETE
↓
Database suffers
you could do:
10,000 rows
10,000 rows
10,000 rows
10,000 rows
...
Each transaction is relatively small.
That means:
- transactions finish faster,
- locks are held for less time,
- memory pressure is lower,
- replication can keep up more easily,
- other queries get opportunities to run.
The goal isn't merely to delete data.
The goal is to delete data without disturbing the rest of the system.
6. But There's Another Problem
You might now say:
“Fine. We'll delete 10,000 rows at a time.”
But how do we find those 10,000 expired rows?
Suppose the table looks like this:
statuses
--------------------------------
id
user_id
content
created_at
expires_at
If expires_at isn't indexed, the database may have to scan a huge portion of the table to discover which rows are expired.
That's expensive.
So we usually want an index.
For example:
CREATE INDEX idx_status_expiry
ON statuses(expires_at);
Now the database can efficiently find records whose expiration time has passed.
Think of an index like the index at the back of a textbook.
Without the index, you might have to read every page to find a topic.
With the index, you can jump closer to where the information lives.
7. Don't Delete Randomly — Delete in Order
There's another optimization.
Suppose expired records have IDs:
1001
1002
1003
1004
...
Instead of repeatedly asking:
“Give me any 10,000 expired rows.”
we can process them in a predictable order.
For example:
DELETE FROM statuses
WHERE expires_at < NOW()
ORDER BY expires_at
LIMIT 10,000;
Or, in some database designs, we can use an ID/cursor-based approach.
Conceptually:
Start at record 0
↓
Process 10,000
↓
Remember where we stopped
↓
Process the next 10,000
↓
Repeat
This is often called cursor-based processing or keyset pagination.
8. The Secret Weapon: Don't Wake Up Every Millisecond
Here's another beginner mistake.
You might create a worker that constantly asks:
"Anything expired?"
"Anything expired?"
"Anything expired?"
"Anything expired?"
...
That's unnecessary.
Instead, a cleanup system can work on a schedule.
For example:
12:00 → clean expired data
12:01 → clean expired data
12:02 → clean expired data
...
Or it can continuously process a queue at a controlled rate.
The important concept is backpressure.
9. What Is Backpressure?
Backpressure simply means:
Slow down when the database is under heavy load.
Imagine your cleanup worker can delete 100,000 records per second.
But the database is already extremely busy.
Instead of saying:
“I must delete 100,000 per second!”
the cleanup system can say:
“The database is busy. I'll reduce my deletion rate.”
For example:
Database healthy
→ delete faster
Database busy
→ delete slower
Database overloaded
→ pause cleanup temporarily
This is extremely useful in large distributed systems.
Cleanup is important.
But serving users is usually more important.
10. Even Better: Use a Queue
Instead of having one giant cleanup process constantly scanning the database, you can use a queue.
Imagine every Status gets an expiration event.
When Alice creates a Status:
Alice creates Status
↓
Status expires in 24 hours
↓
Create expiration task
↓
Queue
Later:
Queue
↓
Worker
↓
Delete Status
You can have many workers processing the queue.
For example:
┌── Worker 1
Queue ───────────┼── Worker 2
├── Worker 3
└── Worker 4
Now cleanup can scale horizontally.
Need more capacity?
Add more workers.
Need less capacity?
Remove workers.
11. But What If a Worker Crashes?
Distributed systems assume something will eventually fail.
A worker might crash halfway through its work.
That's why cleanup jobs should generally be idempotent.
Don't worry about the fancy word.
It basically means:
Running the same cleanup operation again should not cause a disaster.
For example:
Delete Status 123
If Status 123 is already gone, trying to delete it again shouldn't break the system.
This makes retries much safer.
12. A Very Important Idea: Soft Delete vs Hard Delete
There are actually two different meanings of "delete."
Soft delete
Instead of physically removing the row, mark it as deleted:
deleted = true
The application simply ignores deleted records.
Hard delete
Actually remove the database record.
For example:
DELETE FROM statuses
WHERE id = 123;
Large systems may sometimes use a combination of both approaches.
For example:
Status expires
↓
Immediately stop showing it
↓
Physical cleanup happens later
This is useful because the user-facing requirement is:
“Don't show expired content.”
It doesn't necessarily mean:
“The physical database bytes must disappear at exactly 24:00:00.”
Those are two different requirements.
13. This Difference Is Huge
Suppose a Status expires at:
10:00:00
The application can immediately treat it as unavailable:
expires_at <= current_time
So the user can't see it anymore.
The physical deletion might happen at:
10:02
10:05
10:20
11:00
depending on system load.
That's often perfectly acceptable.
This gives the cleanup system flexibility.
14. What About Photos and Videos?
There's another important detail.
The database might not actually contain the entire photo or video.
A database record might simply contain metadata:
status_id
user_id
storage_location
created_at
expires_at
The actual video could live in an object-storage system.
So deleting a Status may involve multiple steps:
Database record
+
Media object
+
Caches
+
Indexes
+
Metadata
You need to make sure all of these eventually get cleaned up.
15. Don't Delete Everything at the Exact Expiration Time
Here's a subtle but important design idea.
Imagine 100 million people uploaded Statuses between 10:00 AM and 10:01 AM.
Twenty-four hours later, a huge number of objects become eligible for deletion at roughly the same time.
If your system tries to delete all of them immediately, you get a thundering herd.
Instead, you can spread the cleanup work out.
For example:
10:00 → 100,000 deletions
10:01 → 100,000 deletions
10:02 → 100,000 deletions
...
The work is distributed over time.
This makes the system much smoother.
16. Partitioning Can Help Too
At very large scale, a single enormous table can become difficult to manage.
One technique is partitioning.
Imagine splitting Status data by time:
statuses_2026_08_20
statuses_2026_08_21
statuses_2026_08_22
statuses_2026_08_23
Now old data is grouped together.
Instead of deleting billions of individual rows one by one, a database may be able to remove or detach an entire old partition.
Conceptually:
Old partition
↓
No longer needed
↓
Drop partition
↓
Large amount of data removed efficiently
This can be dramatically cheaper than row-by-row deletion, depending on the database architecture.
However, partitioning isn't a magic solution. It has its own design and operational trade-offs.
17. A Realistic Architecture
A simplified large-scale design might look like this:
User creates Status
│
▼
Status Service
│
┌─────────┴─────────┐
▼ ▼
Database Media Storage
│
▼
Expiration Scheduler
│
▼
Cleanup Queue
│
┌─────┼─────┐
▼ ▼ ▼
Worker Worker Worker
│ │ │
└─────┼─────┘
▼
Batch Delete
The key idea is that expiration and physical deletion are separate concerns.
18. What Happens If the Database Is Overloaded?
A good cleanup system should be able to say:
“Not now.”
For example:
Cleanup worker
↓
Check database health
↓
Is database overloaded?
/ \
Yes No
↓ ↓
Slow down Continue
or pause deleting
This is a crucial production principle:
Background work should be a good citizen.
User-facing traffic gets priority.
Cleanup can wait.
19. Monitoring Is Just as Important
You don't want to build the cleanup system and forget about it.
You need metrics.
For example:
Expired items waiting
50 million
Cleanup rate
200,000 items/sec
Database CPU
45%
Queue size
2 million jobs
Cleanup latency
Average: 3 minutes
Now you can see whether the system is keeping up.
20. What If Cleanup Falls Behind?
Suppose:
New expired Statuses:
1 billion/day
Cleanup capacity:
800 million/day
You're losing the race.
The queue will grow forever.
So the system needs to scale.
Possible solutions include:
- increase worker count,
- increase batch size carefully,
- optimize indexes,
- partition the data,
- move cleanup to replicas or specialized stores where appropriate,
- spread work over a longer window,
- improve storage architecture.
The important thing is to measure the system instead of guessing.
21. The Beginner's Mental Model
You don't need to understand every database engine to understand the core idea.
Think of expired Statuses as garbage.
Imagine a city produces billions of pieces of garbage every day.
You wouldn't wait until the end of the month and tell one truck:
“Collect everything.”
You'd probably use:
- many trucks,
- scheduled pickups,
- different neighborhoods,
- manageable loads,
- monitoring,
- extra capacity during busy periods.
Database cleanup works in a similar way.
Don't create one giant cleanup operation. Build a controlled garbage-collection system.
22. The Big Lessons
If you remember only a few things, remember these:
1. Don't perform gigantic deletes
Break the work into batches.
10,000
10,000
10,000
...
2. Make finding expired data cheap
Indexes on expiration-related fields can be important.
3. Don't block user traffic
Cleanup should run as background work.
4. Use queues and workers when appropriate
They allow cleanup to scale horizontally.
5. Use backpressure
When the database is busy, slow down.
6. Consider partitioning
Time-based data can sometimes be efficiently managed through partitions.
7. Separate logical expiration from physical deletion
A Status can stop being visible immediately while physical cleanup happens later.
8. Make cleanup retry-safe
Failures are normal in distributed systems.
9. Monitor everything
Know how much data is waiting and how quickly you're processing it.
Final Picture
So, how can a service like WhatsApp deal with billions of temporary Statuses?
The answer isn't one magical SQL query.
It's an architecture.
Instead of:
Billions of expired records
↓
ONE DELETE
↓
💥 Database
you want something closer to:
Expired Statuses
│
▼
Cleanup Queue
│
┌──────────┼──────────┐
▼ ▼ ▼
Worker Worker Worker
│ │ │
└──────────┼──────────┘
▼
Small Batches
│
▼
Database / Storage
The trick is not to make the database delete billions of things instantly.
The trick is to make the system continuously and safely remove expired data at a controlled rate.
That's one of the fundamental lessons of large-scale backend engineering:
At massive scale, the question isn't just “Can we do this?” It's “Can we do this continuously without hurting everything else?”
Top comments (0)