SQL API

This page documents the SQL functions and GUCs exposed by the installed extension today. Signatures below match the generated koldstore--<version>.sql output from pgrx.

Call style

PostgreSQL accepts both positional and named arguments for the same function. These docs use named arguments (arg => value) so call sites stay readable and resilient to optional trailing parameters. Positional calls remain valid; they are not a second API.

-- Preferred (named)
SELECT koldstore.register_storage(
  name         => 'local-dev',
  storage_type => 'filesystem',
  base_path    => '/tmp/koldstore-demo',
  credentials  => '{}'::jsonb,
  config       => '{}'::jsonb
);

-- Also valid (positional)
SELECT koldstore.register_storage(
  'local-dev',
  'filesystem',
  '/tmp/koldstore-demo',
  '{}'::jsonb,
  '{}'::jsonb
);

Session

SELECT snowflake_id();
SELECT koldstore_version();
SELECT koldstore_user_id();
SELECT koldstore.preload_status();

Function	Returns	Meaning
`snowflake_id()`	`bigint`	Monotonic Snowflake-like id
`koldstore_version()`	`text`	Extension version
`koldstore_user_id()`	`text`	Active `koldstore.user_id` GUC value, or `NULL` when unset
`koldstore.preload_status()`	`jsonb`	Whether `shared_preload_libraries` lists `koldstore`, whether this process loaded via shared preload, and `enable_merge_scan`

shared_preload_libraries = 'koldstore' is mandatory for correct managed-table reads. Without it, _PG_init / CREATE EXTENSION / LOAD fail closed, and manage_table errors. session_preload_libraries is not sufficient.

Configuration

Runtime settings use the koldstore. GUC prefix:

SET koldstore.user_id = 'tenant-a';
SET koldstore.cold_reads = 'auto';
SET koldstore.enable_merge_scan = on;
SET koldstore.max_open_parquet_readers = 32;
SET koldstore.log_level = 'info';
SET koldstore.min_max_rows_per_file = 1000;

Use PostgreSQL-native persistence for durable configuration, for example ALTER SYSTEM SET, ALTER DATABASE ... SET, or ALTER ROLE ... SET, followed by the normal PostgreSQL reload rules for the chosen scope.

Public GUCs

