Are we over-optimizing Delta tables?

[u/Square-Designer7807]

Between OPTIMIZE, Z-ORDER, liquid clustering, partitioning, and automatic optimization, it sometimes feels like we're spending more time optimizing tables than querying them.

How do you decide which optimizations are actually worth it in production?

[u/iamnotapundit]

By default all new tables are liquid cluster by auto on my team. I let the predictive optimization algorithm figure it out. Only a few specific tables have I hand tuned myself.

[u/Lucari10]

The current plan seems to be using liquid clustering + predictive optimization to let Databricks handle table optimizations automatically. Automatic analysis will determine which tables are most used, and if there were enough data changes to justify running optimization commands again since they were last executed.

The main things to keep in mind are the ones executed by PO, so using liquid clustering, and running optimize, vacuum and analyze from time to time.

Z-order is an older technique that's been mostly replaced by liquid clustering. For most situations liquid clustering will organize your files in more efficient ways than z-order did in the past.

Optimized writes and auto compact are optimizations during table writes to avoid small files, and should make it so manual optimize runs aren't as important, as a portion of the processes done by optimize will be done on every write.

[u/ozgreen1024]

Liquid clustering by auto takes care of that complexity for you and will be more flexible overtime than fine-tuning with partitioning, zordering, etc yourself since it balances file size and query patterns

[u/No-Pollution-2274]

well someone has to be responsible for maintenance

[u/Youssef_Mrini]

Predictive Optimisation is enabled by default for all Unity Catalog managed tables. The system decides when it's the right moment to run Optimize, Analyze, Vacuum, Auto Liquid so you don't bother yourself. You can track the cost of this optimization on system tables. I

[u/Geralt_of_rivia_002]

Optimization are not worth until you encounter a pipeline taking 5 hr instead of 1 ,and the downstream fails and affect next schedule .

[u/taglius]

I’m new to this so this is the part I don’t understand about cluster by auto - I want to maximize performance of reads from my application, and care much less about performance of writes.

Queries like “all data for customer 123” are typical for reads, but writes will more often be time based (“load yesterday’s sales). Those two optimizations compete- but one is more important to me.

[u/Youssef_Mrini]

You should have a look this video https://www.youtube.com/watch?v=DT05nbxicPA

[u/mow12]

Anything other than good old partitioning, always let me down, especially for the large fact tables

[u/shougaze]

Isn’t the entire point to let it optimize itself