database 使用 git 存储库作为数据库后端

Using git repository as a database backend

提问by GreyCat

I'm doing a project that deals with structured document database. I have a tree of categories (~1000 categories, up to ~50 categories on each level), each category contains several thousands (up to, say, ~10000) of structured documents. Each document is several kilobytes of data in some structured form (I'd prefer YAML, but it may just as well be JSON or XML).

我正在做一个处理结构化文档数据库的项目。我有一个类别树（约 1000 个类别，每个级别最多约 50 个类别），每个类别包含数千个（例如，约 10000 个）结构化文档。每个文档都是一些结构化形式的几千字节的数据（我更喜欢 YAML，但它也可能是 JSON 或 XML）。

Users of this systems do several types of operations:

该系统的用户执行几种类型的操作：

• retrieving of these documents by ID
• searching for documents by some of the structured attributes inside them
• editing documents (i.e. adding/removing/renaming/merging); each edit operation should be recorded as a transaction with some comment
• viewing a history of recorded changes for particular document (including viewing who, when and why changed the document, getting earlier version - and probably reverting to this one if requested)

• 通过 ID 检索这些文档
• 通过其中的一些结构化属性搜索文档
• 编辑文档（即添加/删除/重命名/合并）；每个编辑操作都应该记录为带有一些注释的事务
• 查看特定文档的记录更改历史记录（包括查看谁、何时以及为什么更改了文档，获取较早版本 - 如果需要，可能会恢复到此版本）

Of course, the traditional solution would be using some sort of document database (such as CouchDB or Mongo) for this problem - however, this version control (history) thing tempted me to a wild idea - why shouldn't I use gitrepository as a database backend for this application?

当然，传统的解决方案是使用某种文档数据库（例如 CouchDB 或 Mongo）来解决这个问题——然而，这个版本控制（历史）的事情让我产生了一个疯狂的想法——为什么我不应该使用git存储库作为此应用程序的数据库后端？

On the first glance, it could be solved like this:

乍一看，它可以这样解决：

• category = directory, document = file
• getting document by ID => changing directories + reading a file in a working copy
• editing documents with edit comments => making commits by various users + storing commit messages
• history => normal git log and retrieval of older transactions
• search => that's a slightly trickier part, I guess it would require periodic export of a category into relational database with indexing of columns that we'll allow to search by

• 类别 = 目录，文档 = 文件
• 通过 ID 获取文档 => 更改目录 + 读取工作副本中的文件
• 使用编辑注释编辑文档 => 由不同用户提交 + 存储提交消息
• 历史 => 正常的 git 日志和旧事务的检索
• 搜索 => 这是一个稍微棘手的部分，我想这需要定期将类别导出到关系数据库中，并为我们允许搜索的列建立索引

Are there any other common pitfalls in this solution? Have anyone tried to implement such backend already (i.e. for any popular frameworks - RoR, node.js, Django, CakePHP)? Does this solution have any possible implications on performance or reliability - i.e. is it proven that git would be much slower than traditional database solutions or there would be any scalability/reliability pitfalls? I presume that a cluster of such servers that push/pull each other's repository should be fairly robust & reliable.

这个解决方案还有其他常见的缺陷吗？有没有人已经尝试过实现这样的后端（即对于任何流行的框架 - RoR、node.js、Django、CakePHP）？此解决方案是否对性能或可靠性有任何可能的影响 - 即是否证明 git 会比传统数据库解决方案慢得多，或者是否存在任何可扩展性/可靠性陷阱？我认为推/拉彼此存储库的此类服务器集群应该相当健壮和可靠。

Basically, tell me ifthis solution will work and whyit will or won't do?

基本上，告诉我，如果这个解决方案将工作和为什么它会或不会做？

采纳答案by GreyCat

Answering my own question is not the best thing to do, but, as I ultimately dropped the idea, I'd like to share on the rationale that worked in my case. I'd like to emphasize that this rationale might not apply to all cases, so it's up to architect to decide.

回答我自己的问题并不是最好的做法，但是，由于我最终放弃了这个想法，我想分享一下在我的案例中起作用的基本原理。我想强调的是，这个基本原理可能并不适用于所有情况，因此由架构师来决定。