GUC	Type	Default	Meaning
`koldstore.user_id`	string	empty	Active user-scope id for user-scoped managed tables. Required for scoped reads and writes.
`koldstore.cold_reads`	string	`auto`	`auto`: cold eligible by catalog/cost; `on`: cold eligible without forcing unnecessary object reads; `off`: hot-only and ERROR when correctness requires cold segments.
`koldstore.enable_merge_scan`	bool	`on`	Required for managed-table SELECT. When `off`, `KoldMergeScan` errors at execution instead of allowing an incorrect heap-only read.
`koldstore.explain_pipeline`	bool	`off`	When `on`, `EXPLAIN (FORMAT JSON)` includes the nested `KoldStore Pipeline` diagnostic tree. `EXPLAIN … VERBOSE` also enables it for JSON. Default keeps concise Custom Scan properties plus real `Plans` children.
`koldstore.max_open_parquet_readers`	int	`32`	Per-backend open Parquet reader cap for cold scans (fail-fast when exceeded). Clamped to `1..=1024`.
`koldstore.max_merge_seen_keys`	int	`1000000`	Per-scan cap on exact PK identities retained by `KoldMergeScan` (fail-closed when exceeded). Protects backends from accidental full-table scans. `0` disables the cap. Clamped to `0..=100000000`.
`koldstore.log_level`	string	`info`	Extension log verbosity: `error`, `warn`, `info`, `debug`, or `trace`.
`koldstore.min_max_rows_per_file`	int	`1000`	Minimum allowed `max_rows_per_file` for `manage_table` and flush. Lower temporarily for tests, for example `SET koldstore.min_max_rows_per_file = 100`. Clamped to `1..=1000000`.
`koldstore.flush_check_interval_seconds`	int	`30`	How often ephemeral maintenance evaluates `auto_flush` tables, enqueues at most one due flush job, and asks the supervisor to spawn flush executors. Clamped to `1..=86400`. Independent of PostgreSQL autovacuum.
`koldstore.max_parallel_flush_jobs`	int	`2`	Max concurrent one-shot flush executor workers per database. Clamped to `1..=16`. Use `1` on small-memory hosts so encode spikes do not overlap.
`koldstore.flush_job_max_runtime_seconds`	int	`1800`	Wall-clock budget for one flush job attempt. Checked between passes and between streamed batches within a pass (so one oversized force-flush pass cannot outrun the budget); exceeded attempts fail with an error so a stuck worker cannot run forever. `0` disables. Clamped to `0..=86400`.
`koldstore.flush_execution`	string	`queue`	`queue`: `flush_table` enqueues a durable job and returns its UUID; a one-shot executor runs the work. `inline`: enqueue then run in the calling backend (SPI / `#[pg_test]` only).
`koldstore.job_retention_days`	int	`30`	Days to retain terminal jobs before purge; `0` disables. Jobs still referenced by pending cold segments are never deleted.
`koldstore.async_apply_watchdog_interval_ms`	int	`30000`	Safety watchdog for missed commit wakeups. Managed commits normally set the persistent WAL applier latch immediately. Clamped to `1000..=300000`.
`koldstore.async_apply_max_rows_per_tick`	int	`0`	Max source row changes per apply tick (`0` = unlimited / drain available WAL). Cap this on small machines (for example `8192`) via `ALTER DATABASE` so background workers see it.
`koldstore.async_apply_max_ms_per_tick`	int	`0`	Max wall-clock ms per apply tick (`0` = unlimited). When exhausted, commit `applied_lsn` and continue next wake. Cap alongside row budget on small hosts.
`koldstore.flush_prelock_max_passes`	int	`3`	Max phase-5.5 pre-lock async apply passes during flush before failing closed.
`koldstore.flush_prelock_max_ms`	int	`5000`	Combined wall-clock budget (ms) for flush phase-5.5 pre-lock catch-up.
`koldstore.async_mirror_max_retained_bytes`	int	`1073741824` (1 GiB)	Health threshold for slot-retained WAL bytes. Exceeding it marks `async_mirror_status().retention.ok` false but never blocks the applier from draining WAL. `0` disables this health threshold. Configure PostgreSQL retention/disk safeguards independently.

Internal GUCs

These are reserved for extension maintenance paths. Application roles cannot set them.

GUC	Type	Default	Meaning
`koldstore.internal_system_write`	bool	`off`	Allows internal KoldStore system writes.
`koldstore.internal_flush_cleanup`	bool	`off`	Allows pruning flushed hot and mirror rows during flush cleanup.

Exposed functions

Every SQL-callable function the extension installs today:

Function	Returns	Value
`snowflake_id()`	`bigint`	Generated Snowflake-like id
`koldstore_version()`	`text`	Extension version string
`koldstore_user_id()`	`text`	Active `koldstore.user_id`, or `NULL`
`koldstore.register_storage(...)`	`uuid`	Storage backend id (`koldstore.storage.id`)
`koldstore.alter_storage_credentials(...)`	`void`	No value
`koldstore.alter_storage_location(...)`	`uuid`	Storage backend id
`koldstore.manage_table(...)`	`uuid`	Migration job id (`koldstore.jobs.id`)
`koldstore.set_table_auto_flush(...)`	`boolean`	`true` when an active managed table was updated
`koldstore.unmanage_table(...)`	`bigint`	Count of deactivated `koldstore.schemas` rows
`koldstore.wait_for_async_mirror()`	`bigint`	Async source row changes applied by this fence
`koldstore.async_mirror_status()`	`jsonb`	DB-scoped slot lag, WAL watermarks, apply rates, health
`koldstore.disable_async_mirror()`	`boolean`	Whether async publication or slot infrastructure was removed
`koldstore.enqueue_flush_job(...)`	`uuid`	Flush job id, or `NULL` when nothing is due
`koldstore.flush_table(...)`	`jsonb`	Flush result object (`job_id`, `status`, `error`, …); see below
`koldstore.table_status(...)`	`jsonb`	Operator view: table hot/mirror/cold/jobs + `async_mirror`
`koldstore.recover_segments(...)`	`bigint`	Number of orphan recovery actions planned

