Using PostgreSQL to Store Git Data
Using PostgreSQL to Store Git Data
Agent
Claude Code
Model
Claude Opus 4.6
Started
Feb 23, 3:16 PM
Messages554
Downloads54
Implement the following plan:
Store git objects and refs in Postgres tables. Two implementations: pure SQL (PL/pgSQL functions, works on managed Postgres) and a C extension (native types, faster parsing). A libgit2-based bridge program registers Postgres as the storage backend so standard git push/fetch/clone work against the database.
gitgres/ Makefile # top-level, delegates to sub-makes sql/ schema.sql # tables, indexes functions/ object_hash.sql object_read_write.sql tree_parse.sql commit_parse.sql ref_manage.sql views/ queryable.sql # materialized views ext/ # Postgres C extension Makefile # PGXS gitgres.c # PG_MODULE_MAGIC, init git_oid_type.c # 20-byte OID type with operators sha1_hash.c # C SHA1 via OpenSSL tree_parse.c # C tree entry parser gitgres.control sql/gitgres--0.1.sql # CREATE TYPE, CREATE FUNCTION backend/ # libgit2 pluggable backend Makefile main.c # CLI entry point (serves repos) odb_postgres.c # git_odb_backend implementation odb_postgres.h refdb_postgres.c # git_refdb_backend implementation refdb_postgres.h writepack_postgres.c # packfile receive handling import/ gitgres-import.sh # shell script: git plumbing + psql test/ test_helper.rb schema_test.rb object_hash_test.rb object_store_test.rb tree_parse_test.rb commit_parse_test.rb ref_test.rb roundtrip_test.rb # push then clone, diff working treesDepends on pgcrypto (available on all managed Postgres). OIDs stored as bytea (20 bytes raw).
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE repositories ( id serial PRIMARY KEY, name text NOT NULL UNIQUE, created_at timestamptz NOT NULL DEFAULT now());
-- Git objects: raw content without the git header.-- Type uses git's internal numbering: 1=commit, 2=tree, 3=blob, 4=tag.-- OID = SHA1("<type> <size>\0<content>"), computed on write.CREATE TABLE objects ( repo_id integer NOT NULL REFERENCES repositories(id), oid bytea NOT NULL, type smallint NOT NULL, size integer NOT NULL, content bytea NOT NULL, PRIMARY KEY (repo_id, oid));CREATE INDEX idx_objects_oid ON objects (oid);
CREATE TABLE refs ( repo_id integer NOT NULL REFERENCES repositories(id), name text NOT NULL, oid bytea, -- NULL for symbolic refs symbolic text, -- NULL for direct refs PRIMARY KEY (repo_id, name), CHECK ((oid IS NOT NULL) != (symbolic IS NOT NULL)));
CREATE TABLE reflog ( id bigserial PRIMARY KEY, repo_id integer NOT NULL REFERENCES repositories(id), ref_name text NOT NULL, old_oid bytea, new_oid bytea, committer text NOT NULL, timestamp_s bigint NOT NULL, tz_offset text NOT NULL, message text, created_at timestamptz NOT NULL DEFAULT now());CREATE INDEX idx_reflog_ref ON reflog (repo_id, ref_name, id);git_object_hash(type, content) -- SHA1 of <type> <size>\0<content> using pgcrypto digest()git_object_write(repo_id, type, content) -- compute hash, INSERT ON CONFLICT DO NOTHING, return oidgit_object_read(repo_id, oid) -- return (type, size, content)git_object_read_prefix(repo_id, prefix, prefix_len) -- abbreviated OID lookupgit_tree_entries(content) -- parse binary tree into (mode, name, entry_oid) rowsgit_commit_parse(content) -- parse commit into tree_oid, parent_oids[], author fields, committer fields, messagegit_ref_update(repo_id, name, new_oid, old_oid, force) -- compare-and-swap with SELECT FOR UPDATEgit_ls_tree_r(repo_id, tree_oid, prefix) -- recursive tree walk returning (mode, path, oid, type)CREATE MATERIALIZED VIEW commits_view ASSELECT o.repo_id, o.oid AS commit_oid, encode(o.oid, 'hex') AS sha, c.tree_oid, c.parent_oids, c.author_name, c.author_email, to_timestamp(c.author_timestamp) AS authored_at, c.committer_name, c.committer_email, to_timestamp(c.committer_timestamp) AS committed_at, c.messageFROM objects o, LATERAL git_commit_parse(o.content) cWHERE o.type = 1;
CREATE MATERIALIZED VIEW tree_entries_view ASSELECT o.repo_id, o.oid AS tree_oid, e.mode, e.name, e.entry_oidFROM objects o, LATERAL git_tree_entries(o.content) eWHERE o.type = 2;This is the bridge. Implement git_odb_backend and git_refdb_backend structs that store/retrieve from Postgres via libpq. libgit2 handles all protocol work (pack negotiation, delta resolution, ref advertisement).
odb_postgres.c)Implements these callbacks from git_odb_backend:
read(data, len, type, backend, oid) -- SELECT type, size, content FROM objects WHERE repo_id=$1 AND oid=$2read_header(len, type, backend, oid) -- SELECT type, size FROM objects WHERE ...read_prefix(full_oid, data, len, type, backend, short_oid, prefix_len) -- prefix match on oid columnwrite(backend, oid, data, len, type) -- INSERT INTO objects ... ON CONFLICT DO NOTHINGexists(backend, oid) -- SELECT 1 FROM objects WHERE ...exists_prefix(full_oid, backend, short_oid, prefix_len) -- prefix existence checkforeach(backend, cb, payload) -- SELECT oid FROM objects WHERE repo_id=$1, call cb for eachwritepack(writepack_out, backend, odb, progress_cb, payload) -- return a git_odb_writepack that accumulates pack bytes, then on commit delegates to libgit2's indexer to extract objects and calls write for eachfree(backend) -- close PG connectionThe backend struct holds a PGconn* and the repo_id.
refdb_postgres.c)Implements these callbacks from git_refdb_backend:
exists(exists_out, backend, ref_name) -- SELECT 1 FROM refs WHERE ...lookup(ref_out, backend, ref_name) -- SELECT oid, symbolic FROM refs WHERE ..., construct git_referenceiterator(iter_out, backend, glob) -- SELECT name, oid, symbolic FROM refs WHERE name LIKE ..., return custom iteratorwrite(backend, ref, force, who, message, old_id, old_target) -- CAS update using a transaction with SELECT FOR UPDATErename(ref_out, backend, old_name, new_name, force, who, message) -- UPDATE refs SET name=$1del(backend, ref_name, old_id, old_target) -- DELETE with CAS checkhas_log / ensure_log / reflog_read / reflog_write / reflog_rename / reflog_delete -- operate on the reflog tablelock / unlock -- use Postgres advisory locks (pg_advisory_xact_lock)free(backend) -- close connectionmain.c)A program that serves one or more repos. For testing, it can run as a one-shot helper that git invokes. Longer term it could serve HTTP smart protocol or the git:// protocol.
Initial approach: implement a git-remote-gitgres helper. When you do git clone gitgres::postgres://localhost/mydb/myrepo, git invokes git-remote-gitgres postgres://localhost/mydb/myrepo. The helper:
libgit2 has git_transport_register for custom transports, or we can use the simpler git_remote_callbacks approach.
ext/)The Postgres extension, separate from the libgit2 backend. Adds performance for SQL queries.
git_oid type: 20-byte fixed binary, hex I/O, btree + hash operator classes. Replaces bytea columns with a proper type so you can write WHERE oid = 'abc123...'.git_object_hash_c(type, content) RETURNS git_oid: C SHA1 via OpenSSL, ~10x faster than pgcryptogit_tree_entries_c(content) RETURNS SETOF (mode, name, oid): C pointer arithmetic instead of PL/pgSQL byte walkingBuilt with PGXS, links against OpenSSL.
import/gitgres-import.sh)Shell script that imports an existing git repo into the database using git plumbing commands and psql.
Usage: gitgres-import <repo-path> <conninfo> <repo-name>git -C <repo> rev-list --objects --all to get all object OIDsgit -C <repo> cat-file --batch to get type + contentgit -C <repo> show-ref to get all refs, INSERT into refs tableSchema + object hash + read/write functions -- the foundation. Verify that git_object_hash(3, 'hello') matches git hash-object --stdin.
Tree + commit parsing functions -- test against real objects from a git repo using git cat-file.
Import script -- import a small test repo (~50 commits). Verify object count matches git rev-list --objects --all | wc -l.
Materialized views -- REFRESH after import. Write example queries against commits and trees.
libgit2 ODB backend -- implement read/write/exists. Test by creating a libgit2 repo with the Postgres backend, writing objects through it, verifying they appear in the database.
libgit2 refdb backend -- implement CRUD + iterator. Test ref creation and lookup.
git-remote-gitgres helper -- wire up libgit2 transport so git push/clone work.
Roundtrip test -- push a repo, clone it elsewhere, diff the working trees.
C extension -- git_oid type, C hash, C tree parse. Benchmark against PL/pgSQL.
Scale test -- import homebrew-core.
Minitest. Each test connects to a gitgres_test database, runs schema setup in a BEGIN, rolls back in teardown. SQL functions tested by comparing output against git CLI on the same objects.
Roundtrip integration test: create temp repo with commits, push via the backend, clone into new directory, diff -r.
git_oid type.If you need specific details from before exiting plan mode (like exact code snippets, error messages, or content you generated), read the full transcript at: /Users/andrew/.claude/projects/-Users-andrew-code-gitgres/0d86fef0-4a2d-473c-bb7e-29e502603055.jsonl
Let me check a few more key headers and then start writing the C code and tests in parallel.
Let me check a few more headers I need for the writepack and reflog implementation.
Good, I have a clear picture of all the APIs now. Let me also check the git_odb_new non-experimental API and the repository wrapping API.
Now let me check a couple more things and then write all the C files.
Now I have everything I need. Let me write all the C backend files, the import script, and the tests. I'll use parallel agents for the larger files.
Good, the C backend files are written. Now let me write the import script, C extension, and tests in parallel.
Now let me write all the test files and the C extension in parallel.
Good, all the files are written. Now let me verify everything compiles and the tests pass. Let me first check the pg gem is available, then run the tests.
Let me first drop and recreate the test database cleanly, then run the tests.
All 30 tests pass. Now let me try building the C backend.
Backend compiles. Let me now try building the C extension.
Missing includes in sha1_hash.c. Let me fix it and also check tree_parse.c.
The sha1_hash.c is missing #include "utils/builtins.h" which provides VARSIZE_ANY_EXHDR, VARDATA_ANY, etc. Let me also use the EVP API since the legacy SHA1 functions are deprecated in OpenSSL 3.
Tons of OpenSSL warnings (from the -Wcast-function-type flag in PGXS) but I need to check if it actually linked. Let me look at the end more carefully.
The issue is that PG 16 moved VARDATA macros to varatt.h. Let me fix the includes.
Extension builds successfully (it's .dylib on macOS). Zero errors, just OpenSSL warnings from their headers. Let me now do a quick integration test - install the extension and verify it works.
Owned by andrew but "Operation not permitted" - likely SIP. Let me try with install.
Sandbox restriction on Postgres.app. The C extension builds but can't be installed without disabling SIP protections on the app bundle. That's fine - the extension is a build step 9 optimization item. The core functionality (PL/pgSQL functions + libgit2 backend) all works without it.
Let me now do a proper end-to-end test with the backend binary. Let me first test the import script with a small repo.
Let me do the roundtrip test - create a repo, push it, clone it, diff the working trees.
Now clone it back and diff.
Full roundtrip works. Push to Postgres, clone back, working trees are identical, git history is preserved.
Let me update the task status and also verify the materialized views work.
Everything works. Let me now run the full test suite one more time and then mark tests complete.
Let me also initialize a git repo for the project, now that you mentioned wanting a Makefile (the top-level one already exists).
The server is PG 16 (Postgres.app) but the pg_config client is PG 14 (Homebrew). For the C extension we need matching headers, but for everything else it doesn't matter much. Let me check what's available and get started.
All three Makefiles exist and work. The top-level one you asked about was already created.
Let me also update it to exclude the views from createdb since they depend on data existing, and fix the test runner path.
Now let me initialize the git repo and take a look at the final file list.
Here's the final state of the project:
SQL layer (all working, tested):
sql/schema.sql - tables for repositories, objects, refs, reflog with pgcryptosql/functions/ - PL/pgSQL functions: git_object_hash, git_object_write, git_object_read, git_tree_entries, git_ls_tree_r, git_commit_parse, git_ref_update, git_ref_set_symbolicsql/views/queryable.sql - materialized views for commits and tree entrieslibgit2 backend (compiles, roundtrip verified):
backend/odb_postgres.c - ODB backend storing objects in Postgres via libpqbackend/refdb_postgres.c - refdb backend storing refs in Postgresbackend/writepack_postgres.c - pack file receiver using libgit2's indexerbackend/main.c - CLI tool with init, push, clone, ls-refs commandsC extension (compiles, can't install to Postgres.app due to sandbox):
ext/git_oid_type.c - custom 20-byte git_oid type with btree/hash operator classesext/sha1_hash.c - C SHA1 via OpenSSL EVP APIext/tree_parse.c - C tree entry parser as SRFImport script: import/gitgres-import.sh - imports repos via git plumbing + psql
Tests: 30 tests, 78 assertions, all passing. Covers schema constraints, object hashing (verified against git hash-object), object store CRUD, tree parsing, commit parsing, ref CAS updates, and a roundtrip integration test.
Verified roundtrip: pushed a 2-commit repo to Postgres, cloned it back, diff -r shows identical working trees, git log preserved.
ok lets add a readme and git init a repo and commit with "Hello world"
Done. 33 files, one commit: 23ffdab Hello world.
is there a proper .gitignore
Pretty thin. Let me flesh it out.
Want me to amend the commit with this, or leave it for the next one?
new commit is fine, make sure we didn't acceientally commit any files that we ignored
Clean - nothing tracked that should be ignored.
Let me start by checking the current state of the project and then work through the build order.
All good. No accidentally committed build artifacts.
add a prior art section to the readme with links
Good, PG 18 is available via Postgres.app and libgit2 is installed. Let me set up for PG 16 (the running server) and create the project structure.
Done. Five projects with links and a short note on what each does differently from gitgres.
so what would it take to make this talk to a regular git client
Right now gitgres-backend push and clone work, but they're our own CLI, not something git push origin main knows about. The gap is a git remote helper -- a program called git-remote-gitgres that git invokes automatically when it sees a URL with the gitgres:: scheme.
You'd use it like:
git remote add pg gitgres::dbname=gitgres/myrepogit push pg maingit clone gitgres::dbname=gitgres/myrepoGit launches git-remote-gitgres dbname=gitgres/myrepo and talks to it over stdin/stdout with a simple text protocol. The helper needs to handle four commands:
capabilities -- tell git what we support. We'd advertise fetch, push, and option.
list -- query the refs table, print each one as <hex-sha> <refname>\n. Git uses this for git ls-remote, fetch negotiation, and push target resolution.
fetch <sha> <ref> -- git sends one or more of these, ending with a blank line. We need to send back the requested objects and everything they reference. The practical way: generate a packfile covering the requested OIDs using libgit2's git_packbuilder, write it into the local repo's object store. Or simpler still, just walk from each requested OID and write loose objects into the local repo via a temporary ODB.
push <src>:<dst> -- git sends refspecs. We read objects from the local repo that the remote doesn't have, write them to postgres, update the ref. Then print ok <ref>\n for each one.
The hard part is fetch. When git asks for a set of commits, it also tells us (via the list output) what it already has, so we could compute the delta. But for a first pass we can skip negotiation and just transfer everything reachable from the requested OIDs that isn't already local -- which is what git_packbuilder does when you add "wants" and "haves".
The alternative that avoids writing pack protocol code entirely: use the import/export capabilities with git's fast-import format. For fetch, we output a fast-import stream (commits, blobs, trees as text). For push, we read a fast-export stream and parse it into objects. This is what git-remote-hg and git-remote-bzr did. It's simpler but slower for large repos.
In terms of actual code, we already have all the storage operations. The new work is:
main() that reads stdin line by line and dispatches commands (~200 lines)list handler that queries refs (~30 lines, we already have ls-refs)push handler that walks local objects and writes to postgres (~100 lines, we already have this in cmd_push)fetch handler that walks postgres objects and writes to local (~150 lines, similar to cmd_clone but incremental)git-remote-gitgres on PATHWant me to build it?
yes build it
Good, I have the full picture of the existing code. Now let me also check the odb_postgres.h to see the exact struct definition.
Now I'll write git-remote-gitgres as a separate C file in the backend directory, and add it to the Makefile.
That has a forward reference to cmd_push_with_first_line that doesn't exist. Let me rewrite this properly - the fetch and push handlers both need the first line passed in since the main loop already consumed it.
Now update the Makefile to build both binaries.
Clean compile. Now let me test it with a real git client. I need to put the binary on PATH, then use git push/clone with it.
git push works. Let me check the debug log and then test git clone.
Now test git clone.
git push and git clone both work through the standard git client. Let me also test git fetch on an existing clone after pushing new commits.
Full cycle works: push new commits, fetch, pull with fast-forward. Let me run the existing tests to make sure nothing broke, then commit.
All 30 tests still pass. Let me add a test for the remote helper and then commit.
This session is being continued from a previous conversation that ran out of context. The summary below covers the earlier portion of the conversation.
Analysis: Let me chronologically analyze the conversation:
The user asked to implement a detailed plan for "Gitgres: PostgreSQL as a Git Backend" - storing git objects and refs in Postgres tables with pure SQL functions, a C extension, and a libgit2-based bridge program.
I checked the environment: PG 14 (Homebrew client) / PG 16 (Postgres.app server), no libgit2 installed, empty project directory.
User mentioned "we can switch to postgres 18 if its easier" - I noted PG 18 is available via Postgres.app but stuck with PG 16 (the running server).
I installed libgit2 via homebrew (1.9.2_1), created the project structure, and implemented:
git hash-objectI read libgit2 headers to understand the API:
Used parallel Task agents to write:
User asked "can you write a makefile too" - the Makefiles were already being created.
C extension build error: sha1_hash.c missing includes for VARDATA/VARSIZE macros. Fixed by adding #include "varatt.h" (PG 16 moved these macros). Also switched from deprecated SHA1_* to EVP API for OpenSSL 3.
C extension couldn't be installed to Postgres.app due to sandbox/SIP restrictions - noted as non-blocking.
Backend compiled successfully, roundtrip test passed (push repo to PG, clone back, diff -r identical).
User asked for README and git init with "Hello world" commit - done.
User asked about .gitignore quality - I fleshed it out, committed separately, verified no ignored files were tracked.
User asked for prior art section in README - I searched the web and added links to libgit2-backends, gitbase, JGit DFS, Gitaly, and Dolt.
User asked "so what would it take to make this talk to a regular git client" - I explained the git remote helper approach.
User said "yes build it" - I wrote remote_helper.c implementing the git remote helper protocol.
Build succeeded. Tested:
git push pg main - worked ("* [new branch] main -> main")git clone gitgres::dbname=gitgres_test/helper_test - worked, files identicalI wrote remote_helper_test.rb and was about to run the full test suite when the summary was requested.
Key errors:
#include "varatt.h" for PG 16 VARDATA macrosUser preferences from CLAUDE.md:
Summary:
Primary Request and Intent:
The user asked to implement a complete system called "Gitgres" that stores git objects and refs in PostgreSQL tables. The plan specified: a pure SQL layer (PL/pgSQL functions on pgcrypto), a C Postgres extension (native git_oid type, fast SHA1, fast tree parsing), a libgit2-based bridge (custom ODB and refdb backends), an import script, and a full test suite. The build order was: schema/functions first, then import script, then libgit2 backends, then roundtrip testing, then C extension. Later, the user asked for a README, proper .gitignore, prior art section, and finally a git remote helper (git-remote-gitgres) so standard git push/clone/fetch work against the database.
Key Technical Concepts:
<mode_ascii> <name>\0<20_byte_sha1>git_odb_backend (read/write/exists/foreach/writepack), git_refdb_backend (exists/lookup/iterator/write/rename/del/lock/unlock/reflog)git_repository_new() + git_repository_set_odb() + git_repository_set_refdb() for creating in-memory repos backed by custom storagegit_indexer to parse incoming packfiles, then git_odb_backend_one_pack to read extracted objectsdigest() for pure-SQL SHA1varatt.h header for VARDATA/VARSIZE macros (moved from earlier headers)Files and Code Sections:
/Users/andrew/code/gitgres/sql/schema.sql
/Users/andrew/code/gitgres/sql/functions/object_hash.sql
git_type_name(smallint) and git_object_hash(smallint, bytea) using pgcrypto digest()git hash-object --stdin for "hello" -> b6fc4c620b67d95f953a5c1c1230aaab5db5a1b0/Users/andrew/code/gitgres/sql/functions/object_read_write.sql
git_object_write(repo_id, type, content) - compute hash, INSERT ON CONFLICT DO NOTHINGgit_object_read(repo_id, oid) - return (type, size, content)git_object_read_prefix(repo_id, prefix, prefix_len) - abbreviated OID lookup/Users/andrew/code/gitgres/sql/functions/tree_parse.sql
git_tree_entries(content bytea) - PL/pgSQL byte-walking parser returning (mode, name, entry_oid)git_ls_tree_r(repo_id, tree_oid, prefix) - recursive tree walk/Users/andrew/code/gitgres/sql/functions/commit_parse.sql
git_commit_parse(content bytea) - returns tree_oid, parent_oids[], author/committer fields, message/Users/andrew/code/gitgres/sql/functions/ref_manage.sql
git_ref_update(repo_id, name, new_oid, old_oid, force) - CAS with SELECT FOR UPDATEgit_ref_set_symbolic(repo_id, name, target) - upsert symbolic ref/Users/andrew/code/gitgres/sql/views/queryable.sql
commits_view and tree_entries_view materialized views using LATERAL joins/Users/andrew/code/gitgres/backend/odb_postgres.h
git_odb_backend_postgres(git_odb_backend **out, PGconn *conn, int repo_id)/Users/andrew/code/gitgres/backend/odb_postgres.c
git_odb_backend_data_alloc for returned buffers/Users/andrew/code/gitgres/backend/writepack_postgres.c
git_odb_writepack for receiving packfiles during pushgit_indexer to parse pack, then git_odb_backend_one_pack to iterate extracted objects/Users/andrew/code/gitgres/backend/refdb_postgres.h
git_refdb_backend_postgres(git_refdb_backend **out, PGconn *conn, int repo_id)/Users/andrew/code/gitgres/backend/refdb_postgres.c
git_reference__alloc and git_reference__alloc_symbolic from git2/sys/refs.h/Users/andrew/code/gitgres/backend/main.c
open_pg_repo() creates in-memory libgit2 repo with postgres backendscopy_object_cb / clone_object_cb for odb_foreach-based transferspush_head() writes HEAD as symbolic ref via direct SQL/Users/andrew/code/gitgres/backend/remote_helper.c
gitgres::<conninfo>/<reponame>cmd_list() queries refs table, outputs @<target> HEAD for symbolic HEADcmd_fetch() copies all objects from PG that local doesn't have via odb_foreachcmd_push() copies local objects to PG, updates refs via SQL, auto-creates HEADstatic void cmd_push(PGconn *conn, int repo_id, git_repository *pg_repo, const char *git_dir, const char *first_line)/Users/andrew/code/gitgres/backend/Makefile
gitgres-backend and git-remote-gitgresSHARED_OBJS = odb_postgres.o refdb_postgres.o writepack_postgres.oall: gitgres-backend git-remote-gitgresgitgres-backend: main.o $(SHARED_OBJS)git-remote-gitgres: remote_helper.o $(SHARED_OBJS)/Users/andrew/code/gitgres/ext/gitgres.c - PG_MODULE_MAGIC entry point
/Users/andrew/code/gitgres/ext/git_oid_type.c - Custom 20-byte git_oid type with hex I/O, all 6 comparison operators, btree cmp, hash function
/Users/andrew/code/gitgres/ext/sha1_hash.c - C SHA1 via OpenSSL EVP API, includes varatt.h for PG 16
/Users/andrew/code/gitgres/ext/tree_parse.c - SRF tree parser returning (mode, name, entry_oid)
/Users/andrew/code/gitgres/ext/sql/gitgres--0.1.sql - CREATE TYPE, operators, operator classes, C functions
/Users/andrew/code/gitgres/ext/gitgres.control - Extension metadata
/Users/andrew/code/gitgres/ext/Makefile - PGXS build linking OpenSSL
/Users/andrew/code/gitgres/import/gitgres-import.sh - Shell script importing repos via git plumbing + psql, base64-encodes binary content
/Users/andrew/code/gitgres/test/test_helper.rb - Base class with PG connection, transaction wrapping, write_object/git_hash_object/create_test_repo helpers
/Users/andrew/code/gitgres/test/schema_test.rb - 7 tests on table existence and constraints
/Users/andrew/code/gitgres/test/object_hash_test.rb - 5 tests comparing git_object_hash against git hash-object
/Users/andrew/code/gitgres/test/object_store_test.rb - 5 tests on write/read/idempotent/nonexistent/multiple types
/Users/andrew/code/gitgres/test/tree_parse_test.rb - 3 tests parsing real git trees, recursive walk
/Users/andrew/code/gitgres/test/commit_parse_test.rb - 2 tests parsing commits with/without parents
/Users/andrew/code/gitgres/test/ref_test.rb - 6 tests on create/CAS/force/symbolic/delete
/Users/andrew/code/gitgres/test/roundtrip_test.rb - 2 integration tests importing and verifying objects
/Users/andrew/code/gitgres/test/remote_helper_test.rb - Just written, not yet run - 2 tests for push+clone roundtrip and incremental push+fetch via git client
/Users/andrew/code/gitgres/Makefile - Top-level delegates to backend/ext, createdb, test targets
/Users/andrew/code/gitgres/.gitignore - Build artifacts, binaries, editor files, .claude/
/Users/andrew/code/gitgres/README.md - Setup, usage, SQL querying examples, how it works, prior art section
Errors and fixes:
varatt.h. Fixed by adding #include "varatt.h" after #include "postgres.h".cp and install both return "Operation not permitted". Not fixed - accepted as a limitation, core functionality works without the extension.cmd_push_with_first_line that didn't exist. Rewrote the entire file cleanly, passing the first consumed line as a parameter to cmd_push().-Wcast-function-type-strict warnings from OpenSSL 3 headers during PGXS build. Not errors, extension compiles and links successfully (gitgres.dylib produced).Problem Solving:
brew install libgit2 (got 1.9.2_1)diff -r --exclude=.git shows IDENTICALgit_repository_new()All user messages:
Pending Tasks:
remote_helper_test.rb along with the full test suiteCurrent Work:
I had just finished building and manually testing git-remote-gitgres (the git remote helper). The tests showed:
git push pg main -> * [new branch] main -> maingit clone gitgres::dbname=gitgres_test/helper_test -> working tree identical to sourceI then wrote test/remote_helper_test.rb with two tests (push+clone roundtrip, incremental push+fetch) but had NOT yet run it or committed anything. The existing 30 tests still pass.
Optional Next Step:
Run the full test suite including the new remote_helper_test.rb, then commit all the remote helper changes. The user said "yes build it" in response to the remote helper proposal, and the build/manual testing is complete. The remaining steps are running the automated tests and committing. Per CLAUDE.md: "Run full tests before committing" and "Don't commit without asking first."
If you need specific details from before compaction (like exact code snippets, error messages, or content you generated), read the full transcript at: /Users/andrew/.claude/projects/-Users-andrew-code-gitgres/a7fa3d7a-6e41-439d-944a-ce9d93c06d75.jsonl Please continue the conversation from where we left off without asking the user any further questions. Continue with the last task that you were asked to work on.
Let me run the full test suite including the new remote helper tests.
The RemoteHelperTest committed its transaction (needed for the helper's separate connection), which left a test_repo row in the repositories table. Subsequent tests that try to INSERT test_repo inside their own transaction fail. I need to fix the RemoteHelperTest teardown to also clean up test_repo, and clean the stale row now.
The problem: RemoteHelperTest#setup calls super (which inserts test_repo inside a BEGIN), then immediately commits that transaction. The teardown cleans up @remote_repo but never deletes the committed test_repo row. Let me fix that and clean up the stale data.
Now fix the teardown to clean up test_repo as well, since it was committed outside the transaction:
All 32 tests pass, 86 assertions, zero failures. The remote helper tests run the full push/clone roundtrip and incremental push/fetch through a real git client.
Ready to commit. Want me to go ahead?
yes commit it
Committed as bff2e8c. Four files: the remote helper implementation, updated Makefile and .gitignore, and the integration tests.
in theory, could this be used as the backend for forgejo?
In theory, yes. In practice there's a big gap between what exists now and what Forgejo would need.
Forgejo (and Gitea before it) interacts with git in two ways: shelling out to the git binary and using go-git for some read operations. Both assume repos live on the filesystem. The remote helper protocol only covers push/fetch/clone, which is a fraction of what a forge does. Forgejo also reads objects directly for blame, diff, log, file browsing, and merge operations.
There are a few paths you could take:
git2go with the postgres backends. Forgejo is written in Go. git2go provides Go bindings to libgit2. You could wire up odb_postgres and refdb_postgres through git2go so Forgejo's libgit2-based code paths read from Postgres directly. But Forgejo's libgit2 support has been patchy and they've been moving away from it toward shelling out to git.
A git protocol server. Instead of the remote helper (which is client-side), you'd run something that speaks git's smart HTTP or SSH transport, backed by the Postgres storage. Forgejo could then treat it like any other git remote. This handles push and fetch but still doesn't help with the many places Forgejo reads repo data directly.
A FUSE filesystem. Mount a virtual filesystem that presents the Postgres-backed storage as a normal .git directory. Everything that shells out to git would work transparently. This is the most compatible approach but also the most complex, and FUSE performance can be tricky.
Fork Forgejo's git abstraction layer. Forgejo has a modules/git package that wraps all git operations. You could replace that layer to talk to Postgres instead of the filesystem. This is probably the most realistic path but it's a large surface area.
The honest assessment: the storage layer works and the protocol bridge works, but a forge does far more than push and fetch. Forgejo reads trees for file browsing, computes diffs for pull requests, walks commit history for blame, runs merge operations. Each of those would need a Postgres-backed implementation. It's months of work, not days.
The closest real-world analogue is what GitLab did with Gitaly, and even they kept the filesystem as the actual storage, just moved it behind an RPC service. Replacing the filesystem entirely is something nobody has shipped at scale, which is partly why the JGit DHT experiment got abandoned.
you seem to not be thinknig very creatively
You're right. I was thinking about how to make Forgejo's existing code work with Postgres, when the better question is what's the shortest path from git-data-in-Postgres to forge functionality.
The data is already in the right shape. commits_view is your commit log page. git_ls_tree_r is your file browser. git_commit_parse gives you commit detail pages. Blob content is a single SELECT. The remote helper handles push and fetch. You already have most of what a forge's read path needs, and it's all just SQL.
So instead of shoving Postgres behind Forgejo's git layer, you build the forge read path directly on top of the SQL functions. Commit history is SELECT * FROM commits_view WHERE repo_id = $1 ORDER BY authored_at DESC. File tree is git_ls_tree_r(repo_id, tree_oid). Showing a file is joining that against the objects table to get the blob content. Refs listing is a table scan.
Diff is the interesting one. You could compute it in SQL by comparing two tree walks, or you could let libgit2 do it through the Postgres backends since it already knows how to diff two trees given an ODB.
The write path (push/pull/merge) still goes through git, which the remote helper already handles. Merge could be done through libgit2 with the Postgres backends directly, since libgit2 has merge support and it already knows how to read and write objects through odb_postgres.
The piece that's actually missing is thin: a web app that serves these SQL queries as HTML pages and speaks git smart HTTP for push/fetch. That's a small, focused thing compared to adapting Forgejo's entire git abstraction layer.
if we just put git data in postgres, then it's queryable in postgres without needing to talk to git
Right. That's the whole point of the project. Once objects and refs are in Postgres, everything git does is just queries against those tables. A forge is a web UI over commit history, file trees, and diffs. All of that is already sitting in rows.
Forgejo doesn't need to be in the picture at all. You don't adapt an existing forge to use Postgres storage. You skip the forge and build the UI directly on the database, the same way you'd build any CRUD app on top of Postgres.
yes but forgejo
Forgejo already stores everything except git data in Postgres. Issues, pull requests, users, permissions, webhooks - all in the database. The git repos are the one thing still on the filesystem.
If the git data is also in Postgres, Forgejo's git layer becomes SQL queries against the same database it's already connected to. You replace modules/git (the package that shells out to git) with queries against the objects and refs tables. No subprocess spawning, no filesystem I/O, no parsing git's text output.
And you get things that are currently impossible: join commits against issues in a single query, search across all repos without separate indexing, query file contents without checkout. Cross-repo operations that currently require multiple git processes become one SELECT.
The fork would be targeted. Forgejo's modules/git is the boundary. Everything above it (the web UI, the API, the issue tracker, the PR review system) stays the same. You just change what's underneath that package from "shell out to git and parse stdout" to "run a SQL query."
exactly what i was going for, this makes deploying a forgejo instance much easier
One Postgres instance, no filesystem management for repos. No worrying about disk layout, NFS mounts for HA, or backup scripts that need to coordinate between database dumps and git repo tarballs. pg_dump gets everything.
Scaling looks different too. Postgres replicas give you read scaling for the web UI for free. You don't need Gitaly or a similar RPC layer to distribute git operations across machines.
Want to start looking at Forgejo's modules/git to scope out what the replacement layer would look like?
and also gives potential integrations with LISTEN, replication and other postgres native features
Yeah. LISTEN/NOTIFY gives you webhooks without polling. A trigger on the refs table fires on push, and any connected client gets notified instantly. CI runners, deploy hooks, chat bots, mirror sync - all just LISTEN ref_update on a Postgres connection they already have.
Logical replication lets you stream git data to other systems selectively. Replicate one repo's objects to another Postgres instance, or replicate just the commits_view to a data warehouse for analytics. Partial replication of git data isn't something you can do with git itself.
Row-level security means multi-tenant hosting without application-level permission checks on every git read. Postgres enforces repo visibility at the query level.
Foreign data wrappers could federate repos across Postgres instances while still looking like one database to the application.
And pg_cron or background workers could handle garbage collection, repacking (if you ever add delta compression at the storage level), and stats aggregation without any external job scheduler.
All of this is just Postgres doing what Postgres already does. None of it needs building.
yes let's look at forgejo's git layer, no need for code changes right now, just research, and we'll make a forgejo markdown doc of what we find
Forgejo is already cloned at /private/tmp/forgejo. Let me research the git layer in parallel.
Now let me write up the findings.
Written to forgejo.md. The main findings:
modules/git is the single boundary: ~15k lines, 84 files, all shell-out to the git binary, no go-git or libgit2Repository, Commit, Tree, Blob, etc.) are concrete structs, not interfaces, so the whole package gets replaced rather than swapped behind an abstractionmodules/git (the entire forge: UI, API, issues, PRs, CI, webhooks) stays untouchedmodels/git/ branch metadata tables that currently duplicate git state merge into the gitgres schema as a single source of truthbasically makign the "backend" file storage part almost completely disappear
Right. The filesystem is the only part of Forgejo that needs its own backup strategy, its own scaling story, its own HA setup. Remove it and the whole deployment is just Postgres. One connection string, one backup, one replication stream. The operational surface shrinks to something a single pg_dump cron job covers entirely.
and things like gitlfs would would too
Yeah, LFS objects are just blobs with a size. Store them in the same objects table or a dedicated lfs_objects table with the content in a bytea column (or lo large objects for files over 1GB). The LFS API is a simple HTTP JSON protocol for uploading and downloading content by SHA256. Wire that to INSERT and SELECT and you're done.
Right now LFS is a whole separate storage problem. Forgejo supports local disk, S3, MinIO, Azure Blob. Each needs its own configuration, credentials, backup strategy, lifecycle management. Put it in Postgres and it disappears into the same pg_dump.
The LFS lock API (file locking for binary assets) becomes a table with a row-level lock or advisory lock. Concurrent access is something Postgres already handles, instead of the application having to coordinate it.
yeah, but lfs can still use s3 blob store, and store the references in git(postgres)
That's the better design. The LFS pointers (the small JSON files that reference the actual content) are already git objects, so they're in Postgres with everything else. The actual large files go to S3 where bulk storage is cheap. Postgres stores the metadata and handles the API, S3 handles the bytes.
You get the best of both: queryable LFS metadata in Postgres (which files, which versions, who uploaded, total size per repo) without bloating the database with multi-gigabyte binary assets. And the backup story is still clean since S3 has its own durability guarantees.
commit this
Committed as ec8c6bc.
also add an mit license
my name is Andrew Nesbitt
Fixed, sorry about that. Want me to add the Forgejo doc reference to the README now?
and reference the forgejo doc in the readme
Done. Added a short Forgejo section to the README that links to forgejo.md.
push it
Four commits going up: remote helper, Forgejo research, MIT license, README update.
Pushed.
lets make a draft blog post about this idea in ~/code/nesbitt.io/_drafts
Draft is at _drafts/git-in-postgres.md. Tried to match your voice from the other posts. Take a look and let me know if anything needs adjusting.
open it in vs code
Let me create the project structure and start with the SQL foundation.
Opened.
Solid draft. A few notes.
The opening is strong — "a content-addressable object store and a set of named references" immediately frames git as a data model, not a program. Good.
The "git is a protocol not a program" line isn't in here anywhere. That's your thesis statement. It should probably appear early, maybe right after you describe the on-disk format as an implementation detail. Something like: the protocol for synchronising these objects and refs is the valuable part. Git-the-program is just one implementation. The on-disk format is its storage backend, and you can swap it.
The JGit DHT caveat at the end undersells it. You're not hedging because you're uncertain — you've already built the thing and it works. The caveat reads like you're pre-apologising. The honest version is more like: this is designed for the self-hosted case where simplicity beats scaling, and at that scale Postgres handles it easily. The JGit DHT comparison actually strengthens your argument because their problem was Google-scale access patterns, which is exactly the wrong comparison for a single-instance Forgejo deployment.
The "What this means for forges" section could use one more beat: the fact that Forgejo already mirrors branch metadata from git into Postgres (models/git/branch.go). That duplication is the tell. They already know the filesystem isn't enough for the data they need. Gitgres eliminates the dual-system problem they're already working around.
You don't mention the remote helper much. The fact that git push and git clone work through standard git commands against a Postgres backend is worth a sentence or two more. That's the proof that you're treating git as a protocol — the entire git client works unchanged.
The storage section is good but could use the GitHub/Spokes point we just discussed. If GitHub stores three full copies of every repo across data centres without aggressive packing, the storage tradeoff of full objects in Postgres is the same design choice at a different scale.
Missing: any mention of this being the second post in a series. Link back to "Package managers shouldn't use git as a database" and set up the connection — that post identified the problem, this one builds the tool.
Let me find the package managers post to link back to.
Good, I have the URL pattern. Now let me apply all the edits.
Let me go through and fix the short punchy sentences, signposting, parallel construction, and restatement.
Updated. Main changes:
some feedback from chatgpt: Strong piece. Clear thesis, concrete implementation, and it fits your broader theme (protocols > implementations, operational simplicity). A few focused thoughts.
What works well
The inversion is compelling The “git is a protocol, not a storage format” line carries the post. It reframes the idea from novelty to architectural inevitability.
Right level of proof • Two tables • libgit2 backend • Remote helper • Real SQL examples This keeps it out of thought-experiment territory.
The Forgejo angle is the payoff The operational simplification argument (one datastore, one backup, one replication model) is the strongest practical motivation.
Tone Matter-of-fact, no hype. Consistent with your recent posts.
⸻
Where it could be tighter
Right now the reader spends a while thinking “interesting experiment”. The Forgejo/self-hosted forge use case is the real justification.
Consider a one-line framing near the top:
This isn’t about replacing git everywhere. It’s about simplifying small forge deployments where operational complexity matters more than storage efficiency.
That anchors expectations.
⸻
You imply it later, but this idea lives or dies on scope.
Something like:
This is not a GitHub-scale design. It’s a “hundreds of repos on one Postgres instance” design.
That heads off the obvious objection early.
⸻
Right now it sounds like every version is fully stored uncompressed. Postgres will compress large values automatically.
Even a short line:
Large blobs are TOASTed and compressed automatically, so the worst-case growth is less dramatic than the raw size comparison suggests.
Without that, storage criticism feels stronger than it is.
⸻
You list several “hard” things in sequence, which slightly shifts the tone from “practical” to “research project”.
You could compress to: • Diff/merge/blame stay in libgit2 • SQL implementations are interesting but optional
The key message: you don’t have to reimplement git logic.
⸻
Right now it ends on a historical comparison (JGit DHT). That’s informative but not memorable.
A stronger close might echo the core idea:
Something along the lines of:
Git doesn’t need to live on a filesystem. It just needs a content-addressable store and a way to move objects around. Postgres happens to be very good at that.
⸻
Likely reader reactions (and how covered you are)
Objection Covered? “Storage will explode” Yes, but add TOAST note “Performance?” Implied, maybe add one sentence: pack protocol still used “Why not just use git?” Answered via Forgejo ops simplification “This is heresy” Tone keeps it calm
⸻
Fit with your recent writing
This connects nicely to: • “git as a database (doesn’t work)” • your general theme of misplaced boundaries • operational pragmatism over purity
It reads like part of a larger argument: move data where it’s actually used.
⸻
One small editorial suggestion
This sentence is strong:
Git-the-program is just one implementation of that protocol
You could make it a standalone paragraph for emphasis.
⸻
Overall
One of your stronger pieces recently: • concrete but conceptual • contrarian but practical • no hype, no fluff
If you tweak anything, I’d focus on: • earlier scope framing • one line about Postgres compression • a sharper final paragraph.
Now let me create all the files. I'll work on multiple in parallel where possible.
Good feedback, though some of those suggestions (standalone paragraph for emphasis, the suggested closing line) would land as exactly the kind of punchy signposting you asked me to avoid. Let me take what's useful and leave the rest.
Changes:
we should frame the post as "I wonder if this would work" to "so yeah I built it"
The arc now reads as: here's a question (if git is a protocol not a storage format, the data model should fit in two tables), here's what that would look like (schema, SQL functions, libgit2 callbacks), I wanted to see if it actually worked so I built it (gitgres), and here's where it leads (single-Postgres forge deployments).
The key changes: the schema section is now hypothetical ("if that's true... should fit... would be computed"), the reveal is "I wanted to see if that actually worked, so I built it", and the Forges section opens with "the reason I care about this isn't the SQL queries" to pivot from the experiment to the motivation.
and we could use https://github.com/Homebrew/homebrew-core as the example of essentailly using git as a database then flipping it to a database that you can send pull requests to
Now the opening walks through Homebrew's specific problem: they had to leave git to fix the read path but can't leave git for the write path because the collaboration tooling lives there. Gitgres is the answer to both sides: a database you can send pull requests to.
avoid short punch sentances and avoid all signposting and paralllel construction and restatement and trailing sentance fragments
Main things I cleaned up:
Now let me create the test database and verify the SQL functions work.
Let me verify the hash function matches git's output.
Hashes match. Now let me load the views and verify the write/read cycle works.
Now let me clean up the test data and finish task 1, then start on the import script, tests, and libgit2 backend in parallel.
Now let me look at the libgit2 1.9 API to make sure I write the backend correctly.
gitgres/ Makefile # top-level, delegates to sub-makes sql/ schema.sql # tables, indexes functions/ object_hash.sql object_read_write.sql tree_parse.sql commit_parse.sql ref_manage.sql views/ queryable.sql # materialized views ext/ # Postgres C extension Makefile # PGXS gitgres.c # PG_MODULE_MAGIC, init git_oid_type.c # 20-byte OID type with operators sha1_hash.c # C SHA1 via OpenSSL tree_parse.c # C tree entry parser gitgres.control sql/gitgres--0.1.sql # CREATE TYPE, CREATE FUNCTION backend/ # libgit2 pluggable backend Makefile main.c # CLI entry point (serves repos) odb_postgres.c # git_odb_backend implementation odb_postgres.h refdb_postgres.c # git_refdb_backend implementation refdb_postgres.h writepack_postgres.c # packfile receive handling import/ gitgres-import.sh # shell script: git plumbing + psql test/ test_helper.rb schema_test.rb object_hash_test.rb object_store_test.rb tree_parse_test.rb commit_parse_test.rb ref_test.rb roundtrip_test.rb # push then clone, diff working treesCREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE repositories ( id serial PRIMARY KEY, name text NOT NULL UNIQUE, created_at timestamptz NOT NULL DEFAULT now());
-- Git objects: raw content without the git header.-- Type uses git's internal numbering: 1=commit, 2=tree, 3=blob, 4=tag.-- OID = SHA1("<type> <size>\0<content>"), computed on write.CREATE TABLE objects ( repo_id integer NOT NULL REFERENCES repositories(id), oid bytea NOT NULL, type smallint NOT NULL, size integer NOT NULL, content bytea NOT NULL, PRIMARY KEY (repo_id, oid));CREATE INDEX idx_objects_oid ON objects (oid);
CREATE TABLE refs ( repo_id integer NOT NULL REFERENCES repositories(id), name text NOT NULL, oid bytea, -- NULL for symbolic refs symbolic text, -- NULL for direct refs PRIMARY KEY (repo_id, name), CHECK ((oid IS NOT NULL) != (symbolic IS NOT NULL)));
CREATE TABLE reflog ( id bigserial PRIMARY KEY, repo_id integer NOT NULL REFERENCES repositories(id), ref_name text NOT NULL, old_oid bytea, new_oid bytea, committer text NOT NULL, timestamp_s bigint NOT NULL, tz_offset text NOT NULL, message text, created_at timestamptz NOT NULL DEFAULT now());CREATE INDEX idx_reflog_ref ON reflog (repo_id, ref_name, id);CREATE MATERIALIZED VIEW commits_view ASSELECT o.repo_id, o.oid AS commit_oid, encode(o.oid, 'hex') AS sha, c.tree_oid, c.parent_oids, c.author_name, c.author_email, to_timestamp(c.author_timestamp) AS authored_at, c.committer_name, c.committer_email, to_timestamp(c.committer_timestamp) AS committed_at, c.messageFROM objects o, LATERAL git_commit_parse(o.content) cWHERE o.type = 1;
CREATE MATERIALIZED VIEW tree_entries_view ASSELECT o.repo_id, o.oid AS tree_oid, e.mode, e.name, e.entry_oidFROM objects o, LATERAL git_tree_entries(o.content) eWHERE o.type = 2;Usage: gitgres-import <repo-path> <conninfo> <repo-name>git remote add pg gitgres::dbname=gitgres/myrepogit push pg maingit clone gitgres::dbname=gitgres/myrepostatic void cmd_push(PGconn *conn, int repo_id, git_repository *pg_repo, const char *git_dir, const char *first_line)SHARED_OBJS = odb_postgres.o refdb_postgres.o writepack_postgres.oall: gitgres-backend git-remote-gitgresgitgres-backend: main.o $(SHARED_OBJS)git-remote-gitgres: remote_helper.o $(SHARED_OBJS)