[Feature] Support snapshot-based sequence ordering for primary-key tables

[JunRuiLee]

Search before asking

• I searched in the issues and found nothing similar.

Motivation

We run a dataset management platform on top of Paimon for LLM training data. Multiple teams and pipelines (cleaning, annotation, deduplication, etc.) write to the same primary-key tables concurrently.

Each writer assigns sequence numbers independently, so sequence numbers across writers are incomparable. The only reliable cross-writer ordering signal is the commit order (snapshot id). Today Paimon resolves primary-key conflicts solely by sequence number (or sequence.field), which cannot express this.

What we need: records committed in a later snapshot always win, regardless of per-record sequence numbers.

Solution

Add a table option sequence.snapshot-ordering. When enabled, merge uses the commit snapshot id as the primary tiebreaker for primary-key conflicts, with sequence number as the secondary tiebreaker within the same snapshot. This reuses DataFileMeta.minSequenceNumber to carry the snapshot id at file level — same pattern as row-tracking tables repurposing minSequenceNumber for firstRowId.

Design Details

1. File-level stamp at commit time. Repurpose minSequenceNumber to carry the commit snapshot id, same injection point as row-id assignment.
2. Propagate through compaction. Compaction output inherits max(input snapshot ids), so it never shadows concurrent writes.
3. Inflate to record at read time. KeyValueFileReaderFactory stamps the file-level snapshot id onto each KeyValue.
4. Conditional comparator injection. Sort-merge readers use snapshot id as primary tiebreaker only when enabled.

Anything else?

No response

Are you willing to submit a PR?

• I'm willing to submit a PR!

[JingsongLi]

Is this a deduplicated pk table?

[JunRuiLee]

Is this a deduplicated pk table?

Not limited to deduplicate. This feature aims to work with all merge engines — snapshot-ordering only defines record precedence, the same way sequence number does.

[JingsongLi]

Can we use current time as a sequence field? Or is the core requirement of your business to differentiate based on snapshots?

[JingsongLi]

I am more concerned about the modifications to DataFileMeta because it is the most basic structure, unless we really introduce a major feature.

[JunRuiLee]

@JingsongLi Thanks for the question! Let me clarify.

Can we use current time as a sequence field? Or is the core requirement of your business to differentiate based on snapshots?

Unfortunately no. What we are building is essentially a git-like versioning mechanism — commit order is the source of truth for resolving conflicts. Using current time as sequence.field is evaluated at write time, not commit time, so it cannot represent the actual commit order.

I am more concerned about the modifications to DataFileMeta because it is the most basic structure, unless we really introduce a major feature.

Thank you for raising this concern — I completely understand the caution around modifying such a fundamental structure.

I've explored several alternatives (storing it in ManifestEntry, deriving it at read time, using a separate mapping), but so far haven't found a cleaner approach — mainly because the compaction pipeline operates entirely on DataFileMeta, which makes it difficult to place this field elsewhere without introducing more changes.

I'd really appreciate any suggestions if you see a better approach!

[JingsongLi]

@JunRuiLee Can we use minSequenceNumber in this situation? Actually, it has already been assigned as snapshot under row tracking.

[JunRuiLee]

@JingsongLi Thanks for the suggestion! After initial analysis, reusing minSequenceNumber for PK table looks feasible. I'll investigate further and update the design if it holds up.

[JunRuiLee]

Thanks @JingsongLi , I've adopted your suggestion to reuse minSequenceNumber and updated the design in the issue. Will open PRs soon.