System columns

CrateDB automatically provides several system columns for every user-defined table. These columns are reserved and cannot be used as user-defined column names.

Naming rules:

• Start with an underscore (_)

• Lowercase letters only, with optional underscores between words

---

Quick Reference

Column	Purpose	Typical Use Case
_version	Internal version number, incremented on every write	Previously used for optimistic concurrency control (deprecated — use _seq_no and _primary_term instead)
_seq_no	Incremental sequence number for each write on the primary shard	Used with _primary_term for optimistic concurrency control
_primary_term	Generational counter incremented on primary shard promotion	Ensures old primary operations are not applied after shard failover
_score	Document relevance score from a query (BM25 algorithm)	Ranking results in full-text search queries
_id	Unique row identifier (compound key or random ID)	Row identification, shard routing
_docid	Internal Lucene document ID within a segment	Low-level debugging, Lucene-level inspection

---

Column Details

_version

Tracks the internal version number for each row, incremented on every write (insert, update, delete).

Note: Using _version for Optimistic Concurrency Control (OCC) is deprecated. Use _seq_no and _primary_term instead. See Optimistic Concurrency Control for details.

---

_seq_no

A sequence number incremented by the primary shard whenever a row is inserted, updated, or deleted. Used with _primary_term for OCC to ensure write consistency.

---

_primary_term

A generational counter incremented when a primary shard is promoted. This prevents operations from old primaries from being applied after a failover.

When combined with _seq_no, it provides a total ordering of operations across shards.

---

_score

Represents the relevance score of a document in a SELECT query.

• Higher scores = closer match to the query

• Most useful for full-text search

Calculated using Lucene’s BM25Similarity (Okapi BM25), based on:

• Partition-level term statistics

• Term frequencies in the row

Notes:

• Scores from different queries are not directly comparable

• Identical rows in different partitions may have slightly different scores

• Without full-text search, the score is typically 1.0f

---

_id

A unique identifier for each row in a table:

• With primary keys: compound string of all PK values

• Without PKs: randomly generated

• Without a routing column: _id is used for shard distribution

Available for all indexed documents and queryable in the doc schema.

---

_docid

The internal Lucene document ID within a segment.

• Unique within a segment (not across shards)

• May change after segment merges