Change rows

Every table takes UPDATE, DELETE and MERGE, not only keyed ones. Nothing is rewritten when a row changes: the new version is written like any new row, and the old one is hidden from every read. Materialized views, live queries and the change feed follow along.

CREATE TABLE orders (id BIGINT, item VARCHAR, qty INT, status VARCHAR);
INSERT INTO orders VALUES (1, 'tea', 2, 'new'), (2, 'cake', 1, 'new'), (3, 'tea', 1, 'new'), (4, 'pie', 3, 'new');

Update and delete rows

UPDATE orders SET status = 'shipped', qty = qty + 1 WHERE item = 'tea';
DELETE FROM orders WHERE id = 4;
SELECT * FROM orders ORDER BY id;

+----+------+-----+---------+
| id | item | qty | status  |
+----+------+-----+---------+
| 1  | tea  | 3   | shipped |
| 2  | cake | 1   | new     |
| 3  | tea  | 2   | shipped |
+----+------+-----+---------+

UPDATE and DELETE answer with what they did. In Python that is a dict:

import pondra
db = pondra.connect("http://localhost:8080")
print(db.sql("UPDATE orders SET status = 'packed' WHERE id = 2"))

{'rows': 1, 'updated': 1, 'deleted': 0, 'inserted': 0}

A change is one commit, made by the lake’s leader from one snapshot. It applies all or not at all, and two changes to the same rows from different nodes at once don’t lose either. You can send the statement to any node.

Update or delete with another table

UPDATE … FROM and DELETE … USING join the table to another one, as in Postgres:

CREATE TABLE parcels (id BIGINT, status VARCHAR);
INSERT INTO parcels VALUES (1, 'sent'), (2, 'sent'), (3, 'sent');
CREATE TABLE scans (id BIGINT, event VARCHAR);
INSERT INTO scans VALUES (1, 'delivered'), (3, 'lost'), (3, 'lost');

UPDATE parcels SET status = s.event FROM scans s WHERE parcels.id = s.id AND s.event = 'delivered';
DELETE FROM parcels USING scans s WHERE parcels.id = s.id AND s.event = 'lost';
SELECT * FROM parcels ORDER BY id;

+----+-----------+
| id | status    |
+----+-----------+
| 1  | delivered |
| 2  | sent      |
+----+-----------+

Parcel 3 matches two scans and is deleted once.

Insert or update on a key

On a keyed table, INSERT … ON CONFLICT (key) decides what a row does when its key is already there: DO NOTHING skips it, and DO UPDATE SET changes the row that is there. In SET, the table’s name is the row that is there and excluded is the row you sent.

CREATE TABLE visits (page VARCHAR PRIMARY KEY, n BIGINT);
INSERT INTO visits VALUES ('home', 1), ('docs', 1);
INSERT INTO visits VALUES ('home', 1), ('blog', 1) ON CONFLICT (page) DO UPDATE SET n = visits.n + excluded.n;
INSERT INTO visits VALUES ('docs', 99) ON CONFLICT (page) DO NOTHING;
SELECT * FROM visits ORDER BY page;

+------+---+
| page | n |
+------+---+
| blog | 1 |
| docs | 1 |
| home | 2 |
+------+---+

Without ON CONFLICT, an INSERT into a keyed table replaces the row. MERGE does the same jobs and more. MySQL’s ON DUPLICATE KEY UPDATE is refused, with the Postgres form to use instead.

Empty a table

TRUNCATE t deletes every row and keeps the table, its columns and its options. To remove the table too, use DROP TABLE.

CREATE TABLE scratch (a BIGINT);
INSERT INTO scratch VALUES (1), (2);
TRUNCATE scratch;
SELECT count(*) AS n FROM scratch;

+---+
| n |
+---+
| 0 |
+---+

Merge rows from another table

MERGE lines a source up against a table and decides for each row: change it, delete it, or add it.

CREATE TABLE stock (sku VARCHAR, qty INT, note VARCHAR);
INSERT INTO stock VALUES ('tea', 10, ''), ('cake', 5, ''), ('pie', 2, ''), ('jam', 8, '');
CREATE TABLE deliveries (sku VARCHAR, qty INT);
INSERT INTO deliveries VALUES ('tea', 5), ('cake', -5), ('pie', 20), ('milk', 12);

