MinHash SQL functions

Applies to: Embedded ❌, Enterprise ✅ MinHash compares the sets of token shingles in two texts. Use it to find lexical near duplicates, such as copies with small edits. Embedding distance measures a different kind of similarity; use vector deduplication when semantic similarity is the desired criterion. Use exact hashes when only identical values count as duplicates.

These experimental functions require an Enterprise server with native MinHash SQL support. Check the server release you run; installing a newer client does not add server capabilities.

The shared SQL and Functions execution context uses the same implementation. Persisted signatures retain their version, tokenizer profile, hash count and shingle size. Recompute signatures when upgrading to an incompatible signature version. Raw index values do not carry the SQL field metadata required to establish a compatible tokenizer and shingle configuration.

Create a signature

SELECT minhash('the quick brown fox') AS signature;
SELECT minhash(body) AS signature FROM documents;
SELECT minhash(body, 256, 3) AS signature FROM documents;

minhash(text[, num_hashes[, shingle_size]]) returns FixedSizeList<UInt16> with exactly num_hashes elements per signature. The defaults are 128 hashes and shingles of 3 consecutive tokens. num_hashes must be a constant integer from 1 through 4096; shingle_size must be a constant integer from 1 through 4294967295. More hashes increase storage and computation and reduce sampling error. The integer values occupy 2 * num_hashes bytes per row before Arrow validity and storage encoding overhead; there is no per-row header. The fixed tokenizer splits text into alphanumeric tokens and lowercases them. It retains stop words, accents and long tokens, and does not stem words. A text with fewer tokens than shingle_size contributes a single shingle containing all its tokens. NULL, empty text and text with no tokens return NULL. The result field carries the logical-type marker and configuration. The configuration is also attached to its UInt16 item field: Lance storage preserves the outer metadata, while worker function-type transport preserves the item metadata. Readers accept either and reject conflicts. Default metadata:

{
  "field_metadata": {
    "lancedb:logical_type": "minhash_signature",
    "lancedb:minhash:signature_version": "0",
    "lancedb:minhash:tokenizer": "simple_lowercase_v1",
    "lancedb:minhash:shingle_size": "3"
  },
  "item_metadata": {
    "lancedb:minhash:signature_version": "0",
    "lancedb:minhash:tokenizer": "simple_lowercase_v1",
    "lancedb:minhash:shingle_size": "3"
  }
}

Aliases and CREATE TABLE ... AS SELECT preserve this marker, including for empty or all-null results. It identifies the output of minhash, regardless of the column’s name. Distance and band-key results use their ordinary numeric and list types. Consumers require the configuration metadata and validate the list width and element type. Preserve both levels of field metadata when copying or exporting signatures; Python Arrow fields expose metadata keys and values as byte strings. An ordinary integer list is not automatically a MinHash signature. Earlier experimental BINARY signatures must be regenerated from their source text.

Store signatures in a new table

Use a fresh table name to materialize the source columns and their signatures:

CREATE TABLE documents_with_signatures AS
SELECT *, minhash(body) AS signature FROM documents;

Preserve the generated values and field metadata when storing signatures. Use minhash_distance to compare compatible signatures. Storing this column does not build a MinHash LSH index.

Add a signature column with the Python SDK

On an Enterprise server with native MinHash column-transform support, an eager column addition uses the same SQL expression as a query:

table.add_columns({"signature": "minhash(body)"})

This fills the new column for existing rows, using only the input columns the expressions reference. It supports the ordinary eager expression form of add_columns. Deferred computed= declarations and SQL ALTER TABLE ... ADD COLUMN ... AS use a separate planner and still require their own MinHash support. Earlier servers can accept SELECT minhash(body) while rejecting a column addition; use CTAS with an unused destination name on those versions. Verify the column-writing path on the deployed server version.

Estimate Jaccard distance

SELECT minhash_distance(
  minhash('the red fox jumps', 128, 1),
  minhash('the red fox runs', 128, 1)
) AS distance;

