DUPLICATE_PAIRS

Applies to: Embedded ❌, Enterprise ✅ duplicate_pairs scans a vector column’s index partitions for every pair of rows closer than a distance threshold, without a full pairwise scan or a shuffle. It returns one row per close pair, not one row per source row — use it to inspect what would be collapsed before committing to dedupe, which resolves these same pairs into groups.

SELECT * FROM duplicate_pairs(
    <table>,
    <vector_column>,
    distance_threshold => <float>
    [, id_column => <column>]
    [, selection => 'all' | 'first' | 'random', count => <int>, seed => <int>]
)

SELECT * FROM duplicate_pairs(
    'frames',
    'vec',
    distance_threshold => 0.02
)

Earlier releases spelled this vector_duplicate_pairs; that name is rejected on current deployments.

Input

Argument	Type	Required	Meaning
table name	string literal	yes	the table to scan
vector column	string literal	yes	the indexed vector column to compare
`distance_threshold`	float	yes	maximum distance for a pair to count as close
`id_column`	column name	no	project this column’s value instead of the internal row ID — see Application-level identifiers
`selection`	`'all'` (default) \| `'first'` \| `'random'`	no	which index partitions to scan
`count`	int	no	number of partitions to sample when `selection => 'random'`
`seed`	int	no	random seed when `selection => 'random'`

resolution is refused on duplicate_pairs: a pair has no notion of a group, only dedupe accepts it.

Output

One row per close pair found, in a fixed schema independent of the source table:

Column	Type	Meaning
`row_id_a`	uint64	internal row ID of the first row in the pair, the same identifier as the `_rowid` pseudo-column
`row_id_b`	uint64	internal row ID of the second row in the pair
`distance`	float32	distance between the pair’s vectors, at or below `distance_threshold`. For `l2`, this is the squared distance, not its square root
`segment_id`	Utf8	which index segment the pair was found in, a UUID
`partition_id`	uint64	which index partition the pair was found in
`dataset_version`	uint64	the dataset version the scan ran against

Application-level identifiers

A row ID is internal and not stable across compaction. Pass id_column, a top-level scalar column such as a primary key, to also project its value: id_a/id_b, typed like id_column, are appended alongside row_id_a/row_id_b, which remain in the output.

SELECT * FROM duplicate_pairs(
    'frames',
    'vec',
    distance_threshold => 0.02,
    id_column => 'frame_id'
)

Sampling partitions

selection scopes the scan to a sample of partitions instead of all of them, the same all (default) / first / random choice as ivf_partition. Use it to estimate a distance threshold or duplicate rate on a large table before scanning it in full:

SELECT * FROM duplicate_pairs(
    'frames',
    'vec',
    distance_threshold => 0.02,
    selection => 'random',
    count => 100,
    seed => 42
)

DEDUPE

Resolve these same pairs into one representative row per group.

Deduplicate near-duplicates

When to reach for this instead of exact-key deduplication.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

DUPLICATE_PAIRS

Input

Output

Application-level identifiers

Sampling partitions

What to read next

DEDUPE

Deduplicate near-duplicates

​Input

​Output

​Application-level identifiers

​Sampling partitions

​What to read next

DEDUPE

Deduplicate near-duplicates

Input

Output

Application-level identifiers

Sampling partitions

What to read next