Application Integration & Schema Evolution

Batching, Bulk Loading, and Backpressure

Batching amortizes round trips and bulk paths reduce per-row protocol work, but larger units consume memory, locks, log, and recovery time. A safe pipeline bounds both batch size and queued work, defines partial-failure semantics, and slows producers when the database cannot keep up.

Batching and bulk loading optimize different layers.
Transaction size is a tradeoff.
Generated-key semantics are API-specific.
Partial failure needs a contract.
Backpressure bounds unfinished work.
Idempotency survives retries.

Bounded producer-to-database flow

Define an idempotent staging object before use

CREATE TABLE order_import_lines (
  import_id VARCHAR(80) NOT NULL,
  source_line_no BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  line_no INTEGER NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(19, 4) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (import_id, source_line_no),
  UNIQUE (order_id, line_no),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Bind every value; commit bounded chunks only when cross-chunk atomicity is not required.
INSERT INTO order_items (order_id, line_no, product_id, quantity, unit_price)
SELECT order_id, line_no, product_id, quantity, unit_price
FROM order_import_lines WHERE import_id = :import_id;

PostgreSQL streaming bulk path

COPY order_import_lines
  (import_id, source_line_no, order_id, line_no, product_id, quantity, unit_price)
FROM STDIN WITH (FORMAT csv, HEADER true);
-- PostgreSQL-specific. Use the driver COPY API; do not interpolate a client filename or raw SQL.
-- Validate and merge from staging under the intended transaction/failure policy.

Batch control dimensions
Bound	Why it matters	Observe
rows	statement/lock/retry work	rows/s and error position
encoded bytes	memory, packets, parser limits	queue and payload bytes
elapsed time	freshness and request deadline	batch age and commit p99
workers	database concurrency	pool wait and saturation
retries	prevents replay storms	attempt count and reconciliations