minhash_distance(left, right) returns FLOAT: one minus the fraction of hash positions that agree. Zero means identical signatures; one means no matching positions. This is an estimate of token-shingle Jaccard distance, with sampling error and possible hash collisions. A threshold on this value is not an exact Jaccard threshold. Exact token-shingle comparison is needed when exact threshold decisions are required. Both signatures must use the same version, tokenizer profile, hash count and shingle size. Missing or incompatible metadata is rejected, even for empty inputs. UNION, CASE and COALESCE reject incompatible signature configurations before execution. Individual hash elements must not be NULL. If either valid operand is NULL, the result is NULL.

One entry point for text and vectors

dedupe and duplicate_pairs select the algorithm from the column at the requested dataset snapshot. Top-level string columns use MinHash; supported vector columns retain the vector-index workflow and its metric. A text distance threshold is estimated Jaccard distance in [0, 1]; vector thresholds retain their metric’s units. Options are validated by the selected implementation. dedupe and duplicate_pairs also accept stored FixedSizeList<UInt16> MinHash columns with the configuration metadata described above:

SELECT * FROM dedupe('documents', 'signature', distance_threshold => 0.2);
SELECT * FROM duplicate_pairs('documents', 'signature', distance_threshold => 0.2);

This path reads the saved hashes instead of tokenizing and hashing text again. Hash count and shingle size come from the column; conflicting explicit options are rejected. num_bands defaults to 16 and must divide the signature width. An ordinary integer list without MinHash metadata is not a signature column. EXPLAIN ANALYZE reports column_signatures and computed_signatures so the input path can be verified. Producing candidate pairs reads the signature column; returning retained rows still reads the original rows. Eager add_columns calculates existing rows once. Keep the saved signatures in sync when text changes. NULL signatures stay in deduplicated output. Compare the two paths on the same source data and with matching signature settings. text_dedupe and text_duplicate_pairs remain supported for raw-text queries. Use the shared names above for signature-column dispatch and configuration inference.

Generate candidate keys

SELECT minhash_lsh_keys(minhash(body), 16) AS band_keys FROM documents;

minhash_lsh_keys(signature[, num_bands]) returns a list of UINT64 keys. The default is 16 bands. The band count must be a constant integer from 1 through 256 and divide the signature’s hash count. Each key includes its band number; matching keys identify potential duplicate pairs. A null signature returns a null list. Generate candidates only among signatures with the same configuration and band count. Deduplicate pairs that share several bands, then score the candidates with minhash_distance. LSH can miss pairs that meet the distance threshold; increasing the number of bands improves coverage but increases candidate work. For ideal independent MinHash values, similarity s, b bands and r hashes per band give candidate probability 1 - (1 - s^r)^b. This is a tuning model, not a completeness guarantee for the implementation. Identical or heavily templated documents can produce quadratic numbers of candidate pairs. A SQL self-join over keys does not by itself impose a memory, pair-count or concurrency bound. A per-row nearest-neighbor query with a top-k limit also does not enumerate all batch duplicate pairs.

Run complete batch text deduplication

SELECT row_id_a, row_id_b, distance, dataset_version
FROM duplicate_pairs(
  'documents', 'body', distance_threshold => 0.2,
  num_hashes => 128, shingle_size => 3, num_bands => 16
);

CREATE MATERIALIZED VIEW documents_clean AS
SELECT * FROM dedupe(
  'documents', 'body', distance_threshold => 0.2
);

REFRESH MATERIALIZED VIEW documents_clean;

