Storage ID Usage

Use this guide when you want to control where table data is stored, pick storage per table, and understand user-table routing behavior.

For MinIO-specific S3-compatible setup, see /docs/server/integrations/minio. For the storage architecture, see /docs/server/architecture/storage-tiers, /docs/server/architecture/manifests, and /docs/server/architecture/table-types.

Create storage backends first

Before using STORAGE_ID in CREATE TABLE, the storage must exist in system.storages.

Filesystem storage

SQL

1CREATE STORAGE local_archive2    TYPE filesystem3    NAME 'Local Archive'4    DESCRIPTION 'Filesystem storage for archived tables'5    PATH './data/storage/local-archive'6    SHARED_TABLES_TEMPLATE 'shared/{namespace}/{tableName}'7    USER_TABLES_TEMPLATE 'users/{namespace}/{tableName}/{userId}';

PATH is supported for filesystem storage. BASE_DIRECTORY is also accepted.

S3 storage

SQL

1CREATE STORAGE s3_prod2    TYPE s33    NAME 'Production S3'4    DESCRIPTION 'S3 bucket for production data'5    BUCKET 'my-kalamdb-prod-bucket'6    REGION 'us-east-1'7    SHARED_TABLES_TEMPLATE 'shared/{namespace}/{tableName}'8    USER_TABLES_TEMPLATE 'users/{namespace}/{tableName}/{userId}';

For S3, you can use either:

BUCKET 'bucket-name' (optionally with REGION)
BASE_DIRECTORY 's3://bucket/prefix'

Useful checks:

SQL

1SHOW STORAGES;2SELECT storage_id, storage_type, storage_name FROM system.storages;

Use STORAGE_ID in CREATE TABLE

KalamDB accepts STORAGE_ID in WITH (...) table options.

Shared table with explicit storage

SQL

1CREATE TABLE app.config (2    key TEXT PRIMARY KEY,3    value TEXT4) WITH (5    TYPE = 'SHARED',6    STORAGE_ID = 's3_prod'7);

User table with explicit storage and flush policy

SQL

1CREATE TABLE app.messages (2    id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3    content TEXT,4    created_at TIMESTAMP DEFAULT NOW()5) WITH (6    TYPE = 'USER',7    STORAGE_ID = 'local_archive',8    FLUSH_POLICY = 'rows:5000'9);

If STORAGE_ID is omitted

If omitted, KalamDB resolves storage to local by default.

SQL

1CREATE TABLE app.metrics (2    id BIGINT PRIMARY KEY,3    value DOUBLE4) WITH (TYPE = 'SHARED');

Verify table storage metadata

Inspect table metadata in system.schemas:

SQL

1SELECT namespace_id, table_name, table_type, storage_id, use_user_storage2FROM system.schemas3WHERE namespace_id = 'app' AND is_latest = true4ORDER BY table_name;

Change table storage metadata

For USER and SHARED tables, admins can update STORAGE_ID with ALTER TABLE ... SET TBLPROPERTIES:

SQL

1ALTER TABLE app.messages2    SET TBLPROPERTIES (STORAGE_ID = 's3_prod');

The new storage ID must already exist in system.storages.

Per-user storage routing (USE_USER_STORAGE)

For USER tables, you can enable per-user storage routing metadata:

SQL

1CREATE TABLE app.geo_events (2    event_id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3    payload JSON,4    created_at TIMESTAMP DEFAULT NOW()5) WITH (6    TYPE = 'USER',7    STORAGE_ID = 's3_prod',8    USE_USER_STORAGE = true9);

Rules:

USE_USER_STORAGE is valid only for TYPE = 'USER'
STORAGE_ID remains fallback storage for the table
User preference fields exist in system.users (storage_mode, storage_id)

You can also toggle it after creation:

SQL

1ALTER TABLE app.geo_events2    SET TBLPROPERTIES (USE_USER_STORAGE = false);

Changing storage per user (current status)

Current SQL behavior:

DML against system.* tables is blocked
ALTER USER currently supports only SET PASSWORD, SET ROLE, and SET EMAIL

So there is no public SQL command yet to directly update a user’s storage_mode or storage_id.

Current developer workflow:

Set table-level routing with STORAGE_ID (optionally USE_USER_STORAGE)
For user-level overrides, use internal/admin backend flows

Common failures

Unknown storage ID

SQL

1CREATE TABLE app.bad_example (2    id INT PRIMARY KEY3) WITH (TYPE = 'SHARED', STORAGE_ID = 'does_not_exist');

Expected error pattern:

TEXT

1Storage 'does_not_exist' does not exist

Invalid USE_USER_STORAGE on non-user table

SQL

1CREATE TABLE app.bad_shared (2    id INT PRIMARY KEY3) WITH (TYPE = 'SHARED', USE_USER_STORAGE = true);