47 million rows read in a day. Zero users. The app was still in dev, nobody had logged in outside of me, and the Cloudflare D1 dashboard was showing traffic numbers that belonged to a mid-size SaaS with actual customers.
I run a monitoring tool for company filings, the kind of B2B thing where lawyers and analysts check if a director changed or a subsidiary got created. Small scope, small user base, at least for now. So when I finally opened the billing tab that morning, the question wasn't "is Postgres better than SQLite." It was simpler and dumber: can switching database engines actually fix a speed problem, or does it just move the problem somewhere you can't see it as easily.
10 days and 2 migrations later, I have an answer, and it costs more to admit than it does in dollars.
The Double Trap of a Managed Engine
Two separate problems were hiding behind that 47 million number, not a single one.
The first was pure billing math. D1's free tier caps you at 5 million rows read per day. I was averaging 12.9 million, with a spike to 22 million on July 2. A background poller was the culprit, scanning 18,000 rows per tick to check for new filings, and coming back empty in 447 out of 451 runs. Almost all cost, almost no signal.
The second problem had nothing to do with the bill. It was a hard engine limit that only showed up in production: "LIKE pattern too complex," a flat 500 error, triggered by a director's name that happened to be 59 characters long. I couldn't reproduce it locally. bun:sqlite, the engine I was testing against on my machine, doesn't enforce that ceiling at all. Same SQL, 2 different verdicts, depending on which box ran it. This is the database version of "works on my machine," except the machine in question was a live production box with actual filings on it.
I fixed the poller first, because that's the one with a dollar sign attached. The second bug sat there, waiting, structurally unrelated to any billing decision I could make.
Billing catches what you're watching. Production catches what you're not.
Migration 1: D1 to Local SQLite
July 21. I moved off D1, not off SQLite itself, and that distinction matters more than it sounds like it should. Sam Lambert, who runs PlanetScale (a company that sells managed database hosting, so not exactly biased toward "skip the server"), put it cleanly: a server engine handles network clients, concurrent writes, durable storage under load. An embedded engine runs in-process, no network hop, optimized for simplicity and a small footprint. D1 is SQLite wrapped in a managed, billed, network-facing layer. Running SQLite locally strips that layer off entirely.
The difference showed up immediately. Queries that used to round-trip through Cloudflare's edge came back sub-millisecond. /login went from whatever it was before (I genuinely didn't have visibility into that, which is its own small embarrassment) to 110 to 160ms through the tunnel. Same query engine, functionally. Zero cost. Every D1 quota limit, gone, because there was no quota anymore.
For about a week, this looked like the fix.
The Wall at 2.12 Million Companies
Then the dataset grew to 2.12 million companies, and a different wall showed up. Not a billing wall this time. A capacity one.
The working set (roughly 9.7GB of heap plus 8.1GB of index) didn't fit in the VPS's 7GB of RAM anymore. /admin/national, the page that aggregates across every company in the database, took 12.07 seconds cold. HAProxy's timeout sits at 50 seconds, so it wasn't dying, not yet, but the same page could swing by a factor of up to 300x depending on whether the cache happened to be warm. That's not a performance profile, that's a coin flip with expensive tails.
And here's the part that actually scared me more than the RAM math: I had no way to find out why. No serious read/write concurrency handling to reason about, no GIN trigram indexes, no EXPLAIN ANALYZE that told me anything useful. SQLite's query planner gives you output, technically, but it wasn't built to diagnose this kind of cross-table aggregation at this scale. The VPS was still up, in the sense that HAL still had the pod bay doors closed. Technically true. Deeply ominous.
(Unrelated, except that it wasn't: this is also the week my kid decided 4am was a completely normal time to ask about dinosaurs. By the time I actually sat down with the VPS logs I'd already misread 3 timestamps as UTC when they were local, twice.) 🤦
This VPS had already choked once before, on a Docker version bump that broke 2 things I didn't even know were dependent on each other until Claude Code dug through the logs and found them.
I got past the wall that time. Barely. Nothing said it wouldn't come back at the next scale-up, and next time the engine wouldn't have anywhere simple left to run.
Migration 2: SQLite to Postgres
July 29, 00:11, forward-only, no rollback plan because I was tired and confident in roughly equal measure. This switch wasn't about chasing raw speed. It was the national rollout forcing my hand: the numbers I'd just seen at 2.12 million companies told me the next scale jump would break something I couldn't even diagnose, let alone fix.
The numbers right after the switch looked clean. /api/decouvertes at 58 to 93ms. Aggregate endpoints at 9 to 45ms. Pool wait time at p95: 2.2ms.
GET /api/decouvertes 58-93ms
GET /api/aggregates 9-45ms
pool wait (p95) 2.2ms
I want to be honest about what these numbers actually meant at that moment, because it's tempting to read them as "Postgres won." They meant I finally had EXPLAIN ANALYZE that told the truth, connection pooling that behaved predictably, and index types SQLite never offered at this scale. That's tooling, not magic. Whether the magic was ever going to show up on its own is a different question, and at this exact point in the story, I didn't have the answer yet.
The Queries Were Still Bad
Here's the part that actually mattered: on Postgres, the queries were still bad. All of them. Until I went in and fixed each one by hand.
Full-text search went from 2141ms to 21ms, and the fix wasn't the engine, it was a GIN trigram index paired with a bounded subquery. /api/dirigeants sorted by company count went from 468 separate database queries down to 11, which took response time from a range of 5 to 30 seconds (sometimes flat 500s) down to 0.5 to 2 seconds. A breadth-first search across subsidiary relationships dropped from 199 queries to 18. A dirigeants bucket endpoint went from 709ms to 50ms once I stopped it from doing 7,404 disk reads per call, down to 149. Index cleanup shrank a table from 8,168MB to 5,890MB.
And then there was the query that humbled me the hardest: a search filter with 1,282 chained POSITION clauses running against 161,000 rows, which is roughly O(rows x clauses) if you want the ugly math spelled out. It crossed Postgres's 30-second statement timeout exactly the way it used to scan-and-choke on D1. Same wall, different engine, my own personal "YOU DIED" screen showing up twice in the same boss fight.
Chris Munns, who leads migration work at PlanetScale (so, again, someone with zero incentive to defend SQLite), says 90% of the painful Postgres migrations he sees come from queue-style workloads pushed onto Postgres at scale, constant contention, autovacuum fighting the write load, wrong tool for the job nearly always. Worth sitting with that, because it cuts the other way too. Postgres isn't the universal answer any more than D1 was the universal mistake. This is the same discipline I wrote about after ditching vibe coding for a stricter process, except this time the receipts were query plans instead of commit diffs.
A slow query doesn't get faster because the logo changed.
The Question That Replaced It
So no. Switching engines never fixed a single slow query. The queries got fixed because I finally had an engine that let me see them clearly enough to fix them by hand, one by one, over days.
The price for reaching that sentence: 2 migrations in 10 days, a D1 bill that made no sense for an app with no users, a statement timeout crossed on 2 engines by the exact same broken query. I think that's a fair trade, though honestly I'm not fully sure I'd have made the same call if I'd known the RAM wall was coming a week earlier and could have skipped straight to Postgres.
Ingestion still runs out of a single region. The national rollout, the real one, 4 million companies across something like 100 regions, hasn't happened yet. Whether that wall shows up again, and on which engine this time, I don't know.
Sources
- Sam Lambert (@samlambert), on the distinction between server databases and embedded engines
- Chris Munns (@chrismunns), on why most painful Postgres migrations are workload mismatches, not engine failures
This post may contain affiliate links. If you click them, I might earn a small commission, costs you nothing, and helps me keep shipping quality articles every day for your reading pleasure.
Top comments (0)