Storage and Migration

`koldstore.register_storage`

Two overloads are available. Both accept named arguments.

-- Default path templates: {namespace}/{tableName}/ and
-- {namespace}/{tableName}/{scopeId}/
SELECT koldstore.register_storage(
  name         => 'local-dev',
  storage_type => 'filesystem',
  base_path    => '/tmp/koldstore-demo',
  credentials  => '{}'::jsonb,
  config       => '{}'::jsonb
);

-- Custom path templates
SELECT koldstore.register_storage(
  name               => 'local-dev',
  storage_type       => 'filesystem',
  base_path          => '/tmp/koldstore-demo',
  credentials        => '{}'::jsonb,
  config             => '{}'::jsonb,
  regular_path_tmpl  => '{namespace}/{tableName}/',
  scoped_path_tmpl   => '{namespace}/{tableName}/{scopeId}/'
);

storage_type must be one of filesystem, s3, gcs, or azure (cloud kinds require the matching extension cargo feature: s3, gcs, azure, or cloud).

Optional check (default true): opens the configured backend (filesystem, S3, GCS, or Azure) and performs a put/delete probe object (.koldstore-write-probe) so registration fails fast on bad credentials, unreachable endpoints, or unwritable paths. Filesystem backends also create base_path when needed. Pass check => false to skip the probe.

SELECT koldstore.register_storage(
  name         => 'local-dev',
  storage_type => 'filesystem',
  base_path    => '/koldstore-data/cold/',
  credentials  => '{}'::jsonb,
  config       => '{}'::jsonb,
  check        => false   -- skip writability probe
);

Returns: uuid — the storage backend id (koldstore.storage.id). Fails with storage \` already existswhen the name is taken; usealter_storage_credentials/alter_storage_location` to change an existing backend.

`koldstore.alter_storage_credentials`

SELECT koldstore.alter_storage_credentials(
  name        => 'local-dev',
  credentials => '{"access_key_id":"...","secret_access_key":"..."}'::jsonb
);

Rotates credentials without rewriting existing cold object paths.

Returns: void — no value. Errors if the storage name does not exist.

`koldstore.alter_storage_location`

SELECT koldstore.alter_storage_location(
  name      => 'local-dev',
  base_path => '/var/lib/koldstore',
  config    => '{}'::jsonb
);

Updates storage location/configuration without direct catalog DML. Optional check (default true) probes local filesystem paths the same way as register_storage; pass check => false to skip.

Returns: uuid — the storage backend id. Errors if the storage name does not exist.

Default table management with `ALTER TABLE`

ALTER TABLE messages SET (
  koldstore_enabled = true,
  koldstore_storage = 'cold_s3',
  koldstore_move_after = '90 days',
  koldstore_min_flush_rows = 1000,
  koldstore_max_rows_per_file = 10000,
  koldstore_max_rows_per_flush = 10000
);

Use koldstore_hot_row_limit instead of koldstore_move_after to retain a fixed hot-row count. The selectors are mutually exclusive and replace one another atomically; omitted batching settings are preserved. Native PostgreSQL interval input such as '90 days', '3 months', and 'P90D' is accepted. Age is measured from the latest captured mirror mutation encoded in seq.

koldstore_move_when is reserved and fails closed. Use koldstore.unmanage_table(...) rather than koldstore_enabled = false. Storage cannot be changed after management.

