Skip to main content

Primary-Key Indexes

Primary-key tables can maintain Vector, Full Text, FM, BTree, Bitmap, and Multivalue indexes together with compact data files. These indexes are bucket-local and source-backed: every index group records its source data files and maps matches back to physical row positions. Deletion vectors are applied when indexed rows are read, so updates and deletes remain exact.

Primary-key indexes differ from indexes created with create_global_index. A regular Global Index addresses table-wide row IDs and is built independently for a Data Evolution table. A primary-key index follows the write and compaction lifecycle of a primary-key table and addresses rows inside its compact data files.

For a first setup, choose an index family below, check Requirements, and follow Create a Table. Before using search results in production, review Maintenance and Coverage: the default Vector and Full Text search modes omit files without active index coverage.

Choose an Index​

Index familyUse forQuery path
BTreeSelective scalar equality, IN, ranges, and null checksOrdinary batch predicates
BitmapIndexed values such as enum-like dimensionsOrdinary batch predicates
MultivalueElement membership in ARRAY columnsSupported array predicates
FMExact substring matches on character columnsA predicate converted to Paimon's CONTAINS
VectorApproximate nearest neighbors in VECTOR<FLOAT> columnsVector Search
Full TextRanked text search on character columnsFull-Text Search

Scalar and array indexes are used after the connector converts the query expression to a supported Paimon predicate. BTree and Bitmap support equality, comparisons, null checks, complement predicates, and supported string predicates. Multivalue supports ARRAY_CONTAINS, ARRAYS_OVERLAP, and ARRAY_CONTAINS_ALL; null arrays and empty arrays produce no element postings, and array null checks use the ordinary scan path.

FM performs exact substring matching without a configured gram size and accepts needles of any byte length. Vector and Full Text expose dedicated ranked-search APIs in Spark, Flink, and Java. Full Text uses the fixed full-text implementation; Vector uses the configured ANN implementation.

Different columns in one table can use different index families. One column can occur in at most one of pk-vector.index.columns, pk-full-text.index.columns, pk-btree.index.columns, pk-bitmap.index.columns, pk-multivalue.index.columns, and pk-fm.index.columns.

Requirements​

All primary-key indexes require:

  • A primary-key table in fixed-bucket mode (bucket > 0) or postpone-bucket mode (bucket = -2).
  • pk-clustering-override = false.
  • An indexed column supported by the selected index implementation.

Dynamic-bucket tables, append-only tables, compound indexes, and custom index families are not supported by this configuration.

BTree, Bitmap, Multivalue, and FM additionally require:

  • deletion-vectors.enabled = true.
  • deletion-vectors.merge-on-read = false.
  • A supported scalar column type for BTree and Bitmap, or an ARRAY of supported scalar elements for Multivalue. FM requires a CHAR, VARCHAR, or STRING column.

Each entry creates an independent single-column index. Multiple columns are supported as long as a column is not listed by another primary-key index family.

Create a Table​

Start with One Index​

This example uses a BTree index to accelerate selective filters on amount.

CREATE TABLE indexed_orders (
    id BIGINT,
    amount DECIMAL(12, 2),
    PRIMARY KEY (id) NOT ENFORCED
) WITH (
    'bucket' = '8',
    'deletion-vectors.enabled' = 'true',
    'pk-btree.index.columns' = 'amount'
);

Query it with an ordinary predicate:

SELECT id, amount FROM indexed_orders WHERE amount BETWEEN 100.00 AND 200.00;

Indexes are built from eligible compacted data. Uncovered files selected by this batch scan follow the ordinary data path; see Maintenance and Coverage.

Combine Index Families​

Example: configure all six index families on different columns

The following table uses all six families on different columns: Vector for embedding, Full Text for content, FM for raw_text, BTree for amount, Bitmap for status, and Multivalue for tags.

