Promise Concurrency Queue
Run async operations with a concurrency limit to prevent overwhelming servers and network resources.
Batch Insert Pattern
Buffer database inserts and flush in batches for dramatically faster data loading.
Use this when:
You are loading thousands of records into a database and individual INSERTs are too slow.
The Pattern
BatchInserter.php
class BatchInserter
{
private array $buffer = [];
private int $batchSize;
private PDO $pdo;
public function __construct(PDO $pdo, int $batchSize = 100)
{
$this->pdo = $pdo;
$this->batchSize = $batchSize;
}
public function add(array $row): void
{
$this->buffer[] = $row;
if (count($this->buffer) >= $this->batchSize) {
$this->flush();
}
}
public function flush(): void
{
if (empty($this->buffer)) {
return;
}
$columns = array_keys($this->buffer[0]);
$placeholders = '(' . implode(',', array_fill(0, count($columns), '?')) . ')\;
$allPlaceholders = implode(',', array_fill(0, count($this->buffer), $placeholders));
$sql = sprintf(
'INSERT INTO records (%s) VALUES %s',
implode(',', $columns),
$allPlaceholders
);
$values = [];
foreach ($this->buffer as $row) {
foreach ($row as $value) {
$values[] = $value;
}
}
$stmt = $this->pdo->prepare($sql);
$stmt->execute($values);
$this->buffer = [];
}
}What Happens Under the Hood
Let us trace what happens when you insert 250 records with a batch size of 100:
execution-flow.txt
Record 1-99: Added to buffer → No database call
Record 100: Buffer full → Flush 100 records in ONE INSERT
Record 101-199: Added to buffer → No database call
Record 200: Buffer full → Flush 100 records in ONE INSERT
Record 201-250: Added to buffer → Call flush() manually at end
Final flush: Flush remaining 50 records in ONE INSERT
Total database calls: 3 (instead of 250)Why This Matters
Each database INSERT has overhead: network round-trip, query parsing, transaction handling. Batching reduces this overhead dramatically:
performance-comparison.txt
Single inserts: 250 records × 5ms overhead = 1,250ms
Batched inserts: 3 queries × 5ms overhead = 15ms + bulk insert time
Real-world improvement: Often 10-50x fasterUsage Example
usage.php
$inserter = new BatchInserter($pdo, 100);
foreach ($dataStream as $record) {
$inserter->add([
'name' => $record['name'],
'email' => $record['email'],
'created_at' => date('Y-m-d H:i:s'),
]);
}
// Do not forget the final flush for remaining records
$inserter->flush();The buffer pattern ensures efficient database operations regardless of how many records you process. Small datasets work fine. Large datasets stay efficient.