goopg

goopg is an experimental PostgreSQL-compatible database server written in Go. It is driven entirely by coding agents as a study in agent-led implementation: can an AI agent build and evolve a meaningful PostgreSQL-like server while staying behaviourally aligned with upstream PostgreSQL?

The project has three research axes:

1. Agent-driven implementation — can coding agents produce correct, maintainable Go code for a complex stateful system, using PostgreSQL 18 as the behavioural oracle?
2. Go concurrency characteristics — how do throughput and latency scale as execution paths become more concurrent?
3. Direct I/O trade-offs — what happens when storage paths bypass the OS page cache?

This repository is research-oriented and intentionally iterative. It is not intended for production use.

The upstream PostgreSQL repository (REL_18_3) is included as a submodule under postgres/ and is used as the reference for correctness.

• PostgreSQL wire protocol v3 (simple and extended query modes)
• trust and reject authentication (pg_hba.conf)
• GUC / postgresql.conf parser with SHOW, SET, RESET
• pg_stat_activity system view

• 8 KB heap pages in PG18-compatible on-disk format (PageHeaderData, ItemId, HeapTupleHeaderData)
• MVCC with snapshot isolation (READ COMMITTED and REPEATABLE READ)
• HOT (Heap-Only Tuple) updates — avoids index updates when non-indexed columns change
• Visibility Map — tracks all-visible pages for Index-Only Scan eligibility
• Index-Only Scan (single-column B-tree)
• Opportunistic page pruning — reclaims dead tuple chains inline during HOT updates
• VACUUM with tuple freeze (prevents XID wraparound)
• Autovacuum background worker
• Free Space Map (FSM) — guides INSERT to pages with sufficient free space
• Buffer pool with clock-sweep eviction
• Async I/O engine (AIO) for prefetch and parallel page reads

• B-tree index on integer, numeric, text, date, timestamp, boolean columns
• Unique constraint enforcement via B-tree
• Primary key constraint
• CREATE INDEX, DROP INDEX
• Index scan, range scan, index-only scan

• Full SQL parser (SELECT, INSERT, UPDATE, DELETE, COPY FROM)
• Planner: sequential scan, index scan, index-only scan, hash join, nested-loop join, sort, aggregate, limit, project
• Multi-way bushy hash join (spill-to-disk for joins that exceed memory)
• Correlated subquery optimization and IN-unnesting
• Join-order reordering with cost-based cardinality estimates
• MCV histograms and per-column statistics (ANALYZE)
• Window functions: ROW_NUMBER, RANK, LAG, LEAD
• CTEs (WITH clause, non-recursive)
• Subqueries (EXISTS, NOT EXISTS, scalar, lateral)
• UPSERT: INSERT ... ON CONFLICT DO UPDATE
• SELECT FOR UPDATE / FOR SHARE (pessimistic row locking)
• Aggregates: COUNT, SUM, AVG, MIN, MAX, ARRAY_AGG, STRING_AGG
• Type coercions, CASE/WHEN, CAST, string/date/numeric operators
• LIMIT / OFFSET / ORDER BY / GROUP BY / HAVING
• EXPLAIN and EXPLAIN ANALYZE

• MVCC with per-backend ProcArray snapshot
• SAVEPOINT / ROLLBACK TO SAVEPOINT
• Deadlock detection
• Serializable isolation (SSI anomaly prevention) — partial
• Read-only commit skip (no WAL emit for SELECT-only transactions)

• WAL writer with PG18-compatible on-disk format (pg_waldump-readable)
• Checkpoint with fsync
• Crash recovery from WAL
• WAL segment preallocation
• Concurrent WAL append (M0026)

• Physical (streaming) replication: goopg→goopg and goopg↔PG18 (async and sync)
• Logical replication (pgoutput): goopg→PG18 and PG18→goopg (async and sync)
• Replication slots, walsender, walreceiver
• Standby promotion
• 9 replication patterns verified end-to-end (see internal/testport/)

• pg_class, pg_attribute, pg_index heap tables in PG18-compatible format
• pg_namespace, pg_type, pg_proc views
• CREATE TABLE, DROP TABLE, ALTER TABLE
• CREATE VIEW, DROP VIEW
• CREATE INDEX, DROP INDEX
• CREATE PUBLICATION, CREATE SUBSCRIPTION
• Catalog recovery from heap on startup (no JSON side-channel)
• pg_internal.init relcache init file written for PG standby fast-start