Both relations bind one immutable source snapshot, scan live rows across all fragments, generate LSH collisions and apply the estimated distance threshold. They require a native table without WAL and a threshold from zero through one. The top-level input column can contain strings or FixedSizeList<UInt16> MinHash signatures with valid configuration metadata. No MinHash or vector index is required. The source remains intact. These names preserve the vector meaning of the existing duplicate_pairs and dedupe relations. The optional num_hashes, shingle_size and num_bands arguments have the same limits as the scalar functions. Text-column queries use the scalar defaults; saved-signature queries infer omitted num_hashes and shingle_size from column metadata. All arguments must be literals. dataset_version => 7 pins an explicit version; without it, each query or refresh binds the latest snapshot again. Keep that snapshot’s files available throughout execution. Pair row IDs belong to the reported snapshot version; physical row IDs can change across compaction, while stable row IDs remain stable when that table feature is enabled. duplicate_pairs emits each unordered candidate pair at most once, with row_id_a < row_id_b. It does not silently limit a bucket or apply a per-row neighbor cap. Completeness means all collisions in the declared LSH configuration, not all pairs that meet an exact or estimated distance threshold. dedupe returns retained original rows and columns. The default resolution => 'connected_components' keeps the smallest snapshot row ID in each transitive component. A chain A–B–C can remove C even when A and C do not match directly. resolution => 'direct_representative' visits canonical edges in ascending row-ID order and removes only rows that directly match a retained representative; the same chain retains A and C. Neither policy recovers LSH misses. Rows with NULL or no-token text are retained as isolated rows. For connected components, equal signatures form a clique with identical neighbours. Execution connects each such group with a star and compares one representative per signature group. These edges preserve the same connected components and smallest original row IDs. The pair relation and direct-representative resolution enumerate all matching pairs. A refresh recomputes the complete result because an appended row can connect previously separate components. It does not append new representatives to an old result. Functions SQL jobs and distributed SQL use the ordinary result publication path; incomplete worker output is not a successful materialization.

Resource limits

The initial batch implementation assigns the complete candidate stage to one plan executor. A global stage preserves matches across source fragments. It uses the ordinary streaming transport with backpressure, not one worker per bucket. The executor fleet must advertise streaming_datafusion and the capability for the input type: minhash_pairs_v1 for text-column queries, or minhash_signature_pairs_v1 for saved-signature-column queries. Signatures and band membership spill to a temporary redb database with a 64 MiB cache. The stage reserves 192 MiB from the query memory pool for that cache and working buffers. Input batches target at most 128 rows and 8 MiB, with 16 MiB of storage I/O buffering and one batch/fragment read ahead. These are processing budgets, not a process RSS limit; storage readers, shared caches and decoding a single large value also use memory. A document larger than 4 MiB fails the operation explicitly. It is never shortened silently. Candidate output has batches of at most 1,024 pairs and a two-batch queue. The final retained-row scan also uses an 8 MiB byte target, 16 MiB of I/O buffering, and one batch/fragment read ahead. Temporary disk use is charged to the query’s disk manager and exceeding its budget fails the operation. Representative selection can also spill; it reserves additional memory for the final row masks based on source cardinality, plus a 256 MiB sorting allowance for direct-representative resolution. An insufficient memory pool fails before representative processing. Cancellation closes the output queue and releases temporary state. Dense groups of different signatures can still require quadratic comparisons. Complete pair output is also quadratic for a duplicate clique. The operator reports input rows, signed rows, stored signatures, collapsed signature rows, band entries, band comparisons, output rows and peak staging file bytes. In connected-components mode, candidate_signature_pairs counts comparisons between signature groups; full-pair mode reports unique_candidates between source rows. Stage, scan-wait, signature and enumeration timers help locate costs; enumeration time includes output backpressure. Evaluate candidate recall and these costs on representative data before increasing workload size or band count.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

MinHash SQL functions

Create a signature

Store signatures in a new table

Add a signature column with the Python SDK

Estimate Jaccard distance

One entry point for text and vectors

Generate candidate keys

Run complete batch text deduplication

Resource limits

​Create a signature

​Store signatures in a new table

​Add a signature column with the Python SDK

​Estimate Jaccard distance

​One entry point for text and vectors

​Generate candidate keys

​Run complete batch text deduplication

​Resource limits

Create a signature

Store signatures in a new table

Add a signature column with the Python SDK

Estimate Jaccard distance

One entry point for text and vectors

Generate candidate keys

Run complete batch text deduplication

Resource limits