Generally, the first main point my question misses is that I'm dealing with multi-user systemthat work in parallel, concurrently, using my server with a thin client (i.e. just a web browser). This way, I have to maintain statefor all of them. There are several approaches to this one, but all of them are either too hard on resources or too complex to implement (and thus kind of kill the original purpose of offloading all the hard implementation stuff to git in the first place):

通常，我的问题遗漏的第一个要点是，我正在处理并行工作的多用户系统，并发使用我的服务器和瘦客户端（即只是一个 Web 浏览器）。这样，我必须为所有人维护状态。有几种方法可以解决这个问题，但所有这些方法要么在资源上太难，要么太复杂而难以实现（因此，最初将所有困难的实现内容卸载到 git 的初衷有点不切实际）：

• "Blunt" approach: 1 user = 1 state = 1 full working copy of a repository that server maintains for user. Even if we're talking about fairly small document database (for example, 100s MiBs) with ~100K of users, maintaining full repository clone for all of them makes disc usage run through the roof (i.e. 100K of users times 100MiB ~ 10 TiB). What's even worse, cloning 100 MiB repository each time takes several seconds of time, even if done in fairly effective maneer (i.e. not using by git and unpacking-repacking stuff), which is non acceptable, IMO. And even worse — every edit that we apply to a main tree should be pulled to every user's repository, which is (1) resource hog, (2) might lead to unresolved edit conflicts in general case.

  Basically, it might be as bad as O(number of edits × data × number of users) in terms of disc usage, and such disc usage automatically means pretty high CPU usage.

• "Only active users" approach: maintain working copy only for active users. This way, you generally store not a full-repo-clone-per-user, but:

  • As user logs in, you clone the repository. It takes several seconds and ~100 MiB of disc space per active user.
  • As user continues to work on the site, he works with the given working copy.
  • As user logs out, his repository clone is copied back to main repository as a branch, thus storing only his "unapplied changes", if there are any, which is fairly space-efficient.

  Thus, disc usage in this case peaks at O(number of edits × data × number of active users), which is usually ~100..1000 times less than number of total users, but it makes logging in/out more complicated and slower, as it involves cloning of a per-user branch on every login and pulling these changes back on logout or session expiration (which should be done transactionally => adds another layer of complexity). In absolute numbers, it drops 10 TiBs of disc usage down to 10..100 GiBs in my case, that might be acceptable, but, yet again, we're now talking about fairly smalldatabase of 100 MiBs.

• "Sparse checkout" approach: making "sparse checkout" instead of full-blown repo clone per active user doesn't help a lot. It might save ~10x of disc space usage, but at expense of much higher CPU/disc load on history-involving operations, which kind of kills the purpose.

