overlay-db

A PostgreSQL wire-protocol endpoint that serves reads from an upstream Postgres (strictly read-only) and keeps your INSERT/UPDATE/DELETEs in a local, branchable Parquet overlay — merged into query results on the fly, diffable per cell, and never written back upstream.

Why

Sometimes you need to change data you are only allowed to read:

• What-if scenarios — double the prices, drop a region, reshape the forecast, and watch every downstream number move in dashboards that read live production data.
• Dry-running data fixes — apply the correction, diff it cell by cell against the live base, get it reviewed, then run the vetted SQL on the real system through your normal change process.
• Per-user sandboxes — every analyst edits freely on their own branch, nobody sees anyone else's edits, and main stays pristine.
• Demos and tests on real, current data — without a writable copy of it.

The usual answers are copies (stale the moment they finish, plus a second storage bill), staging databases (drift, coordination), or being very careful against something writable (no). overlay-db is the missing option: point it at any Postgres — a production read replica included — connect with unchanged tools (psql, JDBC, BI, ORMs: it speaks the Postgres wire protocol), and write freely. Reads stay live from the upstream. Writes land in a local, branchable overlay that is merged into query results on the fly. Every upstream connection is pinned default_transaction_read_only=on — the upstream is never written, ever.

Branches work like git for your edits: create one per scenario (CREATE DATABASE "scenario-x" right over the wire, or overlay-db branch create), connect to it by database name, diff it per cell, discard it, or copy it and diverge. An audit log keeps the original SQL and author of every change — authentication is delegated to the upstream, so its GRANTs and row-level security apply per user on every read path, and edits are authored under real database identities.

How it works

Reads pass through verbatim (\d, BI introspection, aggregates all work) until a table has overlay edits — then its reads run on the embedded DataFusion engine, merging live upstream rows with your local changes. Writes work in autocommit and inside BEGIN…COMMIT (session-buffered, read-your-writes, atomic multi-table flush at COMMIT), always branch-scoped (dbname=scenario-x edits are invisible on main).

You can also add tables of your own: CREATE TABLE (serial keys and defaults included), CREATE TABLE AS SELECT, COPY … FROM STDIN / psql's \copy, and overlay-db table import for files — the new tables live entirely in the overlay, join freely with upstream tables, and are branch-scoped like every edit. Your tools see them like any other table: \dt/\d, JDBC metadata, information_schema and to_regclass guards all list created tables (catalog queries are rewritten on the fly; a branch without created tables forwards them byte-verbatim). DDL against upstream objects stays rejected by design. The overlay itself is Parquet plus a commit log, on local disk or an object-store prefix — nothing is ever sent back to the upstream.

By default your edits win: a row you changed keeps showing your version even after the source updates it, with the drift flagged as a conflict in the diff surfaces. For live-refreshed sources there is the opposite policy — on_base_drift = "refresh" in overlay.toml (or OVERLAY_ON_BASE_DRIFT): when the source touches a row underneath one of your edits, the fresh source row wins and your edit stops applying (applies = false in overlay.changes). It comes back if the source reverts, or the moment you edit that row again; deleted rows reappear when the source touches them, and an insert loses to a source-created key. Nothing is lost — history stays in the commit log, and discard still works.

serve --force-engine-tables t1,s2.t2 (dev/testing) routes reads touching those tables through the DataFusion engine instead of passthrough — the same path merged reads will use once tables have overlay edits — so engine fidelity can be checked differentially against the upstream.

Install

Prebuilt binaries ship on npm for macOS (Apple Silicon and Intel) and Linux (x64 and arm64) — overlay-db runs on macOS and Linux only; there is no Windows build (the Linux binary works under WSL2). The Linux binaries are fully static: any distro, Alpine included.

npm install -g @incorta/overlay-db   # Node.js >= 18
overlay-db --version

# or run it without installing:
npx @incorta/overlay-db --help

The @incorta/overlay-db package is a thin launcher; the native binary for your platform arrives as an optional dependency — no postinstall scripts, no downloads outside the npm registry.

From source

A Rust toolchain (1.88+) is the only requirement:

cargo build --release   # produces target/release/overlay-db

Quickstart

# The DSN names only the TARGET — no user, no password: the proxy stores no
# credential, and your clients log in with their own database accounts.
overlay-db init --upstream postgres://replica:5432/prod
overlay-db branch create scenario-x
overlay-db serve   # PostgreSQL endpoint on 127.0.0.1:6432

psql "host=127.0.0.1 port=6432 user=you dbname=main"   # your REAL db user;
#                                     prompts for your database password
# UPDATE orders SET qty = qty * 2 WHERE id = 7 RETURNING *;   -- lands in the overlay
# SELECT * FROM orders;                                       -- merged view
# BEGIN; DELETE FROM orders WHERE id = 9; SELECT count(*) FROM orders; COMMIT;
#                                  -- buffered txn: read-your-writes, atomic flush
# SELECT * FROM overlay.changes;               -- per-cell diff, conflicts vs live base
# SELECT * FROM overlay.diff('public.orders'); -- wide per-table diff, typed columns
# SELECT * FROM overlay.commits;               -- the audit log with the original SQL
# CREATE DATABASE "scenario-y";                -- a new branch, connectable right away

# Your own tables, joined with live upstream data (branch-scoped like edits):
# CREATE TABLE adjustments (id bigserial PRIMARY KEY, region text NOT NULL, delta int DEFAULT 0);
# \copy adjustments (region, delta) from 'adjustments.csv' csv header
# CREATE TABLE targets AS
#   SELECT o.region, sum(o.qty) + coalesce(sum(a.delta), 0) AS goal
#   FROM orders o LEFT JOIN adjustments a USING (region) GROUP BY o.region;
# SELECT * FROM orders o JOIN targets t USING (region);   -- upstream ⋈ created

