Manage Tables in KDB-X DB Service (Clustered)¶

This page covers table management for clustered DB Service deployments. In a clustered deployment, tables are defined in the assembly YAML, which is the source of truth for service configuration.

Single-node deployment

If you are using a single-node deployment, refer to Manage Tables for API-based table management.

Overview¶

In a clustered deployment, tables are defined in the assembly file — a YAML configuration file that describes the structure of a database, its lifecycle, and the services that operate on it. The assembly is applied via Helm at deploy time, and all changes to tables go through it.

This approach provides:

Full configuration control, including sharding, storage tiering, and advanced schema settings.
Compatibility with Insights SDK configurations. Existing Insights SDK assembly YAML is compatible with the clustered DB Service.
Reproducibility, since the assembly file represents the complete intended state of the deployment.

API vs YAML configuration

In a clustered deployment, table definitions are managed through the assembly YAML rather than the table management API. API-based schema changes will not work on a deployment managed by assembly YAML, as the YAML is the authoritative configuration source.

Of the table management operations, only list and describe are available in a clustered deployment. Create and drop do not work, and neither does the createTable flag on an import.

Assembly file structure¶

An assembly file has the following top-level sections:

Section	Required	Description
`name`	Yes	Short name for this assembly
`description`	No	Purpose of the assembly
`labels`	No	Key-value pairs used for query routing and sharding across multiple assemblies
`dbSettings`	No	Global database settings (e.g. encryption)
`tables`	Yes	Schema definitions for all tables in this assembly (dictionary)
`mounts`	Yes	Mount points for stored data (dictionary)
`bus`	Yes	Message bus configuration for data ingest (dictionary)
`elements`	Yes	Service components (DAPs, Storage Manager) and their configuration (dictionary)

A minimal working example:

name: kx-db-service
labels:
  region: amer
  assetClass: fx

tables:
  trade:
    type: partitioned
    prtnCol: realTime
    sortColsDisk: [sym]
    sortColsOrd: [sym]
    columns:
      - name: sym
        type: symbol
        attrMem: grouped
        attrDisk: parted
        attrOrd: parted
      - name: realTime
        type: timestamp
      - name: price
        type: float
      - name: size
        type: long

bus:
  stream:
    protocol: rt
    topic: stream

mounts:
  rdb:
    type: stream
    partition: none
    baseURI: none
  idb:
    type: local
    partition: ordinal
    baseURI: file:///data/db/idb
  hdb:
    type: local
    partition: date
    baseURI: file:///data/db/hdb

elements:
  dap:
    instances:
      rdb:
        mountName: rdb
      idb:
        mountName: idb
      hdb:
        mountName: hdb
  sm:
    source: stream
    tiers:
      - name: rdb
        mount: rdb
      - name: idb
        mount: idb
        schedule:
          freq: 00:10:00    # every 10 minutes
      - name: hdb
        mount: hdb
        schedule:
          freq: 1D00:00:00  # every day
          snap: 01:35:00    # at 1:35 AM
        retain:
          time: 2 Years

The assembly is applied when the shard release is deployed with Helm: the assembly files are copied into the chart before install. Refer to Start the DB Service for the deployment steps.

For the full assembly reference, see the Insights database configuration reference.

Labels¶

Labels are key-value metadata used for query routing and sharding across multiple assemblies. A database must have at least one label associated with it. The combination of all assigned label values must be unique across assemblies in the same logical database.

labels:
  region: amer
  assetClass: fx

Labels enable sharding by distributing data across assemblies. A query can target a specific assembly using its labels, or query across all assemblies by omitting labels entirely. Labels appear in query results as virtual columns with a label_ prefix (e.g. label_region). In SQL queries, always reference labels with the label_ prefix.

Multi-assembly sharding example — four assemblies forming one logical database:

# assembly-a.yaml        # assembly-b.yaml
labels:                   labels:
  region: US                region: US
  sector: Finance           sector: Automotive

# assembly-c.yaml        # assembly-d.yaml
labels:                   labels:
  region: EU                region: EU
  sector: Finance           sector: Automotive

For more on query routing with labels, see the Insights data query overview.

Tables¶

Tables are defined under the tables: key as a dictionary keyed by table name. A schema must contain at least one partitioned table.

The following fields can be configured for each table:

