Tables

A table is Parquet files plus a log of the newest rows, in your lake. You make one with CREATE TABLE, and its options decide what kind of table it is: rows that pile up, rows that replace each other by key, or rows that add up per key.

Create a table

SQL

CREATE TABLE orders (id BIGINT, item VARCHAR, qty INT, amount DOUBLE, ts TIMESTAMP);

Python

import pondra
db = pondra.connect("http://localhost:8080")
db.sql("CREATE TABLE IF NOT EXISTS orders (id BIGINT, item VARCHAR, qty INT, amount DOUBLE, ts TIMESTAMP)")

JavaScript

import { connect } from "pondra";
const db = connect("http://localhost:8080");
await db.sql("CREATE TABLE IF NOT EXISTS orders (id BIGINT, item VARCHAR, qty INT, amount DOUBLE, ts TIMESTAMP)");

Every statement on this page runs the same way from SQL, Python (db.sql(...)), JavaScript (await db.sql(...)), the shell and Postgres clients.

The column types are:

Kind	Types
Whole numbers	TINYINT, SMALLINT, INT, BIGINT
Decimals	FLOAT (or REAL), DOUBLE, DECIMAL(10, 2)
Text	VARCHAR (or TEXT, STRING)
True and false	BOOLEAN
Dates and times	DATE, TIME, TIMESTAMP (a wall-clock time), TIMESTAMPTZ (an instant, shown in UTC)
Bytes	BYTEA (or BINARY, VARBINARY, BLOB)
JSON text	JSON (or VARIANT), read with json_get and ->
Lists	FLOAT[], INT[], VARCHAR[] (a list of one type; FLOAT[] holds embeddings)

CREATE TABLE IF NOT EXISTS does nothing when the table is already there with the same columns. To make a table from a query, use CREATE TABLE … AS SELECT; the columns are the query’s:

INSERT INTO orders VALUES (1, 'tea', 2, 7.0, TIMESTAMP '2026-09-27 10:00:00'), (2, 'cake', 1, 4.5, TIMESTAMP '2026-09-27 11:00:00');
CREATE TABLE tea_orders AS SELECT * FROM orders WHERE item = 'tea';

A value that doesn’t fit its column is refused, and the error names the value: 'abc' into a BIGINT is an error, never a NULL. Types are in SQL statements.

Require a value: NOT NULL and DEFAULT

NOT NULL refuses a row that gives the column no value. DEFAULT fills in a column a row leaves out. Both hold on every door: INSERT, POST /append, COPY, Arrow Flight, Kafka, and INSERT … SELECT.

CREATE TABLE accounts (
  id BIGINT PRIMARY KEY,
  email VARCHAR NOT NULL,
  plan VARCHAR DEFAULT 'free',
  made TIMESTAMP DEFAULT now()
);
INSERT INTO accounts (id, email) VALUES (1, 'ann@example.com'), (2, 'bo@example.com');
SELECT id, email, plan FROM accounts ORDER BY id;

+----+-----------------+------+
| id | email           | plan |
+----+-----------------+------+
| 1  | ann@example.com | free |
| 2  | bo@example.com  | free |
+----+-----------------+------+

A row that breaks a rule is refused, and the error names the column:

try:
    db.sql("INSERT INTO accounts (id) VALUES (3)")
except RuntimeError as e:
    print(e)

500: accounts.email is NOT NULL, and a row gives it no value

• Each row gets its own default, so DEFAULT now() is the time of that row.
• A key’s columns are NOT NULL too. A table made by an older release keeps its old rules.
• A JSON row sent to /append or Kafka without the column takes the default. A row with an explicit null keeps the null (and is refused if the column is NOT NULL).
• A DEFAULT that can’t be cast to the column’s type is refused when you create the table.

Choose what kind of table it is

Without options, a table is an append table: every row you write stays, and a query sees them all. Three options change that. An option name Pondra doesn’t know is refused, and the error names it, so a misspelt cluser_by never quietly does nothing.

A keyed table: PRIMARY KEY

With a key, INSERT is an upsert: a new row replaces the row with the same key. UPDATE and DELETE find rows by key.

CREATE TABLE users (id BIGINT PRIMARY KEY, name VARCHAR, plan VARCHAR);
INSERT INTO users VALUES (1, 'ann', 'free'), (2, 'bob', 'free');
INSERT INTO users VALUES (2, 'bob', 'pro');
DELETE FROM users WHERE id = 1;
SELECT * FROM users;

+----+------+------+
| id | name | plan |
+----+------+------+
| 2  | bob  | pro  |
+----+------+------+

Keyed tables answer a lookup of one key without planning a query: db.lookup("users", 2) in Python and JavaScript, GET /lookup/users/2 over HTTP. WITH (key = 'id') says the same as PRIMARY KEY.

A merge table: merge

A merge table has a key and combines the rows that share it, with MERGE sum, count, min or max on each column. Every column except the key needs one: CREATE refuses a table that leaves one out. You only INSERT into it.

