Bulk database writes

A telemetry producer has already handed batch B17 to a durable ingestion service. Its thousand observations wait for a database writer. The intake receipt means the service retained responsibility; it does not mean those observations are present in the query table. This guide begins at that writer and ends with a recorded database outcome. It does not redesign the full telemetry platform.

Each observation has a tenant-scoped stable ID, timestamp and value. In this constructed protocol, an ID names immutable content and belongs to one original chunk. The writer preserves a manifest splitting B17 into C0 and C1, five hundred observations each. A retry uses the same membership and identity. Those boundaries remain meaningful if a process dies or delivery repeats.

We choose atomic publication per chunk, with visible progress for the overall batch. The thousand-row batch can therefore be partly committed while another chunk fails. Every chunk must eventually have a recorded committed or explicit failure outcome. The database example retains normal durability and uniqueness checks; bulk loading changes efficiency, not the meaning of accepted data. Assume fsync is enabled and synchronous_commit is on for primary-local durability; this does not promise lossless failover to an asynchronous replica.

A simple writer sends one INSERT, waits for its commit response, then sends the next. For a thousand observations that means a thousand sequential commands and transactions. Each can pay connection-protocol, parsing and commit-related overhead. Reusing a connection helps but does not combine those commands or remove per-transaction work.

INSERT INTO observations
  (tenant_id, observation_id, observed_at, value)
VALUES
  (:tenant, :id_a, :time_a, :value_a),
  (:tenant, :id_b, :time_b, :value_b);

A multirow INSERT expresses several values in one statement. A transaction can also group multiple statements into one atomic commit; those are separate choices. Sending five hundred individual INSERT statements inside one transaction saves repeated commits, but still has five hundred statements. Prepared statements and protocol pipelining can reduce other costs without changing the transaction boundary.

For an intentionally simplified comparison, suppose a write pays four milliseconds of fixed overhead plus 0.02 milliseconds per row, with no overlap. A single row costs 4.02 milliseconds; five hundred rows cost fourteen. Two such groups total twenty-eight milliseconds of this modeled work, versus 4,020 for a thousand separate writes. This excludes receipt bookkeeping, contention and real network behavior. It motivates measurement rather than predicting production speed.

Our illustrative collector flushes at five hundred rows, 128 KiB of encoded input, or fifty milliseconds since its oldest buffered row, whichever comes first. Count prevents too many tiny records, bytes prevent a few large records consuming too much memory, and age prevents a quiet producer waiting indefinitely for a full group. An oversized single observation needs an explicit rejection or alternate path.

At an assumed 256 encoded bytes per row, five hundred rows occupy 128,000 bytes, below 128 KiB, so the row cap wins. At one KiB per row, the byte cap allows only 128. At a steady hundred rows per second, the age trigger might collect about five rows in fifty milliseconds. Scheduling delay can add to collection age, so a timer value alone is not a hard end-to-end latency guarantee.

Collection delay is already part of accepted-to-visible freshness. Queueing, connection acquisition and commit still follow it. Bound the number of batches waiting in memory as well as each batch, and retain source work durably before relying on this memory. A full buffer should slow intake or invoke the defined admission policy rather than silently drop observations the service accepted.

For C0, the writer begins a transaction, inserts a unique chunk receipt and writes its five hundred observations, then commits. The receipt and rows share that transaction. A reader cannot see a committed success receipt without the corresponding rows. A statement returning successfully inside the transaction is not yet proof that the commit succeeded.

Now C0 commits, but C1 contains an invalid value. With ordinary strict loading, that transaction rolls back and leaves none of C1 committed. C0 remains present because it used a different transaction. The service records C1’s failure and B17’s partial-failure status durably. It must not describe all thousand observations as stored or retry the same bad value forever.

One transaction for all thousand would give batch-wide atomicity at a larger failure and resource scope. That is a credible alternative if consumers need all-or-nothing visibility. Chunking instead bounds retries and transaction duration, at the cost of explicit partial progress. Correcting or splitting a failed chunk creates a recorded revised operation; it must not silently change what an old chunk identity means.

