Of course, we can’t leave out a mention of Fossil here — the SCM system built by and for SQLite.
https://fossil-scm.org/
I use Fossil for all of my long term projects. It can even import Git repositories if you want to try it out.
Today I was working on a semester paper for a non-technical class. It is versioned in fossil and I have all my miscellaneous ideas, initial outline, and the paper guidelines in the Wiki. The branching also makes much more sense, and I’ve used it for major revisions of the paper or its structure.
Fossil is legitimately awesome, and I lament the fact that Git gained popularity over it.
Fossil is great. Not only is it a full suite of tools associated with the repository (discussions, tickets, wiki) but the tool is a single >10mb binary and can run as a web server (or CGI-like interface) for remote hosting.
The web server that powers fossil was also written by its author! It’s nice that unlike git instaweb you don’t need to install an additional web server just to see a read only view of your commits.
And fossil itself is an SQLite database!
> fossil itself is an SQLite database
Can anyone explain what this means and how it works?
Fossil itself is a C binary, not a database. Maybe they meant that Fossil’s source code is hosted in Fossil, or that Fossil repositories are SQLite files? I don’t exactly know either.
How much does it take advantage of being a DB underneath?
yeah fossil is great, but can fossil import the linux kernel (already working on the next post)
Still halfway through reading, but what you've made can unlock a lot of use cases.
> I tried SQLite first, but its extension API is limited and write performance with custom storage was painfully slow
For many use cases, write performance does not matter much. Other than the initial import, in many cases we don't change text that fast. But the simpler logistics of having a sqlite database, with the dual (git+SQL) access to text is huge.
That said, for the specific use case I have in mind, postgres is perfectly fine
SQLite is fine right up until you want concurrent writers. Once you need multiple users, cross-host access, or anything that looks like shared infra instead of a local cache, the file-locking model stops being cute and starts setting the rules for the whole design. For collaborative versioning, Postgres makes more sense.
For a distributed VCS, what would be the need for such things? Even if it were a really big project, how many writes could be going on that this becomes a bottleneck? I don't see it but maybe you have a situation in mind.
In the current environment, even a distributed VCS may have concurrent agents modifying it on different branches.
The problem i faced is mostly importing large repos. But normal use should be fine.
Also SQLite in WAL/WAL2 mode is definitely not amy slower for writing than Postgres either.
The single-file simplicity of SQLite is a huge win for self-hosted apps. I've been using SQLite in WAL mode for a single-user app and it handles concurrent reads from the API while background workers write without issues. Backup is just cp. For anything that doesn't need multi-user concurrent writes, it's hard to justify the operational overhead of Postgres.
ko
if you want to use key value store using sqlite then you can try : https://github.com/hash-anu/snkv
in which i am directly accessing b-tree layer and not consuming query layer.
for kv workloads it is much faster compare to sql.
and yeah you will get same benefits of sqlite storage engine.
Yeah, I get that, and I'm fully on your side. SQLite would have been a nice fit. The only downside is the delta compression problem. Creating an extension for SQLite works, but it's slow. I had two options:
1) Do the delta compression and caching and so on on the pgit side and lose SQL queryability (or I need to do my own), or
2) Use postgres
sounds great yes. maybe an SQLite version will come in the future
Hey, I tried to import Linux kernel master branch from https://git.kernel.org/pub/scm/linux/kernel/git/torvalds/lin... to pgit. My laptop is not the beefiest (some Ryzen 7 with 16G RAM and about 300G disk free), so that did not quite work. It died when trying to rebuild indexes (after bulk import), due to Postgres running out of disk space.
I guess this could have been expected, but it didn't quite occur to me since plain git has had no issues with that repository. Either way, the import process was quite slow: the failure happened after 3h30m. I'm not sure if it would be possible to speed it up, or estimate resource consumption ahead of time and warn the user? The laptop also had gone almost 2G into swap at some point, so there was quite a bit of memory pressure as well, but I don't quite know at which point this happened.
haha, great that you tried! i also imported it multiple times now and it does work. but it's huge. the times actually match quite well, i also had around 3 hours, i'm surprised you managed to do it that fast actually. so yeah, i'm currently working on multiple things to improve the speed for importing and then also for analysing the kernel. but that will be something for the next post. stay tuned! as a quick teaser: it imported the 123GB uncompressed master branch into 2.98 GB pgit actual data while git aggressive puts it into 1.95 GB. but keep in mind, pgit was never meant to beat git in any terms. it really started as a demo XD
Ok, cheers! I occasionally need to investigate older releases and compare to out-of-tree things, and was thinking pgit might be of help there. I put up a reminder for myself to check pgit again next time I need to do that sort of stuff!
Sounds great! Yeah i have been working on a 3 layer cache in pg-xpatch so its not only in-memory cache but a little more sufisticated and hopefully uses less ram... haha. but its still not quite what i want.
What would you do if your entire Git history was instantly queryable with SQL?
How well does this support random-access queries to the file names and content at a certain revision? Like:
- "Checking out" a specific branch (which can be reasonably slow)
- Query all files and folders in path `/src`
- Query all files and folders in path `/src/*` (and maybe with extra pattern matches)
- Be able to read contents of a file from a certain offset for a certain length
These are similar to file system queries to a working directory
Accessing specific files is very fast. For sure sub second and most of the times its just a few milliseconds
I’m confused by the benchmark detail. It says that the “on disk” size for pgit is always larger than the git aggressive size, but then it breaks out just the pgit data size and says that’s typically smaller. If you’re using PG to implement this, don’t you have to account for the PG storage, too, in your comparison? My takeaway is that pgit always has a larger storage requirement than git aggressive compression. Or am I reading that wrong? Obviously, pgit also brings features like SQL querying that git doesn’t have that you might prioritize more highly. But the author seems to be pushing the storage benefit highly.
good question! the "pgit actual" column tries to compare just the compression algorithms, similar to how the git side only counts the .pack file and not .idx/.rev/.bitmap or filesystem overhead. so both sides strip their "container" overhead to make it a fair comparison.
but you're totally right that in practice the on-disk size is what you actually pay. that's why both numbers are in the table. and yes, pgit on-disk is usually larger than git aggressive. the tradeoff is that you get SQL queryability over your entire history, which git just can't do natively.
Okay, thanks. I would revise the write-up then. It makes it sound like there’s a storage benefit here when there really isn’t. The real message might be that it’s very close to git’s aggressive optimization and it also gives you the sql benefits. I’m also a bit confused by all the write up on delta compression. That’s interesting for the size comparison, but if the real benefit to most users is going to be the sql features, then I’m not sure why all the talk of delta compression, which I’m guessing slows things down slightly. I’m assuming you could do all the sql features without any of the delta compression.
yeah i get that. sorry if it comes across as too salesy. but keep in mind that pgit was only meant to be a demo of pg-xpatch and wasn't built with beating git in mind. the fact that it's SQL queryable and comes close to git's compression was a nice side-effect. so the whole thing was really just built for showcasing xpatch's compression and evolved into what it is now. but yes, in theory you could also just store the git history uncompressed, which would actually solve quite a lot of issues i had :)
Would be cool to populate the DB and keep it in sync by pointing to postgres as an upstream remote inside of git itself. That would probably require a custom postgres extension and a way to accept traffic from git.
Wouldn't duckdb be better suited for this?
Forgive the stupid question. I just connected "csv as sql" to "git as sql" and duckdb comes to mind
I did actually look into writing the extension for duckdb. But similar to SQLite the extension possibilities are not great for what I needed. Though duckdb is a great database.
Why a custom LLM prompt for what appears to be the default 'report' you'd want? Wouldn't the CLI just do this for a report command?
Is there an example of the tool enabling LLM 'discovering' something non-deterministic and surprising?
Yes, you also got analysis commands the AI can use. I just did the prompt example before they existed.
What would be the general purpose of storing the history in a remote database? Is it for use by agents? It's not the same as agents cloning the project and running "git log".
1) In the case of pgit, the "remote" database is a local docker container
2) You can do more complex analyses faster and easier (you don't need to pipe the git outputs) since it's just SQL
but pgit is not meant to replace git.
This could be great for larger repos.
If you couple this with an optional FUSE provider, server side user branches, and gerrit like change sets, that would be awesome.
thanks! FUSE is actually a really cool idea, hadn't thought about that. would basically let you mount a repo as a filesystem backed by postgres. server side branches and change sets are interesting too, postgres already handles concurrent access well so that could work nicely. definitely adding these to the ideas list!
I've already spun up claude to make a POC for this.
I like gerrit, but the server is such a pain to handle (java plus FS). PG would be the only server side component required, though you could have an optional review server that would act like a PG client as well.
The FUSE would be extremely nice for CI/CD for instant cloning with a local resource cache, which is much harder to do with a FS based git.
I love it. I love having agents write SQL. It's very efficient use of context and it doesn't try to reinvent informal retrieval part of following the context.
Did you find you needed to give agents the schema produced by this or they just query it themselves from postgres?
so most analyses already have a CLI function you can just call with parameters. for those that don't, in my case, the agent just looked at the --help of the commands and was able to perform the queries.
This is incredibly neat and might actually become a part of my toolbox.
thanks! but it might still need some releases until it's really good. just don't rely on it ;)
Feels like swapping filesystem complexity for database complexity.
I would choose a database for this kind of analysis
haha yeah pretty much. but postgres already solves most of that complexity for you, so you get SQL queryability almost for free.
why do agents need to know these metas about git history to perform its coding functions though?
even humans don’t do this unless there’s a crazy bug causing them to search around every possible angles.
that said, this sound like a great and fun project to work on.
but the difference between you and an agent is that you naturally know the history of the project if you have worked on it. the AI doesnt.
so true!
1) commit messages often capture the "why" something changed - versus the code/tests which focus on the what/how for right now.
2) when you have a regression being able to see the code before it was introduced and the code which was changed at the same time is very helpful in understanding the developer's intent, blindspots in their approach, etc.
debuging and operational investigations. I would say half of my sessions with agent involves those
Interesting... could be used to store multiple git repos and do a full text search across the multiple repos ?
in theory yes. you just need to do the full text search across the databases. pgit doesnt support it but at the end its just postgres under the hood.
I feel it would be more ergonomic to utilize SQLite as a backend, for the scale of repos I tend to interact with (small-medium sized repos). Yet it might be interesting for all the repos to share a single PostgreSQL db for cross-comparisons -- though that isn't a use case I have seen a need for.
yeah totally get that. the main blocker was delta compression. sqlite's extension api made it really slow for custom storage. i either had to do all the compression on the pgit side (and lose native SQL queryability) or just use postgres which handles it natively. but an sqlite version isn't off the table for smaller repos where that tradeoff makes more sense.