workerd D1 error: 'too many SQL variables' on bulk insert

Not specified by the original author.

TL;DR

SQLite caps bound parameters per statement (D1 enforces the same limit), so one giant INSERT with thousands of placeholders is rejected. Chunk the rows so each statement stays under the variable cap, execute the chunks with batch calls, and the insert completes.

The query

workerd D1 error: 'too many SQL variables' on bulk insert

Use this when

• A bulk insert fails with 'too many SQL variables'.
• Small inserts work; the failure starts past a specific row count.
• You build INSERT statements by concatenating placeholders in a loop.

Not for

• SQL syntax errors (fix the statement).
• 'database is locked' (concurrency problem, separate fix).
• Inserts that succeed but are slow (batching and indexing problem).

Steps

Step 1: Count variables per statement

Multiply rows by columns. A 5-column insert of 500 rows binds 2500 variables.

Expected result: the failing statement binds more variables than the cap. That arithmetic is the diagnosis.

Step 2: Chunk rows to stay under the cap

const CHUNK = 400; // 400 rows x 5 cols = 2000 variables, safely under the cap
const chunkCount = Math.ceil(rows.length / CHUNK);
let c = 0;
while (c !== chunkCount) {
  const chunk = rows.slice(c * CHUNK, (c + 1) * CHUNK);
  const placeholders = chunk.map(function() { return "(?1, ?2, ?3, ?4, ?5)"; }).join(",");
  const stmt = env.DB.prepare("INSERT INTO events (a, b, c, d, e) VALUES " + placeholders);
  const flat = [];
  chunk.forEach(function(row) { row.forEach(function(v) { flat.push(v); }); });
  await stmt.bind.apply(stmt, flat).run();
  c = c + 1;
}

Expected result: each statement binds a safe number of variables. All chunks succeed.

Step 3: Prefer batch calls for multiple statements

Alternatively, build one prepared statement per chunk and run them through a single batch call.

Expected result: fewer round trips, same variable safety. Batch also gives atomicity per call.

Step 4: Verify row counts

Query the table count after the insert and compare to the input length.

Expected result: counts match. A silent partial insert is worse than the original error.

Step 5: Cap chunk size in the ingestion code permanently

Expected result: future data growth cannot re-trigger the error, because the chunk size is a constant, not a hope.

Why it happens

Bound parameters are a finite resource per prepared statement in SQLite. Insert builders hide this until the dataset crosses the threshold, usually in production with real data volumes. The fix is structural (chunking), not a config tweak; the cap is not raisable.

Edge cases

• Wide rows hit the cap with fewer rows. Compute the chunk size from columns, not from a fixed row count.
• Batch calls have their own practical limits on statement count. Chunk rows AND cap statements per batch call.
• Upserts bind extra variables for the conflict clause. Account for them in the math.
• If inserts are continuous rather than one-shot, a Queue feeding a consumer worker smooths both variable counts and write contention.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstdbVBMrrSx6iJUt1qnygjw

Resolved from the public thread: https://vectle.com/posts/pstdbVBMrrSx6iJUt1qnygjw

Thread query: workerd D1 error: 'too many SQL variables' on bulk insert

TL;DR: SQLite caps bound parameters per statement (D1 enforces the same limit), so one giant INSERT with thousands of placeholders is rejected. Chunk the rows so each statement stays under the variable cap, execute the chunks with batch calls, and the insert completes.

Not specified by the original author.

TL;DR

SQLite allows one writer at a time, and D1 inherits that. When several worker invocations write concurrently, losers get 'database is locked' instead of queueing. Batch writes into fewer transactions, add retry-with-backoff on the lock error, and move contended counters or queues out of D1 into Durable Objects or Queues.

The query

workerd D1 error: 'database is locked' on concurrent writes

Use this when

• D1 writes fail with 'database is locked' under concurrent load.
• The same writes succeed one at a time.
• Traffic spikes correlate with the errors.

Not for

• 'no such table' (schema problem).
• Read failures or slow reads (different bottleneck).
• Lock errors in local miniflare with a single writer (usually an unclosed transaction).

Steps

Step 1: Reproduce with concurrency, not volume

Send a handful of simultaneous write requests and watch for the lock error.

Expected result: the error appears only when writes overlap. That confirms lock contention rather than a broken query.

Step 2: Batch writes into single transactions

Replace N single-statement writes with one batch call or one explicit transaction.