• "Workers pool" approach: instead of doing full-blown clones every time for active person, we might keep a pool of "worker" clones, ready to be used. This way, every time a users logs in, he occupies one "worker", pulling there his branch from main repo, and, as he logs out, he frees the "worker", which does clever git hard reset to become yet again just a main repo clone, ready to be used by another user logging in. Does not help much with disc usage (it's still pretty high — only full clone per active user), but at least it makes logging in/out faster, as expense of even more complexity.

• “直率”方法：1 个用户 = 1 个状态 = 服务器为用户维护的存储库的 1 个完整工作副本。即使我们谈论的是具有 ~100K 用户的相当小的文档数据库（例如，100 秒 MiB），为所有用户维护完整的存储库克隆会使磁盘使用量飙升（即 100K 用户乘以 100MiB ~ 10 TiB） . 更糟糕的是，每次克隆 100 MiB 存储库需要几秒钟的时间，即使以相当有效的方式完成（即不使用 git 和解包重新打包的东西），这是不可接受的，IMO。更糟糕的是——我们应用于主树的每个编辑都应该被拉到每个用户的存储库中，这就是 (1) 资源占用，(2) 在一般情况下可能会导致未解决的编辑冲突。

  基本上，就光盘使用而言，它可能与O（编辑次数×数据×用户数）一样糟糕，并且这种光盘使用自动意味着相当高的CPU使用率。

• “仅活跃用户”方法：仅为活跃用户维护工作副本。这样，您通常不会存储每个用户的完整 repo-clone，而是：

  • 当用户登录时，您克隆存储库。每个活跃用户需要几秒钟和大约 100 MiB 的磁盘空间。
  • 当用户继续在站点上工作时，他会使用给定的工作副本。
  • 当用户注销时，他的存储库克隆被复制回主存储库作为一个分支，因此只存储他的“未应用的更改”，如果有的话，这是相当节省空间的。

  因此，在这种情况下，磁盘使用量的峰值为 O（编辑次数 × 数据 × 活跃用户数），通常比总用户数少约 100..1000 倍，但它使登录/注销更加复杂和缓慢，因为它涉及在每次登录时克隆每个用户的分支，并在注销或会话到期时将这些更改拉回（这应该以事务方式完成 => 增加了另一层复杂性）。在绝对数字上，在我的情况下，它将 10 TiB 的磁盘使用量降低到 10..100 GiB，这可能是可以接受的，但是，再一次，我们现在谈论的是相当小的 100 MiB 数据库。

• “稀疏结帐”方法：使每个活跃用户进行“稀疏结帐”而不是完整的 repo 克隆并没有多大帮助。它可能会节省大约 10 倍的磁盘空间使用量，但代价是在涉及历史的操作上更高的 CPU/磁盘负载，这会破坏目的。

• “工人池”方法：我们可能会保留一个“工人”克隆池，随时可以使用，而不是每次都为活跃的人进行全面的克隆。这样，每次用户登录时，他都会占用一个“工人”，将他的分支从主仓库拉到那里，当他注销时，他释放了“工人”，这使得聪明的 git hard reset 再次成为一个主要的 repo 克隆，准备被另一个登录的用户使用。 对磁盘使用没有太大帮助（它仍然很高——每个活跃用户只有完整的克隆），但至少它使登录/退出更快，作为代价更复杂。

That said, note that I intentionally calculated numbers of fairly small database and user base: 100K users, 1K active users, 100 MiBs total database + history of edits, 10 MiBs of working copy. If you'd look at more prominent crowd-sourcing projects, there are much higher numbers there:

也就是说，请注意，我特意计算了相当小的数据库和用户群的数量：100K 用户、1K 活跃用户、100 MiB 的总数据库 + 编辑历史、10 MiB 的工作副本。如果你看看更突出的众包项目，那里的数字要高得多：

│              │ Users │ Active users │ DB+edits │ DB only │
├──────────────┼───────┼──────────────┼──────────┼─────────┤
│ MusicBrainz  │  1.2M │     1K/week  │   30 GiB │  20 GiB │
│ en.wikipedia │ 21.5M │   133K/month │    3 TiB │  44 GiB │
│ OSM          │  1.7M │    21K/month │  726 GiB │ 480 GiB │

Obviously, for that amounts of data/activity, this approach would be utterly unacceptable.

显然，对于如此大量的数据/活动，这种方法是完全不可接受的。

Generally, it would have worked, if one could use web browser as a "thick" client, i.e. issuing git operations and storing pretty much the full checkout on client's side, not on the server's side.

通常，如果可以将 Web 浏览器用作“厚”客户端，即发出 git 操作并将几乎完整的结帐存储在客户端，而不是在服务器端，它就会起作用。

There are also other points that I've missed, but they're not that bad compared to the first one:

我还遗漏了其他几点，但与第一点相比，它们并没有那么糟糕：

• The very pattern of having "thick" user's edit state is controversial in terms of normal ORMs, such as ActiveRecord, Hibernate, DataMapper, Tower, etc.
• As much as I've searched for, there's zero existing free codebase for doing that approach to git from popular frameworks.
• There is at least one service that somehow manages to do that efficiently — that is obviously github— but, alas, their codebase is closed source and I strongly suspect that they do not use normal git servers / repo storage techniques inside, i.e. they basically implemented alternative "big data" git.

• 对于普通 ORM，例如 ActiveRecord、Hibernate、DataMapper、Tower 等，具有“厚”用户编辑状态的模式是有争议的。
• 正如我所搜索的那样，从流行的框架中对 git 执行这种方法的现有免费代码库为零。
• 至少有一种服务可以以某种方式有效地做到这一点——这显然是github——但是，唉，他们的代码库是闭源的，我强烈怀疑他们没有在内部使用普通的 git 服务器/repo 存储技术，即他们基本上实现了替代“大数据”git。

So, bottom line: it ispossible, but for most current usecases it won't be anywhere near the optimal solution. Rolling up your own document-edit-history-to-SQL implementation or trying to use any existing document database would be probably a better alternative.

所以，底线：这是可能的，但对于大多数当前用例来说，它不会接近最佳解决方案。汇总您自己的文档编辑历史到 SQL 实现或尝试使用任何现有文档数据库可能是更好的选择。

回答by Kombajn zbo?owy

An interesting approach indeed. I would say that if you need to store data, use a database, not a source code repository, which is designed for a very specific task. If you could use Git out-of-the-box, then it's fine, but you probably need to build a document repository layer over it. So you could build it over a traditional database as well, right? And if it's built-in version control that you're interested in, why not just use one of open source document repository tools? There are plenty to choose from.

确实是一个有趣的方法。我想说的是，如果您需要存储数据，请使用数据库，而不是源代码存储库，后者专为特定任务而设计。如果您可以开箱即用地使用 Git，那就没问题了，但您可能需要在其上构建一个文档存储库层。所以你也可以在传统数据库上构建它，对吗？如果您对内置版本控制感兴趣，为什么不使用其中一种开源文档存储工具呢？有很多可供选择。

Well, if you decide to go for Git backend anyway, then basically it would work for your requirements if you implemented it as described. But:

好吧，如果您无论如何决定使用 Git 后端，那么如果您按照描述实现它，它基本上可以满足您的要求。但：

1) You mentioned "cluster of servers that push/pull each other" - I've thought about it for a while and still I'm not sure. You can't push/pull several repos as an atomic operation. I wonder if there could be a possibility of some merge mess during concurrent work.

