A backfill taught me 5 of our 16 indexes were redundant

I needed to denormalize org_id onto our events table. The column was already there, a trigger was filling it in for new rows, and 8.68 million historical rows still had org_id IS NULL. The table had 10.49 million rows total, 16 indexes, and production traffic running the whole time.

I ended up finishing the backfill in under two hours with a 30-line SQL function. But the fix wasn't the interesting part. The interesting part was what the fix exposed: five of our sixteen indexes were doing nothing.

Three ways a client-side backfill failed on 8.68M rows#

The obvious approach was a single UPDATE events SET org_id = d.org_id FROM devices d WHERE ... touching all 8.68M rows. Supabase's connection pooler killed it at the two-minute statement timeout. Fair enough. A single transaction holding row locks across 8.68M rows with 16 indexes is not something a shared pooler should be asked to carry.

Next I tried per-device batches from Node.js, largest first. It worked for smaller devices, but the biggest one had 1.3M events and the connection dropped mid-transaction. Progress lost.

Then I tried a PostgreSQL procedure with batched commits, calling backfill_events_org_id(50000) with internal COMMIT after each batch. The database handled it fine. The problem was the psql session over SSL: it still had to survive for the full duration, and it didn't.

Every approach needed an external client to maintain a connection for hours. In every case the database handled its side fine; the external session couldn't stay alive long enough to finish.

Scheduling the backfill server-side with pg_cron#

The fix, in retrospect, was obvious. Stop running the backfill from somewhere. Run it inside the database.

pg_cron schedules server-side jobs. Each invocation is its own transaction, so there's no client connection to maintain, no session to keep alive, no pooler timeout to worry about. If the server restarts, the next tick picks up where the last one left off. If a batch runs long, an advisory lock makes overlapping invocations skip gracefully.

CREATE OR REPLACE FUNCTION public.backfill_events_org_id_batch(
  p_batch_size INT DEFAULT 10000
) RETURNS INT AS $$
DECLARE
  v_updated INT;
BEGIN
  IF NOT pg_try_advisory_xact_lock(
    hashtext('backfill-events-org-id')
  ) THEN
    RETURN -1;
  END IF;

  WITH batch AS (
    SELECT e.id, d.org_id
    FROM events e
    JOIN devices d ON e.device_id = d.id
    WHERE e.org_id IS NULL
      AND d.org_id IS NOT NULL
    LIMIT p_batch_size
  )
  UPDATE events
  SET org_id = batch.org_id
  FROM batch
  WHERE events.id = batch.id;

  GET DIAGNOSTICS v_updated = ROW_COUNT;

  IF v_updated = 0 THEN
    PERFORM cron.unschedule('backfill-events-org-id');
  END IF;

  RETURN v_updated;
END;
$$ LANGUAGE plpgsql;

SELECT cron.schedule(
  'backfill-events-org-id',
  '10 seconds',
  $$SELECT public.backfill_events_org_id_batch(10000)$$
);

That schedule call uses 10,000 rows because that's where I started. Skip ahead to the tuning section before you copy it — 20,000 turned out to be the better number on this table, and the right value on yours depends on your index count.

Every ten seconds, the database processes a batch. When none are left, the function unschedules itself. WHERE org_id IS NULL makes every invocation idempotent. Because the insert trigger was already populating org_id for new rows, I could run this alongside production writes without coordination: old rows, new rows, and the backfill converged on their own.

That's where the "without downtime" part comes from. The only locks held at any moment are the row locks for a 10,000-row batch, which last a handful of seconds. Readers and writers against the rest of the table don't notice.

Why 20,000 rows per batch beat 50,000#

The first version used 10,000 rows per batch, with a median of 4.7 seconds. That left the job idle for the rest of every 10-second cron tick, so the obvious next move was to increase the batch size.

At 50,000, the median batch took 32.7 seconds. The throughput math looked better on paper, but the p95 was 56.8 seconds, uncomfortably close to the 60-second statement timeout.

Across four batch sizes, execution time grew a little faster than linearly: doubling the batch size more than doubled the duration. Four data points aren't enough to fit an exponent I'd defend, but the direction was consistent, and the mechanism is clear enough. Larger batches hold row locks longer, write more WAL per transaction, and compound contention across every one of the 16 indexes that has to be updated for each row touched.

The throughput function is batch_size / max(cron_interval, batch_duration). Once batch duration exceeds the 10-second cron tick, the advisory lock starts skipping overlapping runs and the effective cycle time becomes the batch duration itself.

The sweet spot was 20,000 rows: a median of 10.8 seconds, just past the 10-second tick, so batches chained back-to-back with almost no idle time, and a p95 of 23.5 seconds with plenty of headroom against the timeout.

Twenty thousand beat fifty thousand on throughput by 10%, and its p95 of 23.5s left real headroom against the 60-second statement timeout where fifty thousand's 56.8s did not.

The real problem was five dead indexes#

If a backfill needs this much tuning to find a safe batch size, the schema is the problem, not the backfill. That super-linear growth wasn't fundamental to the workload. It was the indexes charging a tax on every row the backfill touched.

I audited all 16:

• One ascending btree on event_at was redundant with the existing event_at DESC index. Btrees scan in both directions.
• A BRIN index on event_at coexisted with a btree on the same column. BRIN works well when data is append-only and correlated with physical row order, which ours was, but for selective queries the btree always wins on cost estimates. The planner never picked the BRIN.
• One index on device_id was a strict prefix of two composite indexes that could serve the same queries.
• One index on status was a strict prefix of a composite (status, event_at DESC) index.
• One index named idx_events_org_device_date didn't reference org_id at all. It was a rename left over from an earlier iteration.

Five indexes doing nothing but slowing down writes. On a table receiving continuous inserts from a fleet of devices, every redundant index is a tax on every row written. The backfill made the cost visible. The cost was always there.

What to know before trying this#

pg_cron has to be available. Supabase ships it, most managed Postgres providers support it, vanilla Postgres needs the extension enabled. AWS RDS requires a parameter group change before CREATE EXTENSION pg_cron works.

Use the transactional advisory lock variant. pg_try_advisory_xact_lock releases automatically at transaction end, which is what you want here. The non-transactional variant needs an explicit release call and leaks on error.

hashtext() returns a 32-bit integer, which implicitly casts to bigint for the single-argument lock. Fine for one backfill, but if you're running several concurrently against the same database, use the two-argument pg_try_advisory_xact_lock(classid, objid) variant for clearer namespacing.

Clean up after the drain. Once the backfill finished, I recreated the two org_id indexes with CONCURRENTLY, ran ANALYZE, and set the column to NOT NULL so the planner could trust non-nullability in future joins.

Steal this function#

Swap events, devices, and org_id for your table and column names. The rest (advisory lock, idempotency check, self-unscheduling) is general-purpose.

If I did this again, I'd audit indexes before writing the backfill. On a cleaner schema the backfill would have been meaningfully faster, and I'd have reached the same conclusion without 8.68 million rows of evidence to prove it.