4 ms·

From my perspective, everything's DuckDB. Single file per database, Multiple ingestion formats, full text search, S3 support, Parquet file support, columnar st

From my perspective, everything's DuckDB. Single file per database, Multiple ingestion formats, full text search, S3 support, Parquet file support, columnar storage. fully typed. WASM version for full SQL in JavaScript.

sanderjd 10mo ago
This is a funny thread to me because my frustration is at the intersection of your comments: I keep wanting sqlite for writes (and lookups) and duckdb for reads. Are you aware of anything that works like this?
- SchwKatze 10mo ago
  I think you could build an ETL-ish workflow where you use SQLite for OLTP and DuckDB for OLAP, but I suppose it's very workload dependent, there are several tradeoffs here.
  - sanderjd 10mo ago
    Right. This is what I want, but transparently to the client. It seems fairly straightforward, but I keep looking for an existing implementation of it and haven't found one yet.
- nlittlepoole 10mo ago
  DuckDB can read/write SQLite files via extension. So you can do that now with DuckDB as is. https://duckdb.org/docs/stable/core_extensions/sqlite https://duckdb.org/docs/stable/core_extensions/sqlite
  - sanderjd 10mo ago
    My understanding is that this is still too slow for quick inserts, because duckdb (like all columnar stores) is designed for batches.
    - theanonymousone 10mo ago
      The way I understood it, you can do your inserts with SQLite "proper", and simultaneously use DuckDB for analytics (aka read-only).
      - sanderjd 10mo ago
        Aha! That makes so much sense. Thank you for this. Edit: Ah, right, the downside is that this is not going to have good olap query performance when interacting directly with the sqlite tables. So still necessary to copy out to duckdb tables (probably in batches) if this matters. Still seems very useful to me though.
        dietr1ch 10mo ago
        Analytics is done in "batches" (daily, weekly) anyways, right? We know you can't get both, row and column orders at the same time, and that continuously maintaining both means duplication and ensuring you get the worst case from both worlds. Local, row-wise writing is the way to go for write performance. Column-oriented reads are the way to do analytics at scale. It seems alright to have a sync process that does the order re-arrangement (maybe with extra precomputed statistics, and sharding to allow many workers if necessary) to let queries of now historical data run fast.
        sanderjd 10mo ago
        Not all olap-like queries are for daily reporting. I agree that the basic architecture should be row order -> delay -> column order, but the question (in my mind) is balancing the length of that delay with the usefulness of column order queries for a given workload. I seem to keep running into workloads that do inserts very quickly and then batch reads on a slower cadence (either in lockstep with the writes, or concurrently) but not on the extremely slow cadence seen in the typical olap reporting type flow. Essentially, building up state and then querying the results. I'm not so sure about "continuously maintaining both means duplication and ensuring you get the worst case from both worlds". Maybe you're right, I'm just not so sure. I agree that it's duplicating storage requirements, but is that such a big deal? And I think if fast writes and lookups and fast batch reads are both possible at the cost of storage duplication, that would actually be the best case from both worlds? I mean, this isn't that different conceptually from the architecture of log-structured merge trees, which have this same kind of "duplication" but for good purpose. (Indeed, rocksdb has been the closest thing to what I want for this workload that I've found; I just think it would be neat if I could use sqlite+duckdb instead, accepting some tradeoffs.)
swyx 10mo ago
very interesting. whats the vector indexing story like in duckdb these days? also are there sqlite-duckdb sync engines or is that an oxymoron
- cfors 10mo ago
  https://duckdb.org/docs/stable/core_extensions/vss https://duckdb.org/docs/stable/core_extensions/vss It's not bad if you need something quick. I haven't had a large need of ANN in duckdb since it's doing more analytical/exploratory needs, but it's definitely there if you need it.

sanderjd 10mo ago

This is a funny thread to me because my frustration is at the intersection of your comments: I keep wanting sqlite for writes (and lookups) and duckdb for reads. Are you aware of anything that works like this?

SchwKatze 10mo ago
I think you could build an ETL-ish workflow where you use SQLite for OLTP and DuckDB for OLAP, but I suppose it's very workload dependent, there are several tradeoffs here.
- sanderjd 10mo ago
  Right. This is what I want, but transparently to the client. It seems fairly straightforward, but I keep looking for an existing implementation of it and haven't found one yet.
nlittlepoole 10mo ago
DuckDB can read/write SQLite files via extension. So you can do that now with DuckDB as is. https://duckdb.org/docs/stable/core_extensions/sqlite https://duckdb.org/docs/stable/core_extensions/sqlite
- sanderjd 10mo ago
  My understanding is that this is still too slow for quick inserts, because duckdb (like all columnar stores) is designed for batches.
  - theanonymousone 10mo ago
    The way I understood it, you can do your inserts with SQLite "proper", and simultaneously use DuckDB for analytics (aka read-only).
    - sanderjd 10mo ago
      Aha! That makes so much sense. Thank you for this. Edit: Ah, right, the downside is that this is not going to have good olap query performance when interacting directly with the sqlite tables. So still necessary to copy out to duckdb tables (probably in batches) if this matters. Still seems very useful to me though.
      - dietr1ch 10mo ago
        Analytics is done in "batches" (daily, weekly) anyways, right? We know you can't get both, row and column orders at the same time, and that continuously maintaining both means duplication and ensuring you get the worst case from both worlds. Local, row-wise writing is the way to go for write performance. Column-oriented reads are the way to do analytics at scale. It seems alright to have a sync process that does the order re-arrangement (maybe with extra precomputed statistics, and sharding to allow many workers if necessary) to let queries of now historical data run fast.
        sanderjd 10mo ago
        Not all olap-like queries are for daily reporting. I agree that the basic architecture should be row order -> delay -> column order, but the question (in my mind) is balancing the length of that delay with the usefulness of column order queries for a given workload. I seem to keep running into workloads that do inserts very quickly and then batch reads on a slower cadence (either in lockstep with the writes, or concurrently) but not on the extremely slow cadence seen in the typical olap reporting type flow. Essentially, building up state and then querying the results. I'm not so sure about "continuously maintaining both means duplication and ensuring you get the worst case from both worlds". Maybe you're right, I'm just not so sure. I agree that it's duplicating storage requirements, but is that such a big deal? And I think if fast writes and lookups and fast batch reads are both possible at the cost of storage duplication, that would actually be the best case from both worlds? I mean, this isn't that different conceptually from the architecture of log-structured merge trees, which have this same kind of "duplication" but for good purpose. (Indeed, rocksdb has been the closest thing to what I want for this workload that I've found; I just think it would be neat if I could use sqlite+duckdb instead, accepting some tradeoffs.)

swyx 10mo ago

very interesting. whats the vector indexing story like in duckdb these days? also are there sqlite-duckdb sync engines or is that an oxymoron

cfors 10mo ago
https://duckdb.org/docs/stable/core_extensions/vss https://duckdb.org/docs/stable/core_extensions/vss It's not bad if you need something quick. I haven't had a large need of ANN in duckdb since it's doing more analytical/exploratory needs, but it's definitely there if you need it.