in DuckDB, if you have a 500m row table and you want to estimate the size of it as a CSV, how would you do it? Originally was using bit_length with VARCHAR casting, but this is REALLY expensive. Maybe just adding a limit and inflating would give an estimate, but error-prone b/c of drift Any ideas? - ThreadSky

hamilton.bsky.social • 192 days ago

in DuckDB, if you have a 500m row table and you want to estimate the size of it as a CSV, how would you do it?

Originally was using bit_length with VARCHAR casting, but this is REALLY expensive. Maybe just adding a limit and inflating would give an estimate, but error-prone b/c of drift

Any ideas?

Comments

greg.materialmachinelearn.ing•192 days ago

Also how has this thread gone on so long without someone suggesting simply smacking your user’s hand for considering writing half a billion rows to csv?

hamilton.bsky.social•192 days ago

users definitely love that

betasci.bsky.social•191 days ago

Had a case where we needed to export a few billion row table to CSV because the client was, quote "used to working with CSV in Excel, so that's gonna be easiest for us" 🫠

greg.materialmachinelearn.ing•192 days ago

How accurate do you need to be? I find most of the time csvs are about 10x the same data in parquet (mostly numeric and low-cardinality varchar in my world), so you’d probably have a reasonable order of magnitude estimate 10xing the DuckDB table size.

hamilton.bsky.social•192 days ago

yeah that'd definitely be a cheaper strategy. The cost of an export failing in duckdb-wasm due to memory issues is pretty high, so we can't underestimate too much. But overestimating too much makes it useless.

duckdb-wasm has a brutally hard cap here.

jdlong.cerebralmastication.com•192 days ago

You’re gonna make me do an OLS regression. On Saturday. I find this question oddly compelling.

Lots of varchar columns?

dinomirmt.bsky.social•191 days ago

Should column stats help here? https://duckdb.org/docs/guides/meta/summarize.html

jdlong.cerebralmastication.com•191 days ago

too slow at scale :(
the size of export is dominated by varchar fields. and those have to have their average length calculated. And that is really hard to do quickly. And even sampling requires a full table scan, so it has a fixed cost even if the sample size is small.

dinomirmt.bsky.social•191 days ago

I was wondering (and could not find) if internal column stats are not filled at ingestion, hence you could use them directly. Same cost, just already incurred. But no idea about this level of internals.

hamilton.bsky.social•191 days ago

I don't think the internals have string lengths for VARCHARs ~ just some notion of max / min / nulls per row group

hamilton.bsky.social•191 days ago

you can look at call pragma_storage_info('table_name') to see what's available. Only for tables

jdlong.cerebralmastication.com•191 days ago

That was my impression too but will validate to be sure. If I don’t forget. Because, squirrel!

jdlong.cerebralmastication.com•191 days ago

you may be right, but I don't see pre-calculated length on strings. And that's the pain point.

dinomirmt.bsky.social•191 days ago

That should be a simple fix @duckdb.org

jdlong.cerebralmastication.com•192 days ago

What’s your target error? +/-10% ? Less?

hamilton.bsky.social•192 days ago

Love to nerd snipe!

1. must be pure SQL (duckdb)
2. must run in < 1sec for any size table
3. target error of +/-10% is great!

hamilton.bsky.social•192 days ago

and yes, assume tons of varchars

hamilton.bsky.social•192 days ago

and of all these assumptions to relax, if it takes more than 1 sec but still a reasonable amount of time, then that would work as well

bschmidt.bsky.social•192 days ago

Is USING SAMPLE out of the question? I might assume all column lens are either normally or log normally distributed, take mean, median and std dev of bit length for both the lengths AND the log lengths over a 1k row sample, infer distribution for each column.

jdlong.cerebralmastication.com•192 days ago

The pure SQL solution is challenging as I want to dynamically ask "what's the average length and number of varchar columns in table xyz?" and I can't quite figure that out in pure SQL. Trivial with a wee bit of python. How can I do that with SQL only?

birdhalfbaked.com•191 days ago

If you do need accuracy well unfortunately you're trading off error for speed. It's gonna come down to row sampling of some sort. Not enough info on what it is you're actually wanting to do (show a user some estimation? Apply the estimation to bandwidth managers? Something else?). Good luck!

birdhalfbaked.com•191 days ago

Honestly the best if it's for estimation would be if you controlled the schema. You can generate your own samples, get a rough mean of the sizes and use that as a baseline size per row.

wang.social•190 days ago

Is there any way to write a plugin to duckdb that would allow you to look at raw memory offsets of a cursor or get micro-timings? Ultimately if it has row offsets and you assume varchar pointers were allocated roughly sequentially in the heap, these could point the way.

wang.social•190 days ago

Oh look, there’s an Arrow interface that gives you pointers! https://duckdb.org/docs/api/c/data_chunk.html

hamilton.bsky.social•190 days ago

great idea! I'm going to see if https://github.com/duckdb/duckdb/pull/12631 helps first, but if not ... your suggestion feels directionally correct

wang.social•190 days ago

It feels somehow isomorphic to a binary sort or a quicksort kind of thing… You’re guessing the “bytes/row” of each partition as you sample pointers, and gradually reducing the error of that estimate.
If you have multiple varchar columns then there’s a fun question of whether they are independent

Comments

Posting Rules

Reply