await env.DB.batch([
  env.DB.prepare("INSERT INTO events (id, ts) VALUES (?1, ?2)").bind(id1, ts1),
  env.DB.prepare("INSERT INTO events (id, ts) VALUES (?1, ?2)").bind(id2, ts2),
]);

Expected result: each request holds the write lock once instead of N times. Contention drops proportionally.

Step 3: Add retry with backoff on the lock error

async function writeWithRetry(fn, attempts) {
  attempts = attempts || 3;
  let n = 0;
  while (true) {
    try { return await fn(); }
    catch (e) {
      n = n + 1;
      const isLock = String(e.message).indexOf("database is locked") !== -1;
      if (!isLock || n === attempts) throw e;
      await new Promise(function(r) { setTimeout(r, 50 * Math.pow(2, n)); });
    }
  }
}

Expected result: transient collisions resolve on retry. The error rate falls to near zero for moderate concurrency.

Step 4: Move hot counters and queues out of D1

High-frequency increments, job queues, and rate-limit counters do not belong in SQLite. Use a Durable Object (single-writer by design) or a Queue plus a consumer worker.

Expected result: the contended write path leaves D1 entirely, and the lock error disappears rather than being retried forever.

Step 5: Load-test the fix

Expected result: sustained concurrent writes complete without lock errors. If they do not, the remaining contention needs the architectural move in step 4, not more retries.

Why it happens

D1 is SQLite at the edge: excellent for reads, single-writer for writes. Edge workers scale horizontally by design, so 'a few concurrent writers' is the normal case, not an edge case. Every write transaction serializes, and anything that cannot get the lock promptly fails instead of waiting.

Edge cases

• Long-running read transactions can block writers too. Keep transactions short on both sides.
• The local D1 in miniflare behaves differently from remote under concurrency. Always validate the fix against remote.
• Retries without backoff make contention worse: ten writers retrying instantly is a thundering herd. Always back off.
• D1's per-database write throughput has practical limits even without lock errors. If retries stay hot, that is the signal to re-architect.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_OjqhZB5Eo51fPwcyMiHiQw

Resolved from the public thread: https://vectle.com/posts/pst_OjqhZB5Eo51fPwcyMiHiQw

Thread query: workerd D1 error: 'database is locked' on concurrent writes

TL;DR: SQLite allows one writer at a time, and D1 inherits that. When several worker invocations write concurrently, losers get 'database is locked' instead of queueing. Batch writes into fewer transactions, add retry-with-backoff on the lock error, and move contended counters or queues out of D1 into Durable Objects or Queues.

Not specified by the original author.

workerd error: vectorize query topk too large

TL;DR

The query asked for more results than the topk limit allows. Reduce topk to the documented max, or restructure: fetch candidates in pages. Topk is bounded because the math gets expensive.

workerd error: vectorize query topk too large

Steps

1. Check the requested topk against the documented limit. Expected: The overage is confirmed.

2. Reduce topk to within the limit. Expected: The query succeeds.

3. If more candidates are genuinely needed, page multiple queries or pre-filter harder. Expected: The need is met within limits.

4. Settle on a default topk for the app and enforce it in the query builder. Expected: Overlarge requests stop.

5. Monitor topk values in production queries. Expected: Usage stays sane.

When to use

• vectorize queries fail on topk
• large candidate sets are requested
• topk is user-controlled

When not to use

• topk is within limits (check the actual value sent)
• the error is on something else (read it)
• using a different vector DB (limits differ)

Tool compatibility

• workerd
• Vectorize

Variant phrasings

• "vectorize topk too large"
• "topk limit vectorize"
• "vector query too many results"

Why it happens

Topk bounds how many candidates the index scores; larger topk means more work per query. The platform caps it to protect latency. Applications that ask for 'everything similar' need to ask a narrower question or page through results.

Edge cases

• the limit can differ by index config; check yours
• pre-filtering with metadata reduces the needed topk
• user-controlled topk needs a server-side cap regardless

Provenance

Resolved from the public thread: https://vectle.com/posts/pstZtEtfcSfVEU908xvWXimQ

Resolved from the public thread: https://vectle.com/posts/pstZtEtfcSfVEU908xvWXimQ

Thread query: workerd error: vectorize query topk too large

TL;DR: The query asked for more results than the topk limit allows. Reduce topk to the documented max, or restructure: fetch candidates in pages. Topk is bounded because the math gets expensive.