Stored counts: retiring the full-table-scan COUNT

[MA2153]

Note

Consider this proposal a draft for now, as I believe I found a better way to solve this problem.

Stored counts: retiring the full-table-scan COUNT

Motivation

Every user-facing count in EmDash is computed by scanning. SQLite does not store a row count, so COUNT(*) is O(rows) — and on D1, rows read is the billed unit and the latency driver. The result is that the heaviest reads in the product are not content queries. They are counts.

This is not a hot-path-only concern. It shows up on the admin dashboard, the taxonomy term list, the media library, and anywhere a "N items" badge is rendered.

Problem

A D1 log from a production site, filtered to queries reading more than 10,000 rows, captured one admin dashboard load and one content-editor open.

Every query above the threshold was a count. No non-count query came anywhere near that magnitude.

One dashboard load:

Query	Source	Rows read
Collection stats	repositories/content.ts getStats()	26,224
Media count	repositories/media.ts count()	23,899
Total		50,123

One content-editor open, on an entry with three taxonomies:

Query	Source	Rows read
Term counts, per taxonomy	taxonomies/term-counts.ts	47,381 / 51,481 / 63,854
Total		162,716

Two page views, 212,839 rows read, entirely to produce a handful of integers.

Three observations:

1. Each is a full table scan. count(id) from media read 23,899 rows — the entire table. getStats() read 26,224 — the entire collection. Its only predicate is deleted_at IS NULL, which nearly every row satisfies, so no index helps.
2. The values change far more slowly than they are read. Counts move only when content is created, published, or deleted, yet the full scan is paid on every render.
3. Latency tracks the scan. The collection-stats scan was observed as high as 335 ms, and every one of these queries was served by the primary rather than a replica.

The recent term-count join-order fix reduced the constant factor on the largest offender. It did not change the shape: these are still O(rows) scans issued per page view to produce numbers that are semantically counters.

Goals

• Serve counts from stored values, so read cost is proportional to the number of counters, not to table size.
• Keep the write path's added cost to a single small append.
• Make total system cost proportional to write volume, never to wall-clock time or table size.
• Work identically on SQLite/D1 and Postgres.
• Give operators a way to repair drift without a redeploy.

Non-goals

• Not a cache. The object cache already exists and is orthogonal; this is durable state that survives eviction and cold start.
• Not approximate counts. No bucketing or "1,000+" display. Stored values are exact between reconciliations.
• Not a general aggregate framework. Counts only — no SUM/AVG over content fields.
• Not a change to public listing semantics. The loader's visibility predicate is untouched.

Considerations

Timer-driven recompute is disqualified on cost

The obvious design — recompute counts on a schedule — is far worse than the problem. The platform scheduler ticks every minute by default. Recomputing the three offenders from truth costs one full set of the scans above:

	Rows
Per tick	212,839
Per day	306.5M
Per month	~9.2B

The per-tick figure is the dashboard load and editor open above, combined — so a per-minute recompute costs the equivalent of 1,440 such sessions every day, on a site nobody visits. It is paid whether or not anything changed, and it grows with table size rather than with usage.

The conclusion generalizes: any repair mechanism must be event-driven, and any reconciliation step must be arithmetic (base += SUM(deltas)), never a rescan. Full recomputation belongs behind an explicit operator action, paid on demand.

Why a delta ledger rather than in-place increments

In-place value = value + 1 requires no ledger and no reconciliation. The ledger is preferred because appends never contend on a hot row, and because reconciliation can then be non-atomic by design: a fold claims a watermark and ignores anything appended after it, picking those up on the next pass. That matters on D1, which has no interactive transactions.

Why application-level emission rather than database triggers

Triggers are genuinely attractive: they would catch every write path — CLI, seeds, plugins, hand-run SQL — by construction, which is the main weakness of the chosen approach.

They were rejected for three reasons:

1. Two implementations. SQLite triggers and Postgres trigger functions maintained in parallel, where a divergence produces counts that differ by dialect. The existing trigger precedent in the codebase (FTS) sidesteps this by being SQLite-only — verifyAndRepairAll() opens with if (!isSqlite(db)) return 0. Counters cannot.
2. The logic already exists in TypeScript. An UPDATE trigger must diff status and deleted_at, then fan out to every assigned term. The taxonomy-pivot denormalization already performs exactly this diffing at the same choke points; emission reuses it rather than reimplementing it twice in SQL.
3. Precedent. That pivot re-stamp has the identical coverage exposure and was solved at application level. Hanging triggers off columns an application-level re-stamp maintains would chain trigger correctness to application correctness — the worst of both.