1）您提到了“相互推/拉的服务器集群”-我已经考虑了一段时间，但仍然不确定。您不能将多个存储库作为原子操作推/拉。我想知道在并发工作期间是否有可能出现一些合并混乱。

2) Maybe you don't need it, but an obvious functionality of a document repository you did not list is access control. You could possibly restrict access to some paths(=categories) via submodules, but probably you won't be able to grant access on document level easily.

2) 也许您不需要它，但是您没有列出的文档存储库的一个明显功能是访问控制。您可以通过子模块限制对某些路径（=类别）的访问，但您可能无法轻松授予文档级别的访问权限。

回答by young chisango

my 2 pence worth. A bit longing but ...... I had a similar requirement in one of my incubation projects. Similar to yours , my key requirements where a document database ( xml in my case),with document versioning. It was for a multi-user system with a lot of collaboration use cases. My preference was to use available opensource solutions that support most of the key requirements.

我的 2 便士值。有点渴望但是......我在我的一个孵化项目中也有类似的要求。与您的类似，我的关键要求是文档数据库（在我的情况下为 xml），具有文档版本控制。它适用于具有大量协作用例的多用户系统。我更喜欢使用支持大多数关键需求的可用开源解决方案。

To cut to the chase, I could not find any one product that provided both, in a way that was scalable enough ( number of users, usage volumes, storage and compute resources).I was biased towards git for all the promising capability, and (probable) solutions one could craft out of it. As I toyed with git option more, moving from a single user perspective to a multi ( milli) user perspective became an obvious challenge. Unfortunately, I did not get to do substantial performance analysis like you did. ( .. lazy/ quit early ....for version 2, mantra) Power to you!. Anyway, my biased idea has since morphed to the next (still biased ) alternative: a mesh-up of tools that are the best in their separate spheres, databases and version control.

切入正题，我找不到任何一种产品能够以足够可扩展的方式（用户数量、使用量、存储和计算资源）同时提供这两种功能。我偏向于使用 git 来获得所有有前途的功能，并且（可能的）解决方案可以制作出来。当我更多地使用 git 选项时，从单用户视角转移到多（毫）用户视角成为一个明显的挑战。不幸的是，我没有像您那样进行大量的性能分析。（..懒惰/早点退出......对于版本2，咒语）给你力量！无论如何，我有偏见的想法已经演变为下一个（仍然有偏见的）替代方案：在各自的领域、数据库和版本控制中最好的工具的网格化。

While still work in progress ( ...and slightly neglected ) the morphed version is simply this .

虽然仍在进行中（...略有忽略），但变形版本就是这样。

• on the frontend: (userfacing ) use a database for the 1st level storage ( interfacing with user applications )
• on the backend, use a version control system (VCS)(like git ) to perform versioning of the data objects in database

