Seventeen million writes a week, for a tool only I use

Kith Tab is a private browsing memory I built for myself. A browser extension in four (soon to be more) of my browsers sends open tabs, visits, and time spent on pages to a Cloudflare Worker, which stores everything in D1, Cloudflare's SQLite database. A dashboard lets me search what I've read, save pages, and clean up tabs. I'm the only user.

So when I noticed D1 writing a lot more than it should, it would have been easy to shrug. Nothing was broken. Nobody else would ever know. But a tool I open every day shouldn't burn through a database for no reason, so I sat down with Claude Code and went looking.

Measure before guessing

Wrangler can show per-query statistics for a D1 database, including how many rows each statement wrote over a time window:

npx wrangler d1 insights kith-tab --sort-by writes --timePeriod 7d

The answer came back in one screen. Over seven days, statements touching live tabs had written about 17 million rows. Recording what I actually browsed barely registered.

Three things stacked up to get there.

The extension sent a snapshot of every open tab whenever I switched tabs, opened or closed one, or a page finished loading, plus once a minute on a timer. That came to about 55,000 snapshots a week.

Each snapshot deleted every tab row for that browser and inserted them all again, with an upsert into the pages table for each tab. Delete and replace is the simplest way to sync a list. It's also the most expensive.

The live tabs table has a primary key, a unique constraint, and three indexes. D1 counts each index entry it updates as a row written, so inserting one tab cost about six rows. And an upsert counts as a write even when every value it sets is identical to what's already there.

Put together, a typical snapshot wrote around 300 rows, almost all of them unchanged.

Write the difference, not the whole list

The fix on the server was to compare before writing. The Worker now reads the browser's current rows (reads are far cheaper than writes), then deletes only tabs that closed, inserts only tabs that are new, and updates only rows that changed, all in a single statement.

For the page upserts I added a WHERE clause so an identical update becomes a no-op:

INSERT INTO pages (...) VALUES (...)
ON CONFLICT(user_id, normalized_url) DO UPDATE SET
  title = COALESCE(excluded.title, pages.title),
  domain = excluded.domain
WHERE pages.title IS NOT excluded.title
   OR pages.domain IS NOT excluded.domain

I also throttled the "last seen" heartbeats, which had been updating on every single request, to once a minute.

Five minutes after deploying, I checked whether rows for tabs I hadn't touched were being left alone. On three browsers, 29 of 29, 36 of 39, and 14 of 14 rows hadn't been rewritten. The three that had were tabs I'd actually changed. By that evening the new code had written about 1,300 rows in eight and a half hours. Earlier in the same 24-hour window, the old code had written 757,000.

Then I fixed the other end. The extension now hashes each snapshot and skips sending it if nothing changed, while still resending at least every five minutes so the server can't drift.

The review that found a real bug

With writes under control, I did a broader pass for other problems, starting from production numbers rather than the code. Two numbers didn't fit. Since late August the database had collected 1,916 pages, but only 13 recorded visits and exactly one row of imported browser history.

Chrome reports visit times as fractional milliseconds, like 1695398812345.678. The server only accepted whole numbers, so it rejected every visit and every history item. The extension then treated rejected events as processed and deleted them from its queue. The popup's import counter kept climbing the whole time. My history import had never worked, and nothing said so.

The fix was to round those times down on the server, which repaired every installed extension the moment it deployed. The extension got a one-time re-import to recover what had been thrown away.

The same pass turned up smaller things. Quick Clear skipped tabs whose URL one of my rules rewrites. "Don't record this site" quietly overwrote an existing rule's subdomain setting, which meant subdomains I'd excluded started being recorded again. A page title over 512 characters dropped the whole event. The dashboard kept polling the API from tabs I wasn't looking at. Each was small, and each now has a test.

The lesson I keep coming back to is that a server rejection the client deletes is silent data loss. Errors you never see might as well not exist.

A speedup that made a bug worse

The re-import ran at 11 history items a minute, so a large history would take hours. To speed it up I let each sync run 12 batches instead of one.

Two days later the write numbers looked wrong again. My main browser had sent 244,718 history events but produced only 682 stored pages, a ratio of about 359 to 1. My other browsers sat near 5 to 1.

The import walks backwards through history with a cursor. It moved that cursor using each page's lastVisitTime, which is the page's newest visit overall, not the visit inside the window it was reading. For pages I revisit often, that time stays ahead of the cursor. The code clamped it with Math.min, so the cursor moved back by one millisecond per round and kept returning the same 11 pages forever. Running 12 rounds a minute instead of one made it 12 times faster at doing nothing useful. It cost about 84,000 redundant events a day and more than a million row writes before I caught it.

One detail almost fooled me. The imported rows had dates going back to June, which looked like proof the walk had covered my full 90 days of history. Those were each page's first visit, and they said nothing about how far the cursor had travelled.

I fixed it in two places. The cursor now steps over a page using its newest visit inside the window, and pages already imported aren't sent again. On the server, an observation identical to what's stored now writes zero rows, so a misbehaving client can't burn writes this way again. After the fix the ratio dropped to about 4 to 1, and one browser profile went from 316 to 568 imported pages in fifteen minutes, history the stuck cursor had never reached.

A week of real data

Tests told me the logic was right. A week of real browsing told me whether it mattered.

That's about 12,000 writes a day, against a free tier of 100,000. Recorded visits went from 13 to 3,829 by the end of September, which is the clearest sign the timestamp fix did its job.

The last 55 percent

The biggest writer left was the heartbeat that marks each browser as recently seen. It accounted for 46,600 rows a week, more than half of everything.

I looked at three ways to cut it. Writing every five minutes instead of every minute would have worked, but the "online" badge would take up to six minutes to notice a closed browser. A Durable Object, Cloudflare's small stateful server per name, could hold presence in memory and save to D1 every few minutes. It's the right tool for real-time features, and I may want it later for instant tab cleanup. For saving about 6,600 writes a day, it's more moving parts than the job needs.

What I did instead kept the timing exactly the same. The live tab sync had its own device-row update, forced on every tab change, on top of the regular heartbeat. I merged them into one. And the query plans showed something I hadn't expected. The index on last_seen_at was only ever used to filter devices by user, because every query sorts on COALESCE(last_seen_at, created_at) instead. Replacing it with an index on user_id alone gives identical plans, and since that column never changes, each heartbeat now writes one row instead of two.

That should take heartbeat writes from about 46,600 to about 17,000 a week. It went out on October 8, so I haven't measured it yet. I'll update this post when I have.

What I'm taking away

• Measure rows written per query, not query counts. wrangler d1 insights gives you this for free.
• Every index on a column you update is another row written. Check whether the index is actually used.
• An upsert writes even when nothing changed. Add a WHERE.
• Sync the difference, not the whole state.
• Never let a client delete what the server rejected.
• Ratios catch bugs that totals hide. Events per stored page found the cursor bug in one query.
• A speedup amplifies whatever bug is already in the loop.
• A week of real traffic beats any test suite for finding out what actually happens.

Claude Code did most of the digging here. It ran the insights queries, read the code, wrote the fixes and tests, and checked production after each deploy. My part was noticing something felt off and deciding what was worth fixing.

Nobody else uses Kith Tab, and that's sort of the point. A personal tool still deserves care. It shouldn't waste resources, and it definitely shouldn't lose my history without saying a word.