• All 22 HammerDB TPC-H queries pass (SF=1, verified 2026-05-26)
• pgbench: standard, simple-update, select-only workloads

The lifecycle below — build, init, start, connect with psql, stop, drop — is driven from the top-level Makefile. Run make help for the full list of targets and overridable variables.

• Go (matching go.mod's toolchain directive).

• A locally built upstream PostgreSQL client toolchain under postgres/local_install/. The Makefile expects:

  • postgres/local_install/bin/ — psql, pg_ctl, etc.
  • postgres/local_install/lib/ — matching shared libraries (libpq.so*, ICU, …)

  If you have not built it yet, build the postgres/ submodule with --prefix=$(pwd)/local_install and run make install inside postgres/. Only the client tools and libraries are needed; the upstream postgres server binary is not used at runtime.

psql and other client tools under postgres/local_install/bin load shared libraries from postgres/local_install/lib, which is not on the system loader path. Every Makefile target that invokes a client tool prepends both directories. If you run steps manually, prepend them explicitly:

export PATH="$PWD/postgres/local_install/bin:$PATH"
export LD_LIBRARY_PATH="$PWD/postgres/local_install/lib:${LD_LIBRARY_PATH:-}"
# macOS: also set DYLD_LIBRARY_PATH to the same value.

make print-env prints the exact lines the Makefile uses.

make build          # → ./bin/goopg
make init           # → tmp/goopg-data/  (override: DATA_DIR=...)
make start          # background server on 127.0.0.1:5432; log: tmp/goopg.log
make psql           # connect with the in-tree psql
make stop           # graceful shutdown
make clean-data     # remove the data directory
# optionally:
make clean          # also removes ./bin/goopg

Common overrides:

make start LISTEN=0.0.0.0:55432 DATA_DIR=/tmp/my-cluster
make psql  LISTEN=0.0.0.0:55432 DATA_DIR=/tmp/my-cluster PSQL_DBNAME=postgres

# 1. Build.
go build -o ./bin/goopg ./cmd/goopg

# 2. Initialize the data directory.
./bin/goopg init -D ./tmp/goopg-data

# 3. Start the server in one terminal.
./bin/goopg start -D ./tmp/goopg-data --listen 127.0.0.1:5432

# 4. Connect from another terminal (with PATH / LD_LIBRARY_PATH set as above).
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres

# 5. Stop the server.
./bin/goopg stop -D ./tmp/goopg-data

# 6. Drop the cluster.
rm -rf ./tmp/goopg-data

goopg start exits when it receives SIGINT/SIGTERM or when goopg stop -D <datadir> requests a shutdown over the control socket.

# Unit tests (fast, no server needed)
go test ./...

# End-to-end replication tests (requires postgres/ client tools on PATH)
export PATH=$PWD/postgres/local_install/bin:$PATH
export LD_LIBRARY_PATH=$PWD/postgres/local_install/lib:$LD_LIBRARY_PATH
go test ./internal/testport/... -v -timeout 300s

cmd/goopg/          entry point (init / start / stop / promote)
internal/
  analyzer/         semantic analysis (type resolution, name binding)
  catalog/          in-memory catalog, publication/subscription state
  executor/         query execution operators
  initdb/           cluster bootstrap and open/close lifecycle
  mvcc/             MVCC manager, snapshots, ProcArray, clog
  parser/           SQL parser
  planner/          query planner (cost model, join order, statistics)
  server/           wire-protocol server, dispatcher
  storage/          buffer pool, heap pages, B-tree, WAL, VM, FSM
  testport/         end-to-end replication and PostgreSQL interop tests
  testutil/         test cluster harnesses
  vacuum/           VACUUM and autovacuum
  wal/              WAL writer, reader, classifier, replication
docs/
  design/           design documents per subsystem
  milestones/       milestone tracking (numbered 0001–0116+)
practice/           research notes and reference material
postgres/           PostgreSQL 18 source submodule (oracle reference)
bench/              TPC-H and pgbench benchmark scripts

See docs/milestones/README.md for the full milestone index.