[feature-request] Prune Invalid Docs During Segment Commit for Upsert Tables

[ankitsultana]

While Upsert Compaction is great, for tables which have very high ingestion throughput, it'd be ideal to prune invalid docs during segment commit itself, since compaction is costly and in many cases not able to catch up. In one of our use-cases, I think this feature would lead to a further reduction in table size of 2-3x.

This should be relatively simple to do for Full Upsert tables but I think would be harder for Partial Upsert tables.

cc: @tibrewalpratik17

[tarun11Mavani]

@ankitsultana i would like to work on this. Please assign this to me.

[tarun11Mavani]

RFC: Commit-Time Compaction for Upsert Tables

In its current implementation, users rely on Compaction Task executed on minions to reduce the disk footprint by compacting older segments. This proposal focuses on a different aspect of optimization for upsert tables: addressing the significant storage and processing overhead caused by invalid and soft-deleted records within immutable segments before they are committed.

Currently, when a consuming segment commits, it retains all records, including obsolete and soft-deleted ones. This leads to:

• Storage Inefficiency: Immutable segments hold substantially more physical records than logically valid ones.

• Query Overhead: Query engines process and filter unnecessary data, impacting performance.

• Compaction Dependency: Users rely on post-commit minion tasks for storage reclamation, adding operational complexity and delaying optimization.

In high-velocity upsert scenarios, this can result in a physical-to-logical record ratio as high as 10:1, severely impacting storage costs and query performance.

To mitigate these challenges, we propose Commit-Time Compaction for Upsert Tables. In this model, invalid and obsolete records will be removed during the segment commit process, before the segment becomes immutable.

Key Benefits

This approach aims to reduce the overall segment size and improve query efficiency by:

• Immediate Storage Optimization: Up to 60-80% reduction in physical record count for high-update workloads.

• Improved Query Performance

• Reduced Memory and I/O Footprint

• Simplified Operations: Less reliance on post-commit compaction tasks, reducing operational complexity.

Here is the RFC for the same: https://docs.google.com/document/d/1kylGQScvvP7t2Yl2Tc-2iS8XGEKuI8J5D2lGcEaZhOM/edit?usp=sharing

[ankitsultana]

This feature is now available on master.

@tarun11Mavani : let's work together on documenting it in the Wiki

[tarun11Mavani]

This feature is now available on master.

@tarun11Mavani : let's work together on documenting it in the Wiki

I am planning to add it here - https://docs.pinot.apache.org/manage-data/data-import/upsert-and-dedup/upsert
Does it make sense?

[tarun11Mavani]

Added a doc PR here - pinot-contrib/pinot-docs#446
@ankitsultana please take a look when possible.