CREATE TABLE totals (user VARCHAR PRIMARY KEY, n BIGINT MERGE sum, total DOUBLE MERGE sum);
INSERT INTO totals VALUES ('ann', 1, 5.0), ('bob', 1, 2.0);
INSERT INTO totals VALUES ('ann', 1, 7.0);
SELECT * FROM totals ORDER BY user;

+------+---+-------+
| user | n | total |
+------+---+-------+
| ann  | 2 | 12.0  |
| bob  | 1 | 2.0   |
+------+---+-------+

Latest by event time: SEQUENCE BY

On a keyed table, SEQUENCE BY makes the row with the latest event time current, not the last one to arrive. A late row doesn’t replace a newer one.

CREATE TABLE positions (vehicle VARCHAR PRIMARY KEY, ts TIMESTAMP, lat DOUBLE, lon DOUBLE) SEQUENCE BY ts;
INSERT INTO positions VALUES ('v1', TIMESTAMP '2026-09-27 10:05:00', 41.30, 69.28);
INSERT INTO positions VALUES ('v1', TIMESTAMP '2026-09-27 10:01:00', 41.20, 69.20);
SELECT * FROM positions;

+---------+---------------------+------+-------+
| vehicle | ts                  | lat  | lon   |
+---------+---------------------+------+-------+
| v1      | 2026-09-27T10:05:00 | 41.3 | 69.28 |
+---------+---------------------+------+-------+

Expire old rows: TTL

On a keyed table, TTL column + INTERVAL '…' hides the rows whose timestamp column is older than that, and a full compaction of the table deletes them.

CREATE TABLE sessions (sid VARCHAR PRIMARY KEY, last_seen TIMESTAMP, page VARCHAR) TTL last_seen + INTERVAL '1 day';

Lay out the files

Two clauses decide how a table’s files are written, and they combine with a key.

• PARTITION BY keeps one partition in each file: a column (region), or the year, month, day or hour of a timestamp (day(ts)). A filter on it skips every other partition’s files.
• CLUSTER BY sorts each file by one or more columns, so a filter on them skips most of a file. With two or more columns the rows follow a Hilbert curve, so a filter on any one of them still skips most of a file.

CREATE TABLE events (id BIGINT, user VARCHAR, amount DOUBLE, ts TIMESTAMP)
  PARTITION BY day(ts) CLUSTER BY (user);

PARTITION BY is fixed once the table is made. CLUSTER BY can change later (see ALTER TABLE below).

Publish a table for other engines

publish also keeps a table as a Delta Lake and/or an Iceberg table, so Spark, DuckDB, Polars and others read it without going through Pondra:

CREATE TABLE shipments (id BIGINT, city VARCHAR) WITH (publish = 'delta,iceberg');
INSERT INTO shipments VALUES (1, 'Tashkent');
CHECKPOINT;

ls lake/data/shipments | grep -v parquet

_delta_log
metadata

The table’s folder, lake/data/shipments, is the Delta table (its _delta_log) and the Iceberg table (its metadata), beside the Parquet files they both point at. CHECKPOINT tiers every table’s newest rows into Parquet now; otherwise it happens within --tier-secs (10 seconds by default). Other engines shows how to read them.

Group tables in schemas

A table belongs to a schema, public unless you name one. Its full name is lake.schema.table, where the lake’s name is its folder’s (./lake is lake).

CREATE SCHEMA sales;
CREATE TABLE sales.orders (id BIGINT, region VARCHAR, amount DOUBLE);
INSERT INTO sales.orders VALUES (1, 'eu', 5.0), (2, 'us', 7.5);
SELECT * FROM lake.sales.orders WHERE region = 'eu';

orders alone means public.orders. Names may hold letters, digits, _ and -. Postgres clients, Flight SQL, the Iceberg catalog and MCP all list the schemas.

Use other lakes as databases

A lake is a database, and other lakes can be databases next to it. On a plain node, CREATE DATABASE makes a new lake in a folder beside this one and attaches it:

CREATE DATABASE archive;
CREATE TABLE archive.public.old_orders AS SELECT * FROM sales.orders;
SELECT o.id, o.amount FROM archive.old_orders o JOIN sales.orders s USING (id) ORDER BY o.id;

To attach a lake that already exists, give its folder or bucket URL. DETACH lets go of it again:

echo "CREATE TABLE customers (id BIGINT, name VARCHAR); INSERT INTO customers VALUES (1, 'ann')" | pondra crm

ATTACH 'crm' AS crm;
INSERT INTO crm.customers VALUES (2, 'bob');
SELECT * FROM crm.customers ORDER BY id;
DETACH crm;

On a node that serves a folder of lakes, CREATE DATABASE makes a new served database instead, and DROP DATABASE drops one, its folder and all.

