Batch and bulk endpoints

API design: REST, HTTP, GraphQL and gRPC Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Predict the output

A batch of 3 inserts uses one statement. psql runs with ON_ERROR_STOP, so the script stops at the first error. What happens?

SQL · PostgreSQL 17
CREATE TABLE items (sku text PRIMARY KEY, qty int CHECK (qty > 0));
INSERT INTO items VALUES ('a', 1), ('b', 0), ('c', 2)
ON CONFLICT DO NOTHING;
SELECT count(*) FROM items;

Options
Hint 1 of 1

Which errors does ON CONFLICT handle?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A The INSERT fails with a CHECK violation, so nothing is inserted

Correct. ON CONFLICT only covers unique conflicts; the bad row aborts the whole statement.

A single INSERT is atomic: a CHECK violation on one row aborts the whole statement, and ON CONFLICT only handles unique or exclusion conflicts. With ON_ERROR_STOP, psql exits before the SELECT runs.

Why not the others?
  • B 2. One statement is atomic: a CHECK failure rejects all rows.
  • C 3. qty 0 violates the CHECK.
  • D 0. The script stops at the error before the SELECT runs.

30-second answer

Accept many items in one request with a firm maximum (for example 500), validate each, and return a per-item result: success with the new id or an error, in the same order or keyed by a client-supplied reference. Decide and document whether the batch is all-or-nothing (one transaction) or partial success; partial is more useful for imports, and 207 Multi-Status or 200 with per-item statuses communicates it.

Practise saying it · 30 s. No recording, just a timer

Design a bulk endpoint.

Say your answer out loud, then check which of these points you covered:

  • Max size
  • Per-item results
  • Atomic or partial, documented
  • Multi-row insert
  • Idempotency

Deep dive

Insert with one multi-row statement or COPY, not a loop of single inserts.

Large imports become a long-running operation: upload a file, return 202, report per-row errors.

Rate limits should count items, not just requests.

Idempotency matters even more: one retried batch could duplicate 500 rows.

Common mistakes

  • Unlimited batch sizes.
  • Returning one error for the whole batch with no item detail.
  • Partial success without telling the client which items failed.

Senior take & follow-ups

Cover size limits, per-item results, atomicity choice, efficient inserts and idempotency.

Follow-up questions

Think of your answer first, then open the hint.

  1. All-or-nothing or partial?

    Show hint

    Depends on the domain: financial postings all-or-nothing; contact imports partial.

  2. Why client references?

    Show hint

    So clients match results to inputs without relying on order.

Related questions

Suggest a fix if something here is wrong or out of date.