Manual table

Goal: build a structured table from scratch — schema you control, data you push, queries you write — then keep it healthy with periodic maintenance. The canonical SQL-first workflow.

Shape:


CREATE TABLE   →   INSERT / MERGE / UPDATE / DELETE
                                   │
                        ┌──────────┼──────────┐
                        ▼          ▼          ▼
                     SELECT     compact    optimize
                   (sees every  (apply     (bin-pack
                    commit)      pending)   files)

Prerequisites

A bucket — kb-prod:


dodil data bucket create kb-prod -d "Events warehouse"

A credential for the wire you’re using — API key, service account, or a bearer JWT from dodil auth login. All three work on every wire; see Connect & wire adapters.
Tables are implicit per bucket. There is nothing to enable.

Connect once and reuse the session for the whole recipe:

psql


# sslmode=require — the pg door enters on a raw host port (5432) but does
# terminate TLS. Never `prefer`: it falls back to plaintext without telling you,
# and your password here is your API-key/service-account secret.
# dbname is the bare bucket name.
psql "host=pg.uk-lon-1.dodil.io port=5432 dbname=kb-prod \
      user=dk_XXXX password=$DODIL_SECRET sslmode=require"

Python (psycopg)


import os
import psycopg
 
conn = psycopg.connect(
    host="pg.uk-lon-1.dodil.io",
    port=5432,
    dbname="kb-prod",          # the bare bucket name
    user="dk_XXXX",
    password=os.environ["DODIL_SECRET"],
    sslmode="require",         # the pg wire terminates TLS; never "prefer"
    autocommit=True,
)

dodil data


# Runs SQL over the tables-gateway gRPC door (Tables.Execute).
dodil data sql -b kb-prod "SELECT 1"
 
# Or force the Postgres wire as the transport:
dodil data pg  -b kb-prod "SELECT 1"
 
# Print a ready connection URL for your own client:
dodil data connect kb-prod -o psql

1. Create the table

We’ll model an events table with a composite primary key, a partition column, and a JSON column for arbitrary payload:


CREATE TABLE events (
  id          BIGINT      NOT NULL,
  user_id     VARCHAR     NOT NULL,
  occurred_at TIMESTAMP   NOT NULL,
  event_type  VARCHAR     NOT NULL,
  payload     JSON,
  PRIMARY KEY (id, user_id)
)
PARTITIONED BY (event_type);

psql


-- paste the CREATE TABLE above straight into the psql prompt
\d events

Python (psycopg)


conn.execute("""
    CREATE TABLE events (
      id          BIGINT    NOT NULL,
      user_id     VARCHAR   NOT NULL,
      occurred_at TIMESTAMP NOT NULL,
      event_type  VARCHAR   NOT NULL,
      payload     JSON,
      PRIMARY KEY (id, user_id)
    )
    PARTITIONED BY (event_type)
""")

dodil data


# Either send the DDL as SQL …
dodil data sql -b kb-prod "CREATE TABLE events (id BIGINT NOT NULL, user_id VARCHAR NOT NULL, occurred_at TIMESTAMP NOT NULL, event_type VARCHAR NOT NULL, payload JSON, PRIMARY KEY (id, user_id)) PARTITIONED BY (event_type)"
 
# … or use the structured shortcut (CreateTable RPC).
dodil data table create events --bucket kb-prod \
  --description "Click + purchase events" \
  --columns-json '[
    {"name":"id",          "type":"bigint",    "nullable":false},
    {"name":"user_id",     "type":"varchar",   "nullable":false},
    {"name":"occurred_at", "type":"timestamp", "nullable":false},
    {"name":"event_type",  "type":"varchar",   "nullable":false},
    {"name":"payload",     "type":"json",      "nullable":true}
  ]' \
  --partition-column event_type \
  --merge-key id --merge-key user_id

The two key choices:

Choice	Effect
Composite PK (`PRIMARY KEY (id, user_id)`, or `--merge-key` twice)	Rows have identity: `UPSERT`, `ON CONFLICT`, keyed `UPDATE` / `DELETE` and the concurrency guard all key on it. A table without one gets a hidden `_rowid` key and keyed DML on your columns is unavailable.
Partition column (`PARTITIONED BY (event_type)`)	Rows are physically grouped by this column. Reads that filter on it prune whole partitions.

Types. INTEGER, INT and BIGINT are all 64-bit (SMALLINT is 16-bit and range-checked); NUMERIC(p,s) is exact — declare the scale; VARCHAR(n) and TIMESTAMP(p) are enforced. The full list with wire types is on Data types.

Verify the schema:


DESCRIBE events;

2. Insert rows


INSERT INTO events (id, user_id, occurred_at, event_type, payload) VALUES
  (1, 'u-101', TIMESTAMP '2026-05-27 10:00:00', 'click',    '{"page":"/pricing"}'),
  (2, 'u-101', TIMESTAMP '2026-05-27 10:01:00', 'click',    '{"page":"/signup"}'),
  (3, 'u-102', TIMESTAMP '2026-05-27 10:02:00', 'purchase', '{"sku":"A-12","amount":49.99}');

psql


-- Bulk load from a local file — COPY FROM STDIN is a Postgres-wire-only
-- capability (pg TEXT format, tab-separated, \N = NULL; CSV also parses).
\copy events (id, user_id, occurred_at, event_type, payload) FROM 'events.csv' CSV

Python (psycopg)


rows = [
    (1, "u-101", "2026-05-27 10:00:00", "click",    '{"page":"/pricing"}'),
    (2, "u-101", "2026-05-27 10:01:00", "click",    '{"page":"/signup"}'),
    (3, "u-102", "2026-05-27 10:02:00", "purchase", '{"sku":"A-12","amount":49.99}'),
]
 
# Prepared statements + executemany: Postgres-wire only.
conn.cursor().executemany(
    "INSERT INTO events (id, user_id, occurred_at, event_type, payload)"
    " VALUES (%s, %s, %s, %s, %s)",
    rows,
)
 
# Or stream a large load through COPY.
with conn.cursor().copy(
    "COPY events (id, user_id, occurred_at, event_type, payload) FROM STDIN"
) as copy:
    for r in rows:
        copy.write_row(r)

dodil data


# The typed shortcut — each --row is one JSON object, lowered to an INSERT.
dodil data table insert events --bucket kb-prod \
  --row '{"id":1,"user_id":"u-101","occurred_at":"2026-05-27 10:00:00","event_type":"click",   "payload":{"page":"/pricing"}}' \
  --row '{"id":2,"user_id":"u-101","occurred_at":"2026-05-27 10:01:00","event_type":"click",   "payload":{"page":"/signup"}}' \
  --row '{"id":3,"user_id":"u-102","occurred_at":"2026-05-27 10:02:00","event_type":"purchase","payload":{"sku":"A-12","amount":49.99}}'


{ "rows_affected": 3 }

--mode overwrite lowers to INSERT OR REPLACE INTO instead of INSERT INTO — but the OR REPLACE modifier is inert: the statement lowers as a plain INSERT and still fails with SQLSTATE 23505 on an existing key. Use dodil data table upsert when you mean replace.

timestamp columns accept both a TIMESTAMP '…' literal and microseconds since the Unix epoch, UTC.

3. Query — reads are read-your-writes


SELECT event_type, COUNT(*) AS n
FROM events
GROUP BY event_type
ORDER BY n DESC;


event_type  n
click       2
purchase    1

There is no freshness knob and nothing to wait for: every read sees every committed write, on this connection and on any other. The one place you carry anything yourself is a gRPC or HTTP client fanning out across processes, where a write’s max_wal_ulid becomes the next read’s min_ulid — see gRPC & HTTP API.

4. Upsert with `MERGE`