CREATE TABLE items (
    id BIGINT,
    status STRING,
    amount DECIMAL(12, 2),
    tags ARRAY<STRING>,
    content STRING,
    raw_text STRING,
    embedding ARRAY<FLOAT> COMMENT '__VECTOR_FIELD;3',
    PRIMARY KEY (id) NOT ENFORCED
) WITH (
    'bucket' = '8',
    'deletion-vectors.enabled' = 'true',
    'pk-vector.index.columns' = 'embedding',
    'fields.embedding.pk-vector.index.type' = 'ivf-flat',
    'fields.embedding.pk-vector.distance.metric' = 'cosine',
    'fields.embedding.pk-vector.index.options' = '{"nlist":"256"}',
    'pk-full-text.index.columns' = 'content',
    'fields.content.pk-full-text.index.options' = '{"tokenizer":"jieba"}',
    'pk-fm.index.columns' = 'raw_text',
    'fields.raw_text.pk-fm.index.options' = '{"partition-row-count":"100000"}',
    'pk-btree.index.columns' = 'amount',
    'fields.amount.pk-btree.index.options' = '{"block-size":"64 kb"}',
    'pk-bitmap.index.columns' = 'status',
    'fields.status.pk-bitmap.index.options' = '{"dictionary-block-size":"16 kb"}',
    'pk-multivalue.index.columns' = 'tags',
    'fields.tags.pk-multivalue.index.options' = '{"dictionary-block-size":"16 kb"}'
);

The vector comment directive converts the SQL ARRAY<FLOAT> column to Paimon's fixed-length VECTOR<FLOAT> type. Java API users can define it directly with DataTypes.VECTOR(3, DataTypes.FLOAT()).

Duplicate, empty, unknown, unsupported, or cross-family duplicate columns are rejected during schema validation.

Options​

OptionDefaultDescription
pk-vector.index.columnsNot setVector column to index. Exactly one vector column is currently supported.
fields.<column>.pk-vector.index.typeRequiredANN implementation, such as ivf-flat, ivf-pq, ivf-sq, ivf-rq, or diskann.
fields.<column>.pk-vector.distance.metricinner_productDistance metric: l2, cosine, or inner_product.
fields.<column>.pk-vector.index.optionsNot setJSON object containing build options for the selected ANN implementation.
pk-full-text.index.columnsNot setCharacter column to index. Exactly one full-text column is currently supported.
fields.<column>.pk-full-text.index.optionsNot setJSON object containing native analyzer options. Unqualified keys are scoped to full-text.
pk-fm.index.columnsNot setComma-separated character columns which own independent exact FM indexes.
fields.<column>.pk-fm.index.optionsNot setJSON object containing FM build and read options. Unqualified keys are scoped to fm-index.
pk-btree.index.columnsNot setComma-separated columns which own independent BTree indexes.
fields.<column>.pk-btree.index.optionsNot setJSON object containing BTree build options. Unqualified keys are scoped to btree-index.
pk-bitmap.index.columnsNot setComma-separated columns which own independent Bitmap indexes.
fields.<column>.pk-bitmap.index.optionsNot setJSON object containing Bitmap build options. Unqualified keys are scoped to bitmap-index.
pk-multivalue.index.columnsNot setComma-separated ARRAY columns which own independent Multivalue indexes.
fields.<column>.pk-multivalue.index.optionsNot setJSON object containing Multivalue build options. Unqualified keys are scoped to multivalue-index.
multivalue-index.dictionary-block-size16 kbTarget dictionary block size.
multivalue-index.compressionnoneDictionary-block compression algorithm.
multivalue-index.compression-level1Dictionary-block compression level.
global-index.search-modeNot setLegacy fallback search mode. Family-specific options take precedence.
vector-index.search-modefastSearch mode for primary-key Vector queries. full and detail search uncovered files exactly.
full-text-index.search-modefastSearch mode for primary-key Full Text queries. Only fast is currently supported.

For algorithm-specific options, see the corresponding BTree, Bitmap, Multivalue, FM, or Vector index page. Full-text analyzer options are listed in the paimon-full-text README.

Maintenance and Coverage​

Paimon builds primary-key indexes from complete COMPACT data files above Level 0. An index group stores the source file names and row counts required to map index results back to physical rows. Data-file and index-file changes are committed in the same snapshot.

New Level-0 APPEND files are not index sources. They remain uncovered until physical data compaction produces an eligible output. A metadata-only level upgrade keeps its APPEND source and therefore remains uncovered as well.

For a postpone-bucket table, foreground writes can land in bucket -2 or in pending Level-0 files inside real buckets. These rows become visible after batch compaction publishes them to real buckets. Indexes are created when that process physically rewrites the rows into eligible compact output; simply assigning or upgrading a pending file does not make it an index source.

Data-Level Maintenance​

