Run this event processing backfill?

Situation

A bug left 3 million events with NULL processed_at timestamps. The platform team needs to backfill them without impacting the live event ingestion pipeline.

Environment

The events table has 180 million rows and receives ~5,000 inserts per second. The application reads unprocessed events with SELECT ... WHERE processed_at IS NULL. PostgreSQL 16.

Select suspicious lines in the terminal to flag them before submitting your verdict.

pending review

DO $$
DECLARE
  batch_size INT := 1000;
  last_id INT := 0;
  max_id INT;
BEGIN
  SELECT MAX(id) INTO max_id FROM events WHERE processed_at IS NULL;
  WHILE last_id < max_id LOOP
    UPDATE events SET processed_at = NOW()
    WHERE id > last_id AND id <= last_id + batch_size
      AND processed_at IS NULL;
    COMMIT;
    last_id := last_id + batch_size;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;