For a large append-oriented load, PostgreSQL COPY FROM STDIN streams input through the client connection. It can avoid the overhead of constructing many INSERT commands. A server-file COPY reads a file available to the database server instead; these interfaces are not interchangeable merely because both contain the word COPY.

COPY does not provide INSERT’s ON CONFLICT clause. Directly loading into a constrained destination can fail on bad input or conflicting identity. Our strict example retains the default stop-on-error behavior. PostgreSQL 18 also supports limited input-conversion error skipping, but choosing that would require rejected-row accounting and a different product outcome; it is not automatic safe partial success.

A staging table is useful when records need validation or merging before publication. Stream into private staging, validate identities and values, then publish selected rows and their receipt in the target transaction. Staging adds work and storage. If its session disappears before publication, replay from the durable input. For modest bounded chunks, parameterized multirow INSERT is a reasonable simpler baseline.

C0’s commit reaches the database, but the connection breaks before the writer receives confirmation. The writer cannot know whether C0 committed. A fresh chunk ID would permit a second logical operation. Instead, retry the same tenant, batch, chunk number and immutable payload digest through the original receipt gate.

begin transaction
attempt unique receipt(tenant, batch, chunk, payload_digest)
if receipt already committed:
    verify its original digest; return its recorded outcome
else:
    insert all immutable observations for this chunk
    record committed counts in this same transaction
commit transaction
only then report the chunk as stored

The receipt has a unique key and commits with its rows. A repeated attempt atomically tries to create that receipt. If the original transaction committed, the uniqueness guard resolves to the existing receipt; compare its digest before returning that recorded outcome. If the original rolled back, a retry can acquire the key and perform the writes. Do not use an absent read from a lagging replica as permission to bypass the unique gate.

Observation uniqueness is another boundary. Our baseline rejects a duplicate observation ID appearing in a different chunk, because original membership is fixed. Supporting independently overlapping batches needs an explicit per-observation duplicate/content policy. ON CONFLICT DO NOTHING can skip a row, but cannot prove its payload matches. Retain receipts and identity records through the supported replay horizon, and keep all retries within the original budget.

Every observation still changes table data and its required indexes. Normal durable PostgreSQL writes also produce WAL used for recovery. Grouping rows can amortize fixed overhead, but a very large transaction can hold resources longer and generate a burst of log and storage activity. More concurrent writers may therefore increase waiting rather than useful committed rows per second.

Measure rows and bytes committed, transaction duration, lock waiting, database CPU, WAL activity and replica delay if replicas matter to the read promise. If the writer reports success at primary commit, a replica can still become queryable later; state that visibility boundary separately. Do not claim bulk insertion makes all read paths immediately current.

Keep uniqueness, validation and normal durability while tuning batch size and writer concurrency. Dropping indexes or changing WAL/durability settings alters correctness or recovery assumptions and may hurt online readers. Initial offline population has different tradeoffs from this live ingestion path. A measured increase in useful committed throughput, within the freshness target, is the result we need.

Choose bounded multirow INSERT chunks with atomic receipts for this moderate live stream. It keeps the writer understandable and makes strict validation and retry outcomes explicit. Evaluate COPY plus staging when measured loading overhead or larger imports justify the extra path. Keep individual prepared writes if volume is low and collection delay costs more than the saved overhead.

Change the requirement and revisit the choice. An audit export imported as one indivisible dataset may need batch-wide publication or a versioned dataset pointer. A freshness target shorter than our fifty-millisecond collection allowance requires smaller or immediate writes. Legitimate overlapping batches require per-observation reconciliation instead of interpreting every unique conflict as harmless success.

Verify a late malformed row, duplicate identity with changed content, concurrent replay of C0, a lost commit response, worker death between chunks and receipt expiry. Track partial failures and oldest uncommitted age alongside throughput. These are expected implementation behaviors and illustrative calculations, not a claim that a production database has been benchmarked or that any one batch size is universally best.