MERGE INTO stock t USING deliveries s ON t.sku = s.sku
  WHEN MATCHED AND t.qty + s.qty <= 0 THEN DELETE
  WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
  WHEN NOT MATCHED THEN INSERT (sku, qty, note) VALUES (s.sku, s.qty, 'new')
  WHEN NOT MATCHED BY SOURCE AND t.qty < 10 THEN UPDATE SET note = 'no delivery';

MERGE answers with how many rows it updated, deleted and inserted:

{"rows":5,"updated":3,"deleted":1,"inserted":1}

tea and pie got their delivery, cake came to 0 and is gone, milk is new, and jam had no delivery and was low.

SELECT * FROM stock ORDER BY sku;

+------+-----+-------------+
| sku  | qty | note        |
+------+-----+-------------+
| jam  | 8   | no delivery |
| milk | 12  | new         |
| pie  | 22  |             |
| tea  | 15  |             |
+------+-----+-------------+

The three kinds of WHEN:

Clause	Which rows	Can do
WHEN MATCHED	target rows with a source row	UPDATE SET …, DELETE
WHEN NOT MATCHED	source rows with no target row	INSERT
WHEN NOT MATCHED BY SOURCE	target rows with no source row	UPDATE SET …, DELETE

Add AND condition to a clause to narrow it. A row takes the first clause of its kind whose condition holds. INSERT without a column list takes the table’s columns in order.

The source can be a table, a view, a subquery or a VALUES list:

MERGE INTO stock t USING (VALUES ('tea', 1), ('fig', 4)) AS s(sku, qty) ON t.sku = s.sku
  WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
  WHEN NOT MATCHED THEN INSERT VALUES (s.sku, s.qty, 'from a list');

In the shell it can be a file on your machine, which is how you apply a CSV of changes:

printf 'sku,qty\ntea,1\nplum,3\n' > changes.csv
pondra lake <<'SQL'
MERGE INTO stock t USING 'changes.csv' s ON t.sku = s.sku
  WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
  WHEN NOT MATCHED THEN INSERT VALUES (s.sku, s.qty, 'from a file')
SQL

{"rows":2,"updated":1,"deleted":0,"inserted":1}

A target row that matches two source rows is an error, not a guess:

try:
    db.sql("""MERGE INTO stock t USING (SELECT 'tea' AS sku, 1 AS qty UNION ALL SELECT 'tea', 2) s ON t.sku = s.sku
              WHEN MATCHED THEN UPDATE SET qty = s.qty""")
except Exception as e:
    print(e)

500: MERGE: a row of stock matches more than one source row

System columns

Every row carries four columns of its own. SELECT * leaves them out, and naming one brings it in.

Column	What it is
_row_id	Which row it is, from its INSERT on. An UPDATE or MERGE keeps it.
_version	The commit that wrote this version of the row.
_created_at	When the row’s first version was committed. Kept through changes.
_updated_at	When this version was committed.

before = db.sql("SELECT _row_id, _version FROM stock WHERE sku = 'milk'").rows()[0]
db.sql("UPDATE stock SET qty = qty + 1 WHERE sku = 'milk'")
after = db.sql("SELECT _row_id, _version, _created_at, _updated_at FROM stock WHERE sku = 'milk'").rows()[0]
print(after["_row_id"] == before["_row_id"], after["_version"] > before["_version"], after["_updated_at"] > after["_created_at"])

True True True

Use _row_id to name a row when a table has no key of its own. Their names are reserved: CREATE TABLE refuses a column called _row_id.

Change rows exactly once

A job id makes a change safe to retry. The node remembers it, and a second statement with the same id does nothing:

Python

bump = "UPDATE stock SET qty = qty + 100 WHERE sku = 'pie'"
print(db.sql(bump, job="restock-2026-09-28"))
print(db.sql(bump, job="restock-2026-09-28"))    # a retry: nothing changes
print(db.sql("SELECT qty FROM stock WHERE sku = 'pie'").rows())

{'rows': 1, 'updated': 1, 'deleted': 0, 'inserted': 0}
{'duplicate': True}
[{'qty': 122}]

Shell

bump="UPDATE stock SET qty = qty + 100 WHERE sku = 'jam'"
for attempt in 1 2; do
  curl -s -w '\n' -X POST "http://localhost:8080/sql?job=restock-jam-2026-09-28" -d "$bump"