Replace an existing row and add a new one in one statement:


MERGE INTO events AS t
USING (VALUES
    (1, 'u-101', TIMESTAMP '2026-05-27 10:00:00', 'click_pricing', '{"page":"/pricing","variant":"B"}'),
    (4, 'u-103', TIMESTAMP '2026-05-27 10:03:00', 'signup',        '{"plan":"pro"}')
  ) AS s (id, user_id, occurred_at, event_type, payload)
  ON t.id = s.id AND t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET
    occurred_at = s.occurred_at, event_type = s.event_type, payload = s.payload
WHEN NOT MATCHED THEN INSERT (id, user_id, occurred_at, event_type, payload)
    VALUES (s.id, s.user_id, s.occurred_at, s.event_type, s.payload);

psql


-- Then confirm both arms landed:
SELECT id, user_id, event_type FROM events
WHERE user_id IN ('u-101', 'u-103')
ORDER BY id;

Python (psycopg)


conn.execute(MERGE_SQL)
print(conn.execute(
    "SELECT id, user_id, event_type FROM events"
    " WHERE user_id IN (%s, %s) ORDER BY id", ("u-101", "u-103"),
).fetchall())

dodil data


# General MERGE with explicit arms (lowered to the SQL above).
dodil data table merge events --bucket kb-prod \
  --match-column id --match-column user_id \
  --when-matched update --when-not-matched insert \
  --row '{"id":1,"user_id":"u-101","occurred_at":"2026-05-27 10:00:00","event_type":"click_pricing","payload":{"page":"/pricing","variant":"B"}}' \
  --row '{"id":4,"user_id":"u-103","occurred_at":"2026-05-27 10:03:00","event_type":"signup","payload":{"plan":"pro"}}'
 
# Or the keyed typed write path — matches the PK by default, returns the
# session watermark (Tables.Upsert, not a SQL lowering).
dodil data table upsert events --bucket kb-prod \
  --row '{"id":4,"user_id":"u-103","event_type":"signup"}' --merge


{ "wal_written": true, "wal_ulid": "01JX…" }

--merge makes it a partial-column merge: columns absent from the row stay untouched. Without it the row replaces the whole tuple. This lane does not check VARCHAR(n) widths — see Writes.

--when-matched accepts update | delete | skip; --when-not-matched accepts insert | skip.

5. Update + delete


UPDATE events SET event_type = 'click_signup'
WHERE id = 2 AND user_id = 'u-101';
 
DELETE FROM events
WHERE id = 3 AND user_id = 'u-102';

A WHERE clause is mandatory. UPDATE and DELETE are refused without one — the plane will not lower an unbounded write, and dodil data table update / delete-rows both require --predicate for the same reason. To empty a table on purpose, use TRUNCATE TABLE events, which keeps the schema, PK, indexes, graph bindings and reservations.

A predicate on non-key columns is fine: the matching keys are resolved first, then each row is written, so the update is as safe under concurrency as a key-addressed one.

SQL


UPDATE events SET event_type = 'click_signup'
WHERE id = 2 AND user_id = 'u-101';

6. Transactions

BEGIN / COMMIT / ROLLBACK are supported. On the Postgres wire a transaction is a real session: statements accumulate, later statements see earlier ones, savepoints work, and everything commits atomically at COMMIT — across tables too. Over Tables.Execute a whole BEGIN; …; COMMIT; script in one call does the same.


BEGIN;
  UPDATE events SET event_type = 'archived' WHERE id = 1 AND user_id = 'u-101';
  DELETE FROM events WHERE id = 2 AND user_id = 'u-101';
COMMIT;

If a concurrent transaction committed a write to a key this one read with FOR UPDATE or wrote, the commit fails with a serialization failure (SQLSTATE 40001) rather than silently overwriting; re-run the block. The full contract is on Transactions & concurrency.

7. Maintenance — compact + optimize

