Studies Multiverse is a free study-abroad directory: 31,000 institutions in 224 countries, course lists, and tools for costs, post-study work rules and scholarships. It runs on Cloudflare Workers and D1 (SQLite), server-rendered, in 7 languages.
Last week the D1 dashboard showed 5 billion rows read in a day. Real visitors were a tiny fraction of that.
What was reading them
- Meta's crawlers (meta-externalagent, meta-webindexer) were walking every directory listing in all 7 languages.
- Applebot and others found the filtered URLs: every subject x city x page combination is a new address.
- 50,000 course pages each ran a "same course at other places" query that read about 2,600 rows.
Three fixes
-
Make the index usable. My list query had optional filters written as
(?2 IS NULL OR e.kind = ?2). SQLite can't use an index through that, so every page counted the whole table. A fast path that builds the WHERE clause from only the filters in use dropped the main listing from ~30,000 rows to a few hundred. -
Add the missing indexes. Two composite indexes on the course table,
(country_slug, search_key)and(country_slug, entity_id), turned the heaviest query into an index lookup. - Don't serve crawlers what they can't index. Filtered lists are noindex and canonical to the plain list anyway, so crawlers now get a 301 to the plain list. Course detail pages (noindex for now) return a short 403 to crawlers. People see everything as before.
Takeaways
- Check rows read, not request counts. A cheap-looking page can be the expensive one.
- "Optional filter" SQL patterns quietly disable indexes. Run
EXPLAIN QUERY PLAN. - If a URL is noindex, crawlers have no reason to fetch it at all.
The site: https://studiesmultiverse.com. Feedback welcome.
Top comments (1)
The optional-filter pattern disabling the index is the kind of thing that only shows up on a bill, so thanks for writing it down with the row numbers.
Two things I would add from the same fight on a free tier:
Put a cache in front of the render, not just a redirect. Server-rendered pages on Workers can go through the Cache API or a cache rule with a short TTL keyed on path plus language, so a second crawl of the same URL never reaches D1 at all. Crawlers re-fetch the same listing far more often than humans do, and for a directory that changes slowly even a 10 minute TTL collapses most of the read volume. It helps real visitors too, which the 403/301 path does not.
Precompute the "same course elsewhere" set. A 2,600 row scan per page view for data that changes rarely is really a write-time job: store the related ids (or a ready-made JSON blob) on the course row when you ingest, and the page becomes one row read. Denormalising hurts when the source changes often, but a directory ingest is exactly the case where it does not.
One measurement note that saved me guessing: every D1 result carries meta.rows_read, so you can log it per query name and sort your endpoints by rows read instead of by request count. The expensive query is almost never the one you would have bet on.