51–60 of 62 posts
this looks so cool. is there pre-loaded a demo page loaded with tables so people can try out queries right away?
select * from 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parqu...' limit 10;
I got a data sets probably not suitable for loading into a memory table (close to 1000M rows CSV). I did split it into 20M rows chunks, read one by one into a DuckDB temporary table and exported as parquet.
SELECT using glob prefix.*.parquet where mycolumb=foobar does work but can be a bit faster. Apart from sorting the input to parquet CSVs, what can he done? The CSV chunks were already sorted.
Cool! This is the first time hearing about DuckDB, exciting as I heavily use SQLite. And these benchmarks are showing it's 6-15 times faster than sql.js (SQLite) [1], along with another small benchmark I found [2]. I usually just slather on indexes in SQLite tho, so indexed queries may not stand up as well; and might not be as fast when it's on storage, as this is comparing in memory performance (I think?), but I'll…
Do you need compression to get more bandwidth or are you trying to save money on storage costs? DuckDB is a column based db, so you are going to see a throughput increase for queries that only use a handful of columns.
I'm manually doing zlib compression for large text columns when there's an obvious opportunity, basically DIY toast [1]. Doing that allowed one SQLite DB to go from about 205 GiB to 35 GiB. And I haven't really felt any performance impact when working with the data; but definitely feel the coding overhead. And there's still so much missed opportunity for compression.
Largest RocksDB is +1 GiB/day (poorly tuned with zlib compression). I just couldn't use SQLite for that one, lots of small rows, but they compress extremely well. I never wrapped up the compression experiments on that, but look at some rough notes snappy was 430G, and lz4 level 6 was 86G. Unfortunately using RocksDB has made coding more difficult.
I think one day I'm just going to snap and build a ZipFS-like extension [2], until them I'm just trying to keep an eye out, and putting out this call for help. :3
Can I connect to Duckdb with a sql ide? E.g. dbeaver?
I'm still not sure I "get" the use case for DuckDB. From what I understand, it's like a nifty, in-memory SQL, but why is that better than just running PostGRES or Microsoft SQL server locally, where your data structures and tables and stuff have a lot more permanence? Like, my workflow is either I query an exiting remote corporate DB and do my initial data munging there, or get givne a data dump that I either work on…
DuckDB is columnar, so in theory a lot faster than Postgres or SQL server for Analytical workloads. DuckDB is to Clickhouse, TimescaleDB, Redshift, etc as SQLite is to Postgres, MySQL, SQL Server.
I'm still not sure I "get" the use case for DuckDB. From what I understand, it's like a nifty, in-memory SQL, but why is that better than just running PostGRES or Microsoft SQL server locally, where your data structures and tables and stuff have a lot more permanence? Like, my workflow is either I query an exiting remote corporate DB and do my initial data munging there, or get givne a data dump that I either work on…
To me, the use case is really obvious: when you reached for SQLite but now want something with Moar Powah. Now I've reduced it to a previously answered question: what's the use case for SQLite? ;) https://www.sqlite.org/whentouse.html That being said, I don't see the point, and shudder at the idea of a web page's javascript doing anything which needs noticeable amounts of the CPU, but I'm a non-standard user...
Earlier quoted context omitted.
Do you need compression to get more bandwidth or are you trying to save money on storage costs? DuckDB is a column based db, so you are going to see a throughput increase for queries that only use a handful of columns.
Saving money definitely, but also delaying having to scale the hardware as long as possible. I'm at 3.5 TiB NVMe (raid 1), and it would cost another $52 USD/mo to add an additional raid of 1.5 TiB NVMe @ Hetzner, not cool. Generally I'm seeing around 6:1 compression, so going from 3.5 TiB to 21 TiB is a big deal for me. I'm manually doing zlib compression for large text columns when there's an obvious opportunity, ba…
As the implementation is block-based it is also faster than the naive approach of just zipping your data files.
Earlier quoted context omitted.
Does DuckDB also use a PAX-like format like Parquet? Without going into code, the best I could find with a little googlefu is the HyPer/Data Blocks paper - is this a relevant read?
DuckDB's storage format has similar advantages as the Parquet storage format (e.g. individual columns can be read, partitions can be skipped, etc) but it is different because DuckDB's format is designed to do more than Parquet files. Parquet files are intended to store data from a single table and they are intended to be written-once, where you write the file and then never change it again. If you want to change anyt…
Earlier quoted context omitted.
Do you need compression to get more bandwidth or are you trying to save money on storage costs? DuckDB is a column based db, so you are going to see a throughput increase for queries that only use a handful of columns.
Saving money definitely, but also delaying having to scale the hardware as long as possible. I'm at 3.5 TiB NVMe (raid 1), and it would cost another $52 USD/mo to add an additional raid of 1.5 TiB NVMe @ Hetzner, not cool. Generally I'm seeing around 6:1 compression, so going from 3.5 TiB to 21 TiB is a big deal for me. I'm manually doing zlib compression for large text columns when there's an obvious opportunity, ba…