Each indexed column maintains one immutable index group for the complete eligible source-file set in every non-zero data level. Each group writes one payload file; an FM payload can contain several independently readable internal partitions. When data compaction changes a level, Paimon rebuilds that whole level group, including files in the target level which were not direct compaction inputs. A level group is used only when its ordered source names and row counts exactly match the current data level; stale payloads and cross-level payloads are rejected.

A rebuild atomically replaces the old group after the complete new group is ready. Unrelated data levels retain their existing payloads.

Index construction can execute asynchronously inside the writer. A writer which waits for compaction also waits for active index maintenance; a non-blocking writer can complete maintenance in a later commit. Coverage can therefore be temporarily partial.

Query Behavior with Partial Coverage​

Coverage is separate from data visibility: first determine which data files the selected batch scan can read, then check which of those files have an active index group.

QueryIndexed filesFiles without an active index group
BTree, Bitmap, Multivalue, or FM predicatePrune using the index; apply predicates and deletion vectorsRead through the ordinary data path
Vector, fast (default)Search the ANN indexOmitted
Vector, full or detailSearch the ANN indexEvaluate vectors exactly and merge the candidates
Full Text, fast (default and only supported mode)Search the persistent text indexOmitted

For files visible to a batch scan, scalar indexes fall back to scanning uncovered files, Vector fast and Full Text omit them, and Vector full or detail evaluates them exactly.

For ordinary scalar and array predicates, partial coverage affects performance rather than result completeness. The original predicate and deletion vectors still apply.

For Vector and Full Text, fast can omit matching rows when coverage is incomplete. Vector's full and detail modes cover unindexed files with exact evaluation, but indexed files still use ANN search; these modes do not promise an exact global Top-K.

Full Text rejects full and detail because a merge-aware logical-row fallback is not implemented. New Level-0 rows become full-text searchable only after compaction publishes eligible data and its persistent text index. A metadata-only level upgrade is not enough.

BTree, Bitmap, Multivalue, and FM Queries​

BTree, Bitmap, Multivalue, and FM indexes are applied automatically to snapshot-scoped Core batch scans after a supported predicate reaches Paimon. BTree and Bitmap can accelerate equality, IN, null checks, comparisons and ranges, complement predicates, and supported string predicates. Multivalue accelerates ARRAY_CONTAINS, ARRAYS_OVERLAP, and ARRAY_CONTAINS_ALL predicates. FM accelerates exact CONTAINS predicates. For example, Spark converts the following literal substring filter to Paimon's CONTAINS predicate:

SELECT id, raw_text
FROM items
WHERE raw_text LIKE '%needle%';

The FM result is exact for indexed files. Paimon reads uncovered files normally and reapplies the original predicate and deletion vectors, so partial index coverage affects performance rather than result completeness.

The Java/Core predicate can be constructed directly:

Predicate predicate = new PredicateBuilder(rowType)
        .arraysOverlap(
                tagsFieldIndex,
                Arrays.asList(
                        BinaryString.fromString("blue"),
                        BinaryString.fromString("green")));
ReadBuilder readBuilder = table.newReadBuilder().withFilter(predicate);
TableScan.Plan plan = readBuilder.newScan().plan();

SQL engines need a connector-specific expression conversion before their array-membership function reaches the corresponding Core predicate.

Paimon combines indexed predicate results as follows:

  • For AND, any usable indexed child can narrow a source file; unindexed children remain residual filters.
  • For OR, index positions are used only when every branch can be evaluated safely. Otherwise, the source file is scanned normally.

Planning uses the data and index manifests from the selected snapshot. If an index group is missing, incomplete, unreadable, corrupt, or returns an invalid physical position, the affected source file falls back to the ordinary data path.

A vector search captures one table snapshot, searches active ANN groups in the selected buckets, and merges their candidates into a global Top-K. Candidates are materialized from source data files by physical row position. Deletion vectors remove stale versions and deleted rows.

Use the vector_search table-valued function. Spark exposes the ANN score through the __paimon_search_score metadata column.

SELECT id, status, __paimon_search_score
FROM vector_search(
    'items',
    'embedding',
    array(0.1f, 0.2f, 0.3f),
    10,
    map('ivf.nprobe', '32')
);

The query dimension must match the indexed vector dimension. Partition predicates are applied before ANN search. When spark.paimon.vector-search.distribute.enabled is true, Spark can distribute sufficiently large groups of bucket-local searches and merge task-local Top-K results on the driver.