done

{"rows":1,"updated":1,"deleted":0,"inserted":0}
{"duplicate":true}

The second answer is duplicate, and the quantity went up by 100 once.

Follow the changes

GET /watch/orders?changes=true streams every change to a table as it commits, as JSON lines. Each row comes with its system columns and a _change_type:

_change_type	When
insert	A row is added.
update_preimage	The row as it was before an UPDATE.
update_postimage	The row as it is after it.
delete	A row is deleted.

A keyed table’s changes are upsert and delete. The clients wrap the route, and after=0 replays what the node still holds first:

Python

import itertools

db.sql("CREATE TABLE notes (id BIGINT, qty INT)")
db.sql("INSERT INTO notes VALUES (1, 1), (2, 2)")
db.sql("UPDATE notes SET qty = 20 WHERE id = 2")
db.sql("DELETE FROM notes WHERE id = 1")

for row in itertools.islice(db.watch("notes", after=0, changes=True), 5):
    print(row["_change_type"], row["id"], row["qty"])

JavaScript

import { connect } from "pondra";
const db = connect("http://localhost:8080");

await db.sql("CREATE TABLE notes_js (id BIGINT, qty INT)");
await db.sql("INSERT INTO notes_js VALUES (1, 1), (2, 2)");
await db.sql("UPDATE notes_js SET qty = 20 WHERE id = 2");
await db.sql("DELETE FROM notes_js WHERE id = 1");

let n = 0;
for await (const row of db.watch("notes_js", { after: 0, changes: true })) {
  console.log(row._change_type, row.id, row.qty);
  if (++n === 5) break;
}

insert 1 1
insert 2 2
update_preimage 2 2
update_postimage 2 20
delete 1 1

db.position is the commit the watcher has reached; pass it as after to carry on where it stopped. How far back after reaches is set by pondra serve --changelog-secs, which keeps the log that many seconds as a replayable feed. Without it, the log is kept for --retain-secs (60 seconds).

Views follow

A materialized view that only filters or projects a table follows UPDATE and DELETE of it: a row that changes is changed in the view, a row that no longer matches leaves it, and one that starts to match joins it. A view that adds up (sum and count, grouped) takes the old rows back and adds the new ones in the same commit as the change. Give it a count(*), so it knows when a group has no rows left and drops it.

CREATE MATERIALIZED VIEW qty_by_item AS SELECT item, sum(qty) AS n, count(*) AS orders FROM orders GROUP BY item;
UPDATE orders SET qty = 5 WHERE id = 1;
DELETE FROM orders WHERE item = 'cake';
SELECT * FROM qty_by_item ORDER BY item;

+------+---+--------+
| item | n | orders |
+------+---+--------+
| tea  | 7 | 2      |
+------+---+--------+

cake has no orders left, so its group is gone. A view of a filter follows in the same way:

CREATE MATERIALIZED VIEW tea_orders AS SELECT id, qty FROM orders WHERE item = 'tea';
UPDATE orders SET qty = 9 WHERE id = 1;

tea_orders now holds order 1 with qty 9. Change an order’s item and it leaves the view or joins it.

Some views can’t take a row back: one that keeps a min or max, one that emits windows or sessions, one that joins another table, and streaming tasks. While one of them reads a table, a change to the table is refused, with the reason. Drop the view, or make it one that adds up.

db.sql("CREATE MATERIALIZED VIEW biggest AS SELECT item, max(qty) AS most FROM orders GROUP BY item")
try:
    db.sql("UPDATE orders SET qty = 1 WHERE id = 3")
except Exception as e:
    print(e)
db.sql("DROP VIEW biggest")

500: orders's rows can't change while these follow it: view biggest (keeps a min or max). Drop them first, or change a copy of the table

Temporary tables

CREATE TEMP TABLE makes a table only your connection sees, held in the node’s memory. It takes INSERT, UPDATE, DELETE and MERGE, and joins with the lake’s tables. It is gone when you close the connection.

db.sql("CREATE TEMP TABLE picks AS SELECT sku, qty FROM stock WHERE qty > 10")
db.sql("UPDATE picks SET qty = 0 WHERE sku = 'tea'")
print(db.sql("SELECT s.sku, s.qty AS in_stock, p.qty AS picked FROM stock s JOIN picks p USING (sku) ORDER BY s.sku").rows())
db.close()

