The simple example of computing an average over a single column makes it seem like each columns data is stored by itself, but as soon as you want to do something with the correlation of two columns (such as a simple single column group by) doesnt that mean that each column would have to store both the column's value and some kind of row identifier that lets you join with the other columns in the same record? I've never looked into columnar stores so this is an honest question, I just always assumed thats how it would have to be structured
You correlate positionally, within a block/rowgroup.
If you run e.g. a `SELECT SUM(col1) WHERE col2 = x`, a columnar DB will typically produce produce a bitmap from of col2, use it to mask col1, then sum what's left—all very efficient operations.
> a columnar DB will typically produce produce a bitmap from of col2
Bitmaps fell out of fashion after the branch predictor became the slowest part of vectorized execution.
Most columnar dbs use compact selection vectors and loop over the values within them, rather than an if() over each row.
Imagine you had 1024 rows in the col1 column and only 1 row is selected, then this optimization has to only do 3-4 branches before the vector is done vs a bit-map which has to always do 1024 checks.
This became relevant somewhere in the mid 2010s and is called out in the Hive design[1] docs from that era.
"if you have worked with analytical systems in the past, you may assume that DuckDB cannot handle transactional workloads. But DuckDB has been built as a transactional, multi-connection database with full MVCC and transaction isolation since day one. Most users just never needed that in a single-user scenario. It turns out DuckDB handles transactions well: it's fast enough to compete with general-purpose databases like PostgreSQL on quite a few workloads, and the client/server pattern finally lets that machinery shine in multi-tenant, long-running deployments."
Does anyone have any links to benchmarks or stories of attempting this in practice? (Even what their internal benchmark that lead them to say "it's fast enough to compete with general-purpose databases like PostgreSQL on quite a few workloads" was?)
The DuckDB devs measured it ~30x faster than PostgreSQL (and ~3x faster than Arrow Flight) for bulk transfer, which pg is known to be very slow at, and a bit faster (<2x) than pg for small writes up to 8 threads, after which they say that pg continues to scale well with number of threads but DuckDB plateaus.
reply