Get the app

← All engineering notes

Engineering

The thousandth row

· 4 min read · Cancel Cultr

PostgREST returns at most a thousand rows per request, even when you ask for more, and does not say so. Every read that grows with users has to page, or it silently stops at row one thousand. This is the small helper that makes the rule hard to break.

The failure

A nightly job selects every active subscription to roll its next bill date forward. On day one that is thirty rows. The query returns all of them, the job works, the test passes. At a thousand and one subscriptions the query still returns a thousand rows and still reports no error, and the thousand-and-first user’s bill date quietly stops moving. Nothing in the response distinguishes “all rows” from “the first thousand”.

The limit is PostgREST’s max-rows, a thousand on Supabase by default. Raising it moves the cliff; it does not remove it.

The rule

Every read whose size grows with users goes through one function, scanByCursor. It walks the table in pages keyed on a unique, ordered column: ask for the first page ordered by the cursor, then for rows greater than the last cursor seen, until a page comes back short. Keyset paging rather than offsets, so a row inserted mid-scan can’t shift the window and cause a skip or a duplicate.

const rows = await scanByCursor(
    (after, limit) => {
      let q = db.from('subscriptions').select('*').eq('status', 'ACTIVE').order('id').limit(limit);
      if (after) q = q.gt('id', after);
      return q;
    },
    row => row.id,
  );

The page size is 500, below the ceiling, so a page that comes back full is unambiguous: there may be more. A page that comes back short ends the scan. A read that only needs a sample passes maxRows and stops early.

Making it the easy path

A rule that lives in a code review comment gets broken by the next job someone writes at midnight. So the helper is the shape every job is written in, the project notes say so in one sentence next to the commands, and the test for the helper asserts the thing that actually matters: a page that fails throws instead of returning a partial table. A partial result with no error is the whole problem, and it must not be possible to produce one from this function.

What it cost

One extra round trip per 500 rows, inside a job that already runs once a night. The alternative was a bug that only appears at a scale where it affects the most users, reports nothing, and looks like the job is working.