Pg_branch: Experimental Postgres extension brings Neon-like branching

by bx376 • 3 years ago

193 67 3 years ago

bhouston• 3 years ago

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?

I guess I am inspired by Dolt’s ability to branch and merge: https://github.com/dolthub/dolt

lifty• 3 years ago

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.

sverhagen2• 3 years ago

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

petepete• 3 years ago

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

https://www.postgresql.org/docs/current/manage-ag-templatedb...

jasfi• 3 years ago

The intention of pg_branch is to optimize this process. They mention two problems:

1. The template database can't have active connections during the CREATE DATABASE process.

2. The CREATE DATABASE from a template database can be slow for large DBs.

dalore2• 3 years ago

If you have lots of data, this can take some time

The appeal of this project is that it uses CoW (copy on write), so in theory it should be fast to copy as it only maintains writes.

vasco• 3 years ago

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.

prpl• 3 years ago

Salesforce does this for customers:

https://developer.salesforce.com/blogs/2022/09/sandboxes-on-...

Zanfa• 3 years ago

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.

vb-84483• 3 years ago

It's also quite common in python and I guess in any other language. It's great for unittests, but not suitable for integration or end-to-end tests.

sverhagen2• 3 years ago

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

skeptrune• 3 years ago

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.

Having it on CI actions would also be amazing.

baq2• 3 years ago

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?

lnenad• 3 years ago

What makes people write these kind of messages? If you don't want to use macOS then don't use it.

baq2• 3 years ago

It's right there - corporate IT

mattgreenrocks• 3 years ago

Most people commenting on the internet are a bit crazy ;)

imiric2• 3 years ago

This is neat, but I wouldn't trust BTRFS for any serious usage. I've read way too many data loss reports to make me stay away even for personal use.

Could this work on ZFS, or a layered FS like Aufs/OverlayFS?

bantunes• 3 years ago

I thought the only major issues with Btrfs were with RAID usage. I mean, some distros default to it out of the box, right?

capableweb• 3 years ago

Both openSUSE and Fedora defaults to BTRFS so not sure what parent is talking about, you won't have any data loss for the average/common use cases.

imiric2• 3 years ago

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.

[1] https://wiki.debian.org/Btrfs#Status

argulane• 3 years ago

There is https://github.com/Photonios/pgcow that supports ZFS.

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.

psi2• 3 years ago

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.

https://m.youtube.com/watch?v=K66r3yZd2_I

psi2• 3 years ago

Err, at GoRuCo, rather. The Gotham Ruby Conference :)

mj4e• 3 years ago

For anyone wondering what "Neon-like branching" is, this is worth a read: https://neon.tech/blog/database-branching-for-postgres-with-...

nerdyadventurer• 3 years ago

Is branching library open sourced?, AFAIK Neon is.

nextaccountic• 3 years ago

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?

phamilton• 3 years ago

A proprietary product, but AWS Aurora Clones are CoW.

samokhvalov3• 3 years ago

(Postgres.ai founder here – we develop https://dblab.dev)

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.

vb-84483• 3 years ago

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.

wdb• 3 years ago

How does this differ with Postgres AI's thin cloning and database branching? https://github.com/postgres-ai/database-lab-engine

vb-84483• 3 years ago

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.

samokhvalov3• 3 years ago

Underlying DBLab Engine is FOSS (Apache 2.0), with API, CLI and UI https://github.com/postgres-ai/database-lab-engine

nalexpear4• 3 years ago

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.

klysm4• 3 years ago

For tests in Postgres you can kind of do something similar by creating template databases

curt15• 3 years ago

Doesn't btrfs suffer crippling performance penalties for DBs unless you disable its marquee features of copy-on-write and checksumming?

vlovich1232• 3 years ago

Don’t XFS and ext4 also support CoW?

klysm4• 3 years ago

If ext4 does, I’m not aware of it. The only widely (up for debate) used file systems I know of that offer snapshotting are btrfs and zfs

ptman• 3 years ago

reflink probably, but not filesystem snapshots, or do they?

ComputerGuru4• 3 years ago

I don’t know about btrfs, but zfs (correctly tuned) is also famously CoW and simultaneously a good option for many Postgres deployments.

smilliken• 3 years ago

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.

nalexpear4• 3 years ago

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.

e12e• 3 years ago

Should presumably be possible to support bcachefs too?

orangepurple2• 3 years ago

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 considered VDO for LVM because it supports compression but VDO IS A JOKE! https://github.com/dm-vdo/vdo/issues/38

PlutoIsAPlanet• 3 years ago

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.

orangepurple2• 3 years ago

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.

jsmith99• 3 years ago

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

mason55• 3 years ago

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.

mrinterweb2• 3 years ago

I see APFS (MacOS default file system) supports copy-on-write. I wonder if there are plans for pg_branch to support apfs.

mrinterweb2• 3 years ago

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.

nalexpear4• 3 years ago

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.

zoomzoom• 3 years ago

If interested in using Neon for ephemeral environments, Coherence (I’m a cofounder) recently launched a first-class integration with Neon for just this purpose: https://docs.withcoherence.com/docs/reference/integrations#n...

Would be cool to try using this extension on top of RDS or Cloud SQL to offer the same experience without the overhead of another SaaS tool!

Pg_branch: Experimental Postgres extension brings Neon-like branching

Related Stories