> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/server-usage/storage-engines/tidesql/tidesql-data-model.md).

# TideSQL Data Model

How TideSQL tables map onto TidesDB column families, how rows and keys are stored, how secondary indexes work, and how auto-increment is assigned.

## Tables and Column Families

Every TidesDB table corresponds to one main column family that holds the row data. Each secondary index gets its own separate column family. The naming is deterministic. A table `test.events` maps to the column family `test__events`, and a secondary index `idx_ts` on it maps to `test__events__idx_idx_ts`.

The separation is meaningful. Because each column family is its own LSM-tree, a secondary index has its own SSTables and its own compaction schedule. The memtable and WAL are database-level and shared by every column family, so a flush is not per index. When a table is renamed with `RENAME TABLE` or `ALTER TABLE ... RENAME`, the engine renames every associated column family, the main data CF and every secondary index CF together.

## Primary Keys and Row Storage

If you define a `PRIMARY KEY`, TidesDB uses it as the physical ordering key for the data column family. Key bytes are stored in a memcmp-comparable form. Integers are encoded big-endian with the sign bit flipped so negatives sort before positives, and strings use their collation's sort key. Physically adjacent keys in the LSM-tree are logically adjacent rows, so range scans on the primary key are efficient.

A table created without an explicit primary key gets a hidden 8-byte row id per row, encoded big-endian. These ids are monotonically increasing, assigned from an atomic counter that is recovered on restart by seeking to the last key in the column family.

Inside the column family every row key carries a single namespace byte, `0x01` for data rows and `0x00` for metadata. The value is a packed binary format with a 5-byte header, followed by the null bitmap and each non-null field serialized with MariaDB's `Field::pack()`. Fixed-size fields like `INT` store at their native pack length, `CHAR` strips trailing spaces, `VARCHAR` stores only its actual length, and `BLOB` and `TEXT` are inlined with a length prefix. This packed form is more compact than the raw record buffer, which reduces I/O and storage cost for variable-length columns.

Composite primary keys are fully supported. Each key part is encoded comparably and concatenated, so a key like `(dept_id, emp_id)` sorts by department first and by employee within a department. The optimizer can use a prefix lookup on the leading columns, for example `WHERE dept_id = 3` on `PRIMARY KEY (dept_id, emp_id)`, through an iterator-based prefix scan.

```sql
-- explicit PK, comparable-key ordering
CREATE TABLE users (id INT NOT NULL PRIMARY KEY, name VARCHAR(100)) ENGINE=TIDESDB;

-- composite PK, multi-column ordering
CREATE TABLE emp_projects (
  emp_id INT NOT NULL, proj_id INT NOT NULL, hours INT NOT NULL,
  PRIMARY KEY (emp_id, proj_id)
) ENGINE=TIDESDB;

-- no PK, hidden auto-generated row id
CREATE TABLE logs (ts DATETIME, message TEXT) ENGINE=TIDESDB;
```

## Secondary Indexes

Secondary indexes live in their own column families, and every regular index entry, unique or not, has the same layout. The key is the comparable index-column bytes followed by the comparable primary-key bytes, with a single zero byte for the value, so the engine recovers the primary key from the key's tail and performs a point lookup into the data CF for the full row.

Because of that primary-key suffix, two rows with the same value in a `UNIQUE` index write two different index keys, which the commit-time conflict check, working key by key, would never see collide. So every write of a value into a `UNIQUE` secondary index also writes a sentinel key naming just the table, the index, and the value into a reserved column family, `__tidesdb_uniq`, shared by every table. Two concurrent transactions that write the same unique value collide on that one sentinel key, and when the second to commit runs at `SNAPSHOT` or higher, the first-committer-wins check fails its `COMMIT` with `ER_ERROR_DURING_COMMIT` (ERROR 1180) wrapping handler error 149. A row whose indexed value is `NULL` in any part writes no sentinel, since SQL gives `NULL` no identity, and neither does a session that sets [`tidesdb_skip_unique_check`](/docs/server/server-usage/storage-engines/tidesql/tidesql-system-variables.md). The same comparable unique value is the key the cluster write-intent map uses to resolve a cross-node conflict.

On insert, update, or delete the engine maintains every secondary index inside the same transaction. For an update, it builds the old and new comparable index key for each index and compares them with `memcmp`. If the indexed columns and PK bytes are identical the index is skipped, unless the row carries a TTL expiry, in which case the entry is rewritten in place so it expires with the row. Either way this avoids a redundant delete-and-reinsert when an update touches only non-indexed columns.

Duplicate key violations on primary keys and unique indexes are detected. Inserting a duplicate primary key returns `ER_DUP_ENTRY`, and the same holds for unique secondary indexes. `REPLACE INTO` and `INSERT ... ON DUPLICATE KEY UPDATE` work correctly, because `write_row()` returns `HA_ERR_FOUND_DUPP_KEY` with the conflicting row's PK in `dup_ref` so the server can delete and reinsert or switch to `update_row()`, cleaning up old index entries in the process.