The coverage risk this leaves is real, but it is detectable: an integration test asserts stored counts equal a live COUNT after every repository mutation, so an un-instrumented write path fails CI rather than shipping.

D1 batching, and the limits of it

D1's batch() is all-or-nothing, which the design uses in two places: appending a delta atomically with the mutation that caused it, and making a fold exactly-once. A dialect-resolving helper maps to batch() on D1 and a real transaction() on Postgres and Node SQLite.

Note this is the D1 API used deliberately, distinct from the existing coalescing dialect, which only merges same-turn SELECTs.

Design

Schema

CREATE TABLE _emdash_counters (
  scope      TEXT NOT NULL,   -- 'collection' | 'media' | 'user' | 'term'
  key        TEXT NOT NULL,   -- collection slug | '' | term translation_group
  metric     TEXT NOT NULL,   -- 'total' | 'published' | 'draft' | 'scheduled' | 'visible'
  value      INTEGER NOT NULL DEFAULT 0,
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (scope, key, metric)
);

CREATE TABLE _emdash_counter_deltas (
  id     TEXT PRIMARY KEY,    -- ULID: lexicographically monotonic, serves as the fold watermark
  scope  TEXT NOT NULL,
  key    TEXT NOT NULL,
  metric TEXT NOT NULL,
  delta  INTEGER NOT NULL
);

CREATE INDEX idx_emdash_counter_deltas_read
  ON _emdash_counter_deltas (scope, key, metric);

CREATE TABLE _emdash_counter_fold_lock (id INTEGER PRIMARY KEY CHECK (id = 0));

Term counts key on translation_group under a flat 'term' scope rather than qualifying the scope by taxonomy name. The write path has the group (it is what the pivot stores) but not the name, so a name-qualified scope would force a lookup on every content write. The read path already loads the term list before fetching counts, so it reads WHERE scope = 'term' AND key IN (…), chunked at SQL_BATCH_SIZE.

Read

One query returns the folded base and any unfolded deltas, grouped by source so the caller also learns how far behind reconciliation is:

SELECT key, metric, src, SUM(v) AS value, COUNT(*) AS n FROM (
  SELECT key, metric, value AS v, 0 AS src FROM _emdash_counters       WHERE scope = ?1
  UNION ALL
  SELECT key, metric, delta AS v, 1 AS src FROM _emdash_counter_deltas WHERE scope = ?1
) GROUP BY key, metric, src

Wrapped in requestCached so a render touching counts twice pays once.

Read	Today	With stored counts
Dashboard (getStats × N + media + user)	2+N queries, 50,123+ rows	1 query, ~4N rows + pending
Term list, one taxonomy	1 query, 47–64K rows	1 query, ~#terms rows
Term list, three taxonomies	3 queries, 162,716 rows	1 query, ~#terms rows

Write

Mutations emit deltas at the existing repository choke points, in the same atomic unit as the mutation.

await withAtomicWrite(db, (stmts) => {
  stmts.push(updateContentRow);
  stmts.push(appendDelta("collection", slug, "draft",     -1));
  stmts.push(appendDelta("collection", slug, "published", +1));
});

Transition	Deltas
create draft / published	total +1, <status> +1
draft → published	draft -1, published +1
unpublish	published -1, draft +1
schedule / unschedule	scheduled ±1
scheduled sweep promotes	scheduled -1, published +1
soft delete	total -1, <status> -1
restore	total +1, <status> +1

media and user are total ±1.

The dashboard metrics are exactly delta-able: getStats() matches status literally (status = 'published', scheduled_at IS NOT NULL) rather than using the scheduled-aware visibility predicate, so none of them carry a time dependency. Only the term visible metric does.

Term deltas must fan out to every taxonomy_id an entry carries. This costs no extra query: the pivot re-stamp already reads and rewrites exactly those rows whenever status or deleted_at moves.

Fold

One bounded read, one atomic batch:

const pending = await db.selectFrom("_emdash_counter_deltas")
  .select(["id", "scope", "key", "metric", "delta"])
  .orderBy("id").limit(FOLD_BATCH_LIMIT).execute();
