Hi all - Delta partitioning question - We are using open-source Delta on S3. Is it okay to partition delta table by an

id

with around 500k unique values. We are using merge operation in that table using

id

and we will be querying using Trino by that

id

. I know those are too many partitions but if the use case is just to query/merge that table by

id

, will this work?

you can partition by anything as long as you have a decent amount of data in each partition. read performance suffers as you have more and more objects to read

def don’t partition by

id

if you have 500k unique values! Everything will work but you gonna have perf issues

Thanks Chris and Theo. I agree with you both but if the use case is to update 5-10% of data every hour and ability to query this table by the id, will there be performance issues?

I guess if you always query by

id

that’s “fine” but querying without using

id

will be very bad… You don’t have access to a

date

column that you can partition on? Also if it’s only 500k total records, I would not partition the data.

a much better idea would be to partition by buckets of IDs

yes definitely!

Thanks Chris. Really appreciate it. I was also thinking about hash/range partition but just to make it easy to query from Trino , we were thinking of partitioning by id.

can you extend trino’s sql engine? then you could handle the query translation in the engine itself

Everything mentioned is spot on. One thing that wasn’t mentioned is high cardinality partitioning tends to impact write performance quite a bit. It can be really bad if there is a distribution skew in the partition values as well. You have to weigh the benefits. If your reads are always very selective wrt partition value, and read performance is much more important than write performance, then high cardinality partitioning might be OK. In general using a generated column that applies a range partition and a native delta lake reader that supports them will be the right overall balance.

thanks @Chris @dohar silalahi , yet to go through the video

high cardinality partitioning tends to impact write performance quite a bit.

I may be wrong- but if the writes are exclusively in overwrite/merge mode, wouldn’t they actually benefit from partitioning of output table by id that we merge on ?

with Deletion Vectors, the benefit is much smaller than it used to be, and even then it still depends on the overall distribution of your data. you want each Partition to have a decent amount of data in it - if you partition by ID, what do you expect the average size of a partition will be? 1MB? 1GB? my general rule of thumb is partitions should be at least 1GB bare minimum. you still have dataskipping by IDs as well. this is where you can definitely experiment and there probably is a global optimum to be found between partition structure, file size, range partitions, etc.

If you write/read using only ID then maybe a key-value DB is more adapted 😆

my general rule of thumb is partitions should be at least 1GB bare minimum. you still have dataskipping by IDs as well.

hmm

If you write/read using only ID then maybe a key-value DB is more adapted

🙂

Truth is if you are only looking up by id and a small number at a time, a KV store is much better suited.

thought we would test predicate pushdown and partition pruning to their absolute limits haha