• 在前端：（面向用户）使用数据库作为第一级存储（与用户应用程序接口）
• 在后端，使用版本控制系统 (VCS)（如 git ）对数据库中的数据对象执行版本控制

In essence it would amount to adding a version control plugin to the database, with some integration glue, which you may have to develop, but may be a lot much easier.

从本质上讲，这相当于向数据库添加一个版本控制插件，并使用一些集成胶水，您可能需要开发它，但可能要容易得多。

How it would (supposed to ) work is that the primary multi-user interface data exchanges are through the database. The DBMS will handle all the fun and complex issues such as multi-user , concurrency e, atomic operations etc. On the backend the VCS would perform version control on a single set of data objects ( no concurrency, or multi-user issues). For each effective transactions on the database, version control is only performed on the data records that would have effectively changed.

它如何（应该）工作是主要的多用户界面数据交换是通过数据库进行的。DBMS 将处理所有有趣和复杂的问题，例如多用户、并发、原子操作等。在后端，VCS 将对一组数据对象执行版本控制（无并发或多用户问题）。对于数据库上的每个有效事务，版本控制仅对可能已有效更改的数据记录执行。

As for the interfacing glue, it will be in the form of a simple interworking function between the database and the VCS. In terms of design, as simple approach would be an event driven interface, with data updates from the database triggering the version control procedures ( hint : assuming Mysql, use of triggers and sys_exec()blah blah ...) .In terms of implementation complexity, it will range from the simple and effective ( eg scripting ) to the complex and wonderful ( some programmed connector interface) . All depends on how crazy you want to go with it , and how much sweat capital you are willing to spend. I reckon simple scripting should do the magic. And to access the end result, the various data versions, a simple alternative is to populate a clone of the database ( more a clone of the database structure) with the data referenced by the version tag/id/hash in the VCS. again this bit will be a simple query/translate/map job of an interface.

至于接口胶，它会以数据库和VCS之间简单的互通功能的形式出现。在设计方面，最简单的方法是事件驱动接口，来自数据库的数据更新触发版本控制过程（提示：假设 Mysql，使用触发器和 sys_exec()等等等等...）。在实现复杂性方面，它将从简单有效（例如脚本）到复杂而精彩（一些编程的连接器接口）。一切都取决于你有多疯狂，以及你愿意花费多少汗水。我认为简单的脚本应该可以发挥作用。为了访问最终结果，各种数据版本，一个简单的替代方法是用 VCS 中的版本标记/id/hash 引用的数据填充数据库的克隆（更多的是数据库结构的克隆）。同样，这一点将是一个简单的接口查询/翻译/映射作业。

There are still some challenges and unknowns to be dealt with, but I suppose the impact, and relevance of most of these will largely depend on your application requirements and use cases. Some may just end up being non issues. Some of the issues include performance matching between the 2 key modules, the database and the VCS, for an application with high frequency data update activity, Scaling of resources (storage and processing power ) over time on the git side as the data , and users grow: steady, exponential or eventually plateau's

仍有一些挑战和未知数需要处理，但我认为其中大部分的影响和相关性在很大程度上取决于您的应用程序需求和用例。有些可能最终成为非问题。一些问题包括数据库和 VCS 两个关键模块之间的性能匹配，对于具有高频数据更新活动的应用程序，git 端作为数据的资源（存储和处理能力）随时间的缩放，以及用户增长：稳定、指数或最终平稳

Of the cocktail above, here is what I'm currently brewing

在上面的鸡尾酒中，这是我目前正在酿造的

• using Git for the VCS ( initially considered good old CVS for the due to the use of only changesets or deltas between 2 version )
• using mysql ( due to the highly structured nature of my data, xml with strict xml schemas )
• toying around with MongoDB (to try a NoSQl database, which closely matches the native database structure used in git )

• 为 VCS 使用 Git（最初被认为是很好的旧 CVS，因为在 2 个版本之间只使用了变更集或增量）
• 使用 mysql（由于我的数据具有高度结构化的性质，xml 具有严格的 xml 模式）
• 玩弄 MongoDB（尝试使用 NoSQl 数据库，它与 git 中使用的本机数据库结构非常匹配）

