A hundred thousand rows pasted into a form: move it to a queue

Somebody pastes a hundred thousand rows into a text area and presses the button. Around a minute later the browser shows a gateway timeout. The rows are not all missing, they are half there, because the request kept running on the server after the proxy gave up. The user, reasonably, pastes the list again.

What actually happens

The timeline is the same every time, and only the numbers move:

1. The browser posts roughly three megabytes of text.
2. The proxy accepts it and forwards it, with a read timeout of sixty seconds.
3. The application parses the text, normalises each row and checks for duplicates against the existing table, one row at a time.
4. At around forty thousand rows the sixty seconds are gone. The proxy closes the connection and answers 504.
5. The application does not know the client has gone. It keeps inserting until it finishes or the process is restarted.
6. The user sees a failure, and the database contains forty thousand rows that the user believes do not exist.

On a system where rows cost money, step six is the expensive one. The rows that were written are real: they are queued, they may already have been charged, and they may already have been sent. The second paste creates the same list again, and now the recipients get two copies and the account is billed twice. The error message said the operation failed, so nobody involved has any reason to suspect otherwise.

Two design choices make the failure certain rather than likely. The first is doing the work inside the request, where the deadline belongs to a proxy that knows nothing about the job. The second is writing as you go without a transaction boundary that matches the unit of work, so an interruption anywhere leaves a state nobody designed.

How to see it

The proxy log tells you immediately whether you are looking at this bug. Log the upstream response time and the status, then count:

awk '$NF ~ /^[0-9.]+$/ && $NF > 30 { print $7, $9, $NF }' /var/log/nginx/access.log \
  | sort -k3 -rn | head
# /api/contacts/import 504 60.001
# /api/contacts/import 504 60.000
# /api/contacts/import 200 47.180

The 504s at exactly the timeout value are the requests that were cut off. The 200 at forty seven seconds is the one that got through today and will not get through next month.

Then look for the state those requests left behind:

SELECT list_id,
       count(*)                       AS rows,
       min(created_at)                AS started,
       max(created_at) - min(created_at) AS spread
FROM contacts
WHERE created_at > now() - interval '7 days'
GROUP BY list_id
HAVING max(created_at) - min(created_at) > interval '20 seconds'
ORDER BY rows DESC;

A list whose rows were written over a spread of a minute is a list that was written by a request, not by a batch. If you also find two lists with nearly the same row count created a few minutes apart, that is the second paste.

The fix

Split the endpoint into two things: one that accepts, and one that works.

The accepting side does almost nothing. It validates the size, stores the raw payload exactly as it arrived, writes a job row and answers:

app.post("/api/contacts/import", async (req, res) => {
  const raw = req.body.text ?? "";
  const lines = countLines(raw);

  if (lines > MAX_ROWS) {
    return res.status(413).json({
      error: "too_many_rows",
      limit: MAX_ROWS,
      submitted: lines,
      message: `This list has ${lines} rows. The limit is ${MAX_ROWS}. Split it and send it in parts.`,
    });
  }

  const key = req.get("Idempotency-Key") ?? sha256(`${req.accountId}:${raw}`);
  const job = await db.jobs.upsertByKey({
    key, accountId: req.accountId, payload: raw, total: lines, state: "DRAFT",
  });

  res.status(202).json({ jobId: job.id, total: job.total, poll: `/api/jobs/${job.id}` });
});

Three things are happening there and each one matters. The payload is stored before anything is interpreted, so the work can be repeated without the user being involved. The idempotency key means a second identical submission returns the first job rather than creating a twin. The cap produces a number and an instruction rather than a stack trace.

The working side reads the stored payload and moves through it in chunks, recording where it got to:

const CHUNK = 2000;

while (job.processed < job.total) {
  const rows = parseSlice(job.payload, job.processed, CHUNK).map(normalise);

  await db.tx(async (t) => {
    await t.query(
      `INSERT INTO contacts (list_id, msisdn, name)
       SELECT $1, r.msisdn, r.name FROM unnest($2::contact_row[]) AS r
       ON CONFLICT (list_id, msisdn) DO NOTHING`,
      [job.listId, rows]
    );
    await t.query(
      `UPDATE jobs SET processed = $2, state = 'RUNNING' WHERE id = $1 AND processed = $3`,
      [job.id, job.processed + rows.length, job.processed]
    );
  });

  job.processed += rows.length;
}

The insert and the progress update are in one transaction, so the recorded position is always true. If the worker is killed between chunks, the next run reads processed and continues from there. The guard AND processed = $3 means two workers cannot both advance the same job.

Deduplication happens at the insert, through the unique index on (list_id, msisdn), and on the normalised value rather than the raw one. Normalising first and letting the database enforce uniqueness is the only version that survives concurrency. Deduplicating after the fact, with a cleanup query that runs later, means the duplicate row already existed long enough to be picked up by whatever consumes the table, and on a sending system that is money already spent. The same argument applies to any queue: a temporary rejection from a provider is not a reason to drop work, and a duplicate is not something to repair afterwards.

What this costs is a jobs table, a worker and a progress endpoint, plus an interface that can show a bar instead of a spinner. What it does not remove is the need for an upper bound. A queue makes a large import survivable, not free.

How to check it worked

The accepting side should answer in milliseconds regardless of size. Time it against a real file:

curl -s -o /dev/null -w "%{http_code} %{time_total}\n" \
  -H "Content-Type: application/json" \
  -H "Idempotency-Key: import-2f9c" \
  -d @/tmp/list-100k.json \
  https://example.com/api/contacts/import
# 202 0.284

Send the same file a second time with the same key. The status is still 202, the job identifier is the same one, and the row count does not move. That is the double paste, neutralised.

Then watch the job finish and confirm the result is exact:

SELECT j.total, j.processed, j.state,
       (SELECT count(*) FROM contacts WHERE list_id = j.list_id) AS in_table,
       (SELECT count(DISTINCT msisdn) FROM contacts WHERE list_id = j.list_id) AS distinct_rows
FROM jobs j WHERE j.id = $1;

processed equals total, and in_table equals distinct_rows. If the pasted list contained duplicates, in_table is lower than total by exactly that many, and the job report should say so in the interface rather than leaving the user to wonder where the missing rows went.

What to watch out for

• The body size limit on the proxy is separate from the timeout. A three megabyte paste is rejected before it reaches your handler unless the limit is raised, and the resulting error looks nothing like a timeout.
• Store the payload, not the parsed result. Parsing is the part most likely to change, and a stored raw payload lets you reprocess a job after fixing a parser bug.
• Report rejected rows with their line numbers and a reason, as a file the user can download. A job that silently imports ninety eight thousand of a hundred thousand rows is worse than one that fails loudly.
• Put retention on the jobs table from the start. Raw payloads are large, and a table of three megabyte blobs that nobody prunes becomes a disk problem faster than anything else in the schema.
• A job that is running when the process restarts must be picked up again, which means the worker has to survive a reboot the same way the rest of the service does.

The general shape is worth keeping. Any operation whose size is chosen by the user is an operation that will eventually exceed whatever deadline sits in front of it, and the request cycle is the wrong place to discover that. Accept the input, persist it, answer with something the user can watch, and do the work where a failure costs a retry rather than a half written table. The same reasoning is what turns a slow sending pipeline into a fast one: small units, a recorded position and a worker that can be stopped at any moment, which is most of the story behind where throughput actually comes from.