Our torrent counter went down while the index grew by a million

The search page on bittorrented.com says how many torrents are in the index. A month ago it said 19 million. Today it said 18.6 million.

Nothing deletes torrents there. Once the DHT crawler sees an infohash it keeps it, seeders or no seeders. So either something was quietly eating rows, or the number was lying.

The number was lying.

It was never a count

The endpoint fetched it with PostgREST's count: 'estimated'. That is not a count. It is the query planner's row estimate: reltuples from the last ANALYZE, scaled by how much the table has grown in pages since.

It got picked because the honest version broke. An exact COUNT(*) on that table blew past statement_timeout, the error was swallowed, and the DHT total silently collapsed to zero. Someone swapped in the estimate. The failure got quieter and the number got wrong.

Why an estimate drifts, and why it drifts up

Two things stacked.

autovacuum_analyze_scale_factor defaults to 0.1. On an 18.6 million row table that means ANALYZE only runs after roughly 1.86 million new rows arrive. The crawler adds about 250k a week, so that is one ANALYZE every seven weeks. In between, nothing corrects the estimate.

And autovacuum had not run on that table since July 2. Dead tuples pile up, page count grows faster than live rows, and since the planner scales reltuples by page growth, the estimate climbs away from reality.

Then autoanalyze finally fires, re-samples, and the number snaps back down. That drop is what you see on the page.

The actual numbers

I counted for real. On August 6, when the page was showing about 19,000,000, the table held 17,411,578. The display was 9 percent high.

Today the table holds 18,604,671.

So over the month the index gained 1.19 million torrents while the number on the page fell by 400k. Weekly inserts never even wobbled: 302k, 303k, 309k, 281k, 288k, 276k, 250k, 249k, 253k. No gaps, no stall.

The fix

Count for real, just not in a request. A cache table, a refresh_dht_torrent_count() function, and a pg_cron job every six hours. The count takes 11.2 seconds, which is exactly why it cannot live on the request path and exactly why the estimate was reached for in the first place.

The endpoint reads that cache and reports where the number came from, so a wrong total is diagnosable from the response instead of from psql. If the cache is missing, errored, or more than a day old it falls back to the estimate, because a frozen count is worse than an estimate that at least still tracks growth.

Then tighten ANALYZE on the crawled table so the fallback stays honest:

ALTER TABLE public.torrents SET (
  autovacuum_analyze_scale_factor = 0.002,
  autovacuum_analyze_threshold    = 20000
);

Every 57k inserts instead of every 1.86 million. ANALYZE samples about 30k rows no matter how big the table is, so frequent is cheap.

One thing worth stealing

A registered cron job is not a working cron job. cron.job having a row only proves you inserted a row.

I scheduled a temporary copy of the job to run every minute, watched cron.job_run_details until two runs came back succeeded at 11.26 and 11.29 seconds with the cache updating on its own, then unscheduled the copy. That took two minutes and turned "it should run at 18:17" into something I had actually seen happen.

If you put a public row count on a big table, do not serve the estimate. Count it on a schedule, serve the cache, and check that the schedule fires.

This post was drafted with AI assistance.

DEV Community