A page streaming progress from Postgres is usually fine but takes 8–20 seconds once in a while; give us hypotheses

performancedebuggingpostgresnext.jssse

Goal and context

Setup: a Next.js App Router application on a single Node process, Postgres behind a connection pool of size 10. One page shows a live progress stream for a running job. The stream is a Route Handler that returns a `ReadableStream`; on each tick it queries the `job_event` table for rows newer than the last one it sent (`WHERE job_id = $1 AND sequence > $2 ORDER BY sequence`), writes them as server-sent events, and then waits 1 second before the next tick. When the job finishes, the handler sends a final event and closes. A separate server action inserts the events as the job progresses; typically 5 to 30 events per job over 1 to 3 minutes. Symptom: most of the time the page shows the first event within about a second of opening. Roughly one in fifteen or twenty opens, the page shows nothing for 8 to 20 seconds and then all events arrive at once, after which streaming is normal. It does not correlate with time of day that we can see. It happens on a machine with only one user. Reloading the page usually fixes it immediately, which is the part we find strange. What we have checked: the query is indexed on `(job_id, sequence)` and `EXPLAIN` shows an index scan. Postgres logs show no slow queries above the 500 ms threshold during the incidents. Node CPU is idle. There is no external cache. We have not yet captured a trace during an incident because we cannot make it happen on demand. What we suspect but have not confirmed, in no particular order: pool exhaustion when a previous stream's connection was not released after the client navigated away; the browser holding the request open because of a limit on concurrent connections to the same host when several tabs are open; the 1-second wait being implemented in a way that blocks the pool; something in the proxy buffering the response until a size threshold; or the first query running before the job's first event exists and the loop then behaving differently. We are not asking anyone to fix it. We are asking for the ordered list of hypotheses you would test, and how you would test each one without being able to reproduce on demand.

Result you want

A Markdown document with these sections. 1. **Hypotheses, ranked.** Each with: the mechanism in one or two sentences, why it fits the symptoms (especially "reload fixes it" and "1 in 15–20"), why it might not fit, and the single cheapest observation that would confirm or rule it out. 2. **Instrumentation to add now,** so the next incident is captured: at most five specific things (a log line here, a timestamp there, a pool metric), and what each would show under each hypothesis. 3. **Things in the description that are suspicious on their own,** regardless of the incident: anything about the design that you would change even if the slowness went away. 4. **What you would need from us** to go further. Do not write code. If a hypothesis needs a code excerpt we did not give you, name the excerpt.

Reference notes

Loop shape in the stream handler (pseudocode, as we run it): ``` lastSequence = 0 while (true) { rows = await db.query(events newer than lastSequence) for row of rows: controller.enqueue(sse(row)); lastSequence = row.sequence if (rows.some(isTerminal)) { controller.close(); break } await sleep(1000) } ``` The `db` object is the pooled client. The handler does not use `request.signal` today.

Details

Response: One answer
Credits: 500

The Request is public. Only the Member who asked and the Member who answers see the Result.