Skip to Content
Tables & SQLCreating Tables

Table DDL

KalamDB supports three application table types: USER, SHARED, and STREAM. For the full architecture, access matrices, storage behavior, and when to choose each type, see /docs/server/architecture/table-types.

CREATE TABLE

Syntax

SQL
CREATE TABLE [IF NOT EXISTS] [<schema>.]<table_name> (  <column_name> <data_type> [NOT NULL|NULL] [DEFAULT <expr>] [PRIMARY KEY],  ...,  [CONSTRAINT <name> PRIMARY KEY (<column_name>)])[WITH (  TYPE = '<USER|SHARED|STREAM>',  STORAGE_ID = '<storage_id>',  USE_USER_STORAGE = <TRUE|FALSE>,  FLUSH_POLICY = '<rows:N|interval:N|rows:N,interval:N>',  TTL_SECONDS = <seconds>,  EVICTION_STRATEGY = '<time_based|size_based|hybrid>',  MAX_STREAM_SIZE_BYTES = <bytes>,  COMPRESSION = '<none|snappy|zstd>')];

Table options are type-specific:

  • USER: STORAGE_ID, USE_USER_STORAGE, FLUSH_POLICY, COMPRESSION
  • SHARED: STORAGE_ID, FLUSH_POLICY, COMPRESSION
  • STREAM: TTL_SECONDS, EVICTION_STRATEGY, MAX_STREAM_SIZE_BYTES

Shared tables always use FORCE row-level security. Creating a shared table without CREATE POLICY is default-deny for user and service (zero rows on SELECT; writes fail). system and dba bypass RLS. ACCESS_LEVEL is not a table option.

For storage-specific options such as STORAGE_ID and USE_USER_STORAGE, see /docs/server/sql-reference/storage-id-usage. For flush behavior, see /docs/server/architecture/storage-tiers.

COMPRESSION accepts only none, snappy, and zstd, and is valid only for USER and SHARED tables. It controls the Apache Parquet codec used when table data is flushed or compacted into cold-storage segments. none writes uncompressed Parquet pages, snappy is the default and optimizes for low CPU overhead, and zstd uses Zstandard level 1 for better compression density with modest extra CPU. This option is not WebSocket gzip and does not change RocksDB hot-tier compression. STREAM tables use hot stream log storage and do not accept table Parquet compression.

Examples

User table (per-user isolated):

SQL
CREATE TABLE app.messages (  id BIGSERIAL PRIMARY KEY,  conversation_id BIGINT NOT NULL,  sender TEXT NOT NULL,  role TEXT NOT NULL DEFAULT 'user',  content TEXT NOT NULL,  created_at TIMESTAMP NOT NULL DEFAULT NOW()) WITH (  TYPE = 'USER',  STORAGE_ID = 'local',  USE_USER_STORAGE = false,  FLUSH_POLICY = 'rows:1000,interval:60',  COMPRESSION = 'snappy');

Shared table (global rows, FORCE RLS):

SQL
CREATE TABLE app.config (  key TEXT PRIMARY KEY,  value TEXT NOT NULL,  updated_at TIMESTAMP DEFAULT NOW()) WITH (TYPE = 'SHARED',  COMPRESSION = 'zstd'); CREATE POLICY public_config_read ON app.config  FOR SELECT TO PUBLIC  USING (true);

Stream table (ephemeral with TTL):

SQL
CREATE TABLE app.events (  event_id TEXT PRIMARY KEY,  payload TEXT,  created_at TIMESTAMP DEFAULT NOW()) WITH (TYPE = 'STREAM',  TTL_SECONDS = 30,  EVICTION_STRATEGY = 'hybrid',  MAX_STREAM_SIZE_BYTES = 1048576);

ALTER TABLE

SQL
-- Add a columnALTER TABLE [<schema>.]<table_name>  ADD COLUMN <name> <type> [NOT NULL|NULL] [DEFAULT <value>]; -- Drop a columnALTER TABLE [<schema>.]<table_name>  DROP COLUMN <name>; -- Modify a column typeALTER TABLE [<schema>.]<table_name>  ALTER COLUMN <name> TYPE <type> USING <conversion_expression>; -- Change table propertiesALTER TABLE [<schema>.]<table_name>  SET TBLPROPERTIES (<table_option> = <value>, ...); -- Create a vector index on an embedding columnALTER TABLE [<schema>.]<table_name>  CREATE INDEX <embedding_column> USING COSINE;

