PostgreSQL Extension Getting Started

This page covers both the release-download path and the current usage path after the pg_kalam binaries are installed.

If you still need to build or package the extension itself, use the repository guide: pg/README.md .

If you want PostgreSQL with the extension already installed, use the published Docker image: jamals86/pg-kalam .

Download a Release Package

KalamDB publishes a prebuilt PostgreSQL extension package in each GitHub release:

• Releases page: github.com/kalamstack/KalamDB/releases 
• Current asset pattern: pg_kalam-0.4.2-rc.3-pg16-linux-x86_64.tar.gz
• Check the matching SHA256SUMS asset before installing in production

The install example below automatically uses the latest published GitHub release tag.

Important constraints:

• The published release package currently targets Linux x86_64.
• The asset name includes the PostgreSQL feature it was built for, such as pg16.
• The binary must match the PostgreSQL major version of the server where you install it.
• If you need another platform or PostgreSQL major, build from source with pgrx.

Example install from a release asset:

VERSION="0.4.2-rc.3"
ASSET="pg_kalam-${VERSION}-pg16-linux-x86_64.tar.gz"

curl -LO "https://github.com/kalamstack/KalamDB/releases/download/v0.4.2-rc.3/${ASSET}"
curl -LO "https://github.com/kalamstack/KalamDB/releases/download/v0.4.2-rc.3/SHA256SUMS"

grep "${ASSET}" SHA256SUMS | sha256sum -c -
tar -xzf "${ASSET}"
./pg_kalam-${VERSION}-pg16-linux-x86_64/install.sh "$(command -v pg_config)"

Run with Docker

For local or CI usage, you can run PostgreSQL with pg_kalam preinstalled directly from Docker Hub:

docker run --name pg-kalam \
  -e POSTGRES_USER=kalamdb \
  -e POSTGRES_PASSWORD=kalamdb123 \
  -e POSTGRES_DB=kalamdb \
  -p 5433:5432 \
  -d jamals86/pg-kalam:latest-pg16

Then connect with psql and install the extension in the database if your init SQL has not already done it:

CREATE EXTENSION IF NOT EXISTS pg_kalam;

Prerequisites

• PostgreSQL with pg_kalam installed into that exact PostgreSQL instance.
• A running KalamDB server reachable from PostgreSQL.
• A stable KalamDB RPC endpoint host and port. The default repository examples use 9188.
• An auth header if your KalamDB server requires one.

1. Preload and Create the Extension

pg_kalam installs SQL objects with CREATE EXTENSION, but the DDL mirroring hook is registered from _PG_init(). For the shorthand CREATE TABLE ... USING kalamdb path, add the extension to PostgreSQL’s preload list and restart PostgreSQL first:

shared_preload_libraries = 'pg_kalam'

Then connect as a superuser and install the extension into the target database:

CREATE EXTENSION IF NOT EXISTS pg_kalam;

Verify that PostgreSQL sees the extension functions:

SELECT kalam_version(), kalam_compiled_mode();

kalam_compiled_mode() should currently return remote.

2. Give KalamDB a Stable RPC Address

Point PostgreSQL at KalamDB’s shared gRPC/RPC listener, not at the HTTP API port.

For local source runs, configure a fixed RPC address instead of relying on a random ephemeral port:

[cluster]
rpc_addr = "127.0.0.1:9188"
api_addr = "127.0.0.1:8080"

If PostgreSQL runs in Docker on macOS while KalamDB runs on the host machine, use host.docker.internal instead of 127.0.0.1 in the foreign-server definition.

3. Register the Default KalamDB Foreign Server

Create the default foreign server that the shorthand authoring path expects:

CREATE SERVER IF NOT EXISTS kalam_server
  FOREIGN DATA WRAPPER pg_kalam
  OPTIONS (
    host '127.0.0.1',
    port '9188',
    auth_header 'Bearer <your-kalamdb-token>'
  );

Supported server options today:

• host and port are required.
• auth_header, timeout, ca_cert, client_cert, and client_key are optional.
• If you set client_cert or client_key, you must set both.

4. Choose the Right Authoring Path

There are two current SQL entry points:

The shorthand USING kalamdb path is convenient, but it is hard-wired to the default server name kalam_server.

5. Create Your First Shared Table

The primary authoring syntax is CREATE TABLE ... USING kalamdb:

CREATE SCHEMA IF NOT EXISTS app;
 
CREATE TABLE app.shared_items (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  title TEXT NOT NULL,
  value INTEGER,
  created_at TIMESTAMP DEFAULT NOW()
) USING kalamdb WITH (
  type = 'shared',
  flush_policy = 'rows:1000,interval:60'
);

That statement is intercepted by the DDL hook, creates the remote namespace and table in KalamDB, then creates a local foreign table and injects the PostgreSQL-visible system columns that the extension manages locally.

Any KalamDB table shape that the server accepts can be authored this way from PostgreSQL. The same option names you would use in KalamDB are forwarded through the PostgreSQL WITH (...) clause.

Important rules for new tables:

• Do not declare _seq, _userid, or _deleted in the CREATE TABLE ... USING kalamdb column list. pg_kalam rejects that DDL because those columns are managed by the extension.
• _seq BIGINT is added to every PostgreSQL table surface that pg_kalam creates.
• _userid TEXT is added only to user tables.
• _deleted is reserved, but it is not auto-injected into the current PostgreSQL table surface.
• Keep the primary key as the first user column. The current UPDATE and DELETE FDW path uses the first non-system column as its row-identity heuristic.
• The local object is a foreign table after interception, so use ALTER FOREIGN TABLE and DROP FOREIGN TABLE for follow-up DDL.