psql "host=127.0.0.1 port=6432 dbname=scenario-x"  # same base, different edits
# SELECT current_database();                   -- 'scenario-x'
psql -h 127.0.0.1 -p 6432 -l                   # lists the BRANCHES, not upstream dbs

overlay-db log     # the commit history (offline, works while serving)
overlay-db diff    # colored per-cell diff (+ --format json|csv)
overlay-db diff --branch scenario-x
overlay-db discard orders --key 7   # back to pristine (history kept)
overlay-db branch create scenario-z --from scenario-x  # copy, then diverge
overlay-db table list                       # your created tables, rows, shadowing
overlay-db table import adjustments data.csv --header   # offline bulk load

# Adding a user = a plain database role on the UPSTREAM (a DBA operation):
#   CREATE ROLE alice LOGIN PASSWORD '…'; GRANT SELECT ON … TO alice;
# alice can then log in through the proxy; her GRANTs/RLS shape every read,
# and her edits are authored "alice". Passwords cross client→proxy as
# cleartext, so remote clients REQUIRE TLS (loopback is exempt):
# set [server] tls_cert/tls_key in overlay.toml; then sslmode=require works.
# Upstream TLS: ...init --upstream "postgres://replica/prod?sslmode=verify-full&sslrootcert=ca.pem"

overlay-db compact # checkpoint folds → fast restarts (history kept)
overlay-db gc      # reclaim superseded checkpoints + orphans
#   (only objects older than --grace, default 1h — safe beside a live server)

Running it: local or in a bucket

Everything overlay-db stores — branches, overlay commits (Parquet), compact checkpoints and the manifest — lives in one data directory. init chooses it once and records it in overlay.toml; serve and every offline command (status, log, diff, discard, compact, gc) then resolve it the same way: --data-dir flag > OVERLAY_DATA_DIR env > overlay.toml > the default ./.overlay.

On a local machine

overlay-db init --upstream postgres://replica:5432/prod
# initialized data directory ./.overlay (branch `main`)
# config: wrote overlay.toml

overlay-db serve                          # PostgreSQL endpoint on 127.0.0.1:6432
overlay-db serve --listen 0.0.0.0:6432    # reachable from other machines —
                                          # set [server] tls_cert/tls_key first:
                                          # remote logins require TLS

• The upstream DSN names only the target (host:port/db). If it embeds a user or password, init warns and serve refuses it: the proxy stores no credential, clients log in with their own database accounts.
• Pick the location explicitly with --data-dir ~/overlays/prod (or OVERLAY_DATA_DIR); relative paths resolve against the directory overlay-db runs from.
• Local dirs are fsync-disciplined and flock-guarded. Read-only commands (status, log, diff) work beside a running server; gc additionally takes the exclusive lock.

In a bucket (stateless serving)

The data dir can be an object-store prefix instead of a path — same commands, same overlay, no volume:

# S3 — credentials come from the standard AWS environment
# (env keys, IRSA, instance roles), NEVER from overlay.toml or flags.
overlay-db init --upstream postgres://replica:5432/prod \
    --data-dir s3://my-bucket/overlays/prod
overlay-db serve

# GCS and Azure work the same way:
#   --data-dir gs://my-bucket/overlays/prod     (GOOGLE_* / workload identity)
#   --data-dir az://my-container/overlays/prod  (AZURE_*)

# MinIO or any S3-compatible endpoint:
AWS_ENDPOINT=http://minio:9000 AWS_ALLOW_HTTP=true \
AWS_ACCESS_KEY_ID=minioadmin AWS_SECRET_ACCESS_KEY=minioadmin \
overlay-db init --upstream postgres://replica:5432/prod \
    --data-dir s3://overlays/prod

# Configless: point any process at the same overlay by env alone — pods, CI,
# or your laptop next to a running server (offline commands included).
export OVERLAY_DATA_DIR=s3://my-bucket/overlays/prod
export OVERLAY_UPSTREAM_DSN=postgres://replica:5432/prod
overlay-db serve         # on one machine…
overlay-db log           # …the same overlay from another
overlay-db gc --dry-run  # preview what a cleanup would reclaim

With the data dir in a bucket, serve is stateless: kill the process — kill -9 included — and start it anywhere; the overlay recovers from the bucket. The commit point is a conditional PUT of a single manifest object (a real compare-and-swap), so a rolling deploy's brief two-pod overlap, or the CLI editing beside a live server, serializes safely — the loser refreshes and retries transparently. Run overlay-db gc as a CronJob: --grace (default 1h) means it only touches objects old enough that a live commit can never lose data to it. An in-memory cache ([limits] object_cache_size) keeps diff surfaces off the bucket. Example Kubernetes manifests: examples/k8s/.

Workspace

Full architecture and design rationale: DESIGN.md.

| Crate | Role | |---|---| | crates/overlay-types | Type-mapping policy: PG OID ↔ Arrow ↔ wire codecs, PG-exact rendering | | crates/overlay-catalog | Upstream introspection (columns, PKs), pooled read-only connections | | crates/overlay-store | Data-dir layout over object_store (local dir or S3/GCS/Azure), branches, commit log, CAS manifest, object cache | | crates/overlay-engine | DataFusion engine: upstream/merged providers, pushdown gate, router refs | | crates/overlay-server | Wire endpoint, router, session, passthrough + engine routes, delegated auth/TLS, per-user pools | | crates/overlay-cli | The overlay-db binary | | tests-integration | Dockerized upstream + the differential oracle |

Dependency direction: cli → server → engine → {catalog, store} → types.

Configuration

Precedence: command-line flags > OVERLAY_* env vars > overlay.toml > defaults. overlay-db init generates a commented overlay.toml.

License

Apache-2.0 © Incorta.