An attachment is kept in the lake, so every node of a cluster sees it and a restarted node attaches it again. Reads join across lakes as one query. INSERT, UPDATE, DELETE and MERGE on an attached lake go to that lake’s own leader. A statement that writes to two lakes is two commits, not one transaction. A lake in a bucket is attached the same way: ATTACH 's3://my-bucket/sales' AS sales.

Change a table’s columns

ALTER TABLE changes columns while rows keep arriving. No file is rewritten: the lake remembers each column’s stored name, old rows read a new column as NULL, and Delta and Iceberg readers follow.

CREATE TABLE readings (sensor VARCHAR, value INT, note VARCHAR);
INSERT INTO readings VALUES ('s1', 20, 'first'), ('s2', 21, 'second');
ALTER TABLE readings ADD COLUMN unit VARCHAR;
ALTER TABLE readings RENAME COLUMN value TO celsius;
ALTER TABLE readings ALTER COLUMN celsius TYPE BIGINT;
ALTER TABLE readings DROP COLUMN note;
INSERT INTO readings VALUES ('s3', 5000000000, 'C');
SELECT * FROM readings ORDER BY sensor;

+--------+------------+------+
| sensor | celsius    | unit |
+--------+------------+------+
| s1     | 20         |      |
| s2     | 21         |      |
| s3     | 5000000000 | C    |
+--------+------------+------+

CLUSTER BY changes how files are sorted, SET changes a table’s options (publish, retention), and on keyed tables TTL and SEQUENCE BY change too. Files written from then on follow them.

ALTER TABLE events CLUSTER BY (user, amount);
ALTER TABLE events SET (publish = delta);

Some changes aren’t possible, and the error says what to do instead:

• A column’s type can only widen: a smaller integer to a bigger one, FLOAT to DOUBLE, a DECIMAL to more digits.
• partition_by can’t change, because each file holds one partition.
• ADD COLUMN … NOT NULL and ADD COLUMN … DEFAULT are refused: the rows already there would need a value, and no file is rewritten. Add the column, then UPDATE it.

Rename a table

ALTER TABLE … RENAME TO changes a table’s name in place. No file is rewritten, and rows still in the log stay the table’s.

CREATE TABLE trips (id BIGINT, city VARCHAR);
INSERT INTO trips VALUES (1, 'Tashkent'), (2, 'Samarkand');
ALTER TABLE trips RENAME TO journeys;
SELECT count(*) AS n FROM journeys;

+---+
| n |
+---+
| 2 |
+---+

• A table published as Delta or Iceberg stays readable by other engines, under its new name in the Iceberg catalog.
• A stored view reads a table by name, so after RENAME TO it reads whatever takes the old name next. That is how to swap a table in: rename the old one away, then create the new one with the old name.
• The rename is refused, with the name, when the new name is taken and when a materialized view or a task follows the table. Drop those first.

ALTER VIEW old_name RENAME TO new_name renames a stored view. A materialized view isn’t renamed: drop it and create it again under the new name.

Views

CREATE VIEW stores a query. It runs over the tables as they are when the view is read, and it spreads over a cluster like any query. A view can read other views.

CREATE VIEW big_orders AS SELECT * FROM orders WHERE amount > 5;
SELECT * FROM big_orders;

CREATE OR REPLACE VIEW changes a view, and ALTER VIEW … RENAME TO renames it. A materialized view, which is kept up to date as rows arrive, is in Streaming.

Drop things

TRUNCATE journeys;
DROP VIEW big_orders;
DROP TABLE tea_orders;
DROP TABLE IF EXISTS sessions;
DROP SCHEMA sales CASCADE;

TRUNCATE empties a table and keeps it. DROP TABLE removes it.

• DROP TABLE is refused while a view or task reads the table: drop those first.
• DROP SCHEMA is refused for a schema that holds anything, unless you add CASCADE, which drops its views and then its tables. public can’t be dropped.
• A dropped table is kept for a day, or for its retention (WITH (retention = '7 days')), and UNDROP TABLE brings it back as it was. pondra.dropped lists the ones that can still come back. After that, its files are deleted. DROP TABLE … PURGE keeps nothing.
• A new table with the same name starts empty. UNDROP brings back the table dropped last under a name, once nothing else has it: rename the new one first.

CREATE TABLE notes (id BIGINT, body VARCHAR);
INSERT INTO notes VALUES (1, 'keep me');
DROP TABLE notes;
SELECT name FROM pondra.dropped WHERE name = 'notes';
UNDROP TABLE notes;
SELECT body FROM notes;

List what you have with the shell’s .tables, or with SQL: pondra.tables gives each one’s kind (table, view, materialized view or external table), and information_schema.tables is there for the tools that read it (to it, a materialized view is a BASE TABLE).

SELECT schema, name, kind FROM pondra.tables WHERE lake = 'lake' ORDER BY 1, 2;
SELECT table_name FROM information_schema.tables WHERE table_catalog = 'lake' AND table_schema = 'public' ORDER BY 1;