6. Query and Mutate Through PostgreSQL

INSERT INTO app.shared_items (id, title, value)
VALUES (SNOWFLAKE_ID(), 'Alpha', 10), (SNOWFLAKE_ID(), 'Beta', 20);
 
SELECT id, title, value
FROM app.shared_items
ORDER BY id;
 
UPDATE app.shared_items
SET value = 25
WHERE id = 2;
 
DELETE FROM app.shared_items
WHERE id = 1;

Autocommit statements flush at statement end. Top-level explicit transactions are covered later in this section.

7. Use Session Identity for User Tables

User tables are tenant-scoped. Set kalam.user_id in each PostgreSQL session before querying or mutating them:

SET kalam.user_id = 'user-alice';
 
CREATE TABLE app.profiles (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  name TEXT,
  age INTEGER
) USING kalamdb WITH (
  type = 'user'
);
 
INSERT INTO app.profiles (id, name, age)
VALUES (SNOWFLAKE_ID(), 'Alice', 30);
 
INSERT INTO app.profiles (id, name, age, _userid)
VALUES (SNOWFLAKE_ID(), 'Bob', 41, 'user-bob');
 
SELECT id, name, age, _userid, _seq
FROM app.profiles
ORDER BY _seq;

The tenant scope comes from the PostgreSQL session value unless you explicitly provide _userid in an INSERT.

kalam.user_id is registered as a PostgreSQL SUSET parameter. In practice, the role running these statements must be allowed by PostgreSQL to change SUSET settings.

Current _userid rules:

• On user tables, omitting _userid uses the current kalam.user_id session value.
• On user tables, an explicit _userid in INSERT overrides the session value for that inserted row batch.
• In a multi-row INSERT, every row must use the same _userid value.
• UPDATE and DELETE still use the session’s kalam.user_id scope. Changing row ownership with UPDATE ... SET _userid = ... is not part of the supported surface.

8. Create a Stream Table

Stream tables use the same authoring path, but they must supply ttl_seconds:

CREATE TABLE app.events (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  event_type TEXT NOT NULL,
  payload JSONB,
  created_at TIMESTAMP DEFAULT NOW()
) USING kalamdb WITH (
  type = 'stream',
  ttl_seconds = '3600'
);

Normal FDW access uses the stream table type, but explicit PostgreSQL transactions reject stream-table writes.

9. Reuse KalamDB Table Options with WITH (...)

The WITH (...) clause is the main place to express KalamDB table options from PostgreSQL:

CREATE TABLE app.orders (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  customer_id TEXT NOT NULL,
  status TEXT DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW()
) USING kalamdb WITH (
  type = 'shared',
  flush_policy = 'rows:1000,interval:60'
);

Use the same option names and values that KalamDB expects. The extension forwards them to the server when it constructs the real KalamDB CREATE TABLE statement.

10. Work with FILE Columns

FILE is the PostgreSQL extension’s main KalamDB-specific type shortcut.

CREATE TABLE app.assets (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  attachment FILE
) USING kalamdb WITH (
  type = 'shared'
);
 
SELECT attachment->>'name', attachment->>'mime'
FROM app.assets;

Current behavior:

• The remote KalamDB column stays FILE.
• The local PostgreSQL column is rewritten to JSONB.
• PostgreSQL sees a serialized FileRef payload with fields such as id, sub, name, size, mime, and sha256.
• Binary upload itself usually happens through KalamLink or another KalamDB file-aware client. PostgreSQL sees the resulting FileRef JSON representation.

11. Send Direct SQL to KalamDB

When you want to run a KalamDB statement directly, use kalam_exec(...):

SELECT kalam_exec('ALTER TABLE app.orders ADD COLUMN priority INTEGER DEFAULT 0');
 
SELECT kalam_exec('DROP TABLE app.orders');
 
SELECT kalam_exec('SELECT * FROM system.tables');

kalam_exec(...) forwards the statement to the KalamDB server as-is.

• For SELECT statements, it returns a JSON array string.
• For DDL and DML statements, it returns a JSON string status message.
• It currently uses the default foreign server kalam_server.
• It is revoked from PUBLIC, so use a superuser or grant EXECUTE explicitly.
• It does not currently synchronize PostgreSQL search_path or current schema to the remote session automatically, so schema-qualify direct SQL.

12. Use a Non-Default Server Name

If you need a different foreign server name, create it and use the explicit CREATE FOREIGN TABLE path:

CREATE SERVER kalam_eu
  FOREIGN DATA WRAPPER pg_kalam
  OPTIONS (
    host '10.0.0.25',
    port '9188',
    auth_header 'Bearer <token>'
  );
 
CREATE FOREIGN TABLE app.audit_log (
  id BIGINT,
  action TEXT,
  payload JSONB
) SERVER kalam_eu
OPTIONS (
  table_type 'shared'
);

Current relation-mapping rule: the local PostgreSQL schema and relation name are the remote namespace and table. OPTIONS (namespace, table) are not the runtime source of truth for scans and writes.

If you mirror a remote FILE column manually, declare the local PostgreSQL column as JSONB.

13. Maintain the Server Definition

Update server options with standard PostgreSQL syntax:

ALTER SERVER kalam_server OPTIONS (SET host 'kalamdb.internal', SET port '9188');
ALTER SERVER kalam_server OPTIONS (ADD auth_header 'Bearer <replacement-token>');

Remove the bridge when you are done:

DROP SERVER kalam_server CASCADE;

Next Steps

• Architecture
• SQL Syntax
• Data Type Conversions
• Status & Limits