I've been using ClickHouse too and it's significantly faster than Postgres for certain analytical workloads. I benchmarked it and while Postgres took 47 seconds, ClickHouse finished within 700ms when performing a query on the OpenFoodFacts dataset (~9GB). Interestingly enough TimescaleDB (Postgres extension) took 6 seconds.
| Insertion | Query speed | |
|---|---|---|
| Clickhouse | 23.65 MB/s | ≈650ms |
| TimescaleDB | 12.79 MB/s | ≈6s |
| Postgres | - | ≈47s |
| SQLite | 45.77 MB/s^1^ | ≈22s |
| DuckDB | 8.27 MB/s^1^ | crashed |
^All^ ^actions^ ^were^ ^performed^ ^through^ ^Datagrip^
^1^ ^Insertion^ ^speed^ ^is^ ^influenced^ ^by^ ^reduced^ ^networking^ ^overhead^ ^due^ ^to^ ^the^ ^databases^ ^being^ ^in-process.^
Updates and deletes don't work as well and not being able to perform an upsert can be quite annoying. However, I found the ReplacingMergeTree and AggregatingMergeTree table engines to be good replacements so far.
Also there's !clickhouse@programming.dev