Enterprise only
LanceDB Enterprise provides dedupe and duplicate_pairs for finding
near duplicates in text and vector columns. Use duplicate_pairs to inspect
matching pairs and dedupe to return retained source rows. Neither function
modifies the source table.
Choose a comparison
The selected column determines the algorithm: string and MinHash signature
columns use text matching; vector columns use the vector-index workflow.
Deduplicate text with MinHash
MinHash finds lexical near duplicates, such as copies of a scraped document with small edits, without generating embeddings.Text deduplication requires an Enterprise server with native MinHash SQL
support. Check support on your deployed server; updating the client alone
does not enable these experimental functions.
documents table without WAL and a top-level body string
column, inspect candidate pairs first:
distance_threshold is estimated Jaccard distance between token-shingle
sets, from zero through one. Lower values require closer matches. The defaults
are 128 hashes, 3 words per shingle, and 16 LSH bands. No MinHash or vector
index is required for these text-column calls. LSH candidate generation can
miss pairs that meet the distance threshold.
By default, dedupe keeps the smallest snapshot row ID in each connected
component. Pass resolution => 'direct_representative' to remove only rows
that directly match a retained representative. Both policies retain rows
with NULL or no-token text.
To reuse signatures across queries, materialize them in a new table:
minhash_distance and
minhash_lsh_keys also support explicit signature scoring and candidate-key
generation.
See the MinHash SQL guide for signature compatibility,
configuration, resolution policies, resource limits, and batch materialization.
Vector example data
Connect with an Enterprise connection. A small fixture keeps the distances easy to check by hand.Create the example table
Create the example table
curation_embeddings has eight records. Rows 10 and 20 are near-identical;
rows 30, 40 and 50 form one looser cluster; the rest are distinct.dedupe and duplicate_pairs read the vector index, so the column needs one
before either function runs; a query against unindexed fragments is rejected
rather than silently returning stale or wrong results.For an l2 index, distance_threshold and the distance column below are
both squared L2, not the plain L2 a plain SQL distance() call returns.Collapse vector near-duplicates
dedupe returns one representative row per cluster of records within
distance_threshold of each other — rows 10/20 collapse to one, 30/40/50
collapse to one, and the four distinct rows pass through unchanged, leaving
ids 10, 30, 60, 70 and 80:
dedupe returns full rows, not
just which ones matched. Clustering is transitive: 30 and 40 are within
distance_threshold of each other, and so are 40 and 50, so all three
collapse into one representative even though 30 and 50 alone sit outside the
threshold. Pass resolution => 'direct_representative'
for a simpler, non-transitive selection instead, if a chained cluster like that
should not merge into one representative.
Which row survives as the representative is not guaranteed to stay the same
across appends: a later row that bridges two previously separate clusters can
change which id represents the merged cluster. Do not treat a representative’s
id as a stable identifier for its cluster.
Inspect vector pairs instead
duplicate_pairs returns the matching edges themselves, without collapsing
anything — useful for review, or for a threshold that is still being tuned:
(10, 20), (30, 40) and (40, 50) — note 30 and 50
are not a direct pair here (their squared L2 distance is 0.01, outside the threshold),
even though dedupe merges all three transitively above.
Keep it current with a materialized view
Wrap either call in a materialized view so the deduplicated set stays current as new records arrive, instead of recomputing it from scratch on every curation pass:Vector index, snapshot and recall limits
Vector deduplication requires one compatible current vector index covering the scanned snapshot. Wait for indexing to finish after ingestion and inspect index coverage before running a full scan. Unindexed fragments are rejected; deleted rows are excluded at the pinned dataset version. The scan compares candidates within each physical IVF partition and index segment. It does not compare across partitions or segments. Scanning every partition therefore does not establish global all-pairs recall. Quantized indexes compare their encoded vector representations;IVF_FLAT compares the
stored vectors. Thresholds must match the index’s metric: L2 is squared L2,
cosine is 1 - cosine, dot distance is 1 - dot, and Hamming counts differing
bits. Inspect close examples and known duplicates before choosing a threshold.
duplicate_pairs reports dataset_version, segment_id and partition_id.
Retain those with the index configuration and source version when comparing
runs. Pass id_column for application IDs, rather than treating physical row
addresses as durable identifiers. A new append, index change or bridge between
components can change groups and representatives.
Efficient vector deduplication and scale qualification
Start withduplicate_pairs partition sampling,
using a recorded seed and a bounded partition count. Inspect pair density and
threshold quality before running dedupe, which always scans every partition.
Sampling partitions is not uniform row sampling and can miss rare groups.
resolution='connected_components' merges transitive chains;
direct_representative uses direct representative relationships instead.
Pair work grows quadratically within dense partitions, and a loose threshold
can emit a quadratic number of edges. An outer LIMIT is not a resource
budget for upstream pair finding or group resolution. Avoid repeatedly
materializing all pairs in a Python list; select needed columns, aggregate on
the server, and persist a reviewed result when repeated reads justify it.
Measure peak memory, spill storage, elapsed time and pair count on
representative partitions before increasing scope.
The LeRobotDataset integration illustrates a
27.6-million-row data workflow. Its timings are not a versioned qualification
of this deduplication path on arbitrary indexes or hardware. A 10-billion-row
run is not qualified by that example. Qualify source/index versions, partition
sizes, metric, threshold, worker resources, spill limits and correctness on your
deployment before extrapolating. There is no universal row-count or latency
guarantee in this guide.
Agents can follow the repository’s
vector deduplication skill
to apply these checks without inventing Function options or scale guarantees.