The latest articles on DEV Community by Studies Multiverse (@studiesmultiverse). https://dev.to/studiesmultiverse https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4191839%2F61350630-ce3e-43fb-9365-c0bd283359e1.png https://dev.to/studiesmultiverse en Studies Multiverse Sun, 18 Oct 2026 09:39:58 +0000 https://dev.to/studiesmultiverse/crawlers-found-50000-pages-on-my-free-side-project-heres-what-it-did-to-my-database-bill-3o1o https://dev.to/studiesmultiverse/crawlers-found-50000-pages-on-my-free-side-project-heres-what-it-did-to-my-database-bill-3o1o <p>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.</p> <p>Last week the D1 dashboard showed 5 billion rows read in a day. Real visitors were a tiny fraction of that.</p> <h2> What was reading them </h2> <ul> <li>Meta's crawlers (meta-externalagent, meta-webindexer) were walking every directory listing in all 7 languages.</li> <li>Applebot and others found the filtered URLs: every subject x city x page combination is a new address.</li> <li>50,000 course pages each ran a "same course at other places" query that read about 2,600 rows.</li> </ul> <h2> Three fixes </h2> <ol> <li> <strong>Make the index usable.</strong> My list query had optional filters written as <code>(?2 IS NULL OR e.kind = ?2)</code>. 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.</li> <li> <strong>Add the missing indexes.</strong> Two composite indexes on the course table, <code>(country_slug, search_key)</code> and <code>(country_slug, entity_id)</code>, turned the heaviest query into an index lookup.</li> <li> <strong>Don't serve crawlers what they can't index.</strong> 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.</li> </ol> <h2> Takeaways </h2> <ul> <li>Check rows read, not request counts. A cheap-looking page can be the expensive one.</li> <li>"Optional filter" SQL patterns quietly disable indexes. Run <code>EXPLAIN QUERY PLAN</code>.</li> <li>If a URL is noindex, crawlers have no reason to fetch it at all.</li> </ul> <p>The site: <a href="https://studiesmultiverse.com" rel="noopener noreferrer">https://studiesmultiverse.com</a>. Feedback welcome.</p> webdev sqlite cloudflare sideprojects