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.
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.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.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.
Here is the shortlist I evaluated.
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.
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.
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');
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.
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.
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.
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.
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.
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.
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.
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.