Last updated on July 31st, 2026 at 06:57 pm
The main lesson: a large database upsert should be split into smaller chunks. This prevents prepared-statement placeholder errors while still allowing the full update to succeed as one logical operation.
What I learned
A multi-row upsert may look like one SQL statement, but every value can become a bound placeholder. When thousands of rows contain several columns, the placeholder count grows quickly.
The failure can be confusing because the application may report a database error first, then use even more memory while formatting the very large failed query for a log or exception page.
Why chunking fixes it
Chunking divides one oversized write into several smaller statements. Each statement stays within practical database and application limits.
Wrapping all chunks in one transaction keeps the operation atomic. Atomic means that either every chunk succeeds or the database rolls the whole change back.
A practical approach
- Build the records that need to be inserted or updated.
- Split the records into batches of a few hundred rows.
- Run every batch inside one database transaction.
- Test with a data set large enough to require multiple batches.
database.transaction(function () use ($records) {
foreach (array_chunk($records, 500) as $chunk) {
Model::upsert($chunk, ['unique_key'], ['value', 'updated_at']);
}
});Common mistakes
- Cause: Sending every row in one upsert. Fix: Use a bounded batch size.
- Cause: Chunking without a transaction. Fix: Wrap the complete loop in one transaction when partial updates are unsafe.
- Cause: Testing only small inputs. Fix: Add a regression test that proves several upsert statements are executed.
- Cause: Raising the memory limit first. Fix: Reduce the query size before adding more memory.
Conclusion
Large batch writes are safer when they are small at the SQL level but atomic at the business-operation level. The next action is to review any bulk insert or upsert that can grow with user data and give it a tested batch limit.