Public

Using PostgreSQL to Store Git DataPublic

The user aimed to integrate Git data storage directly into a PostgreSQL database to simplify deployment and improve integration with tools like Forgejo. They successfully built and tested a helper that allows standard Git clients to push and clone using this setup. The conversation also explored how this approach eliminates filesystem dependencies, enables native database features for notifications and replication, and could streamline Forgejo by replacing its Git layer with SQL queries.

Agent

Claude Code

Model

Claude Opus 4.6

Shared

Feb 23, 3:16 PM

Messages554

Implement the following plan:

Gitgres: PostgreSQL as a Git Backend

Context

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.

Project Structure

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 trees

Schema

Depends on pgcrypto (available on all managed Postgres). OIDs stored as bytea (20 bytes raw).

sql

PL/pgSQL Functions

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 oid
git_object_read(repo_id, oid) -- return (type, size, content)
git_object_read_prefix(repo_id, prefix, prefix_len) -- abbreviated OID lookup
git_tree_entries(content) -- parse binary tree into (mode, name, entry_oid) rows
git_commit_parse(content) -- parse commit into tree_oid, parent_oids[], author fields, committer fields, message
git_ref_update(repo_id, name, new_oid, old_oid, force) -- compare-and-swap with SELECT FOR UPDATE
git_ls_tree_r(repo_id, tree_oid, prefix) -- recursive tree walk returning (mode, path, oid, type)

Materialized Views

sql

libgit2 Backend (C)

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 Backend (`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=$2
read_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 column
write(backend, oid, data, len, type) -- INSERT INTO objects ... ON CONFLICT DO NOTHING
exists(backend, oid) -- SELECT 1 FROM objects WHERE ...
exists_prefix(full_oid, backend, short_oid, prefix_len) -- prefix existence check
foreach(backend, cb, payload) -- SELECT oid FROM objects WHERE repo_id=$1, call cb for each
writepack(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 each
free(backend) -- close PG connection

The backend struct holds a PGconn* and the repo_id.

Refdb Backend (`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_reference
iterator(iter_out, backend, glob) -- SELECT name, oid, symbolic FROM refs WHERE name LIKE ..., return custom iterator
write(backend, ref, force, who, message, old_id, old_target) -- CAS update using a transaction with SELECT FOR UPDATE
rename(ref_out, backend, old_name, new_name, force, who, message) -- UPDATE refs SET name=$1
del(backend, ref_name, old_id, old_target) -- DELETE with CAS check
has_log / ensure_log / reflog_read / reflog_write / reflog_rename / reflog_delete -- operate on the reflog table
lock / unlock -- use Postgres advisory locks (pg_advisory_xact_lock)
free(backend) -- close connection

CLI Entry Point (`main.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:

Parses the connection string and repo name
Connects to Postgres
Creates a libgit2 repo with the Postgres ODB and refdb backends
Speaks the transport protocol over stdin/stdout

libgit2 has git_transport_register for custom transports, or we can use the simpler git_remote_callbacks approach.

C Extension (`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 pgcrypto
git_tree_entries_c(content) RETURNS SETOF (mode, name, oid): C pointer arithmetic instead of PL/pgSQL byte walking

Built with PGXS, links against OpenSSL.

Import Script (`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 OIDs
Pipe through git -C <repo> cat-file --batch to get type + content
Compute hashes (or trust git), INSERT into objects table via psql COPY
git -C <repo> show-ref to get all refs, INSERT into refs table

Build Order

Schema + 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.

Testing

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.

Key Decisions

bytea(20) for OIDs: half the storage of hex text, faster comparisons. The C extension upgrades this to a proper git_oid type.
Content stored without git header: type and size in columns, OID recomputed from type+content on write for verification.
libgit2 for the protocol bridge: it handles pack negotiation, delta resolution, ref advertisement, capabilities. We only implement storage.
writepack delegates to libgit2 indexer: when receiving a push, we let libgit2 parse the packfile and extract objects, then store them individually. We don't need our own pack parser.
pgcrypto for pure SQL SHA1: available on all managed Postgres services.
No delta compression in pack generation initially: libgit2 handles this when building packs for fetch.

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 start by checking the current state of the project and then work through the build order.

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.

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.

Used TaskUpdate

Let me create the project structure and start with the SQL foundation.

Now let me create all the files. I'll work on multiple in parallel where possible.

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.

Used TaskUpdate

Now let me look at the libgit2 1.9 API to make sure I write the backend correctly.

Readodb_backend.h

Readrefdb_backend.h

Readtypes.h

Let me check a few more key headers and then start writing the C code and tests in parallel.

Readodb.h80 lines

Readindexer.h

Readtransport.h

Let me check a few more headers I need for the writepack and reflog implementation.

Readodb_backend.h

Readreflog.h

Readrefdb_backend.h40 lines

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.

Readodb.h80 lines

Readrepository.h

Now let me check a couple more things and then write all the C files.

Greppedgit_odb_add_backend

Greppedgit_refdb_new

Greppedgit_reference_create\b

Greppedgit_refdb_new

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.

Ran sub-agentWrite odb_postgres C files

Ran sub-agentWrite refdb_postgres C file

Ran sub-agentWrite main.c and Makefiles

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.

Ran sub-agentWrite C extension files

Ran sub-agentWrite all test files

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.

Readsha1_hash.c

Readtree_parse.c

Readgit_oid_type.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.

Grepped#define VARDATA\b

200 of 554

Gitgres: PostgreSQL as a Git Backend

Context

Project Structure

Schema

PL/pgSQL Functions

Materialized Views

libgit2 Backend (C)

ODB Backend (odb_postgres.c)

Refdb Backend (refdb_postgres.c)

CLI Entry Point (main.c)

C Extension (ext/)

Import Script (import/gitgres-import.sh)

Build Order

Testing

Key Decisions

Gitgres: PostgreSQL as a Git Backend

Context

Project Structure

Schema

PL/pgSQL Functions

Materialized Views

libgit2 Backend (C)

ODB Backend (odb_postgres.c)

Refdb Backend (refdb_postgres.c)

CLI Entry Point (main.c)

C Extension (ext/)

Import Script (import/gitgres-import.sh)

Build Order

Testing

Key Decisions

ODB Backend (`odb_postgres.c`)

Refdb Backend (`refdb_postgres.c`)

CLI Entry Point (`main.c`)

C Extension (`ext/`)

Import Script (`import/gitgres-import.sh`)

ODB Backend (`odb_postgres.c`)

Refdb Backend (`refdb_postgres.c`)

CLI Entry Point (`main.c`)

C Extension (`ext/`)

Import Script (`import/gitgres-import.sh`)