📉 Small 'CREATE INDEX' Statements Saved Us From a Paid Tier

👋 Introduction

I was using Cloudflare D1 with my Cloudflare Worker. There was just one table, billrequests, created and used by the worker. Everything had been running fine for a while - until one day, I got a mail from Cloudflare saying:
❌ "Your D1 rows_read limit has been exceeded" - along with a friendly nudge to upgrade the plan.

🕵️ The Problem

That mail didn't make sense to me at first. My table wasn't even big. So I dug in:

The free tier allows up to 5M reads per day.
My table had only ~75,840 rows.
My worker had two APIs and a cron trigger, all depending on this one table.
The cron trigger ran every 10 minutes and pulled records in a particular status.

None of that sounded like something that should blow past 5 million reads a day. So, what was actually happening?

🧩 The Analysis

I opened up the Cloudflare dashboard to see exactly which queries were responsible for the reads. Here's what it showed:

Query	Used by	Reads
`UPDATE ... SET status = ? WHERE token = ?`	`updateProcessStatus` (bill processing + cron)	8 × 75.84k = 606.7k
`UPDATE ... r2key, shortlink ... WHERE token = ?`	`updateShlinkMetaData` (shortlink step)	4 × 75.84k = 303.35k
`UPDATE ... smsid, sendtosmsgatewayat ... WHERE token = ?`	`updateSMSSentMeta` (after SMS sent)	2 × 75.84k = 151.68k
`SELECT ... WHERE status = 5 OR 12 OR 13`	`getIncompletedBillRequests` (cron, every 10 min = 144x/day)	up to 10.9M/day

🔍 The Root Cause

Once I saw the numbers laid out like that, it clicked:

There was no index on the token or status column - so every single per-bill update scanned all 75.84k rows.
One bill request needed ~4 such updates - that's ~300k+ reads for a single bill.
On top of that, the cron job ran every 10 minutes and full-scanned the table every single time.

Nothing was wrong with the logic. The table was just too small to "feel" slow, but too unindexed to stay cheap.

💡 The Fix

The fix turned out to be refreshingly simple: add indexes on the columns that were actually being filtered on.
I created a migration file, src/migrations/202609020900_add_indexes.sql, with:

CREATE UNIQUE INDEX idx_billrequests_token ON billrequests(token);
CREATE INDEX idx_billrequests_status ON billrequests(status);
CREATE INDEX idx_billrequests_clientuid_receivedat ON billrequests(clientuid, requestreceivedat); (for report endpoints)

✅ Expected Result

After adding the indexes, the difference was massive:

Per-token update: 75,840 rows read → 1-2 rows.
Cron status select: 75,840 rows → only matching rows (max 50).
Daily D1 reads: 2-5M → a few thousand. Well within the free limit.

🪄 Takeaway for New D1 / SQLite Users

If you're new to Cloudflare D1 (or SQLite in general), here are a few quick tips this incident taught me:

A small table doesn't mean small reads - every filtered query without an index scans the whole table.
Any column used in a WHERE clause on a frequent query deserves an index.
Cron jobs are sneaky - they run silently and often, so an unindexed query there adds up fast.
Check your dashboard's query breakdown before assuming you need to upgrade a plan.

👋 Final Thoughts

It's easy to assume that hitting a usage limit means you've outgrown the free tier. Sometimes, it just means your queries haven't been introduced to indexes yet.

A five-minute migration saved us from an unnecessary upgrade - and taught me to always check the "why" behind a limit before reaching for the "upgrade" button.