RUEN
Package · cdo

Batch & chunking

insertBatch() and upsertBatch() turn a big array of rows into a few multi-row statements instead of one query per row. This page explains how those statements are built — the shape grouping, the chunking, and the per-row placeholder suffixing. Everything here works identically on PostgreSQL, MySQL, MariaDB and SQLite.

Why chunk at all

Databases cap the number of bound parameters and the packet size of a single statement. A naive “one giant INSERT” of 100k rows would blow past both. Chunking emits one multi-row statement per fixed-size batch, keeping every statement comfortably within limits.

Method Default chunkSize
insertBatch 1000
upsertBatch 500

An empty input short-circuits to a no-op before any SQL is built.

Streaming: a generator instead of an array

Both methods take an iterable, not an array. The difference is practical: rows are never accumulated in full — the method walks the input, buffers rows, and sends a statement the moment the buffer fills. Peak memory therefore follows the chunk size, not the size of the job.

Which means a million rows can be inserted without ever assembling them into an array:

php
$inserted = $cdo->insertBatch('users', (function () use ($csv) {
  while (($line = fgetcsv($csv)) !== false) {
      yield ['name' => $line[0], 'email' => $line[1]];
  }
})());

The same works with any generator — reading a file line by line, a cursor over another table, an external API paged request by request. An array is accepted too; it is simply already in memory by the time the call happens.

The exact memory bound

One buffer is kept per row shape (see grouping below), so the bound is chunkSize × number of distinct shapes, not just chunkSize. With uniform rows there is a single shape and no difference; with wildly mixed ones, either lower the chunkSize or feed rows of the same shape consecutively.

Return values

insertBatch() returns an int — the total number of rows inserted, captured as $inserted = $cdo->insertBatch(...). For a plain INSERT this count is reported identically across PostgreSQL, MySQL, MariaDB and SQLite.

upsertBatch() returns void by design: an upsert’s affected-row count isn’t comparable across drivers (MySQL/MariaDB count 2 per updated row, PostgreSQL 1, and IGNORE / DO NOTHING count only real inserts), so there is no stable number to return.

Placeholder suffixing

Within one chunk, every row needs its own placeholders — you can’t bind :name twice with different values. So each row’s keys are suffixed with the row’s index in the chunk (_0, _1, …):

text
rows: [{name:'A', email:'a@x'}, {name:'B', email:'b@x'}]

columns: (name, email)
values:  (:name_0, :email_0), (:name_1, :email_1)
binds:   :name_0 => 'A', :email_0 => 'a@x',
       :name_1 => 'B', :email_1 => 'b@x'

The column list is taken from the row’s keys; the values clause repeats one suffixed tuple per row; all binds are merged into a single execute. upsertBatch builds the same value tuples, then appends the driver-specific conflict clause — PostgreSQL-style ON CONFLICT on PostgreSQL and SQLite, ON DUPLICATE KEY UPDATE / INSERT IGNORE on MySQL/MariaDB (see Driver detection).

Grouping rows by shape

The generated INSERT has one column list, built from the rows in the chunk, and every row’s suffixed placeholders are emitted into the VALUES. For the statement to line up, all rows in a chunk must share the same columns.

You don’t have to arrange that yourself. Before chunking, both methods group the input by each row’s column signature — the sorted set of its non-null columns. Rows that share a signature are batched into one multi-row statement; rows of a different shape form their own statement. This mirrors Hibernate’s @DynamicInsert.

The grouping fixes a bug where a single chunk holding rows with different null patterns produced a column/value count mismatch — invalid SQL like (a, b) VALUES (1, 2), (3). Now each statement only ever sees rows of one shape, so the column list and every value tuple always line up.

Rows are grouped by shape

Because rows are grouped by signature, they are re-ordered — all rows of the first shape are written before the next. For a plain batch insert this is harmless, but do not rely on auto-increment ids following your input array order when rows differ in shape.

Per-row NULL stripping

As with single insert(), a null value is removed from that row — the column falls to its database default. This is exactly what defines a row’s signature: two rows with different null columns have different shapes and land in different groups (and therefore different statements).

A row with only NULLs is rejected

A row whose columns are all null has nothing to insert and raises a CDOException.

Chunk size trade-offs

  • Smaller chunks — less memory per statement, more round-trips, safer for very wide rows (many columns × many rows approaches the parameter cap faster).
  • Larger chunks — fewer round-trips, higher throughput, more memory and more bound parameters per statement.

Tune from the defaults if your rows are unusually wide or narrow. A rough guide: keep chunkSize × columnsPerRow well under your driver’s bound-parameter limit.

Atomicity

Each chunk is its own statement. insertBatch / upsertBatch do not open a transaction across chunks — if the fifth chunk fails, the first four are already committed. When you need all-or-nothing semantics across the whole batch, wrap the call in transaction(): it opens a transaction, commits when the closure returns and rolls back if it throws.

php
$cdo->transaction(function () use ($cdo, $rows) {
  $cdo->insertBatch('users', $rows);
});

A transaction holds everything until the end

Wrapping the whole batch means the database holds locks and uncommitted data until the last chunk. For genuinely large loads that can cost more than the ability to retry the failed piece — choose deliberately rather than by default.