I think I saw an Uber-engineer's presentation (YouTube; can't find it now anymore) about their test environment having the ability to automatically fork the database for every new test run, such that they could easily "revert" (in their case ignore/walk away from) the database after a test run. I find that an appealing idea, since regular test runs in the places where I work, tend to create cruft data in the test enviroment that is lopsided and voluminous in ways that do not properly represent the production system anymore. (And no one wants to troubleshoot performance issues that only ever exist in the test environment.) I have to imagine that pg_branch might be part of building a solution similar to this? Could people speak to some practical applications for this? Maybe I'm thinking about entirely the wrong one here...
> about their test environment having the ability to automatically fork the database for every new test run, such that they could easily "revert" (in their case ignore/walk away from) the database after a test run
I think this should be really straightforward by using one database as a template for another. This is how Postgres's create database works and it defaults to Template1
Right. But the obvious solution is to just not do that. Create a template and base production/staging off it, then use that template. Or use the same bootstrap/migration process to create the template as you do staging.
Regular tests shouldn’t assume access to staging/prod, anyway.
Run it in a transaction, and if it fails, abort the transaction. nbd, did this for years in prod. Discovered some interesting assumptions about sequential IDs in the DB tests while running it against prod under peak load, too!
A common pattern in Ruby-world is running each test case in a database transaction that is rolled back. A simple way to keep a clean slate in your test database.
Been there, done that, but please beware, this means that you're missing out on some nuanced behaviors that you would get if your subject under test would actually commit the transaction. I moved away from this entirely. A more-reliable setup is to let the test commit real transactions, and cleanup afterwards. You might be able to do that by calling "delete" on all repository instances, in the tearDown method of your tests. Or you could use something like Flyway to reset and reinitialize the database entirely before every test. That sounds expensive, and it certainly depends on the kind of project we're talking, but if this is a moderately-sized microservice, and you're running tests scoped to just that microservice, the setup shouldn't be too crazy, and this may actually run pretty smooth. (We run our database in a Testcontainers instance.)
We have something like this with gitlab "review apps". Each MR launches an env with its own db that applies migrations and fake data inserters, so each MR has its own db at current state of MR.
It's very similar! Postgres.ai is built on ZFS and is a separate application that manages the file system and its branches/"thin clones". pg_branch is an extension that hooks directly into Postgres's own commands (specifically, CREATE DATABASE) to trigger branching.
Postgres AI seems to be a "managed solution" while this is an extension you could use on your self-hosted instance. But, under the hood, they are very similar.
Well, understandable. This is true for any open-source product that is under control by a single company and the majority of contributions comes from this company.
Yes, we aim to have revenue (as any company) from our paid offering. But not only we're going to keep DBLab Engine open and free, we recently changed the license from AGPLv3 to Apache 2.0, and currently consider making additional pieces open.
Very glad to see the change to Apache license, and super happy the code is even source available. I was more referring to the perverse incentives that every company built around open source faces with regards to documentation. It may be less so the case here, but there probably is some incentive to make the docs subpar, or not a strong incentive to make them fantastic - it’s a difficult thing to balance.
Side note: I’ve been loving the Postgres.FM episodes, thanks so much for doing them!
Interesting that branching is now better supported and almost free. I wonder if merging can be simplified or whether it already is as simple and as fast as it can be?
Dolt is on a whole different level. They use prolly trees as their underlying data structure, which allowed them to replicate most of Git’s features very efficiently. And I think they’re planning on building a Postgres frontend as well.
Holy hell this thing is awesome. I am incredibly surprised at how little code it took to achieve a MVP on this. Adding this to local docker image would make doing so many things easier during testing and local dev.
Don't use BTRFS! I still encounter crippling data loss and error messages in my dmesg log after giving it another shot for the last 10 months. I try btrfs with Arch Linux every few years because of its native transparent compression abilities. Enough is enough! Just say no. Friends don't let friends use btrfs.
bcachefs is the only hope left for normal Linux users but it has not landed yet. I am using ZFS for /home (with zstd compression) on LVM on LUKS and my root partition is ext4 on LVM on LUKS.
I have used btrfs on Fedora for 5 years, with transparent compression enabled and have not once had any kind of data loss.
I also use it on servers used in production in RAID 1 (on ones that don't run VMs or Databases) and again, have not had any data loss or filesystem issues.
BTRFS has so much faith that Facebook use it on their server farms. I wouldn't use bcachefs in any kind of critical deployment because it hasn't gone through decades of testing that ext4, xfs and btrfs have gone through.
You should still use ZFS instead of btrfs for your use case. It's not worth the risk. I have been running ZFS for 6 years and have not had any issues. I have had multiple issues on freshly wiped (zero filled) btrfs partitions in that time.
Author here: thanks for taking a look at pg_branch! I didn't expect to have this posted to HN today, but I'm happy to answer any questions folks have about it.
For this project, I used btrfs because it was quick to use as a proof-of-concept and I happened to be most familiar with it of the CoW-capable file systems.
But pg_branch is easy to extend with other snapshot-capable fs (it's a single trait, internally, that someone would need to implement). Happy to accept pull requests for supporting e.g. ZFS or XFS.
Any file system that can implement the Branching trait should be possible to support. The only assumption made is that "snapshots" (or whatever equivalent term applies for that file system) are atomic.
PostgreSQL + ZFS is controversial. My experience was that it was inefficient on space and CPU, and especially memory. It would hang for 10+ minutes at a time one every day or two (doing a single threaded sweep across all RAM). And sometimes it would totally hang the system, requiring a reboot. I would describe the experience as hellish. Some of these symptoms only appear when the database has high load, so basic testing on a staging system doesn't tell you about stability on a production system.
PostgreSQL + ext4 is uneventful, lower latency, and less wasteful of resources.
Curious. What OS? We’ve been running it in production on FreeBSD/ZFS for probably five years or so without issue, but not running at near system capacity.
One thing I could see where this could be useful would be unit test suites where you want different sets of seed data, but you want to be sure that each test doesn't accidentally mutate and leak state to other tests. There are other ways to do this, but this seems like it might be a natural fit for isolating persistence changes.
For ad hoc copies I’ve found the third party schema cloning function useful. I assume performance would be an issue for huge databases though. https://github.com/denishpatel/pg-clone-schema
The difference is actually copying the data vs. copy-on-write. With the latter, you can make infinite copies without worrying about how much space you're using or the time it takes to make a copy, thus making it feasible to start with a fresh copy of the prod DB before you do literally anything. Have CI/CD create a fresh copy before every test. Have every feature branch start with a clean copy of prod and give people a button to do it again if they mess up a migration.
COW unlocks a ton of interesting use cases. It's easy to do on AWS Aurora but unfortunately their minimum instance size is way bigger than the regular RDS instances.
I once saw another extension, or product on top of postgres, that achieved copy-on-write semantics (and I think it didn't require a fs with snapshots like btrfs). It wasn't pg_branch or pgcow. Does anyone heard of something like this?
There is one more very important difference here, not only FOSS vs proprietary.
N thin clones on Aurora give you O(1) in terms of storage costs (you pay 1 time plus only for difference, which is 0 initially), but O(N) in terms of compute (N clones = N instances).
That's super expensive and prevents most people from having great DB-related test coverage in CI/CD.
(This is true for Neon's approach to pricing of branches too, if I'm not mistaken.)
But for the tech discussed here, you can run many – dozens, hundreds – clones on a single machine, having predictable budget. It's O(1) costs for both storage and compute. This unblocks dev&test activities with full-size databases for engineers in your team.
Really cool, I've implemented something similar for MySQL on top of Ubuntu and LVM snapshots. With a very low space overhead, we were able to create tens of copies of the main database for development purpose.
BTRFS is not that scary. Quota and RAID56 is broken but other features have been stable quite some time. Btrfs is used by default for few Linux distros and Facebook makes quite heavy use of it on their servers https://lwn.net/Articles/824855/
I have been running btrfs on all of my machines since 2016 as transparrent file compression helps a lot with keeping disk usage down.
Btrfs RAID1 even saved my data once when one of the SSD's decided to die suddenly.
For maximum performance you should run databases on top of XFS anyway. It also supports per-file COW via reflinks.
True, most issues are with RAID, but there are still many others listed here[1] that I wouldn't be comfortable with.
Perhaps I'm being overly cautious after years of not experiencing issues with ext4+LVM+LUKS. BTRFS always gave the impression of being in a perpetual state of instability, but maybe I should take another look at it.
Depends on what you mean by "common/average use cases". Common for desktop use, so at most two disk parity? Yeah, that's probably fine. Common for server use, so something akin to RAID5/6? It's still recommended to stay away from it on BTRFS, which makes it a non-starter for any NAS or most other server uses.
No reason it couldn’t. I gave a micro-talk at RubyConf 2013 on a proof-of-concept I’d been experimenting with at work that used ZFS + git hooks to do pretty much the same thing.
Remind me why macOS is the best development OS again...? This has the potential to 2x my debug speed when bisecting test failures and testing fixes. Wonder what corporate IT will say if they see me doing real work on a Linux vm, I'm very tempted to check.
Edit: quick google tells me APFS supports snapshots/cow, could the same approach be used here?
