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, 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.