Experimenting with Database as Git Storage Backend
Experimenting with Database as Git Storage Backend
Agent
Claude Code
Model
Claude Opus 4.6
StartedFeb 23, 3:16 PM
Shared
Feb 23, 3:16 PM
Messages33
Downloads80
I'd like to do an experiment with using postgresql as the backend for git, two different methods to try: postgres extension or as pure sql, context: Sure. Here's where we got to:
Starting point: You asked whether Postgres could be used as a git backend. I gave a standard answer about libgit2 pluggable backends and performance tradeoffs.
The reframe: You connected it to your blog post about package managers using git as a database. Instead of telling ecosystems to migrate away from git, what if you fix git's storage layer by putting a real database underneath? The interface stays git, the backend becomes queryable.
Key insight: "Pull requests to a database." You keep git's collaboration model (branching, diffing, review, audit trail) but gain everything a database offers (point queries, indexing, replication).
Materialized views: Once objects are in Postgres, you can skip the git protocol entirely for optimized reads. Historical package versions become trivial queries. Reproducible builds become a SQL query against a point-in-time view.
Homebrew as the example: Mirror homebrew-core into Postgres-backed git storage, serve both the git protocol and the JSON API from the same database. No dual-system maintenance, no sync issues.
Postgres ecosystem: LISTEN/NOTIFY for real-time updates, logical replication for mirrors, foreign data wrappers for cross-registry joins, row-level security for private packages. Registries keep rebuilding these features badly.
Forgejo simplification: If repos live in Postgres alongside everything else, deploying a forge becomes "give me a Postgres connection string." One database, one backup, no filesystem to manage.
Extension vs pure SQL: A CREATE EXTENSION git would be elegant but kills managed Postgres deployability. Better to start with pure SQL tables and PL/pgSQL functions that work everywhere, then optionally build the C extension for performance.
Prior art found: libgit2-backends (SQLite, Redis, MySQL backends exist), git_fdw (read-only SQL queries over git repos), gitbase (SQL interface from source{d}, now defunct), MergeStat (SQLite variant). Nobody's built the write path — where git push lands objects directly in the database.
Next step: Claude Code session to build both the pure SQL and extension versions, using homebrew-core as the test case. Blog post as the third in a trilogy after "package managers shouldn't use git as a database" and the refs post. https://github.com/Homebrew/homebrew-core would make a good test once it's working (don't start with such a big repo though)
Excellent research. Now let me design the plan. I have a clear picture of:
Let me now plan the two approaches:
Pure SQL approach: Tables + PL/pgSQL functions that implement git operations. This works on any Postgres including managed.
Extension approach: C extension with native types and functions for performance.
I should build the pure SQL version first since it's more portable. I'm structuring this in layers: an objects table storing the content-addressable store with OID, type, size and data; a refs table tracking branch and tag pointers with symbolic references; and a reflog for append-only history.
Then I need materialized views to make the data queryable—parsed commits, trees, and relationships between them. The core functions would handle hashing objects, reading and writing to the store, updating refs, walking commit history, listing tree contents, and diffing trees, plus import/export utilities. After that I can build the extension version with optimizations.
For testing, I should start small—import a test repo into Postgres, verify the basic operations work, then scale up to something like homebrew-core. Before diving in though, I should clarify the scope and constraints with the user.
Good research. Let me now design the implementation plan.
Excellent, I now have a thorough plan. Let me write it to the plan file and then ask the user a few clarifying questions before finalizing.
Let me think about what questions I should ask:
Actually, let me just write the plan and present it. The user's context was pretty clear on the approach. Let me check if there are specific things I need to clarify.
The user was explicit about starting with pure SQL, so I'll document that decision and move forward with writing out the plan file.
Good research from both agents. Let me write the plan.
The user wants to build a system where PostgreSQL serves as a git backend storage. Two approaches: pure SQL and a Postgres extension. Let me understand the scope:
This is a significant project. Let me plan this out before diving in.
The user has a clear vision from the conversation context. Let me enter plan mode to design the approach.
The user is surprised by Ruby being involved. Let me re-read the context. They said "Claude Code session to build both the pure SQL and extension versions." They want to build SQL functions and a C extension, not a Ruby bridge.
Looking back at the context: "Extension vs pure SQL: A CREATE EXTENSION git would be elegant but kills managed Postgres deployability. Better to start with pure SQL tables and PL/pgSQL functions that work everywhere, then optionally build the C extension for performance."
The user's CLAUDE.md says "Prefer ruby for scripting tasks" but the user clearly doesn't want Ruby here. They want:
The bridge is a separate concern that can come later. For now I should focus on getting git objects into Postgres and queryable—the import script can be a shell script using git CLI and psql, and I can add materialized views and the C extension after that.
Fair point. Your CLAUDE.md says "prefer ruby for scripting" but this is really a SQL + C project. Let me rethink.
The bridge between git and the database needs to speak the git pack protocol, which requires a program somewhere. But that doesn't need to be the first thing we build.
Good, the user wants the bridge in C using libgit2. This makes sense -- libgit2 has the pluggable backend interface, so we implement the ODB and refdb backends that store in Postgres, and libgit2 handles all the protocol work.
Let me revise the plan:
Actually, the libgit2 backend and the Postgres extension could share some code.
I'm organizing this into three directories: SQL schemas and functions, a Postgres extension with the git_oid type and C functions, and the libgit2 backend layer that implements the ODB and refdb interfaces using libpq to query the database. Once Postgres is registered as the backend, libgit2 handles all the git protocol details -- pack negotiation, delta resolution, ref handling -- and my code just needs to execute the right SQL queries.
That's the cleaner approach. libgit2 handles all the protocol work; we just implement the storage layer. Let me revise the plan.
The user doesn't want to proceed with this tool use. The tool use was rejected (eg. if it was a file edit, the new_string was NOT written to the file). STOP what you are doing and wait for the user to tell you how to proceed.Let me explore the current state of the repo and research the git object model to design this properly.
>ls -la /Users/andrew/code/gitgres/Empty directory. Let me research the git internals and prior art to design the schema properly.