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.

Create storage backends first

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

Filesystem storage


CREATE STORAGE local_archive
    TYPE filesystem
    NAME 'Local Archive'
    DESCRIPTION 'Filesystem storage for archived tables'
    PATH './data/storage/local-archive'
    SHARED_TABLES_TEMPLATE 'shared/{namespace}/{tableName}'
    USER_TABLES_TEMPLATE 'users/{namespace}/{tableName}/{userId}';

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

S3 storage


CREATE STORAGE s3_prod
    TYPE s3
    NAME 'Production S3'
    DESCRIPTION 'S3 bucket for production data'
    BUCKET 'my-kalamdb-prod-bucket'
    REGION 'us-east-1'
    SHARED_TABLES_TEMPLATE 'shared/{namespace}/{tableName}'
    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:


SHOW STORAGES;
SELECT 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


CREATE TABLE app.config (
    key TEXT PRIMARY KEY,
    value TEXT
) WITH (
    TYPE = 'SHARED',
    STORAGE_ID = 's3_prod'
);

User table with explicit storage and flush policy


CREATE TABLE app.messages (
    id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
) WITH (
    TYPE = 'USER',
    STORAGE_ID = 'local_archive',
    FLUSH_POLICY = 'rows:5000'
);

If STORAGE_ID is omitted

If omitted, KalamDB resolves storage to local by default.


CREATE TABLE app.metrics (
    id BIGINT PRIMARY KEY,
    value DOUBLE
) WITH (TYPE = 'SHARED');

Verify table storage metadata

Inspect table metadata in system.schemas:


SELECT namespace_id, table_name, table_type, storage_id, use_user_storage
FROM system.schemas
WHERE namespace_id = 'app' AND is_latest = true
ORDER BY table_name;

Per-user storage routing (USE_USER_STORAGE)

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


CREATE TABLE app.geo_events (
    event_id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
    payload JSON,
    created_at TIMESTAMP DEFAULT NOW()
) WITH (
    TYPE = 'USER',
    STORAGE_ID = 's3_prod',
    USE_USER_STORAGE = true
);

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)

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


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

Expected error pattern:


Storage 'does_not_exist' does not exist

Invalid USE_USER_STORAGE on non-user table


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