```sql
CREATE TABLE products (
  id INT NOT NULL PRIMARY KEY, category INT, name VARCHAR(100),
  KEY idx_category (category)
) ENGINE=TIDESDB;

SELECT * FROM products WHERE category = 10;   -- uses the secondary index
```

The optimizer is aware of these indexes. The engine reports cost from the LSM-tree's read amplification, taken from the library's statistics. A secondary-index point lookup costs about one seek into the index CF plus one point-get into the data CF.

That data-CF point-get is skipped when the read is covering. When every column a query needs is carried by the index key, the indexed columns plus the appended primary-key columns, and each is of a type the engine can rebuild from its comparable bytes (integers, `YEAR`, `DATE`, `DATETIME`/`TIMESTAMP` without fractional seconds, and fixed `CHAR`/`BINARY` in a binary or latin1 charset, but not `TIME`), the row is materialized straight from the index bytes with no data-CF fetch. The capability is advertised to the optimizer per key part, so an index that also carries a non-invertible column such as `VARCHAR`, `DECIMAL`, or a float still gets an index-only plan for the queries that read only its reconstructable columns.

### Index Condition Pushdown

The engine supports Index Condition Pushdown for secondary-index scans. When the optimizer pushes a `WHERE` condition down, the engine evaluates it on the index key columns before the primary-key point lookup, by decoding those columns into the record buffer and calling MariaDB's `handler_index_cond_check()`. An entry that fails the condition is skipped without touching the data CF only when every index key part and every primary-key part is of a reconstructable type, meaning integers, `YEAR`, `DATE`, `DATETIME`/`TIMESTAMP` without fractional seconds, and fixed `CHAR`/`BINARY` in binary or latin1. If any part is of a type that cannot be rebuilt from its comparable bytes, such as `DECIMAL`, `VARCHAR`, float, `TIME`, or a multi-byte-charset `CHAR`, the engine fetches the full row first and then applies the condition, whichever columns the condition references. ICP still filters in that case but does not save the fetch.

### Multi-Range Read

The engine implements a custom MRR path for point-lookup batches such as `WHERE col IN (v1, ..., vN)` on a primary or full-key unique index. When every range is a full-key point equality and there are at least two of them, the engine buffers the keys, converts each to comparable bytes, and sorts by those bytes so the LSM sees a monotone stream of seeks rather than scattered ones in user order. Primary-key lookups bypass the iterator through `fetch_row_by_pk`, and secondary-index lookups reuse one cached iterator with a single seek per entry. Rows deleted concurrently are skipped.

The engine declines MRR and falls back to the base handler in three cases, single-range scans where sorting wins nothing, true range scans such as `BETWEEN`/`<`/`>`, and partitioned tables where `ha_partition` runs its own MRR across children.

## Auto-Increment

Auto-increment follows MariaDB's built-in `update_auto_increment()` during `write_row()`. Rather than calling `index_last()` on every insert, which would build and tear down an iterator each time, the engine keeps an in-memory atomic counter on the shared table descriptor. It seeds the counter once at table open. When the auto-increment column is the leftmost part of the primary key, it seeds by seeking that CF's last key. When the auto-increment column is instead the leading column of another index, the engine seeds by seeking that index's last entry, so the counter is correct at open in both cases. Each insert increments it with a compare-and-swap, making assignment constant-time, and an explicit insert larger than the current value bumps the counter to match. Values are reserved on the grid `auto_increment_offset` and `auto_increment_increment` describe, offset plus a multiple of the increment, with a reserved run spaced by the increment, the same values the server assigns. So concurrent sessions never receive the same value with an increment above one, and under Galera, where `wsrep_auto_increment_control` sets both on every node, each node generates values no other node does.

`TRUNCATE TABLE` resets the counter through the engine's `reset_auto_increment` hook, so the next generated id is `1`, for user-defined auto-increment columns and for the hidden row id alike. `ALTER TABLE ... AUTO_INCREMENT=N` only raises the counter. When `N` is above the current maximum the next generated id is `N`, and a lower `N` is ignored. The raised value is persisted, so it survives a restart.

```sql
CREATE TABLE tickets (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, note VARCHAR(200)
) ENGINE=TIDESDB;

INSERT INTO tickets (note) VALUES ('first');        -- 1
INSERT INTO tickets (note) VALUES ('second');       -- 2
INSERT INTO tickets (id, note) VALUES (100, 'x');   -- 100
INSERT INTO tickets (note) VALUES ('next');         -- 101
```

<sub>*This page is licensed: GPLv2*</sub>