Snel: SQL Native Execution for LLVM

That's from October 2017 - would be great to see an update.

Have a look at duckdb, it's is another interesting tool that's columnar and embedded.

It would be amazing if the world of open-source column stores matured a little bit relative to where we are today...

I was referring to the native execution using LLVM. Which should be independent of the underlying storage strategy (column-store, row-store)

ClickHouse is easy to run on local machine and has great performance.

Yea, we really need to improve the handling of those cases. I think there's four (was three) major angles:

1) I'd hoped to get caching for JITed queries into 13 (or at least the major prerequisite), but that looks like it might miss the mark (job changes are disruptive, even if they end up allowing for more development time). The nicest bit is that that the necessary changes also result in significantly better generated code.

2) Background JIT compilation. Right now the JIT compilation happens in the foreground. We really ought to only do the IR generation in foreground, and then do the compilation in the background, while continuing with interpreted execution. Only once codegen is done, we'd redirect to the JITed program (there'd be a bit higher overhead during the interpreted phase, rechecking whether to now redirect, but not that large).

3) Improve costing logic. E.g. we don't take the size of the necessary generated code into account at the moment, and we should. The worker count isn't taken into account either.

4) Improve optimization pipeline. There's plenty cases where we don't run beneficial and cheap-ish optimization passes, and there's plenty cases where we run unlikely to be helpful and really expensive optimization passes.

Edit: Added 4).

Umbra DB was recently posted on HN and has the ability to avoid LLVM for simpler queries, in addition to novel buffer management and index approaches:

https://umbra-db.com/

Look forward to hearing more about it in the future.

In fact, I'd say it's extremely common for database workloads to be compute bound on modern hardware.

For one, storage bandwidth has increased massively (my laptop's NVMe drives can do 2 x 3.2GB/s reads, you can get quite a few into even small servers). While memory latency and CPU throughput have not increased to the same degree.

To benefit from the increases in CPU throughput, one needs to take advantage of superscalar execution. Which isn't significantly possible with interpreted execution.

> SQLite3 compiles queries into bytecode, while PostgreSQL compiles them into AST-like tree structures

FWIW, expressions are compiled into bytecode in PostgreSQL as well. While there'd plenty benefit of doing that for query trees as well, there are not quite as much raw execution speed reason for it as there is for expressions (as the individual "steps" are much coarser, so the tree walk overhead is proportionally smaller).

Being compute-bound is pretty standard for analytic queries (i.e. computing aggregate things over larger data sets). A lot of workloads do have high reuse rates of data so you'll get a lot of data cached in memory, and a lot of the processing is pretty CPU-intensive. Columnar data formats can also achieve very high compression rates, so a relatively small amount of data read off disk turns into a large number of rows. Plus, real queries often have insanely complex expressions (giant case statements, for example), that can burn a lot of compute.

It's very different from a OLTP workload where a query will read 10s or 100s of rows via a btree index.

Actually for the case it was on SSD. Most of the access is to compressed columnar data. The main table had 500+ columns of ~140m rows but with very low entropy (e.g. gender, city/district). Each column had its own file. And queries were relatively simple. At 300MB/s (at the time) it was pretty fast. Data was mmap since as in most real life scenarios, the access to the columns had a power law distribution. Most queries could resolve with RAM data (~24GB/s at the time). Data size was over 10x the size of RAM (~128GB). IO is fast if you do mostly sequential access. Note server NVMe today is ~8x faster so the principle still applies.

The main thing here is the simplicity of the architecture and combining HyPer, careful mmap usage, and having SQLite engine do all the other non-critical work. So this way this system could be implemented on time by 1 main developer (rockstar dev, Marcelo) and a tiny little help from me.

(Actual author of the idea/architecture here, not mentioned on the paper, but that's life, haha)

Ok you have to elaborate a bit further here. Does this mean they are actually leveraging llvm to, at runtime, compile queries?