One Query, One Million Reads a Day

The homepage showed three numbers: 23, 13 and 6. Active deals, deals with a price drop, deals expiring soon.

To render those three numbers, it read about 7,500 documents from Firestore. Every single time.

This is the story of how that cost me a million reads a day for two months, why my first two explanations were wrong, and how my own inbox cleanup buried the warnings.

The bill nobody looked at

I run a small deals site on the side: Astro with server-side rendering on Firebase, products in Firestore, a few scheduled functions that pull in feeds. It earns pocket money at best. It should cost about nothing.

On September 4, during a sweep of all my Google Cloud projects, an agent flagged something odd: 34 million Firestore reads in thirty days, against 41 thousand writes. For a site with a few thousand products and modest traffic, that ratio is absurd.

The actual money was small. The billing report for the first thirteen days of September said €7.32, of which €6.35 was Firestore reads, close to 90% of everything on that billing account. Not a disaster. But something was clearly reading a lot more than it should, and I wanted to know what.

Theory one: the cron job

The first suspect was obvious. There was a statistics function that scanned a sample of the product collection, and scheduled jobs that run all day. Roughly 20,000 reads per run times sixty runs a day would explain a lot. That same analysis even looked at the SSR site and ruled it out: some hours with plenty of page requests had low reads, some with few requests had high reads.

So I fixed the statistics function. Instead of fetching a thousand documents to compute an average, it now used Firestore’s aggregation queries, average() and count(). Clean change, verified, merged on September 6.

It made no difference whatsoever. That function had been called exactly once in thirty days.

Theory two: the number I made up

While verifying that fix, I caught myself in something more embarrassing.

All week I had been talking about “roughly 14,700 products”. It was even in the issue and the PR notes. And I had never measured it. I had inferred it from the floor of the hourly read graph: about 14,700 reads an hour, so one full scan per hour of a collection that size, right?

The collection held 2,784 products.

So the floor was not one scan an hour. It was about five times the entire collection, every hour. A theory built on a number that came from the very thing I was trying to explain. Circular reasoning, with a commit message as evidence.

The pattern in the hourly totals

On September 14 I finally looked at the hourly totals properly instead of the graph. They were suspiciously round:

7,493   14,942   22,542   29,979   44,956   59,608

Multiples of about 7,500. Whatever was reading, it read about 7,500 documents at a time.

Matching per-minute reads against request logs made it obvious:

Time (UTC)	Homepage requests	Reads
00:30	3	22,458 (3 × 7,486)
08:27	1	7,490
Feed cron minutes	0	58–86

One homepage render, 7,500 reads. The cron jobs I had been blaming were reading a few dozen.

Three numbers, three unbounded queries

The homepage called a helper that built those three counters. Simplified, it looked like this:

// before: three unbounded queries
// all products: 2,825 docs
const all = await getDocs(query(collection(db, 'products')));
// with a previous price: 2,439 docs
const priced = await getDocs(query(collection(db, 'products'),
  where('priceOld', '!=', null)));
// with an end date: 2,197 docs
const expiring = await getDocs(query(collection(db, 'products'),
  where('validTo', '!=', null)));
// ...then filter on status === 'active' in memory

Three queries, about 7,500 documents, filtered down in memory to the 23 products that were actually active. The other 2,800 were old, inactive deals that nothing ever cleaned up.

The page was not cached either: cache-control: private, every request a miss, about nine seconds to first byte. And who was requesting the homepage 130 to 190 times a day? Mostly not people. Bingbot, AdSense’s crawler, scanners pretending to be Chrome 120, a suspicious number of “iPhones” running iOS 13, curl, and one IP address that came by 45 times in a week. About one in ten of those requests was a POST.

It also explained the error rate I had not been looking at. The function had 256 MiB of memory, and loading 7,500 documents blew straight through that. In one week: 699 responses with a 500 and 188 with a 503. The cost bug was also an availability bug.

One query

The fix was a small diff, mostly deletions:

// after: ask for what you show
const active = (await getDocs(query(collection(db, 'products'),
  where('status', '==', 'active')))).docs.map((d) => d.data());

// price drops and expiring deals are derived from `active`

The homepage went from about 7,500 reads to 58, and from 9.4 seconds to 1.4. In the first twenty hours: around 3,000 reads in total, projected at about 3,700 a day. Zero homepage 5xx responses, zero out-of-memory logs.

From a million a day to under four thousand, with one where clause.

Twenty good hours prove little, so I checked again on October 2. The baseline had settled at about 151 reads an hour, and since late September the site has been reading roughly 23,500 documents a week, comfortably inside the free tier. The only spike was an alert that morning at just over 10,000 reads in an hour. That was me: a backfill that scanned the collection three times, plus smoke tests in the same hour. The alert did exactly what it was supposed to do, and the culprit was in the mirror.

Those same billing numbers also put a price on the bug: Firestore reads were €17.29 of a €22.68 August invoice. Not a fortune. But for a site that should cost nothing, three quarters of the bill went to three counters.

A follow-up the next day made caching opt-in per page (public, max-age=60, s-maxage=600 for the homepage), returned 405 to anything that is not a GET or HEAD, and switched to getDocsFromServer(). During one failure, getDocs() had quietly fallen back to an empty local cache; with getDocsFromServer() a server problem is an explicit error instead of an empty page. That one deserves its own post someday.

The warnings I never saw

Here is the part that stung.

There was a budget on that billing account. A tiny one, €1, but it did its job. It had fired at 50, 90 and 100 percent in early July, again in early August, and again in early September. Nine emails in total, all delivered, all on time.

I never read a single one. They landed in an inbox with tens of thousands of unread messages, which is exactly the problem the Hub’s mail triage was built to solve. In early September that triage created a filter to move Google Cloud and Firebase notifications into a “Robots/Platform” label, and a later cleanup round marked that label as read. The budget mails went along with the rest. The filter treated “your budget has been exceeded” exactly like “your weekly usage summary is ready”.

The filter now excludes anything with “of budget reached” in the subject. The project has its own €5 budget with alerts at 50, 90 and 100 percent, and there is a monitoring alert when Firestore reads exceed 10,000 an hour; normal is now about 150. The new budget fired almost immediately, on the spend from earlier that month, and this time I actually saw it.

What I took from it

None of this was hard to fix. Everything was hard to see.

• Read what you render. Three counters do not need three collection scans. If you show the active deals, ask for the active deals. And if all I need is a number, I should ask for a count.
• Bots are traffic. An uncached SSR page that is cheap for ten humans a day is not cheap for two hundred crawlers.
• Don’t let a theory feed on its own symptom. My “14,700 products” came from the read graph I was trying to explain. Measure the thing directly.
• Look for the quantum. Round multiples in a metric are a fingerprint. Find what costs exactly that much once.
• Audit your filters. Automation that hides noise will happily hide signal too. Mine filed three months of warnings away, politely, with a label.

One query. One million reads a day. And nine emails telling me about it, in a folder I never opened.