Making a USGS feed usable during a regional event

During the 2023 Turkey–Syria sequence I was on a small volunteer ops team. We were pulling the USGS fdsnws-event GeoJSON feed every 60 seconds into a Postgres table, then rendering a simple map for a local NGO. The first hours of a large sequence broke the pipeline in a way I had not planned for: a single M7.8 event returns fine, but the aftershock burst added roughly 1,400 events in under three hours, and our naive upsert loop began timing out on the geometry column while the map silently showed stale pins.

The fix was not clever. I stopped treating the feed as a stream of inserts and treated it as a set of revisions keyed by eventId. The table held the full payload plus updated and fetched_at. The query asked only for events since last_run - 6h, which covers USGS’s own revision lag, and the upsert became:

insert into quakes (event_id, mag, place, geom, updated, fetched_at, raw)
values ($1, $2, $3, st_setsrid(st_makepoint($4,$5),4326), $6, now(), $7)
on conflict (event_id) do update
  set mag = excluded.mag,
      place = excluded.place,
      geom = excluded.geom,
      updated = excluded.updated,
      fetched_at = now(),
      raw = excluded.raw
  where quakes.updated < excluded.updated;

That where clause matters. Without it, every poll rewrites every row in the 6h window, which during a burst is thousands of rows per minute and creates write amplification we did not need. With it, unchanged events are no-ops, and only revised magnitudes, depths, or review statuses move.

The second change was decoupling the fetch from the render. The map now reads a materialized view that refreshes every 30 seconds, so a slow upsert does not freeze the page. I also added a reviewed boolean, because USGS marks automatic solutions automatic and later revises them; our NGO was pushing alerts on unreviewed M4.5s and then retracting. Reading the status field and holding alerts until reviewed was accepted cost us maybe 10 minutes of latency and killed the false-alarm problem.

Where this fails: the 6h window is a guess. For a very large sequence, USGS can revise events older than 6h, so a long-running operation should widen the window or track per-event revision explicitly. The upsert also assumes eventId is stable, which it is for USGS but not for every aggregator. And if you poll faster than the feed updates, you are just adding load; 60s was already generous for our scale.

If you have run a similar collector through an aftershock burst, how did you handle the revision window without re-pulling the whole catalog every cycle?