Field	Type	Required	Description
`type`	string	Yes	Table type — see Table types below.
`description`	string	No	A description of the table's purpose.
`blockSize`	int	No	Number of records received before data is written to disk. Lower values reduce memory use but increase disk I/O. If not set, data is held in memory until the next EOI.
`prtnCol`	string	No*	Timestamp column used to partition data by date. Required if `type` is `partitioned`. Also used by `startTS`/`endTS` in queries.
`sortColsMem`	string[]	No	Columns to sort in the RDB (memory) tier. Applied on every update — not recommended for high-frequency ingest.
`sortColsOrd`	string[]	No	Columns to sort when data migrates into the IDB (ordinal) tier.
`sortColsDisk`	string[]	No	Columns to sort when data is written to the HDB (disk) tier.
`primaryKeys`	string[]	No	Columns that form the primary key. Incoming rows with matching keys overwrite existing records.
`isSharded`	boolean	No	Marks this table as split across multiple assemblies. Must be consistent across all assemblies that define the same table.
`shards`	int	No	Number of shards for this table across assemblies. Used with `isSharded: true`.
`columns`	object[]	Yes	List of column definitions — see Column definitions below.
`oldName`	string	No	Previous table name, used when renaming a table — see Schema modifications.

Table types¶

Type	Description
`partitioned`	Data stored in date partitions across RDB, IDB, and HDB. Required for timeseries data. At least one table per assembly must be partitioned.
`splayed`	Data stored as a single directory (one file per column) in the IDB. Memory-mapped in RDB, IDB, and HDB. Suitable for reference data.
`basic`	Data stored as a single file in the IDB. Memory-mapped across all tiers. Suitable for small reference data.
`splayed_mem`	Same as `splayed`, but loaded fully into memory rather than memory-mapped. Fastest for read-heavy reference data.
`mem_only`	Held in RDB memory only; never written to disk. Survives EOI and EOD. On RDB restart, rebuilt from the bus from the last EOI only — data before that is lost.

Naming restriction

splayed and basic table names must begin with a letter ([a-zA-Z]).

mem_only constraints

mem_only tables reject disk-related fields: prtnCol, blockSize, sortColsOrd, sortColsDisk, and column-level attrOrd / attrDisk. Use sortColsMem and attrMem only.

Column definitions¶

Each table's columns list defines the schema. The following fields are supported per column:

Field	Type	Required	Description
`name`	string	Yes	Column name. Must be unique within the table and conform to kdb+ naming rules. Do not use reserved words (`date`, `int`) or the reserved prefix `label_`.
`type`	string	Yes	Column data type — see Column types below.
`description`	string	No	Description of the column.
`attrMem`	string	No	Column attribute for the RDB (memory) tier.
`attrOrd`	string	No	Column attribute for the IDB (ordinal) tier.
`attrDisk`	string	No	Column attribute for the HDB (disk) tier.
`foreign`	string	No	Foreign key reference in the form `table.column`. Column type must match the referenced column's type.
`anymap`	boolean	No	Allows lists of lists to be stored using memory-mapped anymap files. Reduces memory use for nested data by deferring column load until accessed.
`oldName`	string	No	Previous column name, used when renaming — see Schema modifications.
`backfill`	string	No	Fully-qualified q function name to call when backfilling a new column into existing partitions — see Backfilling a column.

Column attributes¶

Attributes tune query performance and are set independently per storage tier (attrMem, attrOrd, attrDisk).

Attribute	Notes
`parted`	Enables fast partition-based lookups. Only one column per table can have this attribute. Requires the column to be sorted. Recommended for the column most commonly used in filters (e.g. `sym`).
`grouped`	Indexes the column using a hash map. Suitable for high-cardinality symbol columns. Does not require sorting, but increases storage and RAM usage.
`sorted`	Enables binary search on the column. Requires data to be in ascending order.
`unique`	Constant-time lookup. Requires all values in the column to be distinct.

Column types¶

Column types map to kdb+ base types. Use the singular form for atom values and the plural for vectors (except char, whose vector form is string).

Type	kdb+ type	Description
`boolean`	`1h`	True or false values
`guid`	`2h`	Unique identifiers (`00000000-0000-0000-0000-000000000000`)
`byte`	`4h`	Single byte (`0x00`–`0xFF`)
`short`	`5h`	2-byte integer
`int`	`6h`	4-byte integer
`long`	`7h`	8-byte integer
`real`	`8h`	4-byte float
`float`	`9h`	8-byte float
`char`	`10h`	Single character. Use `string` for a column of character vectors.
`symbol`	`11h`	Interned string. Use for repeated values (e.g. instrument codes). Avoid for unique strings — significant query performance overhead.
`timestamp`	`12h`	Nanoseconds since 2000.01.01. Required for partitioned table `prtnCol`.
`month`	`13h`	Year and month
`date`	`14h`	Year, month, and day
`datetime`	`15h`	Deprecated. Use `timestamp` instead.
`timespan`	`16h`	Duration in nanoseconds
`minute`	`17h`	Hours and minutes
`second`	`18h`	Hours, minutes, and seconds
`time`	`19h`	Hours, minutes, seconds and sub-seconds to nanosecond precision

