Re:ClickHouse matches PG for single-row UPDATEs and 4000 x f...
by compumike on Sunday August 17, 2025 @03:59PM
(#44934444)
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.)
Re:ClickHouse matches PG for single-row UPDATEs and 4000 x f...
by oulipo on Sunday August 17, 2025 @04:04PM
(#44934480)
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?
Re:ClickHouse matches PG for single-row UPDATEs and 4000 x f...
by primitivesuave on Sunday August 17, 2025 @04:27PM
(#44934662)
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.
Re:ClickHouse matches PG for single-row UPDATEs and 4000 x f...
by tehlike on Sunday August 17, 2025 @06:06PM
(#44935339)
We started seeing some developments there, using hybrid approaches like postgres+parquet or iceberg, or citus columnar
Re:ClickHouse matches PG for single-row UPDATEs and 4000 x f...
by DrBenCarson on Sunday August 17, 2025 @06:33PM
(#44935531)
Does “data for application” means the operational db?
Re:ClickHouse matches PG for single-row UPDATEs and 4000 x f...
by brewdad on Sunday August 17, 2025 @10:08PM
(#44936707)
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.
Re:ClickHouse matches PG for single-row UPDATEs and 4000 x f...
by jedberg on Monday August 18, 2025 @12:28AM
(#44937421)
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.
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.