Comment by alexgarcia-xyz | Hacker News Reader

Comment by alexgarcia-xyz | Hacker News Reader

This is an awesome project, I love SQLite extensions and I think they have a ton of use. Giants props to Dan here, I haven't seen many SQLite extensions written in Zig, and I'm learning a ton reading through the source code.

The column-oriented data is stored in large BLOBs inside of regular SQLite tables. It uses the SQLite incremental BLOB I/O API [0] to incrementally read/write data in a column oriented way.

However, this project (and other SQLite extensions) will eventually hit a limit with SQLite's virtual table API. When you create a virtual table, you can perform a number of optimizations on queries. For examples, SQLite will tell your virtual table implementation the WHERE clauses that appear on the virtual table, any ORDER BYs, which columns are SELECT'ed, and other limited information. This allows extension developers to do things like predicate + projection pushdowns to make queries faster.

Unfortunately, it doesn't offer many ways to make analytical queries faster. For example, no matter what you do, a `SELECT COUNT(*) FROM my_vtab` will always iterate through every single row in your virtual table to determine a count. There's no "shortcut" to provide top-level counts. Same with other aggregate functions like SUM() or AVERAGE(), SQLite will perform full scans and do calculations themselves.

So for this project, while column-oriented datasets could make analytical queries like that much faster, the SQLite API does limit you quite a bunch. I'm sure there are workarounds around this (by custom UDFs or exposing other query systems), but would be hefty to add.

That being said, I still love this project! Really would love to see if there's any size benefit to this, and will definitely contribute more when I get a chance. Great job Dan!

[0] https://www.sqlite.org/c3ref/blob_open.html

Thanks for the kind words, Alex! You have probably written more great sqlite extensions than anyone, so your feedback is very meaningful.

You are right that there are limitations. The sqlite virtual table API is very clearly designed with row-oriented access in mind. However, there are still ways that stanchion makes analytical queries faster: lazily loading segments (which are the BLOBs that contain the actual values) so data that is not accessed is skipped, using encodings like bit packing to reduce the size of data that needs to be traversed on disk, and allowing users to specify a clustered index to ensure records are ordered in a way that allows querying to minimize data access.

One area that I want to explore more deeply is the xFindFunction callback in the virtual table API. It allows the a virtual table to "overload" a function with its own implementation. I believe there are some opportunities to work around some of the limitations you are describing, but I'm not even sure at this point if they can apply to aggregate functions.

This is all theoretical until there are solid benchmarks, which is something that I want to add in the near term. And if you know of any workarounds to the limitations that you think may be useful, I am all ears!

That's great to hear! The clustered index sounds really cool. Especially since SQLite tells you about ORDER BYs in xBestIndex (with the nOrderBy[0]), so it would be super cool to have super-fast ORDER BYs with those.

Very interested to see how xFindFunction works for you. One limitation I've found is that you don't know if a user uses a xFindFunction inside of xBestIndex (ie at query time), unless 1) it's part of a WHERE clause and 2) only two arguments are provided, the first being a column value and the 2nd any literal. I've found this limiting in the past, only having 1 argument to work with in that narrow case. But I'm sure there's clever tricks there!

One trick I've noticed: You can kindof detect a COUNT(*) with the `colUsed` field in xBestIndex. In that case, `colUsed` will be 0 (ie 0 columns are requested), so you can use that as a signal to just iterate over N times instead of accessing the underlying data. Still slow, but you can probably do something like ~1 million/sec, but better than accessing the data that many times!

[0] https://www.sqlite.org/vtab.html#order_by_and_orderbyconsume...

That is a great trick for COUNT(*), thank you!

That's disappointing about xFindFunction. Once I start digging into it more, I will let you know if I find any other clever tricks that you might be able to use in your extensions as well.

Have you ever reached out to the sqlite team about limitations in the virtual table mechanism that you have encountered? I'm curious how open they are to extending what is possible with virtual tables.

Just one note on using the incremental BLOB I/O API that you might want to consider (if you're not aware of it, I wasn't, found it unintuitive) is that blobs larger than page size are stored in linked lists of pages, and there is no true random access; accessing a large blob at a large offset touches all database pages til that offset.

I may be missing something, but I'm not sure you need to detect COUNT(*) specifically.

For a query that's just SELECT COUNT(*) FROM… SQLite will just iteratively call xNext/xEof to count rows. As long as you don't actually load data until some is requested by xColumn, what's the point?

And the above (lazily loading only the requested columns) is exactly what I'd expect this extension to be doing already.

> The column-oriented data is stored in large BLOBs inside of regular SQLite tables.

this is similar to how citus and hydra columnar engines for postgres work - it seems to be a fairly successful use-case.

This is true, and I had the similar issues with the virtual table API. They did add some things, but they won't do everything. There are some other problems with SQLite as well. However, there are benefits of SQLite, and I wrote some extensions, too.

Is there any value in just treating rows as columns and vice versa?

Reply on news.ycombinator.com