[{'sku': 'jam', 'in_stock': 108, 'picked': 108}, {'sku': 'milk', 'in_stock': 13, 'picked': 13}, {'sku': 'pie', 'in_stock': 122, 'picked': 122}, {'sku': 'tea', 'in_stock': 17, 'picked': 0}]

The change to picks left stock alone: tea is 0 in the copy and 17 in the table. JavaScript’s await db.sql(…) and await db.close() do the same, and so does a psql session until it ends.

Transactions

BEGIN … COMMIT makes several statements one commit: all of them or none, the views that follow the tables moving with it. Inside, every statement reads the lake as it was at BEGIN, with the transaction’s own writes over it, and nobody else sees those writes until COMMIT.

import pondra
db = pondra.connect("http://localhost:8080")
db.sql("CREATE TABLE accounts (id BIGINT, balance BIGINT CHECK (balance >= 0))")
db.sql("INSERT INTO accounts VALUES (1, 100), (2, 50)")
with db.transaction():
    db.sql("UPDATE accounts SET balance = balance - 30 WHERE id = 1")
    db.sql("UPDATE accounts SET balance = balance + 30 WHERE id = 2")
    print(db.sql("SELECT id, balance FROM accounts ORDER BY id").rows())
print(db.sql("SELECT count(DISTINCT _version) AS commits FROM accounts WHERE id IN (1, 2)").rows())

[{'id': 1, 'balance': 70}, {'id': 2, 'balance': 80}]
[{'commits': 1}]

A statement that fails fails its transaction: until it ends, every statement is refused (25P02, as in Postgres), and COMMIT rolls it back. with db.transaction(): rolls back when its block raises:

try:
    with db.transaction():
        db.sql("UPDATE accounts SET balance = balance + 500 WHERE id = 2")
        db.sql("UPDATE accounts SET balance = balance - 500 WHERE id = 1")
except pondra.PondraError as e:
    print(e.sqlstate)
print(db.sql("SELECT id, balance FROM accounts ORDER BY id").rows())

23514
[{'id': 1, 'balance': 70}, {'id': 2, 'balance': 80}]

Over the Postgres port a connection is the session, so drivers’ transactions work as they do with Postgres (psycopg’s with conn.transaction(), JDBC’s setAutoCommit(false)):

BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
INSERT INTO history VALUES (1, -10);
COMMIT;

Over HTTP, send the statements of one transaction with the same x-pondra-session header (the Python and JavaScript clients do), or all of them in one request: BEGIN; …; COMMIT.

Isolation. A transaction is snapshot isolation, Postgres’s REPEATABLE READ: it reads one snapshot, and if another commit changed a row it changes (by row; a keyed table’s, by key) after that snapshot, its COMMIT is refused with 40001 (could not serialize access due to concurrent update), the first to commit winning. Run it again: PondraError.sqlstate == "40001" in Python, psycopg’s SerializationFailure, pgbench’s --max-tries.

Note

A transaction is kept in the memory of the node its session talks to, and sent to the leader as one request at COMMIT. CREATE, ALTER and DROP aren’t part of one (refused inside, 0A000), nor are savepoints. One idle for PONDRA_TXN_IDLE_SECS (600) ends, rolled back, as does one whose connection closes.

What to know

Caution

• Nothing is rewritten when a row changes, so its old version stays in its file until a purge rewrites the file (once 100,000 changed rows wait, or a tenth of the table; every few seconds for a table that publishes Delta or Iceberg). Queries never see old versions. Delta and Iceberg readers see a change after that purge.
• Kafka consumers of a table see an UPDATE’s new rows but not its deletes.
• A statement that changes tables in two lakes is two commits, not one transaction.
• Tables made before version 0.19 have rows without ids. Their keyed tables change as before; change an older append table after CREATE TABLE t2 AS SELECT * FROM t.

A change costs what reading its rows costs, plus one commit. On a table of 10 million rows in Parquet on a 2-vCPU machine, UPDATE … WHERE id = 12345 took 0.024 s, an update of 100,000 rows 0.26 s, and a MERGE of 100,000 rows 0.9 to 1.2 s.