Table Options

Per-table options set at CREATE TABLE are baked into the column family at creation time and shown in SHOW CREATE TABLE. Most have a tidesdb_default_* session variable, so a deployment can set the policy once and let every CREATE TABLE inherit it, with an explicit option overriding the default for one table. The exceptions are TTL, ENCRYPTED, and ENCRYPTION_KEY_ID, which have no session default. These three and ISOLATION_LEVEL are covered in their own chapters.

The complete set of options is COMPRESSION, BLOOM_FILTER, BLOOM_FPR, KEEP_VALUES_INLINE, BTREE_KLOG_BLOCK_SIZE, LEVEL_SIZE_RATIO, MIN_LEVELS, DIVIDING_LEVEL_OFFSET, L1_FILE_COUNT_TRIGGER, TOMBSTONE_DENSITY_TRIGGER, TOMBSTONE_DENSITY_MIN_ENTRIES, TTL, ENCRYPTED, ENCRYPTION_KEY_ID, and ISOLATION_LEVEL.

Compression

CREATE TABLE archive (id INT PRIMARY KEY, data TEXT) ENGINE=TIDESDB COMPRESSION='ZSTD';

The choices are NONE, SNAPPY, LZ4, ZSTD, and LZ4_FAST, and the default is LZ4. ZSTD gives the best ratio, LZ4 and LZ4_FAST favor speed. An encrypted table is the exception, its data column family is created with compression forced to NONE regardless of this option, because the rows are already ciphertext by the time they reach the library and ciphertext does not compress. The table’s secondary-index CFs hold unencrypted comparable keys and keep whatever algorithm was selected. Session default tidesdb_default_compression.

Every backend except NONE has to be compiled into the linked TidesDB library, which is a build option there, so a library built without a given backend rejects a table that asks for it and the CREATE fails. NONE is always available. On a build that omits the default LZ4, set tidesdb_default_compression to a backend the library carries, or to NONE, before creating tables.

Bloom filters

CREATE TABLE no_bloom (id INT PRIMARY KEY, v INT) ENGINE=TIDESDB BLOOM_FILTER=0;
CREATE TABLE precise  (id INT PRIMARY KEY, v INT) ENGINE=TIDESDB BLOOM_FPR=10;

Bloom filters let a point lookup skip SSTables that cannot contain the key. BLOOM_FILTER enables them, on by default. BLOOM_FPR is the false-positive rate in parts per 10,000, default 100 for a 1% rate. Session defaults tidesdb_default_bloom_filter and tidesdb_default_bloom_fpr.

Keeping values inline

CREATE TABLE inline_vals (id INT PRIMARY KEY, val VARCHAR(200))
  ENGINE=TIDESDB KEEP_VALUES_INLINE=1;

Each SSTable is a key log, and the database keeps one value log shared by every column family. Value separation is a database-wide policy set by tidesdb_value_separation_threshold, so a value at or above that size goes to the shared value log with a pointer left in the key log, and a smaller one stays inline. Separating a large value keeps it out of every later merge, which is the whole point of the threshold, at the cost of one value-log read per row on a scan. KEEP_VALUES_INLINE=1 overrides the policy for one table and holds every value in the key log whatever its size, which is worth it for a table that is scanned far more than it is merged. Session default tidesdb_default_keep_values_inline.

BTREE_KLOG_BLOCK_SIZE sets the block size in bytes of the key log’s B-tree nodes, default 4096. The default matches the block manager’s first-read window so a node is read in one go, and sizing a node just above that window costs a second read on every access. Session default tidesdb_default_btree_klog_block_size.

LSM-tree tuning

CREATE TABLE tuned (id INT PRIMARY KEY, v VARCHAR(200)) ENGINE=TIDESDB
  LEVEL_SIZE_RATIO=8
  MIN_LEVELS=3
  DIVIDING_LEVEL_OFFSET=1
  L1_FILE_COUNT_TRIGGER=4;

LEVEL_SIZE_RATIO is how much larger each level is than the previous one, default 10. MIN_LEVELS is the minimum tree depth, default 1. DIVIDING_LEVEL_OFFSET sets the offset used to compute the dividing level, the primary compaction target, calculated as num_levels - 1 - DIVIDING_LEVEL_OFFSET, default 1. L1_FILE_COUNT_TRIGGER is how many SSTables may accumulate at level 1 before compaction merges them down, default 4. TidesDB does not use a selectable compaction policy, it chooses among full preemptive merge, dividing merge, and partitioned merge automatically from the tree’s state relative to the dividing level. Session defaults tidesdb_default_level_size_ratio, tidesdb_default_min_levels, tidesdb_default_dividing_level_offset, and tidesdb_default_l1_file_count_trigger.

Tombstone density trigger

CREATE TABLE events (
  id BIGINT PRIMARY KEY, ts DATETIME, body TEXT, KEY (ts)
) ENGINE=TIDESDB
  TOMBSTONE_DENSITY_TRIGGER=5000
  TOMBSTONE_DENSITY_MIN_ENTRIES=2048;

When the library’s compaction planner evaluates a column family, it escalates compaction if any SSTable, at any level, has a tombstone count divided by entry count at or above a ratio, provided the SSTable has enough entries to matter. TOMBSTONE_DENSITY_TRIGGER is that ratio in parts per 10,000, so 5000 means 0.50, and the default 0 disables the check. TOMBSTONE_DENSITY_MIN_ENTRIES is the entry-count floor, default 1024, that stops a tiny SSTable from firing compaction. ALTER TABLE ... TOMBSTONE_DENSITY_TRIGGER=N updates the live column family so the new ratio applies the next time the planner evaluates it, without a restart. Session defaults tidesdb_default_tombstone_density_trigger and tidesdb_default_tombstone_density_min_entries. The write-path chapter covers how this fits with the single-delete optimization.

Options covered elsewhere

TTL at the table level and `TTL` on a column set row expiration. See Time-To-Live.
ENCRYPTED and ENCRYPTION_KEY_ID turn on data-at-rest encryption. See Data-at-Rest Encryption.
ISOLATION_LEVEL sets the per-table isolation level that a session at the SQL default REPEATABLE READ uses, default REPEATABLE_READ, which maps to the library’s SNAPSHOT, session default tidesdb_default_isolation_level. A session that asks for READ UNCOMMITTED, READ COMMITTED, or SERIALIZABLE uses its own level instead. See Transactions and Isolation.

Changing options with ALTER TABLE

ALTER TABLE ... option=value changes any of these options instantly, with no table copy, and applies the new configuration to the table’s live column families, so it takes effect without a restart. The exceptions are ENCRYPTED and, on an encrypted table, ENCRYPTION_KEY_ID. Changing either one rewrites every row, so it runs as a table copy, and ALGORITHM=INSTANT or ALGORITHM=INPLACE is refused. An unencrypted table can change its ENCRYPTION_KEY_ID instantly, since nothing is encrypted with it. See Online DDL.

Combining options

CREATE TABLE optimized (id INT PRIMARY KEY, val VARCHAR(100)) ENGINE=TIDESDB
  COMPRESSION='ZSTD'
  BLOOM_FILTER=1
  BLOOM_FPR=50
  KEEP_VALUES_INLINE=1
  ISOLATION_LEVEL='REPEATABLE_READ';