compact applies pending writes to the table’s storage now; optimize bin-packs (and optionally Z-orders) the resulting files. Both are RPCs, not SQL:


# Apply pending writes to storage
dodil data table compact events --bucket kb-prod -o json


{
  "wal_entries_processed": 6,
  "wal_unique_keys": 4,
  "drained_high_ulid": "01JX…"
}


# Bin-pack small storage files (add --z-order-column to cluster)
dodil data table optimize events --bucket kb-prod -o json


{
  "table_name": "events",
  "version": 5,
  "optimize_type": "COMPACTION",
  "files_added": 1,
  "files_removed": 12
}

You don’t usually need to run these by hand — a background maintenance loop does both on its own — but explicit calls are useful:

After large bulk writes, so analytical scans read packed files instead of merging a backlog
As the canonical compact → optimize post-batch sequence
In tests / e2e, to assert a deterministic storage version

Compaction does not gate visibility: rows are readable the moment they are committed. It changes how they are stored, not whether they are seen.

Cleanup

SQL


DROP TABLE IF EXISTS events;

Common gotchas

Symptom	Cause	Fix
`UPDATE`/`DELETE` rejected outright	No `WHERE` clause — the plane refuses unbounded writes	Add a predicate, or use `TRUNCATE TABLE` if you really mean “all rows”
`sslmode=verify-full` fails with “root certificate file … does not exist”	Client-side only: libpq doesn’t read the OS trust store, so psql/psycopg/SQLAlchemy need to be pointed at one. The server’s certificate is a public Let’s Encrypt one and verifies fine	Add `sslrootcert=system` (libpq 16+), or point `sslrootcert` at the system bundle (e.g. `/etc/ssl/cert.pem` on macOS). Go, Node and JVM drivers need nothing — see Choosing an sslmode
Connection refused with a bucket-qualified dbname	The gateway qualifies the db id itself from your authenticated org	Send the bare bucket name as `dbname`
A second gRPC/HTTP process can’t see a write the first just made	Cross-process read-your-writes over gRPC needs the watermark	Carry the writer’s `wal_ulid` as the reader’s `min_ulid`; the Postgres wire needs nothing
`optimize` reports `files_added: 0`	Table was already well-packed	Not an error — a no-op is the expected steady state
CTAS-created table rejects keyed `UPDATE`	A keyless CTAS mints a hidden `_rowid` PK, not your columns	Declare an explicit `PRIMARY KEY` in the CTAS column list

Variations

Variation	What changes
No-PK table (audit-log style)	Omit `PRIMARY KEY`. Appends are fast; keyed upserts and keyed DML are unavailable.
Multi-column partition	`PARTITIONED BY (event_type, user_id)`. Reads that filter on all partition columns prune most aggressively.
JSON-heavy schema	Lean on `payload`. Trade-off: JSON extraction is slower than a typed-column predicate. Promote hot JSON fields to typed columns when you query them often.
Secondary index	`CREATE INDEX ON events (user_id)` — a non-unique index is single-column. `CREATE UNIQUE INDEX ON events (a, b)` may span columns and enforces `23505` on the write path.
Default expressions	`id UUID DEFAULT GENERATE_UUID()`, `created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP`, `sort_key VARCHAR DEFAULT ULID()`, or a literal — applied at commit when the column is absent. See Data types → Defaults.

Next: Double-entry ledger for the transactional patterns, or CTAS & materialize to derive tables from this one.

Manual table

Prerequisites

psql

Python (psycopg)

dodil data

1. Create the table

psql

Python (psycopg)

dodil data

2. Insert rows

psql

Python (psycopg)

dodil data

3. Query — reads are read-your-writes

4. Upsert with MERGE

psql

Python (psycopg)

dodil data

5. Update + delete

SQL

dodil data

6. Transactions

7. Maintenance — compact + optimize

Cleanup

SQL

dodil data

Common gotchas

Variations

4. Upsert with `MERGE`