5 gotchas I hit moving LLM logs from Postgres to ClickHouse

The problem

I am building Spanlens, an open-source LLM observability platform. Every call to OpenAI, Anthropic, or Gemini gets recorded with its model, latency, tokens, cost, and full request and response body. At low traffic on Supabase Postgres this was fine, but I could already see a few signs that this specific table would not stay fine for long.

The requests table will dominate the DB at any meaningful scale. Every other table is bounded by org or project counts, but requests grows with every API call.
Date-range scans on created_at are the dashboard's primary query pattern, and Postgres cannot compress the JSON body columns well, so these queries would get slower as the table grew.
Daily backup size scales with table size, and backing up large JSON bodies in Postgres looked like a clear path to pain.
Aggregation queries like "top expensive prompts in 7 days" that work in milliseconds on a small table are not going to scale that way.

So I migrated to ClickHouse early, before it became a fire. This post is what I wish I had known before the migration, with 5 gotchas that bit me and the fallback design I built so I would not lose data while finding them.

If you want to see the full implementation in context, Spanlens is open source on GitHub under MIT.

Why ClickHouse and not something else

Here is the shortlist I evaluated.

TimescaleDB was too close to Postgres and didn't really solve the storage cost concern.
DuckDB is great for embedded analytics but I wanted a managed service.
BigQuery's pricing model felt unpredictable.
ClickHouse Cloud's Development tier was cheap to start with, and columnar compression handles JSON body blobs well.

The decision driver was the workload shape. It is insert-heavy, append-only, and almost all reads are time-range scans with one or two equality filters like organization_id and model. ClickHouse is built for exactly this.

The new architecture

I kept Postgres (Supabase) for everything relational with RLS, including orgs, projects, members, API keys, prompts, alerts, and billing. ClickHouse holds only one table called requests and it is the one that will grow.

Web/Server  -> ClickHouse  (write path, fire-and-forget INSERT)
            |  on failure
            v
            -> Supabase requests_fallback  (durable queue)
            |  every 5 minutes (cron)
            v
            -> replay back into ClickHouse

This is the outbox-ish pattern. I will come back to why.

Reads always go through a single helper that injects scope and retention.

// apps/server/src/lib/requests-query.ts
export async function requestsScope(
  orgId: string,
  opts: { ignoreRetention?: boolean } = {},
) {
  const plan = await getOrgPlan(orgId)            // 'free' | 'pro' | 'team'
  const retentionDays = LOG_RETENTION_DAYS[plan]  // 14 | 90 | 365
  const whereScope = opts.ignoreRetention
    ? 'organization_id = {orgId:UUID}'
    : 'organization_id = {orgId:UUID} ' +
      'AND created_at >= now() - INTERVAL {retentionDays:UInt32} DAY'
  return { whereScope, scopeParams: { orgId, retentionDays }, plan }
}

Direct getClickhouse().query() calls outside this helper are something I avoid. Multi-tenant data leaks are the worst kind of bug, and ClickHouse has no row-level security, so the discipline has to live in the query layer.

Gotcha 1. DateTime64 rejects the Z suffix

JavaScript's new Date().toISOString() returns 2026-05-16T11:49:23.749Z. ClickHouse expects 2026-05-16 11:49:23.749 with a space instead of T and no trailing Z. Insert with the JS default and you get this.

Code: 27. DB::Exception: Cannot parse input: expected " " but got "T"

I added a tiny helper and banned .toISOString() in any code path that writes to ClickHouse.

export function toClickhouseTimestamp(d: Date): string {
  return d.toISOString().replace('T', ' ').replace('Z', '');
}

Reading is the reverse. If you parse a DateTime64 back into a JS Date, you need to put T and Z back.

const date = new Date(row.created_at.replace(' ', 'T') + 'Z');

Gotcha 2. Every number comes back as a string

This one is sneaky because the bug is silent.

ClickHouse's JSONEachRow format returns all numeric columns including Decimal(18, 8), UInt64, and Int32 as JSON strings, not numbers.

{ "cost_usd": "0.00012345", "tokens": "421" }

Then your innocent r.cost_usd + 1 does string concatenation, so "0.00012345" + 1 === "0.000123451". No error. Just wrong.

The fix is mechanical but you have to do it everywhere.

const rows = (await ch.query(...)).map(r => ({
  ...r,
  cost_usd: Number(r.cost_usd ?? 0),
  tokens:   Number(r.tokens ?? 0),
}));

I now treat this as a strict boundary. The helper that wraps ch.query() does the coercion before anything else touches the rows.

Gotcha 3. No ilike, no nulls last

ClickHouse does not have Postgres's ILIKE. If your previous code looked like .ilike('model', '%gpt%'), the direct rewrite is this.

WHERE positionCaseInsensitive(model, 'gpt') > 0

NULLS LAST ordering is a similar story. It has to be explicit instead of implicit.

ORDER BY cost_usd DESC NULLS LAST

Both are easy fixes once you know about them, but they are easy to miss if you rewrite queries in a hurry because they are syntactic differences, not semantic ones. The previous query keeps "working" with no SQL error but silently changes behavior.