Advanced and compatibility management: `koldstore.manage_table`

SELECT koldstore.manage_table(
  table_name        => 'chat.messages',
  storage           => 's3_archive',
  hot_row_limit     => 10000,
  min_flush_rows    => 1000,
  max_rows_per_file => 1000,
  target_file_size_mb => 256,
  migration_order_by  => 'created_at'
);

Registers a heap table for KoldStore management with structured flush settings. hot_row_limit is required in the call (pass NULL for hot-only tables). table_type defaults to shared; optional scope_column, migration_order_by, compression, and target_file_size_mb arguments are also available.

Parameter	Default	Meaning
`table_name`	required	Table to manage (`regclass`)
`storage`	required	Registered storage backend name
`hot_row_limit`	required (`NULL` allowed)	Maximum mirror rows to keep hot; `NULL` for hot-only tables
`min_flush_rows`	`1000`	Minimum excess rows required before a flush moves data cold
`max_rows_per_file`	`1000`	Maximum rows written into one Parquet segment per flush batch (minimum `1000` unless lowered via `koldstore.min_max_rows_per_file`). Dominates flush peak RSS — keep small on low-RAM hosts; see Memory and small machines
`table_type`	`'shared'`	`shared` or `user`
`scope_column`	`NULL`	Required when `table_type => 'user'`
`migration_order_by`	`NULL`	Optional oldest-to-newest column used for populated-table migration
`compression`	`NULL`	Optional Parquet compression name
`target_file_size_mb`	`NULL`	Optional target Parquet segment size in MiB; stored for future size-aware flushing
`auto_flush`	`true`	When `true`, ephemeral maintenance may enqueue flush jobs for this table; set `false` to reserve flushes for cron / manual `flush_table`

Returns: uuid — the migration job id written to koldstore.jobs (empty tables get a completed migrate job; populated tables run mirror initialization inline and return that job id).

Flush selection keeps the newest rows hot by mirror seq and always flushes the oldest eligible excess first. Example with hot_row_limit = 10000 and min_flush_rows = 1000:

Constraint note: when hot_row_limit is set, manage_table rejects tables with non-primary-key UNIQUE constraints or foreign keys. Koldstore enforces those constraints on hot rows only after management; cold Parquet is not checked on normal DML. See Limitations.

Mirror rows	Flush result
10,505	No flush (`505` excess is below `min_flush_rows`)
11,000	Flush `1,000` rows into `1` file (`max_rows_per_file = 1000`)
11,250	Flush `1,000` rows, keep `10,250` hot
11,500	Flush `1,500` rows into `2` files

Mirror capture

manage_table always configures committed-WAL capture. It requires wal_level=logical; KoldStore manages the empty koldstore_async_mirror publication and one deterministic logical slot per database. Applications must tolerate the normal short lag or call koldstore.wait_for_async_mirror() at a required consistency boundary. flush_table invokes the same catch-up path automatically.

Authoritative mirror seq values are allocated only by the serialized WAL applier and are the exclusive changes_since cursor (seq > last_seq). See Mirror capture.

`koldstore.set_table_auto_flush`

SELECT koldstore.set_table_auto_flush(
  table_name => 'chat.messages',
  enabled    => false
);

Updates koldstore.schemas.options.auto_flush for an active managed table. Manual flush_table / enqueue_flush_job ignore this flag. See Scheduling.

Returns: boolean — true when an active managed row was updated.

`koldstore.unmanage_table`

SELECT koldstore.unmanage_table(
  table_name => 'chat.messages'
);

Disables management after rehydration or archive-detach mode. Optional rehydrate and drop_cold arguments default to NULL and can be passed by name when needed:

SELECT koldstore.unmanage_table(
  table_name => 'chat.messages',
  rehydrate  => true,
  drop_cold  => false
);

Returns: bigint — number of koldstore.schemas rows deactivated for the table (normally 1 when the table was actively managed, 0 if none were active).

