5 gotchas I hit moving LLM logs from Postgres to ClickHouse

The developer behind Spanlens, an open-source LLM observability platform, migrated the platform's `requests` table from Supabase Postgres to ClickHouse after recognizing that the insert-heavy, append-only workload would cause Postgres performance to degrade at scale. During the migration, the developer encountered five key pitfalls, including ClickHouse's requirement for space-separated timestamps instead of ISO 8601 format, and its silent conversion of numeric columns to JSON strings in `JSONEachRow` output. To prevent data loss during the transition, the developer implemented a fallback design that writes failed ClickHouse inserts to a Supabase `requests_fallback` table, with a cron job replaying the data back into ClickHouse every five minutes.

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 https://github.com/spanlens/Spanlens 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. php 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. js 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. js 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. js // 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 https://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.