Re: Liquid clustering performance issue

HariSankar · ‎06-15-2025

Great question—and you're definitely not alone in running into this when first applying liquid clustering.

It sounds counterintuitive at first: you apply a performance optimization (clustering), but the table size increases and the queries slow down.

Let me break down what's likely happening and share some insights that might help.

Why Your Table Size Increased (3.4 GB ➝ 5 GB)

When you enable liquid clustering, the engine rewrites the table into fewer, larger files that are organized by the clustering key (in your case, a date column).

While this reduces the number of files (305 ➝ 24), it also introduces some overhead:

1. Compression efficiency drops: If the original files were compressed better (due to more uniform data chunks), the new clustered layout might actually be less

compressible—especially if the date column has a wide range of values per file.

2. Metadata overhead: Clustering introduces additional metadata to help with file pruning and optimization. That metadata has a cost, especially at scale.

3. Larger files can mean more I/O: If your queries only need small slices of data, larger files mean more unnecessary data gets read.

Why Your Queries Are Slower

Even though clustering is meant to improve performance, it depends heavily on how you query the data:

Are your queries filtering on the date column?

If not, the clustering isn’t helping prune files—and you’re paying the price of larger files without any benefit.

How selective is the date filter?

If you’re scanning large date ranges, the system still needs to read most of the files.

Was your original table accidentally optimized?

It’s possible the original 305 small files gave better parallelism or caching benefits.

What You Can Try Next

Here are some tips to investigate and improve things:

1. Check pruning effectiveness

Run this to see how much data is being skipped:

explain select * from your_table where date_col = '2024-01-01';

If it's still reading most of the data, clustering isn’t helping much yet.

2. Use `describe detail` or `input_file_name()`

These can help you analyze file sizes and distribution after clustering.

3. Test with Z-Ordering (if applicable)

If your queries filter on multiple columns (not just date), consider Z-ordering for multi-dimensional clustering.

4. Try a smaller clustering target

Smaller file sizes (using config like `delta.targetFileSize`) may help restore parallelism.

5. Review distribution of your clustering column

Is the data evenly spread across dates, or is there a skew (e.g., most records fall in a few days)? Skewed data can lead to unbalanced files, which slows things down.

In Summary

Liquid clustering is a powerful tool, but it’s not a silver bullet. Its effectiveness depends heavily on:

What column you use for clustering

How your queries are written

How your data is distributed

You’re on the right track by experimenting with it! I’d recommend measuring query performance again after a few days or with different filters—sometimes clustering

benefits kick in once the system has had time to optimize further.

harisankar