Equality indexes on USER and SHARED tables use parentheses, not USING COSINE:

SQL
CREATE INDEX idx_messages_conversation  ON chat.messages (conversation_id, created_at); ALTER TABLE chat.messages DROP INDEX idx_messages_conversation;

See Indexes for prefix seeks, uniqueness, and membership-policy patterns. Vector indexes stay on Vector Search.

SET TBLPROPERTIES supports the same type-specific persisted options as CREATE TABLE. Use FLUSH_POLICY = NULL to clear a user/shared flush policy.

Examples:

SQL
ALTER TABLE app.config  SET TBLPROPERTIES (COMPRESSION = 'zstd'); ALTER TABLE app.messages  SET TBLPROPERTIES (FLUSH_POLICY = 'rows:5000', USE_USER_STORAGE = true); ALTER TABLE app.events  SET TBLPROPERTIES (    TTL_SECONDS = 3600,    EVICTION_STRATEGY = 'size_based',    MAX_STREAM_SIZE_BYTES = 1048576  );

Vector index example

SQL
CREATE TABLE rag.documents_vectors (  id BIGINT PRIMARY KEY,  embedding EMBEDDING(384)) WITH (TYPE = 'USER'); ALTER TABLE rag.documents_vectors  CREATE INDEX embedding USING COSINE;

After the index exists, rank nearest rows with:

SQL
SELECT idFROM rag.documents_vectorsORDER BY COSINE_DISTANCE(embedding, '[0.12,0.03,0.98]')LIMIT 10;

DROP TABLE

SQL
DROP TABLE [IF EXISTS] [<schema>.]<table_name>;

Grant shared-table rows with CREATE POLICY, not table options.

CREATE VIEW

SQL
CREATE VIEW [<schema>.]<view_name> AS <select_query>;CREATE VIEW [<schema>.]<view_name> (<column1>, <column2>, ...) AS <select_query>;

Example:

SQL
CREATE VIEW chat.recent_messages AS  SELECT id, sender, content, created_at  FROM chat.messages  WHERE created_at > NOW() - INTERVAL '1 hour'  ORDER BY created_at DESC;

SHOW TABLES

SQL
SHOW TABLES;SHOW TABLES IN <schema>;SHOW TABLES IN NAMESPACE <schema>;

DESCRIBE TABLE

SQL
DESCRIBE TABLE [<schema>.]<table_name>;DESC TABLE [<schema>.]<table_name>; -- Show table historyDESCRIBE TABLE [<schema>.]<table_name> HISTORY;

Returns full schema-registry metadata (column_id, is_primary_key, schema_version, Kalam data_type names, and more).

For the DataFusion-style shorthand (DESCRIBE namespace.table), CLI \d behavior, and EXPLAIN / EXPLAIN ANALYZE VERBOSE plan inspection, see /docs/server/sql-reference/diagnostics.

SHOW STATS

SQL
SHOW STATS FOR TABLE [<schema>.]<table_name>;

More Table Architecture

Constraints and schema evolution

Use NOT NULL, defaults and a primary key for application columns. PostgreSQL-shaped declarations and catalog metadata do not guarantee PostgreSQL constraint enforcement. In the tested 0.8 server, table CHECK, secondary UNIQUE and foreign-key declarations were accepted but did not reject violating writes. Do not rely on them as integrity boundaries; use validated application/routine writes and enforced domain checks where appropriate. A primary key is optional: a table without one gets a hidden _row_id, omitted by SELECT *. Use an explicit ID for application row references.

SQL
CREATE TABLE app.products (  id BIGSERIAL PRIMARY KEY,  sku TEXT NOT NULL,  price NUMERIC(12, 2) NOT NULL) WITH (TYPE = 'USER');ALTER TABLE app.products ADD COLUMN description TEXT;ALTER TABLE app.products ALTER COLUMN price TYPE NUMERIC(14, 2)  USING price::NUMERIC(14, 2);

A new column default supplies a value to existing rows; later default changes affect new writes rather than rewriting historical values. Changing a column type converts existing rows with USING. Coordinate schema changes with live clients: incompatible DDL disconnects subscriptions, which must reconnect against the committed schema.

See sequences and generated IDs and transactions.

Schemas & Session SettingsSequences & Generated IDs