For a mixed-type column (0h), set type: "". All values in a mixed column must be lists — inserting an atom into an empty mixed column corrupts the schema.

Mounts¶

Mounts define where data is physically stored across storage tiers.

mounts:
  rdb:
    type: stream
    partition: none
    baseURI: none
  idb:
    type: local
    partition: ordinal
    baseURI: file:///data/db/idb
  hdb:
    type: local
    partition: date
    baseURI: file:///data/db/hdb

Field	Type	Required	Description
`type`	string	Yes	`stream` for in-flight data (RDB), or `local` for data co-located on the file system (IDB, HDB).
`partition`	string	Yes	Partitioning scheme: `none` (stream mounts only), `ordinal` (IDB — uses interval-based numeric partitions), or `date` (HDB — partitioned by the `prtnCol` date).
`baseURI`	string	Yes	Storage location. Must be `none` for stream mounts. For local mounts, use `file://` followed by an absolute path (e.g. `file:///data/db/hdb`). Each mount must have a unique `baseURI`.

Bus¶

The bus section configures the message stream used for data ingest and coordination between assembly components.

bus:
  stream:
    protocol: rt
    topic: stream

Each entry in the bus dictionary provides:

Field	Type	Required	Description
`protocol`	string	Yes	Messaging protocol: `rt` (Reliable Transport), `tp` (Ticker Plant, compatible with `tick.q`), or `custom`.
`topic`	string	No	Stream topic name. For RT, this is typically the assembly name concatenated with the topic name.
`nodes`	string[]	No	Connection strings (`host:port`) for subscribing to the bus. Required for some deployment configurations.

The SM's source field and the RDB tier must both point to the same bus entry to ensure a deterministic sequence of ingest events.

Elements¶

The elements section configures the services that run within the assembly. For the DB Service, the two key elements are dap (Data Access Processes) and sm (Storage Manager).

DAP (Data Access Process)¶

DAPs serve query results from each storage tier. There are two scaling modes:

Scaling independently (recommended for most deployments) — a separate DAP instance per tier, allowing each tier to scale independently:

elements:
  dap:
    instances:
      rdb:
        mountName: rdb
      idb:
        mountName: idb
      hdb:
        mountName: hdb

Scaling uniformly — all tiers in a single container, adding another instance adds a copy of all tiers:

elements:
  dap:
    instances:
      db:
        mountList: [rdb, idb, hdb]

Use mountName to reference a single mount (independent scaling) or mountList to reference multiple mounts (uniform scaling). Do not use both on the same instance.

SM (Storage Manager)¶

The Storage Manager handles data writedown and tier migration. It is configured under elements.sm.

Field	Type	Required	Description
`source`	string	Yes	Name of the `bus` entry that is the data entrypoint.
`tiers`	list	Yes	Ordered list of storage tiers, from most recent to least. See Tiers below.
`initialImport`	boolean	No	When enabled, SM checks for an existing kdb+ database under the HDB mount's `baseURI/data` directory. If none is found, SM terminates. Remove this flag after first startup. See Initial Import.
`enforceSchema`	boolean	No	If `true`, data that doesn't match the schema is logged and discarded. Recommended during development. Disabled by default.

For additional SM fields (chunkSize, sortLimitGB, eodPeachLevel, and others), see the Insights storage configuration reference.

Tiers¶

Tiers describe how data migrates over time, ordered from most recent to least recent. Each tier has the following fields:

Field	Type	Required	Description
`name`	string	Yes	Tier name. Must be unique. Used in logs to identify the tier.
`mount`	string	Yes	Name of the corresponding `mounts` entry.
`schedule`	dict	No	Controls when rollovers are triggered — see below. Required for IDB and HDB tiers.
`retain`	dict	No	Controls how much data is kept in this tier before rolling over — see below.
`compression`	dict	No	Compression policy for on-disk data. Options: `algorithm`, `block`, `level`. Applies to `local` mounts with `date` partitioning only.
`store`	string	No	URI specifying where this tier physically stores data within its mount. Defaults to `<baseURI>/data`. Required when using object storage (e.g. `s3://...`).

schedule fields:

