---
metadata:
- name: generator
content: Diplodoc Platform v5.50.6
alternate:
- https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main
- https://ydb.tech/docs/ru/concepts/datamodel/table.md?version=main
sourcePath: en/core/concepts/datamodel/table.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt
# Table
A table is a relational [table](https://en.wikipedia.org/wiki/Table_(database)) containing a set of related data, composed of rows and columns. Tables represent entities. For instance, a blog article can be represented by a table named `article` with columns: `id`, `date_create`, `title`, `author`, and so on. Rows contain data, and columns define data types. For example, the `id` column cannot be empty (`NOT NULL`) and should contain only unique integer values. A record in YQL might look like this:
```yql
CREATE TABLE article (
id Int64 NOT NULL,
date_create Date,
author String,
title String,
PRIMARY KEY (id)
)
```
YDB supports creating row-based and column-based tables. Their main difference lies in the use case and the data storage format on the hard disk. For row-based tables, data is stored sequentially as rows, while for column-based tables, data is stored as columns. Each table type has its own purpose.
# Column Naming Rules {#column-naming-rules}
Column names in YDB must meet the following requirements:
- A column name can consist of the following characters:
- Uppercase Latin letters
- Lowercase Latin letters
- Digits
- Special characters: `-` and `_`.
- The length of a column name must not exceed 255 characters.
- Column names must not start with the system prefix `__ydb_`.
## Row-Oriented Tables {#row-oriented-tables}
Row-based tables are well suited for transactional queries generated by Online Transaction Processing (OLTP) systems, such as backends for weather services or online stores. Row-based tables provide efficient access to a large number of columns at once. Search in row-based tables is very fast due to the use of indexes.
[An index](https://en.wikipedia.org/wiki/Database_index) is a data structure that increases the speed of data retrieval operations based on one or more columns. It is analogous to an index in a book: instead of scanning every page to find the needed section, you can refer to the index at the end of the book and quickly jump to the relevant page.
When a query is executed based on a column (or columns) for which an index has been created, the DBMS can use that index to quickly find the matching rows, avoiding a full scan of all data. For example, if you have an index on the "author" column and you search for all articles written by the author "Gray", the DBMS uses the index to quickly find all rows with that surname.
You can create a row-based table via the YDB web interface, using the CLI or SDK. Regardless of how you interact with YDB, remember the general rule for creating a row-based table: the table must have at least one key column, and creating a table consisting only of key columns is allowed.
By default, when creating a row-oriented table, all columns are optional and can have `NULL` values. This behavior can be modified by setting the `NOT NULL` conditions for key columns that are part of the primary key. Primary keys are unique, and row-oriented tables are always sorted by this key. This means that point reads by the key, as well as range queries by key or key prefix, are efficiently executed (essentially using an index). It's permissible to create a table consisting solely of key columns. When choosing a key, it's crucial to be careful, so we recommend reviewing the article: ["Choosing a Primary Key for Maximum Performance"](https://ydb.tech/docs/en/dev/primary-key/row-oriented.md?version=main).
### Partitioning Row-Oriented Tables {#partitioning_row_table}
A row-based table in the database can be partitioned by ranges of primary key values. Each partition of the table is responsible for its own range of primary keys. The key ranges served by different partitions do not overlap. Different partitions of the table can be served by different servers of the distributed database (including those located in different locations), and can also move independently between servers for rebalancing or to maintain partition health in case of server or network equipment failures.
With a small amount of data or low load, a table can consist of a single partition. As the data volume of a partition or the load on a partition grows, YDB automatically splits it into two:
- If the data volume exceeds the [partition size threshold](#auto_partitioning_by_size), splitting occurs by the median value of the primary key.
- If [load increases](#auto_partitioning_by_load), the partition first collects a sample of requested keys (read, written, and deleted) and, based on this sample, selects a key for splitting so that the load is distributed evenly between the new partitions. Thus, in the case of load-based splitting, the new partitions may have substantially different sizes.
The partition size threshold for splitting and enabling or disabling automatic splitting can be configured individually for each database table.
Regardless of the [AUTO_PARTITIONING_PARTITION_SIZE_MB](#auto_partitioning_by_size) parameter value, YDB performs partition splitting and merging based on a size of 2000 MB. At the same time, YDB does not restrict user settings and works in parallel with them. For example, if you set the [partition size](#auto_partitioning_by_size) to 100 MB and the [limit](#auto_partitioning_max_partitions_count) to 10 partitions, the table stops splitting when it reaches 10 partitions of 100 MB each, and partition sizes begin to grow. When any partition exceeds 2000 MB, it splits, and the table has 11 partitions. If the size of two adjacent partitions drops to 1000 MB, they merge, and the table has 10 partitions again.
In addition to automatic splitting, you can create an empty table with a predefined number of partitions. You can manually set exact key split boundaries for partitions or specify uniform distribution across a predefined number of partitions. In the latter case, boundaries are created based on the first component of the primary key. Uniform distribution can be specified for tables whose first primary key component is an integer with data type `Uint64` or `Uint32`.
Partitioning parameters apply only to the table itself, not to secondary indexes built on its data. Each index is served by its own set of partitions, and decisions to split or merge its partitions are made independently based on default settings. In the future, these settings may become available to users, similar to the settings of the main table.
The typical duration of a split or merge operation is about 500 ms. During this time, the data involved in the operation becomes briefly unavailable for reads and writes. Specialized wrapper methods in the YDB SDK automatically retry when they detect that a partition is in the process of splitting or merging, without surfacing this information to the application level. It is important to note that if the system is overloaded for any reason (for example, due to insufficient CPU or bandwidth of the allocated disk resources), split and merge operations may take longer.
The data schema defines the following table partitioning parameters:
#### AUTO_PARTITIONING_BY_SIZE
* Type: `Enum` (`ENABLED`, `DISABLED`).
* Default value: `ENABLED`.
Automatic partitioning by partition size. If a partition size exceeds the value specified by the [AUTO_PARTITIONING_PARTITION_SIZE_MB](#auto_partitioning_partition_size_mb) parameter, it is enqueued for splitting. If the total size of two or more adjacent partitions is less than 50% of the [AUTO_PARTITIONING_PARTITION_SIZE_MB](#auto_partitioning_partition_size_mb) value, they are enqueued for merging.
#### AUTO_PARTITIONING_BY_LOAD
* Type: `Enum` (`ENABLED`, `DISABLED`).
* Default value: `DISABLED`.
Load-based automatic partitioning mode. If a partition consumes more than 50% CPU for several tens of seconds, it is queued for splitting (split). If the total load on two or more adjacent partitions is less than 35% of one CPU core for an hour, they are queued for merging (merge).
When deciding to split a partition by load or merge multiple partitions by load, YDB considers the CPU load on both the leader of the partition and all its replicas.
Performing split or merge operations uses the CPU and takes time. Therefore, when dealing with a variable load, we recommend both enabling this mode and setting [AUTO_PARTITIONING_MIN_PARTITIONS_COUNT](#auto_partitioning_min_partitions_count) to a value other than 1. This ensures that a decreased load does not cause the number of partitions to drop below the required value, resulting in a need to split them again when the load increases.
When choosing the minimum number of partitions, it makes sense to consider that a single table partition can reside on only one server and use no more than 1 CPU core for data modification operations. Based on this, for a table that may experience high load, you can set the minimum number of partitions to at least the number of nodes (servers), or better, on the order of the number of CPU cores allocated to the database.
#### AUTO_PARTITIONING_PARTITION_SIZE_MB
* Type: `Uint64`.
* Default value: `2000 MB` (`2 GB`).
The desired partition size threshold in megabytes. Recommended values range from `10 MB` to `2000 MB`. If this threshold is exceeded, a shard may split.
The specified value serves only as a recommendation for splitting. Splitting may not occur even if the configured size is exceeded.
This setting applies when the [`AUTO_PARTITIONING_BY_SIZE`](#auto_partitioning_by_size) mode is enabled.
#### AUTO_PARTITIONING_MIN_PARTITIONS_COUNT
* Type: `Uint64`.
* Default value: `1`.
Partition merging (merge) is performed only if the actual number of partitions exceeds the value set by this parameter. When using load-based automatic partitioning, it is recommended to set this parameter to a value other than 1 so that periodic load drops do not reduce the number of partitions below the required level.
#### AUTO_PARTITIONING_MAX_PARTITIONS_COUNT
* Type: `Uint64`.
* Default value: `50`.
Partition splitting (split) is performed only if the number of partitions does not exceed the value set by this parameter. With any enabled automatic partitioning modes, it is recommended to set a meaningful value for this parameter and monitor the actual number of partitions approaching it; otherwise, partitions will eventually stop splitting as data or load grows, leading to a failure.
#### UNIFORM_PARTITIONS
* Type: `Uint64`.
* Default value: not applicable.
The number of partitions for uniform initial table partitioning. The primary key's first column must have type `Uint64` or `Uint32`. A created table is immediately divided into the specified number of partitions.
When automatic partitioning is enabled, make sure to set the correct value for [AUTO_PARTITIONING_MIN_PARTITIONS_COUNT](#auto_partitioning_min_partitions_count) to avoid merging all partitions into one immediately after creating the table.
#### PARTITION_AT_KEYS
* Type: `Expression`.
* Default value: not applicable.
Boundary values of keys for initial table partitioning. It's a list of boundary values separated by commas and surrounded with brackets. Each boundary value can be either a set of values of key columns (also separated by commas and surrounded with brackets) or a single value if only the values of the first key column are specified. Examples: `(100, 1000)`, `((100, "abc"), (1000, "cde"))`.
When automatic partitioning is enabled, make sure to set the correct value for [AUTO_PARTITIONING_MIN_PARTITIONS_COUNT](#auto_partitioning_min_partitions_count) to avoid merging all partitions into one immediately after creating the table.
### Reading Data from Replicas {#read_only_replicas}
When executing queries in YDB, the actual execution of a query to each partition is performed at a single point that serves the distributed transaction protocol. However, thanks to storing data on shared storage, it is possible to launch one or more replicas of a partition without allocating additional storage space — the data is already stored replicated, and more than one reader can be served (but the writer is still strictly one at any given moment).
Using read replicas provides the following capabilities:
* Serve clients that are critical to minimal latencies that cannot be achieved otherwise in a multi-DC cluster. This is achieved by bringing the query execution point closer to the query submission point, which eliminates the inter-DC transfer latency. As a result, while preserving all the multi-DC cluster's storage reliability guarantees, point read queries can be answered in milliseconds.
* Serve read requests from replicas without affecting modifying requests running on the partition. This can be useful both for isolating different scenarios and for increasing the partition's throughput.
* Continue serving during partition leader moves (both routine during balancing and during failures). This allows surviving cluster processes without affecting read clients.
* Overall, increase the read performance limit of partitions if many read requests hit the same keys.
In the table data schema, you can specify the need to launch read replicas for each table partition. Read replica accesses typically occur without leaving the data center network, which ensures response times in milliseconds:
| Parameter name | Description | Type | Allowed values | Can be
changed | Can be
reset |
| --- | --- | --- | --- | --- | --- |
| `READ_REPLICAS_SETTINGS` | `PER_AZ` means using the specified number of replicas in each AZ and `ANY_AZ` in all AZs in total. | String | `"PER_AZ:"`, `"ANY_AZ:"`, where `` is the number of replicas. To remove replicas, set the value to 0. For example: "PER_AZ:0". | Yes | No |
The internal state of each replica is restored exactly from the leader's state and is fully consistent.
Besides the data state in storage, followers also receive a stream of updates from the leader. Updates are sent in real time, immediately after the commit to the log. However, they are sent asynchronously, resulting in some delay (usually no more than dozens of milliseconds, but sometimes longer in the event of cluster connectivity issues) in applying updates to followers relative to their commit on the leader. Therefore, reading data from followers is only supported in the [transaction mode](https://ydb.tech/docs/en/concepts/transactions.md?version=main#modes) `StaleReadOnly()`.
If there are multiple replicas, their lag behind the leader may differ, i.e., although each replica of each partition maintains internal consistency, artifacts may be observed between different partitions. Application code must be prepared for this. For the same reason, it is currently impossible to execute cross-shard transactions from replicas.
### Deleting Expired Data (TTL) {#ttl}
YDB supports automatic background deletion of expired data. A table data schema may define a column of a [suitable type](https://ydb.tech/docs/en/concepts/ttl.md?version=main#restrictions); the value in this column is compared with the current time for all rows in the background. Rows for which the current time becomes greater than the column value plus the specified delay are deleted.
| Parameter name | Type | Allowed values | Can be
changed | Can be
reset |
| --- | --- | --- | --- | --- |
| `TTL` | Expression | `Interval("") ON [AS ]` or `Interval("literal1") action1, ..., Interval("literal1") action1 ON [AS ]` | Yes | Yes |
Syntax of TTL value is described in the article [Time to Live (TTL)](https://ydb.tech/docs/en/yql/reference/syntax/create_table/with.md?version=main#time-to-live). For more information about deleting expired data, see [Time to Live (TTL)](https://ydb.tech/docs/en/concepts/ttl.md?version=main).
### Renaming a Table {#rename}
YDB allows you to rename an existing table, move it to another directory in the same database, and replace one table with another, in which case the data of the replaced table is deleted. When performing operations, only the table metadata changes, such as its path and name. The table data is not moved or overwritten.
Operations are performed in isolation; the external process sees only two states of the table: before and after the operation. This is critical, for example, for table replacement: the data of the replaced table is deleted by the same transaction that renames the replacing table. During the replacement, there might be errors in queries to the replaced table that have [retryable statuses](https://ydb.tech/docs/en/reference/ydb-sdk/error_handling.md?version=main#termination-statuses).
The speed of the rename operation is determined by the type of data transactions currently running on the table and does not depend on the amount of data in the table.
* [Renaming a table in YQL](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/rename.md?version=main)
* [Renaming a table via the CLI](https://ydb.tech/docs/en/reference/ydb-cli/commands/tools/rename.md?version=main)
### Local Bloom skip indexes {#bloom-filter}
Using a [Bloom filter](https://en.wikipedia.org/wiki/Bloom_filter) allows more efficiently determining the absence of keys in a table during multiple point lookups by primary key, reducing the number of required disk I/O operations at the cost of increased memory consumption.
The recommended way to manage bloom filters on row-oriented tables is via [local Bloom skip indexes](https://ydb.tech/docs/en/concepts/glossary.md?version=main#local-bloom-skip-index) (`LOCAL USING bloom_filter`), created with [ALTER TABLE ... ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-bloom) and removed with [ALTER TABLE ... DROP INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#drop-index). For details, see [Bloom skip indexes](https://ydb.tech/docs/en/dev/bloom-skip-indexes.md?version=main#row-vs-column).
{% note warning %}
| Parameter name | Type | Allowed values | Can be
changed | Can be
reset |
| --- | --- | --- | --- | --- |
| `KEY_BLOOM_FILTER` | Enum | `ENABLED`, `DISABLED` | Yes | No |
The `KEY_BLOOM_FILTER` setting is deprecated. It enables a bloom filter over the full primary key and, when set to `DISABLED`, clears all bloom filters on the table.
{% endnote %}
### Column Groups {#column-groups}
YDB allows you to group columns in a table to optimize their storage and usage. The column group mechanism improves the performance of partial row read operations by splitting the storage of table columns into multiple groups. The most common scenario is storing rarely used attributes in a separate column group. This way, you can enable data compression and/or store them on slower storage devices.
Each column group has its own name, unique within the table. Column group composition is set during [table creation](https://ydb.tech/docs/en/yql/reference/syntax/create_table/family.md?version=main) and can be [modified](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/family.md?version=main) later. Removing column groups from an existing table is not supported.
A column group can contain any number of columns from its table. Each table column belongs to one and only one column group (column groups do not overlap).
Every table has a primary column group named `default` containing all columns not explicitly assigned to another group. Primary key columns always belong to the primary column group and cannot be moved to another group. Also, if there are no other column groups, changing attributes of the primary column group lets you set attributes for the entire table.
For a column group, attributes that affect data storage and caching are set:
* the type of storage devices used (SSD or HDD, with availability depending on the YDB cluster configuration)
* Data compression mode (no compression or [LZ4](https://en.wikipedia.org/wiki/LZ4_(compression_algorithm)) algorithm compression);
* caching mode.
Column group attributes are set during table creation and can be modified later. Storage attribute changes aren't immediately applied to existing data; instead, they take effect during subsequent background [LSM compaction](https://ydb.tech/docs/en/concepts/glossary.md?version=main#compaction). Caching attribute changes take effect immediately.
Access to data stored in the fields of the main column group is faster and requires fewer resources than access to data of the same table row stored in the fields of additionally created column groups. Primary key lookup is always performed in the main column group. When accessing fields in other column groups, in addition to the primary key lookup, additional lookup operations are required to determine the specific storage position of these fields. Changing the caching mode does not affect the need for additional operations for non-main columns, so with the same caching modes, access to the main column group will also be faster.
Thus, moving some table columns to a separate column group speeds up reading of the most important and frequently used columns (those in the main column group) at the cost of somewhat slower access to the remaining columns. In addition, column groups allow you to manage data storage parameters, such as the type of storage devices, compression mode, and caching parameters.
### Caching modes {#cache-modes}
You can set caching modes for column groups. Caching modes in YDB allow you to control the strategy for caching table data in RAM. This is especially important for optimizing response time when reading small, frequently used tables. Two caching modes are supported:
* `regular` (default);
* `in_memory`.
In the `regular` caching mode, column group data is loaded into the [shared cache](https://ydb.tech/docs/en/concepts/glossary.md?version=main#shared-cache) only on first access. If a column group uses the `in_memory` mode, its data is automatically preloaded into the cache when the system starts.
When the total amount of data in the cache exceeds the configured [limit](https://ydb.tech/docs/en/reference/configuration/memory_controller_config.md?version=main#cache-memory-limits), memory occupied by pages with the `regular` mode is freed first. If that is not enough, pages with the `in_memory` mode are evicted. Thus, the `in_memory` mode provides priority storage in RAM, but the cache size remains limited by the configuration — memory is not used without limit.
The `in_memory` caching mode can be useful in various scenarios. One common scenario is storing small lookup tables that fit entirely in RAM. In this case, using the `in_memory` mode minimizes access latency because the table always remains in memory, even if it is accessed irregularly.
However, this is not the only use case. For example, in some cases it may be advisable to store all data of a particular table in memory, regardless of its purpose, if the application business logic requires constant fast access to the full version of the table.
When using the `in_memory` mode, remember that memory allocated to such tables reduces the amount available for caching regular tables, which may negatively affect their performance (increase latency).
Note that the `in_memory` caching mode affects read operations only. Write operations are written to disk regardless of the mode, so data durability guarantees remain unchanged. This distinguishes YDB caching modes from some other in-memory DBMSs that may keep entire tables in memory at the cost of reduced data durability guarantees.
### Custom attributes {#users-attr}
User attributes allow you to add arbitrary information to table metadata. This information is not interpreted by the server, but can be interpreted by the database client (a person or, more often, a program).
Attributes are specified as a key-value pair. The key and value of an attribute can only be a string or a type that can be represented as a string (for example, using base64 encoding).
User attribute keys and values have the following restrictions:
* Key length: 1–100 bytes
* Value length: 1–4096 bytes
* Maximum total size of attributes (sum of lengths of all keys and values): 10240 bytes.
To add, change, retrieve current values, or delete attributes, see [Custom attributes in tables](https://ydb.tech/docs/en/dev/custom-attributes.md?version=main).
## Column-Oriented Tables {#column-oriented-tables}
Columnar tables YDB store data of each column separately (independently) from other columns. This data storage principle is optimized for Online Analytical Processing (OLAP) workloads, because when executing a query, only the columns that directly participate in the query are read. Another advantage of this approach is a high degree of data compression, since columns often store repeated or similar data. The downside is that performing operations on rows becomes more expensive.
At the moment, the main use case for YDB column-oriented tables is writing data with an increasing primary key (for example, event time), analyzing this data, and deleting outdated data based on TTL. The optimal way to add data to YDB column-oriented tables is [batch upload](https://ydb.tech/docs/en/dev/batch-upload.md?version=main), performed in MB-sized blocks. Data packet insertion is atomic: data will be written either to all partitions or none.
In most cases, working with columnar tables YDB is similar to working with row-based tables, but there are differences:
* Only `NOT NULL` columns can be used as the primary key.
* Data is partitioned not by the primary key, but by the hash of the partitioning columns.
* Columnar tables support a limited set of data types:
+ Available in both the primary key and other columns: `Date`, `Datetime`, `Timestamp`, `Int32`, `Int64`, `Uint8`, `Uint16`, `Uint32`, `Uint64`, `Utf8`, `String`;
+ Available only in columns not included in the primary key: `Decimal`, `Double`, `Float`, `Int8`, `Int16`, `JsonDocument`, `Json`, `Yson`.
* You can configure compression and encoding individually for each column. For details, see [CREATE TABLE request parameters](https://ydb.tech/docs/en/yql/reference/syntax/create_table/index.md?version=main#parametry-zaprosa).
Let's recreate the `article` table, this time in column-oriented format, using the following YQL command:
```yql
CREATE TABLE article_column_table (
id Int64 NOT NULL,
author String,
title String,
PRIMARY KEY (id)
)
WITH (STORE = COLUMN);
```
### Local Bloom skip indexes {#local-bloom-indexes}
In column-oriented and row-oriented tables you can define [local Bloom skip indexes](https://ydb.tech/docs/en/concepts/glossary.md?version=main#local-bloom-skip-index) on columns with `LOCAL USING bloom_filter` or `LOCAL USING bloom_ngram_filter`, either during [table creation](https://ydb.tech/docs/en/yql/reference/syntax/create_table/bloom_skip_index.md?version=main) or with [ALTER TABLE ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-bloom). See [local indexes](https://ydb.tech/docs/en/concepts/query_execution/local_indexes.md?version=main) and [Bloom skip indexes](https://ydb.tech/docs/en/dev/bloom-skip-indexes.md?version=main).
### Local min_max index {#local-min-max-index}
In columnar tables, you can define a [local min_max index](https://ydb.tech/docs/en/concepts/glossary.md?version=main#local-min-max-index) on columns using `LOCAL USING min_max`. The index is defined when [creating a table](https://ydb.tech/docs/en/yql/reference/syntax/create_table/min_max_index.md?version=main) or added via [ALTER TABLE ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-min-max). For more details: [local indexes](https://ydb.tech/docs/en/concepts/query_execution/local_indexes.md?version=main), [min_max index](https://ydb.tech/docs/en/dev/min_max-skip-index.md?version=main).
### Partitioning Column-Oriented Tables {#olap-tables-partitioning}
Unlike row-based tables YDB, columnar tables partition data not by primary keys, but by dedicated keys: partitioning keys. Partitioning keys are a subset of the table's primary keys.
Unlike data partitioning in row-based tables YDB, data partitioning for columnar tables is performed not by key values, but by hash values of the keys, which allows data to be evenly distributed across all existing partitions. Such partitioning helps avoid hotspots during insertion and speeds up analytical queries that process (read) large amounts of data.
The choice of partitioning keys substantially affects the performance of column-oriented tables. For more information, see ["Choosing a primary key for maximum performance of column-oriented tables"](https://ydb.tech/docs/en/dev/primary-key/column-oriented.md?version=main).
To manage data partitioning, an additional partitioning parameter is used: AUTO_PARTITIONING_MIN_PARTITIONS_COUNT. Other partitioning parameters are ignored for columnar tables.
AUTO_PARTITIONING_MIN_PARTITIONS_COUNT defines the minimum physical number of partitions for storing data.
Type: `Uint64`.
Default value: 64.
Given that other partitioning parameters are ignored, this same value also determines the maximum number of partitions.
### Column-oriented table limitations
Currently, not all columnar table functionality is implemented. The following are not supported:
* Reading from replicas.
* Global secondary indexes.
* Vector and full-text indexes.
* Bloom filter for the primary key (`KEY_BLOOM_FILTER`; see [Bloom filter](#bloom-filter) for row-oriented tables).
* Change Data Capture.
* User-defined table attributes.