Some fun facts - git actually does clear things to optimize storage, such as compression, and storage of only deltas between revision of objects - YES, git does store only changesets or deltas between revisions of data objects, where is it is applicable ( it knows when and how) . Reference : packfiles, deep in the guts of Git internals- Review of the git's object storage ( content-addressable filesystem), shows stricking similarities ( from the concept perspective) with noSQL databases such mongoDB. Again, at the expense of sweat capital, it may provide more interesting possibilities for integrating the 2, and performance tweaking

一些有趣的事实 - git 实际上做了明确的事情来优化存储，例如压缩和仅存储对象修订版之间的增量 - 是的，git 确实仅存储数据对象修订版之间的变更集或增量，它在哪里适用（它知道何时以及如何）。参考：packfiles，深入Git 内部结构- 回顾 git 的对象存储（内容可寻址文件系统），显示了与 noSQL 数据库（如 mongoDB）的惊人相似之处（从概念角度）。同样，以牺牲汗水为代价，它可能为整合 2 和性能调整提供更多有趣的可能性

If you got this far, let me if the above may be applicable to your case, and assuming it would be , how it would square up to some of the aspect in your last comprehensive performance analysis

如果你做到了这一点，让我看看上面的内容是否适用于你的情况，并假设它适用于你上次综合性能分析中的某些方面

回答by ioquatix

I implemented a Ruby libraryon top of libgit2which makes this pretty easy to implement and explore. There are some obvious limitations, but it's also a pretty liberating system since you get the full git toolchain.

我在其上实现了一个Ruby 库，libgit2这使得实现和探索变得非常容易。有一些明显的限制，但它也是一个非常自由的系统，因为您获得了完整的 git 工具链。

The documentation includes some ideas about performance, tradeoffs, etc.

该文档包括一些关于性能、权衡等的想法。

回答by 7mp

As you mentioned, the multi-user case is a bit trickier to handle. One possible solution would be to use user-specific Git index files resulting in

正如您提到的，多用户案例处理起来有点棘手。一种可能的解决方案是使用特定于用户的 Git 索引文件，从而导致

• no need for separate working copies (disk usage is restricted to changed files)
• no need for time-consuming preparatory work (per user session)

• 不需要单独的工作副本（磁盘使用仅限于更改的文件）
• 无需耗时的准备工作（每个用户会话）

The trick is to combine Git's GIT_INDEX_FILEenvironmental variable with the tools to create Git commits manually:

诀窍是将 Git 的GIT_INDEX_FILE环境变量与工具结合起来手动创建 Git 提交：

• git hash-object
• git update-index
• git write-tree
• git commit-tree

• git 哈希对象
• git 更新索引
• git 写树
• git 提交树

A solution outline follows (actual SHA1 hashes omitted from the commands):

解决方案大纲如下（命令中省略了实际的 SHA1 哈希）：

# Initialize the index
# N.B. Use the commit hash since refs might changed during the session.
$ GIT_INDEX_FILE=user_index_file git reset --hard <starting_commit_hash>

#
# Change data and save it to `changed_file`
#

# Save changed data to the Git object database. Returns a SHA1 hash to the blob.
$ cat changed_file | git hash-object -t blob -w --stdin
da39a3ee5e6b4b0d3255bfef95601890afd80709

# Add the changed file (using the object hash) to the user-specific index
# N.B. When adding new files, --add is required
$ GIT_INDEX_FILE=user_index_file git update-index --cacheinfo 100644 <changed_data_hash> path/to/the/changed_file

# Write the index to the object db. Returns a SHA1 hash to the tree object
$ GIT_INDEX_FILE=user_index_file git write-tree
8ea32f8432d9d4fa9f9b2b602ec7ee6c90aa2d53

# Create a commit from the tree. Returns a SHA1 hash to the commit object
# N.B. Parent commit should the same commit as in the first phase.
$ echo "User X updated their data" | git commit-tree <new_tree_hash> -p <starting_commit_hash>
3f8c225835e64314f5da40e6a568ff894886b952

# Create a ref to the new commit
git update-ref refs/heads/users/user_x_change_y <new_commit_hash>

Depending on your data you could use a cron job to merge the new refs to masterbut the conflict resolution is arguably the hardest part here.

根据您的数据，您可以使用 cron 作业将新引用合并到其中，master但冲突解决可以说是这里最难的部分。

Ideas to make it easier are welcome.

欢迎提出更容易的想法。