Hi Hung,
The performance issues you're experiencing with Delta tables and deletion vectors are common challenges when working with Delta Live Tables. Let me address your questions:
How to Optimize Spark Read Performance with Deletion Vectors
Deletion vectors can significantly impact read performance, especially when they accumulate over time. Here's how to optimize:
1. Strategic OPTIMIZE Scheduling: Run OPTIMIZE operations after significant write/delete operations rather than on a fixed schedule.
2. Proper VACUUM Implementation: Ensure your VACUUM operations are actually removing the physical files by checking the retention period against your table history.
3. Monitor Deletion Vector Accumulation: Use system tables like `delta.table_history` to track how many deletion vectors are being created and applied.
4. Consider Disabling Deletion Vectors: If your workload is read-heavy with infrequent writes, consider disabling deletion vectors for those specific tables.
Why OPTIMIZE May Not Improve Performance
Your OPTIMIZE commands might not be improving performance for several reasons:
1. Deletion Vectors Still Exist: OPTIMIZE alone doesn't remove deletion vectors completely - it applies them to create new data files but the deletion vector files themselves may still exist until VACUUM is run.
2. Incomplete Application: OPTIMIZE might not rewrite all files with deletion vectors, especially if they don't meet compaction criteria.
3. Read Overhead Remains: After OPTIMIZE, if deletion vectors aren't fully purged, readers still need to process them, causing overhead.
4. Timing Issues: The performance benefits of OPTIMIZE might be negated if new deletion vectors are created shortly after optimization.
Alternative Strategies to Mitigate Performance Impact
1. Force Hard Deletes for Critical Tables: For tables where read performance is critical, implement a workflow that ensures hard deletes rather than relying on deletion vectors:
- Run DELETE operations followed by OPTIMIZE
- Use REORG TABLE with APPLY (PURGE) explicitly
- Follow with VACUUM using appropriate retention periods
2. Partition Optimization: Ensure your tables are properly partitioned to limit the scope of operations that generate deletion vectors. Traditional Hive partitioning is not recommended. Delta Liquid Clustering is the way to go!
3. Batch Updates: Consolidate your update/delete operations to minimize the frequency of deletion vector creation.
4. Selective Deletion Vector Usage: Consider a hybrid approach where deletion vectors are only enabled for specific tables or partitions based on their access patterns.
5. Read-Optimized Copies: For critical read workloads, consider maintaining read-optimized copies of tables where deletion vectors are regularly purged.
6. Upgrade Runtime: Ensure you're using Databricks Runtime 14.3 LTS or above, which includes optimizations for deletion vectors.
Remember that deletion vectors trade faster writes for potentially slower reads. If your workload is read-heavy, you may need to be more aggressive with your optimization strategy or reconsider whether deletion vectors are appropriate for your use case.
