When a slow query delayed our ShakeAlert-style console

We ran a small internal earthquake console for a regional network: a Node 18 service polling a GeoJSON feed every 20 seconds, writing events into Postgres 15, and a Leaflet front end that re-fetched on a 10-second timer. Around 4,000 stored events, but on a busy aftershock sequence we’d get 30–60 inserts in a few minutes. Symptom: the map froze for 8–12 seconds whenever a burst landed, and the “last updated” clock drifted. No errors in the logs, just latency.

The cause was not the feed. It was the front end’s poll hitting a query that did an unindexed ORDER BY time DESC over the whole table plus a ST_DWithin filter on a geometry column with no spatial index. Under a burst, inserts held locks long enough that the poll queued behind them.

What I did, in order. First, add the indexes the query actually needed:

CREATE INDEX CONCURRENTLY idx_events_time ON events (time DESC);
CREATE INDEX CONCURRENTLY idx_events_geom ON events USING GIST (geom);

Second, stop the front end from re-querying the whole table. Switch to a since cursor, and return only new rows:

SELECT id, time, mag, place, ST_AsGeoJSON(geom) AS geojson
FROM events
WHERE time > $1
ORDER BY time ASC
LIMIT 500;

Third, batch inserts in the poller instead of one transaction per event. I used a single INSERT ... ON CONFLICT (id) DO NOTHING with a multi-row VALUES list, wrapped in one transaction per poll cycle. That cut lock churn from dozens of small transactions to one.

Why it works: the cursor turns an O(n) scan into an index range scan, and batching means the writer holds locks for one short window instead of many. The cost is real: CONCURRENTLY takes longer to build and can fail, and the cursor model means the client must handle gaps if it disconnects mid-stream. I store the last seen time per client in memory and accept that a reconnect may replay a few rows; ON CONFLICT makes that harmless.

Where this breaks. If two events share the exact same time value, a strict > cursor drops one. I hit this once with a cluster of near-simultaneous picks. The fix was a composite cursor on (time, id) and ordering by both. Also, LIMIT 500 silently truncates during a very large sequence; I log when the result hits the limit and force a full resync instead of pretending it’s complete. And none of this helps if the upstream feed itself lags—during one event the feed’s own timestamps were 40 seconds behind, and I spent an hour blaming my query before checking the source.

One thing I still haven’t solved: the 10-second front-end poll is arbitrary and I haven’t found a clean way to push new events without adding a websocket layer I’d have to maintain. For those running similar consoles, what’s your trigger for switching from polling to push—event rate, or just user complaints?