Async Mirror Operations

These functions operate on database-scoped infrastructure used by managed tables (WAL-only committed-WAL mirror capture).

`koldstore.wait_for_async_mirror`

SELECT koldstore.wait_for_async_mirror();

Applies committed source changes available at the fence boundary and returns when the mirror has reached that boundary. The fence LSN is captured at call time (and forced durable), so concurrent writers after that point do not extend the wait. This is an optional strong-consistency API for reads/benchmarks — flush_table and auto-flush do not call it (they enqueue/spawn and return). The background worker normally keeps the mirror caught up without an explicit call.

Returns: bigint — the number of source row-change messages applied by this invocation. A return value of 0 can mean the worker had already caught up; it does not mean async capture is disabled.

`koldstore.async_mirror_status`

SELECT koldstore.async_mirror_status();

Database-scoped async mirror health (no managed table required). Prefer table_status(table) when you already have a table — it embeds the same object under async_mirror.

Includes:

Field	Meaning
`slot_name`	Deterministic logical-slot name for this database
`wal.current_lsn`	Current WAL tip (`pg_current_wal_lsn`) — latest commit frontier
`wal.applied_lsn`	Durable mirror apply watermark (`async_mirror_state`) — latest read/apply
`wal.confirmed_flush_lsn`	Slot ack / retention frontier
`wal.lag_bytes`	Bytes between current WAL and confirmed flush
`slot` / `state` / `apply` / `retention` / `healthy`	Slot presence, durable state, process-local rates, retention threshold

`koldstore.disable_async_mirror`

SELECT koldstore.disable_async_mirror();

Drops the current database's async logical slot and publication and clears its apply checkpoint. It refuses cleanup while any actively managed table uses async capture. Unmanage those tables first. Calling it repeatedly is safe; a later async manage_table recreates compatible infrastructure automatically.

Returns: boolean — true when a slot or publication existed and was removed, otherwise false.

Flush and Cold Data

`koldstore.enqueue_flush_job`

SELECT koldstore.enqueue_flush_job(
  table_name => 'chat.messages'
);

Inserts a pending flush job when none is already active for the table, or returns the existing active job UUID. Returns NULL when no flush work is due (same eligibility rules as flush_table). Does not spawn flush executors — use flush_table when you want work to start. Flush jobs are table-wide; user-scope partitioning uses the managed table's scope_column and session koldstore.user_id, not an enqueue argument.

Returns: uuid — the flush job id (koldstore.jobs.id), or NULL when nothing is due.

`koldstore.flush_table`

SELECT koldstore.flush_table(
  table_name => 'chat.messages'
);
-- → jsonb, for example:
-- {"ok": true, "job_id": "...", "status": "queued", "execution": "queue", ...}
-- {"ok": true, "job_id": null, "status": "not_due", "reason": "..."}
-- {"ok": false, "job_id": "...", "status": "error", "error": "Permission denied ..."}

Ensures a durable flush job exists and starts queue execution (production default koldstore.flush_execution = queue):

Inserts a pending job or returns the existing active job UUID inside JSON
Spawns a one-shot flush executor when capacity allows
Returns immediately with status = queued — progress is visible in koldstore.jobs / list_jobs / table_status

status = not_due (with job_id null) when policy selection is empty — including when mirror excess is positive but below max_rows_per_file — so undersized Parquet segments are never queued.

Queue-mode storage failures happen in the executor after return; they set koldstore.jobs.error_trace and emit a PostgreSQL WARNING (koldstore flush: FAILED ...) so docker logs shows them. Inline mode includes error / rows_flushed in the same JSON response.

With flush_execution = inline (SPI tests only), the calling backend also runs the flush before returning (status = completed or error).

If another backend already holds this table's flush lock while no active job row is visible yet, the call fails with an ERROR such as flush already in progress. When an active job already exists, status = already_running and job_id is that job.

Returns: jsonb — see fields above.

