Getting Started

This walkthrough creates a managed table on local filesystem storage, flushes excess rows to Parquet, and shows both merge-scan reads and the change feed.

Prerequisites: Installation completed — shared_preload_libraries includes koldstore, wal_level=logical, and CREATE EXTENSION koldstore succeeded.

1. Register storage

1SELECT koldstore.register_storage(2  name         => 'local-dev',3  storage_type => 'filesystem',4  base_path    => '/tmp/koldstore-demo',5  credentials  => '{}'::jsonb,6  config       => '{}'::jsonb7);

storage_type may also be s3, gcs, or azure. For MinIO-style S3:

1SELECT koldstore.register_storage(2  name         => 'minio',3  storage_type => 's3',4  base_path    => 'koldstore-bucket',5  credentials  => '{"access_key_id":"minioadmin","secret_access_key":"minioadmin"}'::jsonb,6  config       => '{"endpoint":"http://127.0.0.1:9000","region":"us-east-1","path_style":true}'::jsonb7);

2. Create a normal heap table

Managed tables need a primary key. Supported types today include boolean, integers, real, double precision, text, varchar, uuid, jsonb, and timestamptz.

1CREATE SCHEMA IF NOT EXISTS app;2 3CREATE TABLE app.messages (4  id bigint PRIMARY KEY,5  body text NOT NULL,6  created_at timestamptz NOT NULL DEFAULT now()7);8 9INSERT INTO app.messages (id, body)10SELECT gs, 'row ' || gs11FROM generate_series(1, 1012) AS gs;

3. Manage the table

Two equivalent styles:

A. manage_table (structured options)

1SELECT koldstore.manage_table(2  table_name         => 'app.messages',3  storage            => 'local-dev',4  hot_row_limit      => 1000,5  min_flush_rows     => 1,6  max_rows_per_file  => 500,7  migration_order_by => 'id'8);

B. ALTER TABLE options

1ALTER TABLE app.messages SET (2  koldstore_enabled = true,3  koldstore_storage = 'local-dev',4  koldstore_hot_row_limit = 1000,5  koldstore_min_flush_rows = 1,6  koldstore_max_rows_per_file = 5007);

Do not turn management off with koldstore_enabled = false — use koldstore.unmanage_table(...) instead. Storage cannot be changed after manage.

With hot_row_limit set, manage_table rejects non-PK UNIQUE constraints and foreign keys (those stay hot-only after flush). See Status & Limits.

4. Fence the async mirror

Foreground DML writes the heap only. A database worker applies committed WAL into the latest-state mirror (koldstore.messages__cl) with short lag. Before work that must observe every commit visible at the start of the call:

1SELECT koldstore.wait_for_async_mirror();

flush_table runs this fence automatically.

5. Page the change feed

1SELECT seq, op, pk, deleted, source2FROM koldstore.changes_since(3  table_name => 'app.messages'::regclass,4  since_seq  => 0,5  limit_rows => 1006);

Resume with an exclusive cursor (seq > since_seq). Or rewind to the newest N rows (delivered oldest → newest) when since_seq = 0:

1SELECT seq, op, pk, deleted, source2FROM koldstore.changes_since(3  table_name => 'app.messages'::regclass,4  since_seq  => 0,5  limit_rows => 1000,6  last_rows  => 507);

Full semantics: Change Feed.

6. Flush excess rows to cold storage

1SELECT koldstore.flush_table(table_name => 'app.messages'::regclass);

With hot_row_limit = 1000 and more than 1000 mirror keys, the oldest excess rows (by mirror seq) move to Parquet and leave the hot heap. Inspect status:

1SELECT jsonb_pretty(2  koldstore.describe_table(table_name => 'app.messages'::regclass)3);

7. Query one table

1SELECT count(*) FROM app.messages;  -- hot + cold via KoldMergeScan2SELECT * FROM app.messages WHERE id = 1;

After flush, cold-capable plans show KoldMergeScan. Hot-only PK lookups that cannot hit cold may keep a native Index/Seq/Bitmap Scan with no wrapper — that path is intentional and performance-critical.

What just happened

1. PostgreSQL still owns transactions, MVCC, permissions, and the hot heap
2. WAL capture wrote latest-state rows into koldstore.messages__cl
3. Flush published Parquet segments and pruned flushed hot/mirror rows
4. SELECT merges hot + cold; changes_since pages the same latest-state stream

Next: Under the Hood for the architecture, or Manage & Flush for policy details.