> ## Documentation Index > Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt > Use this file to discover all available pages before exploring further. # MinHash SQL functions > Create lexical text signatures and probabilistic duplicate candidates. **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. > [!NOTE] > > 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 ```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}} 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` 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: ```json theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}} { "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: ```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}} 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: ```python theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}} 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 ```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}} 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` MinHash columns with the configuration metadata described above: ```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}} 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 ```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}} 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 ```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}} 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` 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. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.