[hypequery](https://hypequery.com/) › [Recipes](https://hypequery.com/recipes) › ClickHouse async inserts from Node.js: per-row vs batch vs async_insert
Index for agents: https://hypequery.com/llms.txt

# ClickHouse async inserts from Node.js: per-row vs batch vs async_insert (https://hypequery.com/recipes/async-inserts-node)

Insert the same 1,000 rows from Node per row, in client batches and with async_insert, and count the parts each creates: 1,000, 4, 10 and 1.

## Run it

```bash
npx @hypequery/cli create async-inserts-node -y
cd async-inserts-node
pnpm install
pnpm dev
```

- Packages: @hypequery/clickhouse (tested from hypequery 2.14.0)
- Stack: node, clickhouse
- Source: https://github.com/hypequery/hypequery/tree/main/recipes/async-inserts-node
- Test: `pnpm test`
- Verify: `node dist/recipe.js --json`

| Env var | Required | Description |
| --- | --- | --- |
| CLICKHOUSE_URL | no | ClickHouse HTTP endpoint (default http://localhost:8123) |
| CLICKHOUSE_USERNAME | no | ClickHouse user with INSERT, CREATE TABLE and SYSTEM rights (default: default) |
| CLICKHOUSE_PASSWORD | no | ClickHouse password (default: hypequery, the seed's password) |
| CLICKHOUSE_DATABASE | no | Database for the recipe's ingest table (default hypequery_examples) |

Every synchronous `INSERT` into a MergeTree table writes at least one new part, so 1,000 single-row inserts make 1,000 parts. Batch in the client when you can (1,000 rows in batches of 250 made 4 parts); when many small writers cannot batch, set `async_insert = 1` and the server buffers their rows and writes one part per flush (10 parts here, with 100 inserts in flight and a 500 ms flush; the exact count depends on how many inserts land in each flush window, so the test only bounds it). Keep `wait_for_async_insert = 1`, the default, so each insert returns only after its row is written and errors reach the caller; with `0` the calls returned while the table was still empty. The recipe uses hypequery's typed `db.insert()`, which sends JSONEachRow through the official `@clickhouse/client`.

## Demo

```text
1000 rows into a MergeTree table, four ways (merges stopped, so parts = parts created)

strategy                               inserts  parts  rows  rows when calls returned   time
per-row INSERT                            1000   1000  1000                      1000   7.5-8.7 s
client batches of 250                        4      4  1000                      1000   41-48 ms
async_insert, wait=1, 100 in flight       1000     10  1000                      1000   5.4 s
async_insert, wait=0, then flush          1000      1  1000                         0   3.6-4.6 s
```

Parts and rows are pinned in `output.json`; times are from a laptop (8 cores, ClickHouse in Docker) and are not pinned. The async rows above use a fixed 500 ms flush so the part count is reproducible; with the default adaptive timeout, one run of the same 1,000 rows took 2.2 s and made 13 parts.

## Install

```bash
npx @hypequery/cli create async-inserts-node -y
cd async-inserts-node
docker compose -f seed/docker-compose.yml up -d --wait && (cd seed && pnpm install && pnpm seed)   # or set CLICKHOUSE_* for your own ClickHouse
pnpm install
pnpm dev
```

`pnpm dev` creates `async_inserts_node_events`, stops background merges on it so every new part stays visible, runs the four strategies, prints the table and drops the table. It needs a user that may `CREATE`, `INSERT` and run `SYSTEM STOP MERGES` / `SYSTEM FLUSH ASYNC INSERT QUEUE`.

## What it shows

- **Typed inserts** (`src/ingest.ts`): `db.insert('async_inserts_node_events').values(rows)` checks row shapes against the table type; `.settings({ async_insert: 1, ... })` turns on server-side batching per call.
- **Parts created, measured** (`src/table.ts`): active parts and rows from `system.parts`, with merges stopped so nothing hides the count.
- **What is actually sent** (`expected.sql`): the test reads the statement back from `system.query_log`. It is `INSERT INTO async_inserts_node_events FORMAT JSONEachRow`; the rows go in the request body and `async_insert` travels as a setting.
- **Fire and forget, visibly** (`src/smoke.test.ts`): with `wait_for_async_insert = 0` all 1,000 calls returned while the table had 0 rows; the rows appeared only at the flush.

## Key file

```ts
// 2. Buffer in the app and send one INSERT per batch: one part per batch.
for (let i = 0; i < rows.length; i += batchSize) {
  await db.insert('async_inserts_node_events').values(rows.slice(i, i + batchSize)).execute();
}

// 3. Many small writers: let the server batch. Each call resolves once its row is in a part.
await Promise.all(rows.map((row) =>
  db.insert('async_inserts_node_events').values(row)
    .settings({ async_insert: 1, wait_for_async_insert: 1 })
    .execute(),
));
```

## Which one to use

- **You control the writer and can buffer**: batch in the client. Fewest parts, fastest, and errors come back on the call that sent the batch. Bigger batches mean fewer parts and less merge work.
- **Many processes, each sending a few rows** (serverless functions, webhooks, per-request logging): `async_insert = 1` with `wait_for_async_insert = 1`. Batching comes from concurrency: rows that arrive within one flush window share a part. Sent one at a time and awaited, async inserts batch nothing: 50 sequential awaited inserts made 50 parts and took 5.5 s (about 110 ms each, the flush wait), slower than plain inserts.
- **`wait_for_async_insert = 0`** only when losing rows on a crash is acceptable. The call returns once the row is queued; parse errors and server restarts are never reported back.

## Surprises worth knowing

- **Too many parts throttles, then fails.** The per-row run ends at exactly 1,000 parts in one partition, the default `parts_to_delay_insert`; the next insert was delayed (`DelayedInserts = 1` in `system.query_log`), and at `parts_to_throw_insert` (3,000) inserts fail. Background merges normally keep you below that, but a per-row ingest loop can outrun them.
- **`written_rows` is 0 for async inserts.** The summary hypequery returns from `execute()` reports `written_rows: '0'` even with `wait_for_async_insert = 1`, because the flush writes the rows, not the INSERT. Check `system.asynchronous_insert_log` (one row per insert, with its `flush_query_id`) instead.
- **`async_insert_max_query_number` did not flush.** It is 450 by default, yet 1,000 queued inserts with a 60 s timeout stayed in the buffer until `SYSTEM FLUSH ASYNC INSERT QUEUE`. Flushes here came from the busy timeout or the manual flush; buffer size (`async_insert_max_data_size`, 10 MiB) is the other trigger.
- **The connection pool caps concurrency.** Each awaited async insert holds an HTTP connection until its flush, and `@clickhouse/client` opens 10 by default; `src/client.ts` sets `max_open_connections: 100` to keep 100 in flight.
- **A readonly API user cannot insert.** Give ingest its own user with its own settings profile. See [ClickHouse settings for application backends](https://hypequery.com/blog/clickhouse-settings-for-application-backends).

## Architecture

```text
src/ingest.ts ── db.insert(table).values(rows).settings({...}).execute()
                     │  hypequery InsertBuilder → @clickhouse/client.insert(JSONEachRow)
                     ▼
ClickHouse   sync:  one part per INSERT
             async: server-side buffer (per query and settings) ── flush on timeout / size ──▶ one part per flush
                     │
src/table.ts ── system.parts (merges stopped) ──▶ output.json (parts and rows, asserted in CI)
```

## Adapt it

- **Your table.** Add it to `src/schema.ts` (or generate it with `npx hypequery generate`), and `db.insert()` type-checks your rows against it.
- **Production settings.** Drop `async_insert_use_adaptive_busy_timeout: 0` and the 500 ms timeout; the adaptive default (50 to 200 ms) is tuned for latency. Do not stop merges: the recipe does that only to make every part countable.
- **Set it once.** Put `async_insert: 1` in the ingest user's settings profile instead of on every call, so a forgotten `.settings()` cannot fall back to one part per row.
- **Deduplication.** On replicated tables, retried async inserts are deduplicated only with `async_insert_deduplicate = 1`.

## Related recipes

- [cursor-pagination](https://hypequery.com/recipes/cursor-pagination): reading the same kind of event table back, page by page.

## Related examples

- [materialized-view-rollups](../../examples/materialized-view-rollups): roll up rows as they land.

Tested on ClickHouse 25.8 with `@hypequery/clickhouse` 2.14.

## Key file: src/ingest.ts

```typescript
import { db } from './client.js';
import { TABLE } from './schema.js';

/** One row as sent: UInt64 as a string, DateTime as Unix seconds. */
export interface Row {
  event_id: string;
  tenant_id: number;
  user_id: number;
  event_type: string;
  occurred_at: number;
}

/** 1. One INSERT per row: one part per row. The pattern to avoid. */
export async function insertPerRow(rows: Row[]): Promise<void> {
  for (const row of rows) {
    await db.insert(TABLE).values(row).execute();
  }
}

/** 2. Buffer in the app and send one INSERT per batch: one part per batch. */
export async function insertBatched(rows: Row[], batchSize: number): Promise<void> {
  for (let i = 0; i < rows.length; i += batchSize) {
    await db.insert(TABLE).values(rows.slice(i, i + batchSize)).execute();
  }
}

/**
 * Server-side batching settings. The adaptive timeout is switched off so the
 * flush interval is a fixed 500 ms and the part count is reproducible; in
 * production the default adaptive 50-200 ms is usually right.
 */
export const ASYNC = {
  async_insert: 1,
  async_insert_use_adaptive_busy_timeout: 0,
  async_insert_busy_timeout_ms: 500,
} as const;

/**
 * 3. async_insert = 1, wait_for_async_insert = 1: every row is its own
 * INSERT, the server buffers them and writes one part per flush, and each
 * call resolves only once its row is in a part. Throughput comes from
 * concurrency: `concurrency` inserts in flight share one flush.
 */
export async function insertAsyncWait(rows: Row[], concurrency: number): Promise<void> {
  for (let i = 0; i < rows.length; i += concurrency) {
    await Promise.all(
      rows.slice(i, i + concurrency).map((row) =>
        db.insert(TABLE).values(row).settings({ ...ASYNC, wait_for_async_insert: 1 }).execute(),
      ),
    );
  }
}

/**
 * 4. async_insert = 1, wait_for_async_insert = 0: fire and forget. Each call
 * returns once the server has queued the row, before it is written, so a
 * server crash or a bad row is never reported back to the caller.
 */
export async function insertAsyncNoWait(rows: Row[], busyTimeoutMs = 60_000): Promise<void> {
  for (const row of rows) {
    await db
      .insert(TABLE)
      .values(row)
      .settings({ ...ASYNC, async_insert_busy_timeout_ms: busyTimeoutMs, wait_for_async_insert: 0 })
      .execute();
  }
}

/** The same 1,000 synthetic rows for every strategy. */
export function makeRows(n = 1000): Row[] {
  const start = Date.UTC(2026, 10, 1) / 1000;
  return Array.from({ length: n }, (_, i) => ({
    event_id: String(i + 1),
    tenant_id: 1 + (i % 5),
    user_id: 100 + (i % 37),
    event_type: ['page_view', 'api_call', 'query_run'][i % 3],
    occurred_at: start + i,
  }));
}
```

## Dataset: src/schema.ts

```typescript
/**
 * The recipe's own ingest table. Merges are stopped on it while measuring
 * (see src/table.ts), so the active part count is exactly the number of
 * parts the inserts created.
 */
export type Schema = {
  async_inserts_node_events: {
    event_id: 'UInt64';
    tenant_id: 'UInt32';
    user_id: 'UInt32';
    event_type: 'LowCardinality(String)';
    occurred_at: "DateTime('UTC')";
  };
};

export const TABLE = 'async_inserts_node_events' as const;

export const CREATE_TABLE = `
CREATE TABLE IF NOT EXISTS ${TABLE}
(
    event_id     UInt64,
    tenant_id    UInt32,
    user_id      UInt32,
    event_type   LowCardinality(String),
    occurred_at  DateTime('UTC')
)
ENGINE = MergeTree
ORDER BY (tenant_id, occurred_at, event_id)`;
```

## Generated SQL

```sql
INSERT INTO async_inserts_node_events FORMAT JSONEachRow
```

## Output on the seed dataset

```json
[
  {
    "strategy": "per-row INSERT",
    "inserts": 1000,
    "parts": 1000,
    "rows": 1000,
    "rowsWhenCallsReturned": 1000
  },
  {
    "strategy": "client batches of 250",
    "inserts": 4,
    "parts": 4,
    "rows": 1000,
    "rowsWhenCallsReturned": 1000
  },
  {
    "strategy": "async_insert, wait=1, 100 in flight",
    "inserts": 1000,
    "parts": 10,
    "rows": 1000,
    "rowsWhenCallsReturned": 1000
  },
  {
    "strategy": "async_insert, wait=0, then flush",
    "inserts": 1000,
    "parts": 1,
    "rows": 1000,
    "rowsWhenCallsReturned": 0
  }
]
```

## Related

- [ClickHouse materialized view rollups: incremental vs refreshable](https://hypequery.com/examples/materialized-view-rollups): Pre-aggregate a million events two ways, incremental and refreshable, and query both through one typed hypequery dataset.
- [Cursor pagination in ClickHouse: keyset vs LIMIT/OFFSET, measured](https://hypequery.com/recipes/cursor-pagination): Page a tenant's events newest first with a keyset cursor on (occurred_at, event_id) and see from EXPLAIN and query_log why it beats OFFSET on deep pages.
- [All recipes](https://hypequery.com/recipes)
- [/clickhouse-nodejs](https://hypequery.com/clickhouse-nodejs)
- [/clickhouse-js](https://hypequery.com/clickhouse-js)
- [/clickhouse-real-time-analytics](https://hypequery.com/clickhouse-real-time-analytics)