PostgreSQL hit a wall with dynamic attribute search. Every OTEL span has different attributes, and creating indexes for every possible JSON path wasn't feasible. Filtering/grouping across runs became impossibly slow at scale.

ClickHouse's columnar storage changes everything. Instead of storing entire rows together, it stores each column separately. This means querying just `run_id` and `duration` only reads those columns from disk, not the entire event structure.

The compression gains are also pretty amazing. Similar log attributes compress extremely well when stored together column-wise. We use ZSTD with delta encoding for timestamps and specialized codecs for different data patterns.

Our schema uses some clever @ClickHouseDB features: - Bloom filters for exact ID lookups - Token-based full-text search on attributes - minmax indexes for duration queries - Native JSON type that preserves full OTEL structure

The `attributes_text` field is a materialized column. ClickHouse auto-generates this as `toJSONString(attributes)` on insert. It lets us do full-text search on the JSON structure using tokenbf_v1 indexes without storing it twice.

Partitioning by date is perfect for logs since most queries are time-range based. Combined with ORDER BY on (environment_id, timestamp, trace_id), queries that filter by environment and time range are lightning fast.

TTL is built into the table definition: `TTL toDateTime(expires_at) + INTERVAL 7 DAY`. ClickHouse automatically drops old partitions. No cron jobs, no manual cleanup, it just handles data retention natively.

We built a dual-store architecture during migration. Both PostgreSQL and ClickHouse implement the same `IEventRepository` interface, with feature flags controlling which store is active. This enabled zero-downtime migration and easy rollback.

ClickHouse gives us eventual consistency instead of ACID guarantees. For observability data, slight delays are acceptable in exchange for massive performance gains. You don't need perfect consistency to debug a failed task run.

Materialized views unlock real-time usage metrics without expensive on-the-fly aggregations. They automatically maintain pre-aggregated stats as events are inserted.

The hardest parts? Unicode handling (unpaired surrogate pairs crash ClickHouse inserts), async insertions for write throughput, and maintaining nanosecond timestamp precision (DateTime64(9)) for accurate trace correlation.

Result: 50,000 logs per run in the dashboard (up from ~10k), instant filtering/aggregation, and we're now building log search on top of this foundation.

Checkout the full changelog for more details: https://t.co/9PzepIQuTZ

@triggerdotdev @ClickHouseDB This makes sense in general. To clarify, were you using generic Postgres? No extensions from @TigerDatabase or @neondatabase

@triggerdotdev @ClickHouseDB Perfect I need to do something similar soon and will steal your schema

@triggerdotdev @ClickHouseDB Ofc

@triggerdotdev @ClickHouseDB Great we also move to click house

@triggerdotdev @ClickHouseDB Seems like you just reinvented @hyperdxio

@triggerdotdev @ClickHouseDB How is the on base performance of it?

@triggerdotdev @ClickHouseDB The migration sounds solid. ClickHouse's performance for log data is a game changer, especially with large datasets. How's the learning curve with those specialized features?

@triggerdotdev @ClickHouseDB just migrated to clickhouse too - where are you hosting it?

@triggerdotdev @ClickHouseDB Interesting to see the shift from PostgreSQL to ClickHouse for logs.