if (!pending.length) return 0;

const watermark = pending[pending.length - 1]!.id;
const sums = groupSum(pending);

await withAtomicWrite(db, (stmts) => {
  stmts.push(acquireFoldLock);                             // INSERT id=0 — errors if held
  for (const s of sums) stmts.push(upsertCounterBase(s));  // value = value + excluded.value
  stmts.push(deleteDeltasUpTo(watermark));
  stmts.push(releaseFoldLock);
});

Rows read equals the pending count, capped by FOLD_BATCH_LIMIT, and is zero on an idle site. A failed fold leaves the ledger intact and retries; there is no partial state. Deltas appended after the watermark fold on the next pass.

The scheduler folds on its normal tick. As a backstop for deployments with no scheduler wired, a read that observes more than FOLD_THRESHOLD pending rows serves the correct value and schedules a fold via after(), off the response path.

Concurrent folds

Two folds reading the same pending set would each apply the sum, double-counting. batch() prevents partial application within one fold, not duplication between two.

A WHERE-clause compare-and-swap does not solve this. A WHERE matching nothing is a successful statement affecting zero rows, not an error, and batch() rolls back on error only — so the guarded UPDATE would silently no-op while the DELETE in the same batch still committed, turning a double-count into a lost count. Inspecting rows-affected afterward is too late: batch() returns results only after committing.

A primary-key collision does raise an error, portably across D1, Postgres, and Node SQLite. Acquiring the lock row as the batch's first statement makes the loser roll back entirely. The lock is taken and released inside the same atomic unit, so it cannot outlive the batch — no lease, no expiry, no stale-lock recovery.

Keying the lock on the watermark instead would not work: two folds reading a moment apart see different watermarks, no collision fires, and the overlap is double-counted. The lock must be on the act of folding, not on what is folded.

A lost race is expected rather than exceptional; it should return 0 quietly instead of logging.

Repair

The current scan queries are not deleted — they move out of the read path and become the repair path, driven by an admin Recalculate counts action and a verifyCounters() pass. This is the on-demand equivalent of the recompute rejected above: the same scan, paid when an operator asks for it rather than every 60 seconds. It needs a permission in rbac.ts and single-flight guarding.

Risks and failure modes

A failed delta append fails the content save. This is the deliberate cost of atomicity: it trades "a counters bug can block editing" for "a counters bug silently corrupts counts." The append is an INSERT into a small table on the connection that just wrote the content row, so the added failure surface is minimal — but it is the one place this design can degrade content editing.

Absent counter rows read as zero. Falling back to a live COUNT would reinstate the cost being removed, so initialization must be airtight: the migration backfills all counters once, and collection and taxonomy creation seed zero rows. A code path that creates a collection without seeding would report 0 until someone recalculates. A fallback is gated on isMissingTableError alone, covering the deploy window and nothing else.

Scheduled entries join term counts one tick late. Today a scheduled entry enters the visible count the moment scheduled_at passes, because the predicate is evaluated per query. With deltas it enters when the sweep promotes it.

On a deployment with no scheduler wired at all, it never enters — while still appearing in public listings, since the loader is unchanged. Those deployments already have broken scheduled publishing, but this converts "late" into "visibly inconsistent." Counters should be documented as depending on the scheduler.

Testing

Dialect-parametrized throughout, since this is query-builder code.

• Coverage oracle — after each repository mutation, assert stored counts equal a live COUNT, across create, publish, unpublish, schedule, sweep-promote, soft delete, restore, hard delete, and term assign/unassign. This is what catches an un-instrumented write path.
• Concurrent fold — two folds from one pending read; the loser rolls back entirely and the base is not doubled.
• Watermark — deltas appended between a fold's read and its batch survive and fold next pass.
• Drift repair — write to ec_* directly, bypassing the repository, then assert recalculate restores truth.
• Missing table — reads fall back rather than returning 0 when the ledger is absent.
• pnpm query-counts snapshot diff as the proof of the read-path reduction.

Rollout

Two PRs, each fully wired — no infrastructure landed ahead of a consumer.

1. Migration, withAtomicWrite, emit/fold/read, recalculate action; wires the dashboard (collection, media, user).
2. Term counts, where the assignment fan-out and visible semantics live.