PERF-17: Why Batch Inserts Matter — and Where We're Auditing Next

Performance series · v0.1.338

---

Background

As tenancy volumes grow, the cost of individual database INSERT statements inside loops becomes significant. Each call carries round-trip latency to Postgres, statement parsing overhead, and a separate write-ahead log entry. At scale, a naive CSV import that fires one INSERT per row can turn a 500-row upload into 500 sequential database round-trips.

PERF-17 is the platform's first structured audit of insert patterns across all data-write paths.

---

What Was Confirmed Safe

The primary tenancy data entry flows — including tenancy tenant creation — already use the correct batched pattern:

await db.insert(tenancyTenants).values(arrayOfTenants);

This emits a single INSERT ... VALUES (row1), (row2), ... statement regardless of how many rows are being written. No action is needed for these paths.

---

What Needs Further Audit

Three routers and the Inngest background function layer could not be fully verified due to their size:

src/lib/routers/csv-import.ts (22 KB) — Highest Risk

CSV import is the most common source of the N+1 insert anti-pattern. When a user uploads a spreadsheet, the naive implementation iterates over parsed rows and inserts each one individually. The fix is to collect all rows first, then insert in a single batch (or in chunks for very large files).

src/lib/routers/analytics.ts (47 KB)

Analytics writes (event tracking, aggregation updates) may accumulate per-event inserts inside processing loops. These should be batched at the point of flush.

src/lib/routers/archive.ts (28 KB)

Archive operations move records in bulk. Any row-by-row copy pattern should be replaced with a INSERT ... SELECT or a values-array batch.

src/inngest/functions/

Inngest background functions run asynchronously and are easy to overlook in performance reviews. Any function that loops over a dataset and writes to the database is a candidate for batching.

---

The Fix Pattern

Simple batch (< 500 rows)

// Collect all rows into an array first
const rows = parsedCsvData.map(record => ({
  tenancyId: record.tenancy_id,
  amount:    record.amount,
  // ...
}));

// Insert once
await db.insert(deductions).values(rows);

Chunked batch (large imports)

Postgres has a practical limit on the number of bound parameters in a single statement (typically ~65,535). For large CSV uploads, chunk the inserts:

const CHUNK_SIZE = 250;

for (let i = 0; i < rows.length; i += CHUNK_SIZE) {
  const chunk = rows.slice(i, i + CHUNK_SIZE);
  await db.insert(deductions).values(chunk);
}

A chunk size of 100–500 rows is a safe default for most schemas. Tune downward if individual rows are wide (many columns).

---

How to Identify the Anti-Pattern

Search the codebase for db.insert( calls that appear inside loop bodies:

# Find insert calls — then check if they're inside for...of or .map()
grep -n 'db\.insert(' src/lib/routers/csv-import.ts
grep -n 'db\.insert(' src/lib/routers/analytics.ts
grep -n 'db\.insert(' src/lib/routers/archive.ts
grep -rn 'db\.insert(' src/inngest/functions/

Any db.insert() call nested inside a for...of, for, while, or .map() / .forEach() loop is a candidate for batching.

---

Next Steps

The PERF-17 findings will drive follow-up tickets to patch each identified loop-insert site. Once remediated, the audit will be re-run to confirm all high-volume write paths are using batch inserts.

---

This post is part of the platform's ongoing performance data patterns series.