SQL API Reference

Signatures match the installed extension SQL today. Prefer named arguments so optional parameters stay readable. Positional calls remain valid.

SQL

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

Session helpers

Function	Returns	Meaning
`snowflake_id()`	`bigint`	Monotonic Snowflake-like id
`koldstore_version()`	`text`	Extension version
`koldstore_user_id()`	`text`	Active `koldstore.user_id`, or `NULL`
`koldstore.preload_status()`	`jsonb`	Preload / merge-scan status

Public GUCs

GUC	Default	Meaning
`koldstore.user_id`	empty	Scope id for user-typed managed tables
`koldstore.cold_reads`	`auto`	`auto` / `on` / `off` (`off` errors when cold is required)
`koldstore.enable_merge_scan`	`on`	`off` → ERROR at exec (never silent hot-only)
`koldstore.max_open_parquet_readers`	`32`	Per-backend open reader cap (`1..=1024`)
`koldstore.max_merge_seen_keys`	`1000000`	Per-scan PK identity cap; `0` disables
`koldstore.log_level`	`info`	`error` / `warn` / `info` / `debug` / `trace`
`koldstore.min_max_rows_per_file`	`1000`	Floor for `max_rows_per_file`
`koldstore.flush_check_interval_seconds`	`30`	Auto-flush worker tick (`1..=86400`)
`koldstore.async_apply_poll_interval_ms`	`100`	Apply loop latch poll (`50..=5000`)
`koldstore.async_apply_max_rows_per_tick`	`0`	Max source changes per tick (`0` = unlimited)
`koldstore.async_apply_max_ms_per_tick`	`0`	Max ms per tick (`0` = unlimited)
`koldstore.flush_prelock_max_passes`	`3`	Flush phase-5.5 pre-lock apply passes
`koldstore.flush_prelock_max_ms`	`5000`	Flush phase-5.5 wall budget
`koldstore.async_mirror_max_retained_bytes`	`1 GiB`	Health threshold only — never blocks apply

Internal GUCs (internal_system_write, internal_flush_cleanup) are reserved for extension maintenance paths and cannot be set by application roles.

Configure durable settings with ALTER SYSTEM / ALTER DATABASE / ALTER ROLE and follow PostgreSQL reload rules. Session SET does not always reach the background worker.

Function catalog

Function	Returns
`koldstore.register_storage(...)`	`uuid`
`koldstore.alter_storage_credentials(...)`	`void`
`koldstore.alter_storage_location(...)`	`uuid`
`koldstore.manage_table(...)`	`uuid` (migration job id)
`koldstore.set_table_auto_flush(...)`	`boolean`
`koldstore.unmanage_table(...)`	`bigint`
`koldstore.wait_for_async_mirror()`	`bigint`
`koldstore.async_mirror_status()`	`jsonb`
`koldstore.async_mirror_slot_name()`	`text`
`koldstore.disable_async_mirror()`	`boolean`
`koldstore.enqueue_flush_job(...)`	`bigint` (`1` / `0`)
`koldstore.flush_table(...)`	`uuid`
`koldstore.list_jobs(...)`	`jsonb` array
`koldstore.cancel_job(...)` / `cancel_table_jobs(...)`	cancel status
`koldstore.describe_table(...)`	`jsonb`
`koldstore.recover_segments(...)`	`bigint`
`koldstore.changes_since(...)`	set of change rows

Storage

SQL

1SELECT koldstore.register_storage(2  name         => 'local-dev',3  storage_type => 'filesystem',  -- filesystem | s3 | gcs | azure4  base_path    => '/tmp/koldstore-demo',5  credentials  => '{}'::jsonb,6  config       => '{}'::jsonb7  -- optional: regular_path_tmpl, scoped_path_tmpl8);9 10SELECT koldstore.alter_storage_credentials(11  name => 'local-dev',12  credentials => '{"access_key_id":"...","secret_access_key":"..."}'::jsonb13);14 15SELECT koldstore.alter_storage_location(16  name => 'local-dev',17  base_path => '/var/lib/koldstore',18  config => '{}'::jsonb19);

Default path templates: {namespace}/{tableName}/ and {namespace}/{tableName}/{scopeId}/.

Manage / unmanage

See Manage & Flush for parameters and policy examples. Quick reference:

SQL

1SELECT koldstore.manage_table(2  table_name => 'chat.messages',3  storage => 's3_archive',4  hot_row_limit => 100005);6 7SELECT koldstore.set_table_auto_flush('chat.messages'::regclass, false);8 9SELECT koldstore.unmanage_table(10  table_name => 'chat.messages'::regclass,11  rehydrate => true,12  drop_cold => false13);

Async mirror

SQL

1SELECT koldstore.wait_for_async_mirror();  -- strong fence; 0 ≠ disabled2SELECT koldstore.async_mirror_status();3SELECT koldstore.async_mirror_slot_name();4-- After unmanaging every table:5SELECT koldstore.disable_async_mirror();

Flush and jobs

SQL

1SELECT koldstore.enqueue_flush_job(table_name => 'chat.messages'::regclass);2SELECT koldstore.flush_table(table_name => 'chat.messages'::regclass, force => false);3SELECT koldstore.list_jobs();4SELECT koldstore.describe_table(table_name => 'chat.messages'::regclass);5SELECT koldstore.recover_segments(table_name => 'chat.messages'::regclass, dry_run => true);

Change feed

SQL

1SELECT * FROM koldstore.changes_since(2  table_name => 'chat.messages'::regclass,3  since_seq => 0,4  limit_rows => 1000,5  last_rows => NULL6);

Full semantics: Change Feed.

Not shipped yet (do not call)

Documented as roadmap / ops intent only:

Cold DML helpers (hydrate_pk, cold update_row / delete_row)
backup_manifest, validate_cold_storage, packaged export/import
Packaged ALTER EXTENSION … UPDATE edges

Standard SQL UPDATE/DELETE that only matches cold rows affects zero hot rows in the current MVP — use unmanage/rehydrate paths when you need cold rows back in the heap.