Exact Rerank​

Primary-key vector search can retrieve additional ANN candidates and rerank them with the original vectors. For example, this table option retrieves up to four times the requested Top-K before computing exact distances:

'fields.embedding.ivf-flat.refine_factor' = '4'

refine_factor, refine-factor, rerank_factor, and rerank-factor are accepted. Query options override table options, and field/index prefixes take precedence over less-specific prefixes. The factor must be a positive integer. A factor of 1 performs exact reranking without retrieving additional candidates.

Only ANN candidates can win the rerank, so a larger factor can improve recall but does not guarantee the exact global Top-K. It also increases ANN work and data-file I/O. In full or detail mode, uncovered files are searched exactly and merged separately with the ANN candidates; the default fast mode does not search them.

Full-Text Search​

A primary-key full-text search captures one table snapshot, searches active archives in the selected buckets, applies deletion vectors, and merges native relevance scores into a global Top-K. The score is preserved as __paimon_search_score; it is not rewritten by RRF unless the full-text route is later combined by Hybrid search.

SELECT id, content, __paimon_search_score
FROM full_text_search(
    'items',
    'content',
    '{"match":{"query":"paimon lake"}}',
    10
)
ORDER BY __paimon_search_score DESC;

Partition predicates can prune buckets before ranking. Arbitrary row predicates cannot be pushed into a full-text Top-K route.

Spark hybrid_search can fuse primary-key Vector and Full Text routes by physical data-file position. Hybrid captures one snapshot for all physical routes, rejects a mix of physical and global row-ID routes, and deduplicates their source files before reading rows. Supported rankers are rrf, weighted_score, and mrr; route weights must be finite and positive.

SELECT id, __paimon_search_score
FROM hybrid_search(
    'items',
    array(named_struct(
        'field', 'embedding',
        'query_vector', array(0.1f, 0.2f, 0.3f),
        'limit', 20,
        'weight', 1.0f,
        'options', map())),
    array(named_struct(
        'column', 'content',
        'query', '{"match":{"query":"paimon lake"}}',
        'limit', 20,
        'weight', 1.0f,
        'options', map())),
    10,
    'rrf'
)
ORDER BY __paimon_search_score DESC;

Merge-Engine Behavior​

Vector and Full Text maintenance follow the table's merge engine:

  • deduplicate: an update indexes the latest row and the deletion vector hides the replaced physical row. A delete removes the old row from both search indexes through the deletion vector.
  • partial-update: the vector index is built from the lookup-completed compact-output row.
  • aggregation: the vector index is built from the aggregated compact-output row.
  • first-row: the retained first row is indexed. Deletion vectors must be disabled because later rows with the same primary key are ignored rather than deleting the retained row.

Full Text indexes the lookup-completed, aggregated, or retained compact-output text using the same rules. Null text consumes a physical row ordinal but is not added to the native term index.

Schema Evolution​

An indexed column cannot be renamed, dropped, or have its type changed while its definition is present. Treat index column lists, field-scoped implementation settings, and build options as part of the index definition. To change the family or incompatible build options of an indexed column, create a table with the desired definition and migrate the data.

Limitations​

  • BTree, Bitmap, Multivalue, and FM definitions are single-column indexes.
  • Multivalue supports arrays of scalar element types accepted by the global-index key serializer. Null elements are not indexed. Higher array and element cardinality increases the expanded-sort I/O and index-file size.
  • Exactly one vector index column is currently supported per table.
  • Exactly one full-text index column is currently supported per table.
  • Only FLOAT vectors are supported.
  • Indexes are built from eligible compact output, not directly from Level-0 appends.
  • Index acceleration and vector search are snapshot-scoped batch operations; continuous streaming and lateral Vector or Full Text search are not supported.
  • Flink vector search returns rows but does not expose the ANN score as a separate column.
  • FAST is the default search mode and excludes uncovered files from primary-key Vector and Full Text results. Vector supports exact fallback for uncovered files in FULL and DETAIL modes; primary-key Full Text supports only FAST until compaction creates persistent archives.
  • Primary-key Full Text routes support partition pruning but not arbitrary row predicates before Top-K. Row filters on full-text search are supported for Data Evolution tables only; see Row Filters.
  • Hybrid search cannot mix source-backed physical routes with global row-ID routes.
  • Online replacement between two definitions on the same column is not supported.