System columns

_version

CrateDB uses an internal versioning for every row, the version number is increased on every write.

Note

Using the column for Optimistic Concurrency Control has been deprecated in favour of using the and _primary_term. See for usage details.

_seq_no

_primary_term

The sequence numbers give us an order of operations that happen at a primary shard, but they don’t help us distinguish between old and new primaries. For example, if a primary is isolated in a minority partition, a possible up to date replica shard on the majority partition will be promoted to be the new primary shard and continue to process write operations, subject to the write.wait_for_active_shards setting. When this partition heals we need a reliable way to know that the operations that come from the other shard are from an old primary and, equally, the operations that we send to the shard re-joining the cluster are from the newer primary. The cluster needs to have a consensus on which shards are the current serving primaries. In order to achieve this we use the primary terms which are generational counters that are incremented when a primary is promoted. Used in conjunction with we can obtain a total order of operations across shards and Optimistic Concurrency Control.

_score

This internal system column is available on all documents retrieved by a SELECT query. It is representing the scoring ratio of the document related to the used query filter and makes most sense on fulltext searches.

The scoring ratio is always related to the highest score determined by a search, thus scores are not directly comparable across searches.

_id

is an internal system column that is available on each indexed document and can be retrieved by a SELECT query from doc schema tables.

The value is a unique identifier for each row in a table and is a compound string representation of all primary key values of that row. If no primary keys are defined the id is randomly generated. If no dedicated is defined the value is used for distributing the records on the shards.

_docid

_docid exposes the internal id a document has within a Lucene segment. Although the id is unique within a segment, it is not unique across segments or shards and can change the value in case segments are merged.