exqlite

exqlite copied to clipboard

Hi, Can in introduce you to "duckdb"

https://duckdb.org/

I believe that the C api for sqlite can be compiled against duckDB, giving some possibility that this is a straightforward integration? However, I believe that the sql variant that it speaks is postgresql, so I suspect some assumptions may be broken?

My specific use case is that I have an embedded device and need to store some simple time series (bytes through interfaces) and then do simple aggregations on them. Disk space is very precious, DB sizes will be relatively small (MBs to hundreds of MBs), however, I need to be more efficient with storage than just denormalised rows of {timestamp, tag, large_byte_counter}, and a columnar storage seems to be more efficient here. The data is generally non square, with missing entries, which excludes many of the obvious alternatives

Does this look like it might have legs?

@ewildgoose I've actually looked at duckdb and am probably more inclined to make another package specifically for duckdb as opposed to trying to shim it in with exqlite.

I too have the need for timeseries on a raspberry pi as well. Maybe if I get some time this weekend, I'll take a look. It should be realtively easy to hook up. The problem is, with the duckdb code is that it isn't a single amalgamation file so compiling it with the package is a little more difficult.

The nice thing is that if we get a db_connection-powered driver set up, we should be able to just adapt (i.e., fork) the existing Ecto.Adapters.Postgres code to use that and get most the the Ecto part of the equation "for free", if I'm understanding correctly.

I agree that it would be best in another package.

Just seeing this now as I'm looking at duckdb for similar needs. In the interest of sharing my findings:

• duckdb does produce an amalgamation file. You have to run a python script, but it's pretty straightforward.
• There are a few elixir and erlang projects for duckdb:
  • https://github.com/mmzeeman/educkdb
  • https://github.com/mpope9/exduckdb

I'm not 100% on this, but the first one appears to use full duckdb via the amalgamation file which allows you to use the extensions while the second one uses a sqlite compatible api and plugs in nicely to existing bindings but does not support extensions.

@mdwaud it seems like exduckdb could be used for the ecto_sqlite3 in place of this adapter. I tried not to tightly couple exqlite to ecto_sqlite3 in case someone comes up with a better adapter solution or wanted something like duckdb.

Btw, we have initial support for duckdb on ADBC as well: https://github.com/elixir-explorer/adbc/ - it probably makes more sense there, as it is a columnar design (as DuckDB). We can probably close this one. :)