Key	Format	Description
`freq`	`HH:MM:SS` or q timespan	Interval length for ordinal (IDB) tiers. Controls how often an EOI is triggered. Default `00:10:00`.
`snap`	`HH:MM:SS`	Time at which data rolls from IDB to HDB (date-partitioned). Default `00:00:00`.
`offset`	`HH:MM:SS`	Staggers EOI triggers across multiple SMs to reduce resource contention. Default `00:00:00`.

retain fields (applicable to date-partitioned mounts):

Key	Format	Description
`time`	e.g. `2 Years`, `7 Days`, `3 Months`	Data older than this is rolled to the next tier. Units: `Years`, `Months`, `Weeks`, `Days` for date-partitioned mounts; `Hours`, `Minutes`, `Seconds` for stream mounts.
`sizePct`	`1`–`100`	Maximum percentage of the mount's total storage to use before rolling. Applies to date-partitioned mounts only.

If multiple retain keys are set, they apply as an inclusive OR — rollover triggers when either condition is met.

Example SM configuration with multiple tiers, retention, and object storage:

elements:
  sm:
    source: stream
    tiers:
      - name: rdb
        mount: rdb
      - name: idb
        mount: idb
        schedule:
          freq: 00:10:00
      - name: hdb
        mount: hdb
        schedule:
          freq: 1D00:00:00
          snap: 01:35:00
        retain:
          time: 2 Years

Database settings¶

Global settings are configured under dbSettings.

Encryption¶

dbSettings:
  encryption:
    encryptAll: true

Setting encryptAll: true encrypts all tables across all storage tiers. The default is false. If you later set this to false or remove it, the database is decrypted.

To use encryption, you must provide the key file and password via environment variables on all SM and DAP containers:

KXI_ENCRYPTION_KEY_FILE — path to the encryption key file
KXI_ENCRYPTION_PASSWORD — encryption password

Encryption applies to all storage tiers and is orthogonal to compression settings. Refer to Data At Rest Encryption for details on creating a key.

Schema modifications¶

The Storage Manager supports offline schema modifications. The following changes are supported:

Adding, renaming, deleting, or reordering tables and columns
Changing column attributes or data types
Enabling or disabling encryption

Changing column sort order is not supported

Modifying sortColsOrd or sortColsDisk for data that is already on disk is not supported. To change the sort order of an existing database, you must manually export, re-sort, and re-import using an initial import.

Schema conversion blocks ingest

SM does not accept new data while a schema conversion is in progress. All data publishers must be stopped before starting a schema conversion. For large databases, the conversion can take significant time — especially when an object storage tier is used.

Step-by-step process¶

Stop all containers (teardown the assembly).
Update the assembly YAML with the desired schema changes.
Start SM and DAP containers.
Check SM logs for Performing on-disk conversion (conversion started) and On-disk conversion complete, elapsed=XXX (finished).

Renaming a table¶

Set oldName on the table to link the new name to the previous one:

tables:
  tbl2:                  # new name
    oldName: tbl         # previous name
    type: partitioned
    prtnCol: realTime
    ...

Remove the oldName field from the assembly once the conversion is complete.

Renaming a column¶

Set oldName on the column:

columns:
  - name: bidPrice       # new name
    oldName: bid         # previous name
    type: float

Remove oldName from the assembly once the conversion is complete.

Note

oldName values must not match each other or the current name of any other table or column. Swapping two column names requires two separate upgrade steps.

Backfilling a column¶

When adding a new column, it is populated with null values by default. To populate it with calculated values from existing data, set backfill to a fully-qualified q function name:

columns:
  - name: spread
    type: float
    backfill: .my.calcSpread

The backfill function is called for every partition during the schema conversion. It receives the table name, a partition identifier, and the table data for that partition, and must return an atom or list of the correct type with the same length as the other columns.

To provide the function definition, create a package with a storage-manager entrypoint and load it via the KXI_PACKAGES environment variable on the SM. See the DB Configuration Reference for details.

Applying changes¶

To add, modify, or remove tables, update the assembly YAML, copy it into the chart as you did at install time, and run helm upgrade on the affected release with the same values files used at install. Refer to Start the DB Service for the charts and values files involved.

Add --dry-run to the upgrade to validate the assembly without applying any changes.

Removing tables

Removing a table from the assembly and running helm upgrade permanently deletes all data associated with that table.

Next steps¶

Storage tiers — understand RDB, IDB, and HDB
Initial Import — migrate an existing kdb+ HDB
Import data
Query data
Manage Tables (single-node)
Insights database configuration reference — full assembly YAML reference