Skip to main content

Branching for Agents

By the end of this page you will have given an agent an instant, disposable, full copy of real data to work against, watched it work without touching your source database, and cleaned up after it, start to finish.

1. Start with a real database​

Assume you already have a database with real data in it, for example the sensors table from Quick Start:

CREATE TABLE sensors (
    id BIGINT PRIMARY KEY,
    device_id INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    recorded_at TIMESTAMP
);

INSERT INTO sensors VALUES
    (1, 101, 22.5, 45.0, '2024-03-15 10:00:00'),
    (2, 101, 23.1, 44.0, '2024-03-15 10:05:00'),
    (3, 102, 19.8, 55.0, '2024-03-15 10:00:00'),
    (4, 102, 20.1, 54.0, '2024-03-15 10:05:00'),
    (5, 103, 25.3, 38.0, '2024-03-15 10:00:00');

2. Give the agent a branch​

Connected to the default scramdb database, run:

CREATE DATABASE agent_run_142 CLONE scramdb;

Expected result: a bare success response, no rows. CREATE DATABASE ... CLONE is instant regardless of the source database's size, because every existing storage segment is shared by reference between the clone and its source, not copied. You are not waiting on a copy job; the clone exists the moment the statement returns.

Verify by connecting to the new branch (see Connecting an Agent for the exact connection forms) and checking the data is already there:

-- after connecting with dbname=agent_run_142
SELECT COUNT(*) FROM sensors;

Expected: 5, the same row count the source had at the moment you ran CLONE.

If it fails: CREATE DATABASE runs outside of an explicit transaction block; if you are inside one (BEGIN already issued), commit or roll it back first and re-run the statement.

3. Scope the agent to its own role and branch​

Give the agent a role, and grant it only what it needs on the branch:

CREATE ROLE agent_run_142_role LOGIN PASSWORD 'a-real-password';
GRANT SELECT, INSERT, UPDATE ON sensors TO agent_run_142_role;

Both statements run on the branch connection. CREATE ROLE is instance-wide (roles are shared across databases, PostgreSQL semantics), while the GRANT is scoped to the branch: it covers the branch's sensors only and grants nothing on the source database's table of the same name. Granting an agent access on its branch never widens access to production.

Connect the agent as agent_run_142_role against dbname=agent_run_142, exactly as shown in Connecting an Agent.

One real limitation to plan around: this GRANT covers sensors as it exists right now. A table the agent creates later on the branch is not automatically covered by anything similar to PostgreSQL's ALTER DEFAULT PRIVILEGES, because that statement has no working SQL form here. If the agent is expected to create its own tables during the run, either grant permissively on the branch (reasonable, since the branch is already the isolation boundary and disposable) or re-run GRANT after the agent creates something another role needs to read.

4. Let the agent do whatever it likes​

This is the point of the branch: nothing on it is precious. The agent can bulk-load with COPY (including COPY ... FROM STDIN), run bulk updates or a MERGE, refresh statistics with ANALYZE, drop and rebuild an index, TRUNCATE a table, or run an exploratory ALTER TABLE, and none of it touches the source database.

UPDATE sensors SET temperature = temperature + 1.0 WHERE device_id = 101;
DELETE FROM sensors WHERE humidity < 40.0;
ALTER TABLE sensors ADD COLUMN reviewed BOOLEAN DEFAULT false;

5. Promote or discard​

Discarding is the common case, and the full cleanup end to end:

-- from a connection to a database OTHER than agent_run_142; you cannot
-- drop the database you are currently connected to
DROP DATABASE agent_run_142;

DROP DATABASE streams a bounded, per-table drop and releases each table's reference count as it goes. A storage segment the branch still shares with its parent (anything the agent never wrote to) survives untouched, exactly as it would with an ordinary DROP TABLE. Dropping ten agent branches in a row does not copy or re-touch the shared parent data ten times: the cost of cleanup scales with what the agent actually changed, not with the size of the database it branched from.

If it fails: the server's default/bootstrap database cannot be dropped, and the attempt is refused by name. This is expected: branches are meant to be the disposable side of the relationship, never the default database itself.

If you want to keep what the agent produced instead of discarding it, a branch is just an ordinary database, so "promoting" it is one of:

  • Point your application's connection string at the branch's dbname going forward, treating it as the new source of truth.
  • Move specific rows back at the application layer: connect to both the branch and the source database and copy the rows your application logic decides to keep. There is no confirmed cross-database query that reaches from one database into another's tables within a single statement, so promotion of individual rows is an application-layer step, not a single SQL statement.

6. Automate the loop​

Steps 2 through 5 are a repeatable unit: clone, scope a role, let the agent work, drop. See Agent Patterns for how this loop fits into guardrails around agent-written SQL, and Using Graphs for building and discarding a speculative graph the same way.

A branch does not expire on its own and has no read-only mode today. Your agent or its orchestrator is responsible for calling DROP DATABASE when the run ends; nothing on the server does it automatically.