The Index Existed. SQLite Picked Another One and Blew the D1 Free Tier

On 10 September 2026 Hot Deal Sheet went over the D1 free tier. The free plan allows 5 million rows read per day across the whole account. That day the account read 5,385,422. Nine days earlier this would have been a line on a usage graph. Since 1 September 2026, D1 queries on a free account fail once the account is over, until the counter resets at midnight UTC.

I expected the cause to be a missing index. The project notes already had a rule that every column used to filter or sort gets an index, and this query followed it. The cause was an index that existed and was not used.

D1 bills the rows it scans

This is the rule that makes the rest of the story expensive. D1 counts rows read, not rows returned. A query that walks 457 rows to hand back 6 is billed for 457. On a site where one page view runs a handful of queries, the gap between those two numbers is the budget.

The query in question fills the “related deals” box on every deal page: up to six other live deals from the same store, newest first, excluding the one you are looking at. A query builder writes it, but the SQL comes out like this:

SELECT ... FROM deals
JOIN sources ON sources.id = deals.source_id
WHERE deals.merchant = ? AND deals.is_expired = 0 AND deals.id != ?
ORDER BY deals.posted_at DESC
LIMIT 6

There was an index on (merchant, posted_at). There was also one on (is_expired, posted_at), used by the main list. Both can return rows already sorted by posted_at, so neither needs a separate sort step.

SQLite chose the second one. It walked live deals in date order and checked merchant on each row until it had six matches. That would be fine if live deals were rare, but only about 7% of deals were expired at the time, so “live deals in date order” meant 6,773 rows. A store with few recent deals forced a long walk before the sixth match turned up. Averaged across real traffic, the query read 457 rows for every 6 it returned.

My best reading of why SQLite chose that way is that it had no statistics to tell it merchant narrows the set far more than is_expired does. Two equality filters on two indexes look about the same to a planner that is guessing, and the index it picked also happened to serve the sort. I did not run ANALYZE to test that theory. I fixed the index instead.

Put every equality filter before the sort column

The fix was a migration with no code change:

DROP INDEX deals_merchant_posted_idx;
CREATE INDEX deals_merchant_posted_idx
  ON deals (merchant, is_expired, posted_at);

The same change went into the category index, which had the same shape. With both equality columns ahead of the sort column, the index takes the planner straight to “this store, live, newest first”, and there is nothing left to filter. EXPLAIN QUERY PLAN against production showed the new index in use, and rows read per call went from 457 to 6. Projected daily reads for the account dropped from over five million to about 750,000.

The rule I wrote into the project notes afterward is narrower than “index your filter columns”, because I had done that. It is this: an index that exists is not an index that is used. If a query has equality filters and an ORDER BY, every equality column goes in front of the sort column in one index. Then check the plan, because the plan is the only thing that tells you which index the database actually picked. Cloudflare’s index guide also recommends running PRAGMA optimize after creating an index, which I had not done.

Finding it: two datasets that disagree

The query that did this was not the slow one or the complicated one. It was a lookup of six rows for a sidebar. I found it through D1’s GraphQL analytics, and the first thing I learned was that two datasets answer “how many rows did I read on 10 September” differently.

Dataset	Rows read, 10 Sep	Good for
d1QueriesAdaptiveGroups	4,689,737	Ranking queries to find the culprit
d1AnalyticsAdaptiveGroups	5,385,422	The total the limit is judged on

The dataset broken down by query is sampled and undercounts. If I had looked only at it, I would have seen a day under five million and wondered why the account was blocked. The dataset that totals each database is the one that matches the limit, and the limit is per account, so with more than one database you add them up.

The query breakdown is still the right tool for the hunt. Sort it by rows read, compare each query’s rows read to the LIMIT it asks for, and the one with a ratio in the hundreds is the problem. The related deals query alone was about 4.5 million of that day’s reads.

The eleven hours were my fault, not the index’s

The index fix went in at 13:05 UTC. What followed is the more expensive lesson. All times are UTC.

Time	What happened
12:32	Email alert: daily D1 limit exceeded. Queries kept working.
13:05	Index migration applied. Reads per call 457 → 6.
13:19	Site returns 200, collection running.
14:54	Bought Workers Paid, charged $2.42.
14:57	Three collection runs succeed.
15:00	Cancelled the plan so it would not renew next month.
15:00 to 23:38	Every D1 query fails. Site returns 500.
23:41	Bought the plan again. Recovered at once.

I bought the paid plan as insurance, watched three minutes of healthy runs, and cancelled to avoid the next month’s charge. Cloudflare’s billing docs say cancellations follow the same timing as downgrades: “service continues until the end of the billing period.” That sentence describes billing. It did not describe the D1 limit. The account was already over five million rows for the day, and the moment the subscription showed as cancelled, the free plan check applied again and every query failed. The outage lines up exactly with the window between cancelling and buying again.

There is no refund for the unused part of a month, so cancelling early gains nothing. If you buy the paid plan to get through a bad day, cancel it just before the renewal date, and only after a clean 24 hours of usage under the free limits. Docs quoted here were read on 14 October 2026.

The index was a mistake of one line, fixed about half an hour after the alert once I had the right dataset open. The outage came from reading a billing sentence as a description of a runtime check, and that cost eleven hours. A plan change on an account with a usage limit deserves the same care as a deploy: watch the error rate for the next ten minutes.