`koldstore.list_jobs`

SELECT koldstore.list_jobs();
SELECT koldstore.list_jobs(
  statuses  => '["running","pending"]'::jsonb,
  job_types => '["flush"]'::jsonb,
  table_name => 'chat.messages'::regclass
);

Returns a JSON array of job objects (status, phase, progress fields, payload). Filters are optional. Progress updates from an in-progress flush are visible to other sessions when that flush statement ends (mid-statement live commits are not used).

`koldstore.cancel_job` / `koldstore.cancel_table_jobs`

SELECT koldstore.cancel_job(job_id => '…'::uuid);
SELECT koldstore.cancel_table_jobs(table_name => 'chat.messages'::regclass);

Cooperative cancel:

pending jobs are marked cancelled immediately when unlocked
running jobs are signalled via koldstore.table_cancel_requests (avoids blocking on the flush statement's jobs-row lock) and stop at the next wave boundary (before activate when possible)

If cancel is observed after cold publish already committed, the job finishes as completed with payload.cancel_requested_after_publish = true (data is not unpublished).

DROP TABLE on a managed relation cancels active jobs, waits for any in-flight flush/migrate advisory lock to release, deactivates catalog metadata, deletes cold objects under the table prefix, drops the change-log mirror, and records a completed drop_table_cleanup job before PostgreSQL removes the heap.

`koldstore.table_status`

SELECT jsonb_pretty(koldstore.table_status(
  table_name => 'chat.messages'
));

Preferred single operator API. Returns table hot / mirror / cold / jobs fields plus async_mirror (same object as async_mirror_status()), so one call shows table state and WAL tip vs applied LSN / slot health.

async_mirror is database-scoped (one slot per DB), not per-table — it is embedded here for convenience when you already know the table.

Returns: jsonb — managed-table storage, mirror, cold-segment, size, manifest, recent jobs, and async_mirror. Counters are table-wide across scopes. Errors if the table is not actively managed.

Sample result after a small flush:

{
  "jobs": [
    {
      "id": "e30eb374-a9db-4ff1-97d3-72f8511dfc60",
      "phase": "finished",
      "status": "completed",
      "job_type": "flush",
      "updated_at": "2026-07-07T16:56:10.123456+03:00",
      "duration_ms": 842,
      "rows_flushed": 12,
      "checkpoint_seq": 332882280212668416,
      "rows_processed": 12,
    },
    {
      "id": "2c2bcf44-d6ea-4b3e-b62c-cfaf18ad5225",
      "phase": "finished",
      "status": "completed",
      "job_type": "migrate_backfill",
      "updated_at": "2026-07-07T16:56:09.987654+03:00",
      "duration_ms": 1205,
      "rows_flushed": 0,
      "checkpoint_seq": 0,
      "rows_processed": 1012,
    }
  ],
  "hot_rows": 1000,
  "mirror_rows": 1000,
  "cold_row_count": 12,
  "cold_segment_count": 1,
  "heap_size_bytes": 442368,
  "table_size_bytes": 606208,
  "index_size_bytes": 16384,
  "manifest_state": "in_sync",
  "manifest_max_seq": 332882280212668416,
  "pending_jobs": 0,
  "storage_binding": "4a3b2ab3-5ea8-4761-9e37-1a2f98b128e4",
  "last_error": null
}

Top-level fields:

Field	Type	Meaning
`hot_rows`	`bigint`	Rows still present in the PostgreSQL heap
`mirror_rows`	`bigint`	Primary keys tracked in the `__cl` mirror
`cold_row_count`	`bigint`	Rows already copied to active cold segments
`cold_segment_count`	`bigint`	Active Parquet segment count
`heap_size_bytes`	`bigint`	`pg_relation_size(table)` — main heap fork only
`table_size_bytes`	`bigint`	`pg_table_size(table)` — heap + TOAST + FSM/VM, excluding indexes
`index_size_bytes`	`bigint`	`pg_indexes_size(table)` — all indexes on the table
`manifest_state`	`text`	Catalog/manifest sync state; `in_sync` means they agree
`manifest_max_seq`	`bigint`	Highest mirror `seq` represented in cold data
`pending_jobs`	`bigint`	Jobs for this table with status `pending` or `running`
`jobs`	`jsonb`	Up to 20 recent jobs, newest first (see below)
`storage_binding`	`text`	Bound storage backend id as text
`last_error`	`text`	Last manifest or storage error, or `null`
`async_mirror`	`jsonb`	Same payload as `async_mirror_status()`

Each element of jobs:

Field	Type	Meaning
`id`	`text`	Job uuid
`job_type`	`text`	e.g. `flush`, `migrate_backfill`
`status`	`text`	Job status (`pending`, `running`, `completed`, …)
`phase`	`text`	Current or final phase
`rows_processed`	`bigint`	Rows processed by the job
`rows_flushed`	`bigint`	Rows written cold by the job
`checkpoint_seq`	`bigint`	Mirror `seq` checkpoint
`checkpoint_seq`	`bigint`	Seq checkpoint
`duration_ms`	`bigint`	Wall time from job start (flush run start when available) to last update; live for in-progress jobs
`updated_at`	`timestamptz`	Last job update time

Size notes:

heap_size_bytes + index_size_bytes is not the same as pg_total_relation_size(table) (that also includes TOAST).
table_size_bytes excludes indexes; use table_size_bytes + index_size_bytes for a closer total, or call pg_total_relation_size directly when you need PostgreSQL’s total.
Percent-saved figures require a caller-held baseline; table_status does not store pre-flush sizes.

For job-level progress, inspect koldstore.jobs:

SELECT
  job_type,
  status,
  phase,
  rows_processed,
  rows_flushed,
  (payload->>'duration_ms')::bigint AS duration_ms,
  error_trace
FROM koldstore.jobs
WHERE table_oid = 'chat.messages'::regclass
ORDER BY created_at DESC
LIMIT 5;

`koldstore.recover_segments`

SELECT koldstore.recover_segments(
  table_name => 'chat.messages'
);

SELECT koldstore.recover_segments(
  table_name => 'chat.messages',
  dry_run    => true
);

Discovers orphan cold objects under the table prefix that are not referenced by the current object-store manifest or by active koldstore.cold_segments rows, plans recovery actions for them, and applies the plan unless dry_run => true. dry_run defaults to false. Catalog-referenced segments are preserved so crash-before-manifest-publish recovery does not delete Parquet that merge scan still needs.

Returns: bigint — number of recovery actions planned (orphan objects found). With dry_run => true, the count is still returned and no objects are changed.

DML Boundaries

Normal hot INSERT, UPDATE, and DELETE operate on the heap and mark local manifest state pending.
Standard SQL cold-only UPDATE affects zero rows in the MVP.

The following explicit cold DML SQL functions are planned but not yet exposed by the extension (tracked: #55):

koldstore.hydrate_pk(...)
koldstore.update_row(...)
koldstore.delete_row(...)

Changes and Operations

`koldstore.changes_since`

-- Resume from an exclusive last-seen cursor (KalamDB `from` / `from_seq_id`).
SELECT seq, op, pk, deleted, row_image, source
FROM koldstore.changes_since(
  table_name => 'messages'::regclass,
  since_seq  => 0,
  limit_rows => 1000
);

-- Newest-N rewind (KalamDB `last_rows`); delivered oldest→newest.
SELECT seq, op, pk, deleted, source
FROM koldstore.changes_since(
  table_name => 'messages'::regclass,
  since_seq  => 0,
  limit_rows => 1000,
  last_rows  => 100
);

Returns changes ordered by exclusive seq, catalog-routing to the oldest applicable cold Parquet segment (streamed until limit_rows) or the hot __cl mirror. The same primary key may appear again on a later page with a higher seq (no in-page latest-state collapse). Modes match KalamDB live subscribe options:

Mode	Args	Behavior
Resume	`since_seq > 0`	exclusive `seq > since_seq`, ASC, capped by `limit_rows`; `last_rows` ignored
Rewind	`since_seq = 0` + `last_rows`	newest N retained changes, ASC after rewind; no older pages
From start	`since_seq = 0`, no `last_rows`	from start of retained history, ASC, `limit_rows`

last_rows must be <= limit_rows. A positive since_seq older than the retained floor raises a retention-gap error. source is hot or cold.

The following operator SQL functions are planned but not yet exposed by the extension (tracked: #56):

koldstore.backup_manifest(...)
koldstore.validate_cold_storage(...)
koldstore_exec('EXPORT TABLE ...') — IMPORT TABLE remains rejected until ownership and conflict rules are complete

Security

User-scoped tables require koldstore.user_id and fail closed when it is missing. RLS/security qualifiers must be enforceable on cold rows or planning must fail closed.

Upgrade note

Existing flat hot_row_limit catalog JSON remains readable; new policy writes use tagged flush_policy JSON and require no eager rewrite. The ALTER TABLE hook becomes available after installing the new shared library and restarting PostgreSQL with KoldStore in shared_preload_libraries.

Navigation Menu

Uh oh!

SQL API

Call style

Session

Configuration

Public GUCs

Internal GUCs

Exposed functions

Storage and Migration

`koldstore.register_storage`

`koldstore.alter_storage_credentials`

`koldstore.alter_storage_location`

Default table management with `ALTER TABLE`

Advanced and compatibility management: `koldstore.manage_table`

Mirror capture

`koldstore.set_table_auto_flush`

`koldstore.unmanage_table`

Async Mirror Operations

`koldstore.wait_for_async_mirror`

`koldstore.async_mirror_status`

`koldstore.disable_async_mirror`

Flush and Cold Data

`koldstore.enqueue_flush_job`

`koldstore.flush_table`

`koldstore.list_jobs`

`koldstore.cancel_job` / `koldstore.cancel_table_jobs`

`koldstore.table_status`

`koldstore.recover_segments`

DML Boundaries

Changes and Operations

`koldstore.changes_since`

Security

Upgrade note

Uh oh!

FilesExpand file tree

sql-api.md

Latest commit

History

sql-api.md

File metadata and controls

SQL API

Call style

Session

Configuration

Public GUCs

Internal GUCs

Exposed functions

Storage and Migration

koldstore.register_storage

koldstore.alter_storage_credentials

koldstore.alter_storage_location

Default table management with ALTER TABLE

Advanced and compatibility management: koldstore.manage_table

Mirror capture

koldstore.set_table_auto_flush

koldstore.unmanage_table

Async Mirror Operations

koldstore.wait_for_async_mirror

koldstore.async_mirror_status

koldstore.disable_async_mirror

Flush and Cold Data

koldstore.enqueue_flush_job

koldstore.flush_table

koldstore.list_jobs

koldstore.cancel_job / koldstore.cancel_table_jobs

koldstore.table_status

koldstore.recover_segments

DML Boundaries

Changes and Operations

koldstore.changes_since

Security

Upgrade note

`koldstore.register_storage`

`koldstore.alter_storage_credentials`

`koldstore.alter_storage_location`

Default table management with `ALTER TABLE`

Advanced and compatibility management: `koldstore.manage_table`

`koldstore.set_table_auto_flush`

`koldstore.unmanage_table`

`koldstore.wait_for_async_mirror`

`koldstore.async_mirror_status`

`koldstore.disable_async_mirror`

`koldstore.enqueue_flush_job`

`koldstore.flush_table`

`koldstore.list_jobs`

`koldstore.cancel_job` / `koldstore.cancel_table_jobs`

`koldstore.table_status`

`koldstore.recover_segments`

`koldstore.changes_since`