Caveat: PostgreSQL is fully transactional by default; ClickHouse isn’t. Results compare each engine’s native execution model, not identical transaction guarantees.
I appreciate that they call this out. This means that if you're using Postgres primarily for storing data where it's ok if you lose a few (like time series data) then Clickhouse is the clear and obvious choice.
But if the transactionality is key for you (like a financial application or the data for an application) then Postgres still makes more sense.
PostgreSQL defaults to: "fsync = ON" and "synchronous_commit = ON".
ClickHouse defaults to: "fsync_after_insert = false".
A more fair comparison might at least do "SET synchronous_commit TO OFF" on the PostgreSQL side. (But for this UPDATE benchmark, I think the results would still largely be the same.)
Since Postgres is open-source and the kind of database model they use is out there for so long, how come ClickHouse can't have a "postgres subset" (or just include a mini-Postgres) so that it gets the best of both worlds out of the box?
Fundamentally, Postgres is storing rows in 8kb pages of tuples, where each tuple has transaction metadata like `xmin` and `xmax` which are used in MVCC. This means a transactional update is usually only updating one block of memory + any TOASTable columns. Meanwhile, ClickHouse is storing data by columns, so a transactional update on multiple fields of a row necessarily involves updating multiple memory pages.
Personally, I architect systems to use Postgres for the write-heavy parts of a workload, and ClickHouse for the write-once parts (time series, analytics, logs, etc). ClickHouse is also the best tool I've ever found for compressing enormous datasets, and is very useful even as a simple data warehouse.
Do you need every record you add/update to the database to be there when you try to read it later or are you ok with a best effort to save that works to some 9x.xxxxx degree but occasionally drops some things?
If it needs to be there, use a a fully transactional database.
It could mean that, or it could mean your user database or where you store your blog comments or your customer's todo lists. Basically anything where you want to make sure all the data is still there the next time you go looking for it.
Comments
I appreciate that they call this out. This means that if you're using Postgres primarily for storing data where it's ok if you lose a few (like time series data) then Clickhouse is the clear and obvious choice.
But if the transactionality is key for you (like a financial application or the data for an application) then Postgres still makes more sense.
PostgreSQL defaults to: "fsync = ON" and "synchronous_commit = ON".
ClickHouse defaults to: "fsync_after_insert = false".
A more fair comparison might at least do "SET synchronous_commit TO OFF" on the PostgreSQL side. (But for this UPDATE benchmark, I think the results would still largely be the same.)
Since Postgres is open-source and the kind of database model they use is out there for so long, how come ClickHouse can't have a "postgres subset" (or just include a mini-Postgres) so that it gets the best of both worlds out of the box?
Fundamentally, Postgres is storing rows in 8kb pages of tuples, where each tuple has transaction metadata like `xmin` and `xmax` which are used in MVCC. This means a transactional update is usually only updating one block of memory + any TOASTable columns. Meanwhile, ClickHouse is storing data by columns, so a transactional update on multiple fields of a row necessarily involves updating multiple memory pages.
Personally, I architect systems to use Postgres for the write-heavy parts of a workload, and ClickHouse for the write-once parts (time series, analytics, logs, etc). ClickHouse is also the best tool I've ever found for compressing enormous datasets, and is very useful even as a simple data warehouse.
We started seeing some developments there, using hybrid approaches like postgres+parquet or iceberg, or citus columnar
Does “data for application” means the operational db?
Do you need every record you add/update to the database to be there when you try to read it later or are you ok with a best effort to save that works to some 9x.xxxxx degree but occasionally drops some things?
If it needs to be there, use a a fully transactional database.
It could mean that, or it could mean your user database or where you store your blog comments or your customer's todo lists. Basically anything where you want to make sure all the data is still there the next time you go looking for it.