Gotcha 4. New column means deploy ordering matters

ClickHouse rejects unknown fields in JSONEachRow inserts by default.

Code: 117. Unknown field 'truncated'

If you add a new column in your INSERT code, ship that code, and your production ClickHouse cluster has not run the migration yet, every insert from that pod fails until the migration lands. Streaming and non-streaming, all of it.

I addressed this with two patterns.

First, run the migration before deploying the code. ALTER TABLE ... ADD COLUMN IF NOT EXISTS lands first, then the code that writes the column ships.

Second, I added a belt-and-suspenders setting.

// lib/clickhouse.ts
const ch = createClient({
  url,
  clickhouse_settings: { input_format_skip_unknown_fields: 1 },
});

This silently skips unknown columns instead of failing the insert. It rescues you from deployment-order mistakes, but it also hides typos. Pair it with a smoke-test that asserts new fields actually make it in.

Gotcha 5. Every failed insert is data loss unless you queue

ClickHouse has good uptime, but "good" isn't 100%. And LLM logs are write-heavy in a way that means every dropped insert is a dollar of cost data you will never get back.

I added a Postgres-backed fallback queue.

export async function logRequestAsync(data: RequestLogData) {
  try {
    await getClickhouse().insert({
      table: 'requests',
      format: 'JSONEachRow',
      values: [data],
    })
  } catch (err) {
    // ClickHouse rejecting or unreachable, queue to durable Postgres backup
    const message = err instanceof Error ? err.message : String(err)
    await supabaseAdmin.from('requests_fallback').insert({
      payload: data,
      organization_id: data.organization_id,
      last_error: message.slice(0, 500),
    })
  }
}

A cron job at /cron/replay-fallback runs every 5 minutes and drains the queue.

export async function replayFallbackQueue() {
  // 1. Drop poisoned rows first (7+ days old or 100+ retries) in one DELETE
  //    so the limited batch budget goes to fresh entries.
  const expiry = new Date(Date.now() - 7 * 86400_000).toISOString()
  await supabaseAdmin.from('requests_fallback')
    .delete()
    .or(`created_at.lt.${expiry},retry_count.gte.100`)

  // 2. Pull the next 50 in FIFO order so a long outage drains in arrival order.
  const { data: rows } = await supabaseAdmin.from('requests_fallback')
    .select('id, payload')
    .order('created_at', { ascending: true })
    .limit(50)

  if (!rows?.length) return

  // 3. One bulk INSERT for the whole batch instead of N round trips.
  //    ClickHouse JSONEachRow accepts arrays trivially.
  try {
    await getClickhouse().insert({
      table: 'requests',
      format: 'JSONEachRow',
      values: rows.map(r => r.payload),
    })
    // 4. Success ??delete the entire batch in one query.
    await supabaseAdmin.from('requests_fallback')
      .delete()
      .in('id', rows.map(r => r.id))
  } catch {
    // Leave them in the queue. Next cron run picks them up and the
    // expiry step above eventually drops them if they stay stuck.
  }
}

Two design choices worth calling out.

I use Postgres for the queue instead of Redis. I already had Postgres for transactional state. Adding Redis just for a recovery queue would be a separate failure domain. Postgres going down is so much worse than ClickHouse going down that pairing them is fine, because if Postgres is also down then the whole product is down and that is a more obvious incident than missing logs.

I do not deduplicate. The requests table has no unique constraint, so a race could insert a row twice. I accepted this trade-off because duplicate logs are a UI cosmetic problem, not a billing problem. If I ever sell on "exactly-once logging" I will redesign this.

Where I am now

It is still early days at Spanlens, so I do not have dramatic before-and-after benchmarks to share. What I can say qualitatively is this.

The architecture feels right. Reads are fast, writes are fire-and-forget, and the storage shape no longer fights the workload shape.
The fallback queue exists and tests pass, but I have not yet had a real long outage to stress it. I would rather have it and not need it than the other way around.
I am no longer worried about the requests table dominating my Postgres backups or query times as Spanlens grows.

The single biggest lesson is to invest in the safety net before you need it.

What I would do differently

A few things in hindsight.

Set input_format_skip_unknown_fields: 1 from day one. It is a small change that buys real resilience against deploy-order mistakes.

Add a synthetic write and read smoke test on every deploy that confirms a known row makes it through. This catches typos that the unknown-field setting now hides.

Make helpers the API boundary loudly. Direct ch.query() calls are the easiest way to introduce a multi-tenant leak. I plan to enforce this with CI lint rules.

Wrapping up

If you are building anything write-heavy with time-range queries on top, whether that is observability, audit logs, event streams, or IoT telemetry, ClickHouse is worth considering early instead of waiting until Postgres becomes a fire. The footguns are real but they all show up early, and the runtime characteristics after that are very pleasant.

Spanlens, the open-source LLM observability platform I built this for, is on GitHub at github.com/spanlens/Spanlens under MIT. The migration helpers quoted here are all under apps/server/src/lib/. If you have done a similar migration or are staring at one, I would love to hear what gotchas hit you in the comments.

推荐订阅源

DEV Community