Ranking every WordPress plugin without melting the database

StatWP is a stats dashboard for WordPress.org: installs, downloads, ratings, version spread, rankings, growth over time. Every plugin. Every one of them, more than seventy thousand, refreshed daily.

The charts were the easy part 😌. Recharts draws whatever you hand it. The four problems worth writing about all came from the same place: WordPress.org gives you the present, and a stats site is mostly about the past.

Problem 1: seventy thousand plugins, and an API that talks about one

The obvious shape is a loop. For each plugin, call plugin_information, write a row. That is seventy thousand HTTP requests and seventy thousand inserts, every day, and it would take hours before anything else went wrong.

query_plugins returns 250 per page. So the crawl pages that instead, and each page is written with a single statement using unnest to turn parallel arrays into rows:

INSERT INTO plugins (slug, name, active_installs, downloads, rating, ...)
SELECT * FROM unnest($1::text[], $2::text[], $3::bigint[], ...)
ON CONFLICT (slug) DO UPDATE SET ...

The arithmetic changes completely:

Same data, same freshness, about 1% of the traffic. The whole catalogue syncs in roughly five minutes.

Problem 2: the numbers barely move

Here is the thing that decides the entire schema. WordPress.org does not publish an exact install count. It publishes a bucket: 10,000+, then 20,000+, then 30,000+. A plugin can sit on 50,000+ for eight months.

A daily snapshot per plugin would be seventy thousand rows a day, twenty five million a year, and almost every one of them identical to the row above it.

So snapshots are a change log. A row is written only when the value actually differs from that plugin's last snapshot:

INSERT INTO plugin_snapshots (slug, snapshot_date, active_installs, ...)
SELECT $1, CURRENT_DATE, $2, ...
WHERE $2 IS DISTINCT FROM (
  SELECT active_installs FROM plugin_snapshots
  WHERE slug = $1 ORDER BY snapshot_date DESC LIMIT 1
)

IS DISTINCT FROM rather than <> is deliberate: on a plugin's very first snapshot the subquery is NULL, and NULL <> 5000 is NULL, which is not true, so the first row would never insert 😬. IS DISTINCT FROM treats NULL as a difference and the first write lands.

The table grows something like 100 times slower. Charts render as step charts, which is also more honest: the data really is a staircase, and drawing a smooth line through it would be inventing days that never happened.

Problem 3: only store what you cannot get back

The instinct with a stats product is to hoard. Store everything, decide later.

But downloads-per-day is already available from downloads.php going back about a year, free, for any plugin. Storing it would mean paying to duplicate an endpoint that is not going anywhere.

Active installs are the opposite: there is no historical API at all. If you do not record it today, that day is gone forever.

Store the data nobody else keeps. Fetch the data somebody else already keeps.

That single rule is why the database is small enough to be boring.

Problem 4: there is no trending endpoint

WordPress.org has no notion of trending, so it has to be derived. Trending is download velocity: the last seven days against the seven before them.

const recent = history.slice(-7).reduce((s, p) => s + p.downloads, 0);
const prior  = history.slice(-14, -7).reduce((s, p) => s + p.downloads, 0);
const momentum = prior > 0 ? recent / prior : 1;

One detail cost me an afternoon: I asked for 14 days and kept getting plugins with no result. downloads.php trims partial rows, including today's, so a 14-day request comes back short and every candidate failed the length check. The fix is to fetch 30 and slice what you need.

Where the work actually runs

Crawling seventy thousand plugins does not fit in a serverless function, and no amount of config makes it fit. So the daily crawl runs on GitHub Actions, on a schedule, where a five-minute job is unremarkable. The hosting platform's cron handles the small, fast things: draining the mail queue, daily snapshots, digests.

Mail is queued rather than sent inline. A request inserts a pending row and returns; delivery drains afterwards. Nobody waiting on a signup should be waiting on somebody else's SMTP.

What I would tell myself at the start

• Look at the data's shape before designing the schema. Bucketed installs that change twice a year are a completely different problem from a live metric, and I designed for the wrong one first.
• Bulk beats loops by an order of magnitude. unnest turned a seventy-thousand-request crawl into about 280.
• Duplicating a stable public API is not caching, it is cost. Store what disappears.
• Match the runtime to the job. A five-minute crawl belongs where five minutes is normal, not in a function that times out at sixty seconds.

None of these are clever. Each one only became obvious after I had built the version that ignored it 😅.