Persistence, backups and replication

Everything ProvSQL keeps in tables (the provsql column of a tracked table, the per-relation metadata in provsql.table_info, the data-modification log provsql.update_provenance, the tool registry) behaves like the rest of your database: it rolls back, is written to the WAL, replicates, and is carried by pg_dump.

The provenance circuit does not: it lives in four memory-mapped files in the database’s directory, outside PostgreSQL’s WAL, with the consequences below.

What a transaction does to the circuit

  • A gate is never removed and never changes. A gate created by a transaction that rolls back stays, unreferenced, until circuit_cleanup reclaims it.

  • A probability is written once. set_prob writes one on a gate that has none, accepts the identical value again (so setup scripts stay re-runnable), and refuses a different one. A write made by a transaction that rolls back is cleared.

    To give a tuple a different probability, give it a new input gate with replace_input:

    UPDATE s SET provsql = provsql.replace_input(provsql, 0.3) WHERE id = 42;

    This is an ordinary row update, so it rolls back, replicates and is dumped like any other. A table materialised earlier keeps the old tokens and the old probability. replace_block does the same for a whole repair_key block, and replace_update for a recorded data modification.

  • Per-relation metadata follows the transaction, like any table.

The circuit itself is not isolated: another session can read a probability between its write and the rollback that clears it. Every provenance query writes to the circuit, reads included, so the store grows with ad hoc exploration, even in a READ ONLY transaction. A transaction that has written a probability refuses PREPARE TRANSACTION.

Durability

A crash of PostgreSQL loses no gate; a crash of the machine can lose the gates of transactions committed a fraction of a second earlier, the way synchronous_commit = off can lose rows. A lost gate silently reads back as an independent input with probability 1. To remove that window, at the cost of one flush per transaction that writes to the circuit:

SET provsql.store_synchronous_commit = on;

check_store reports whether the four files agree with each other:

SELECT * FROM provsql.check_store();

Every count is 0 for a healthy store (unclean_shutdown, true after a crash, is not by itself a problem). Otherwise, the files were typically copied at different instants; circuit_cleanup rebuilds the store from what is still reachable.

Backups

  • pg_dump and logical replication do not carry the circuit, only the tokens. Restored elsewhere, every token reads back as an input gate: base tables survive this, anything derived (a CREATE TABLE AS SELECT provenance(), a data-modification history, a repair_key block) is lost.

  • File-level backups must include the store: the files provsql_gates.mmap, provsql_wires.mmap, provsql_mapping.mmap and provsql_extra.mmap in each database’s directory. Copy them with the server stopped, or within a filesystem snapshot of the whole data directory.

  • Cloning a database needs STRATEGY = FILE_COPY; the PostgreSQL 15+ default copies no circuit:

    CREATE DATABASE b TEMPLATE a STRATEGY = FILE_COPY;

    ALTER DATABASE ... SET TABLESPACE carries the store.

  • pg_upgrade does not carry the circuit. ALTER EXTENSION provsql UPDATE keeps it; a major-version PostgreSQL upgrade needs the four files copied by hand into the new cluster’s database directories, which works only from PostgreSQL 15 onwards (database OIDs are preserved).

Replication

By default, a physical standby holds only what its base backup copied, and its own queries create gates of their own: after a promotion, the two stores cannot be reconciled. From PostgreSQL 15, the circuit can be WAL-logged and replayed on the standby:

-- in postgresql.conf, or per session as a superuser
provsql.store_synchronous_commit = on
provsql.store_wal_logging = on

The standby then carries the provenance the primary computed, but refuses to write to the circuit, so provenance queries, reads included, run only on the primary. After turning this on, give existing replicas a fresh base backup. ProvSQL’s WAL resource-manager id is 151, which no other loaded extension may claim. provsql.store_wal_logging must stay off on PostgreSQL forks that replay WAL in their storage layer (Amazon Aurora, Neon, Google AlloyDB, Microsoft HorizonDB).

Reclaiming space

The store grows with every dropped table, remove_provenance, reloaded dataset, rolled-back transaction and exploratory query. circuit_cleanup removes the unreachable gates, much as VACUUM FULL reclaims space, and repairs a damaged store:

SELECT * FROM provsql.circuit_cleanup(dry_run => true);
SELECT * FROM provsql.circuit_cleanup();

It needs the database to itself: it refuses to run while another session is connected, and makes new connections wait. Run it after a bulk reload or a round of experiments.

It keeps every gate reachable from a value of a uuid, agg_token or random_variable column (or an array of those) of any table or materialised view. A token kept only outside these (in a notebook, a file, a text or jsonb value, a foreign table) is not kept: re-running the query that built it brings it back, except for freshly created leaves such as a random_variable.

Upgrading from before 1.13.0

The per-relation metadata, previously in a fifth file provsql_table_info.mmap, is imported into provsql.table_info by the upgrade script, or by hand with migrate_table_info. In a store written by an older version, a probability of 1 can be written once more, until the first circuit_cleanup. An older ProvSQL cannot read a store this version has written to.