pgrundev/pgx

What it is

PGX “Postgres for agents” is a database runtime that runs many short-lived Postgres-compatible databases inside a single process. It speaks the Postgres wire protocol and SQL dialect, so framework tools (Rails, Django, Prisma, SQLAlchemy, node-postgres) run their full migrate → test → schema-change lifecycles without modification.

The problem it solves

Today, one database means one PostgreSQL server — a process, tens of megabytes of memory, and a cold start each time. Agent workflows, CI jobs, test suites, and preview environments create databases constantly and throw them away minutes or hours later. PGX replaces that with a different lifecycle:

sealed template → mint database (milliseconds, copy-on-write) → agent/CI/test uses it → discard

Instead of 1 database → 1 PostgreSQL server, PGX runs 1 PGX runtime → hundreds or thousands of isolated databases.

How it works (main ideas)

• Share everything immutable, pay for divergence. All databases cloned from the same template share immutable state — memory (catalog caches) and storage (unchanged blocks) alike. Each database pays mainly for what makes it different.
• Shared runtime. Databases are isolated by name, roles, and limits inside one process. A database costs its catalog caches, not a full server.
• Mint-on-connect. Connecting to a special name like <prefix><template>__<token> creates that database from a sealed template and connects you to it. A warm pool of pre-created spares serves bursts.
• Copy-on-write storage. With file_copy_method = clone on a ZFS filesystem, a branch of a 10 GB database shares every unchanged block with its template — ready in ~674 ms, with only ~13.6 MB of new physical storage. On filesystems without block cloning, it’s a full copy.
• Limits at every level. Session, database, and runtime memory limits inside the engine, connection limits per database, and a Linux cgroup around the runtime as a last backstop.

Key measured numbers

Benchmarks come from PostgreSQL’s own test suite and from a 4 vCPU / 8 GB Linux VPS with ZFS block cloning (the harness is in benchmarks/). All figures are described configurations, not claims of general superiority.

Area	Number
Database creation (warm pool, p50)	~6.2 ms
Memory per untouched idle database	~70 KB
Memory per idle database after one query	~128 KB
Databases on one 4 vCPU / 8 GB VM	1,000
Total runtime memory with 1,000 untouched databases	128–155 MB
PostgreSQL src/test/regress (PG 18.6 expected output)	219 / 231 byte-exact; 0 result differences (12 differ only in EXPLAIN plan text)
Framework runs (Rails 8.1, Django 5.2, Prisma 6, SQLAlchemy 2, node-postgres)	36 / 36 pass
Branch of a 10 GB template (ZFS clone)	ready in ~674 ms, ~13.6 MB new storage
Long churn: 30,000 create→use→drop lifecycles	~111 MB total runtime memory
Query speed vs PostgreSQL 18	short statements ~0.05–0.15 ms slower; large single-threaded scans 1.5–2.2× slower; COPY and 4-client throughput equal

PGX does not try to beat Postgres on single queries — its strengths are creation latency, density, memory efficiency, copy-on-write storage, high churn, and per-tenant isolation.

What it is NOT (v0.1 limitations)

The README is explicit: PGX v0.1 is for disposable databases (agent, CI, PR, test, preview) built from sanitized copies of production. It is not a production primary, a system of record, or HA/PITR. Specific gaps:

• Not crash-safe — the profile runs with fsync = off.
• One process — a runtime is one crash domain.
• No C extensions — cannot load PostgreSQL C extensions; only built-in contrib modules (pgvector among them). PL/Python, PL/Perl, PL/Tcl are unavailable.
• It is at version 0.1.0, an early release.

Quickstart

One command installs, starts, creates an app database, and prints the connection URL:

npx pgxdb@latest

Then point DATABASE_URL at the printed URL. Other CLI commands (available as npx pgxdb <cmd> or pgx if globally installed):

pgx clone app agent-001 --ttl 5m   # 5-minute copy-on-write branch
pgx ls                             # databases, TTL, expiry, size
pgx create scratch --ttl 2h        # throwaway database that self-deletes
pgx ttl pr-123 unlimited           # change a branch’s TTL
pgx stop                           # stop the runtime

Databases created without --ttl are permanent; a background supervisor drops temporary ones after their deadline and resumes cleanup after a restart. The binary is SHA-256 verified and cached in ~/.pgx/dist/<version>. No Docker required — native Mac (Apple silicon) and Linux (arm64, x86-64); Intel Macs unsupported. A shell installer (curl -fsSL https://pgx.dev/install | sh) works without Node.

Relation to PGRun

PGX is the database engine behind PGX branches in PGRun. PGRun adds the control plane around it — orchestration, routing, templates, lifecycle, access, branch management, automatic expiry — while PGX is the runtime. They are separate projects.

Project state & contribution guidance

The project says correctness and reproducibility come before benchmark marketing. It tracks PostgreSQL compatibility, Rails/application compatibility, concurrency semantics, lifecycle correctness, resource containment, and real agent workloads. The most useful contribution is a concrete compatibility case: an issue with PostgreSQL version, PGX version, minimal SQL reproduction, expected and actual result — especially for MVCC, locking, deadlocks, isolation levels, transactional DDL, and ORMs.