Migrating 50M monthly events from Postgres to ClickHouse: Lessons & benchmarks

When DevMetrics.io hit 400+ connected repositories, our write pipeline on PostgreSQL began experiencing severe lock contention on analytical time-series inserts. ### The Problem: Single-Node PostgreSQL Bottleneck 1. Write amplification on JSONB payload indexes. 2. Vacuum freezes during peak European and US developer working hours. 3. Monthly storage costs on AWS RDS increasing faster than gross margins. ### The ClickHouse Migration Architecture We chose an asynchronous batching buffer backed by Go routines with a Vector daemon writing partitioned `MergeTree` tables. ```sql CREATE TABLE dev_events ( org_id UUID, event_type LowCardinality(String), event_timestamp DateTime64(3, 'UTC'), payload_json String CODEC(ZSTD(3)) ) ENGINE = MergeTree() PARTITION BY toYYYYMM(event_timestamp) ORDER BY (org_id, event_type, event_timestamp); ``` ### Results & Key Takeaways - **p99 Query Latency**: Dropped from 3,200ms to 18ms. - **Storage Compression**: 8.4x reduction in raw bytes stored on NVMe disks. - **Monthly Cloud Cost**: Down from $1,840/mo to $580/mo.