10 ms·
Instant database clones with PostgreSQL 18
- mvcosta91 10mo agoIt looks very interesting for integration tests
- radimm 10mo agoOP here - yes, this is my use case too: integration and regression testing, as well as providing learning environments. It makes working with larger datasets a breeze.
- febed 10mo agoIf possible could you share a repo/gist with a working docker example? I’m curious how the instant clone world work there.
- drakyoko 10mo agowould this work inside test containers?
- radimm 10mo agoOP here - still have to try (generally operate on VM/bare metal level); but my understanding is that ioctl call would get passed to the underlying volume; i.e. you would have to mount volume
- odie5533 10mo agoI use CREATE DATABASE dbname TEMPLATE template1; inside test containers. Have not tried this new method yet.
- presentation 10mo agoWe do this, preview deploys, and migration dry runs using Neon Postgres’s branching functionality - seems one benefit of that vs this is that it works even with active connections which is good for doing these things on live databases.
- 1f97 10mo agoaws supports this as well: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Managing.Clone.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...
- horse666 10mo agoAurora clones are copy-on-write at the storage layer, which solves part of the problem, but RDS still provisions you a new cluster with its own endpoints, etc, which is slow ~10 mins, so not really practical for the integration testing use case.
- nateroling 10mo agoThis is on the cluster level, while the article is talking about the database level, I believe.
- TimH 10mo agoLooks like it would probably be quite useful when setting up git worktrees, to get multiple claude code instances spun up a bit more easily.
- 1a527dd5 10mo agoMany thanks, this solves integration tests for us!
- BenjaminFaal 10mo agoFor anyone looking for a simple GUI for local testing/development of Postgres based applications. I built a tool a few years ago that simplifies the process: https://github.com/BenjaminFaal/pgtt https://github.com/BenjaminFaal/pgtt
- okigan 10mo agoWould love to see a snapshot of the GUI as part of the README.md. Also docker link seems to be broken.
- BenjaminFaal 10mo agoFixed the package link. Github somehow made it private. I will add a snapshot right now.
- peterldowns 10mo agoIs this basically using templates as "snapshots", and making it easy to go back and forth between them? Little hard to tell from the README but something like that would be useful to me and my team: right now it's a pain to iterate on sql migrations, and I think this would help.
- BenjaminFaal 10mo agoThats exactly what it is, just try it with the provided docker-compose file you will get it then.
- radarroark 10mo agoIn theory, a database that uses immutable data structures (the hash array mapped trie popularized by Clojure) could allow instant clones on any filesystem, not just ZFS/XFS, and allow instant clones of any subset of the data, not just the entire db. I say "in theory" but I actually built this already so it's not just a theory. I never understood why there aren't more HAMT based databases.
- zX41ZdbW 10mo agoThis is typical for analytical databases, e.g., ClickHouse (which I'm the author of) uses immutable data parts, allowing table cloning: https://clickhouse.com/docs/sql-reference/statements/create/table#with-a-schema-and-data-cloned-from-another-table https://clickhouse.com/docs/sql-reference/statements/create/...
- ozgrakkurt 10mo ago`ClickHouse (which I'm the author of)` just casually dropped that in the middle
- orthecreedence 10mo agoIt was so casual I didn't even notice it until you pointed it out XD
- nine_k 10mo agoThis is typical HN: everyone is here. I've seen a number of threads that unflod like this: "Lately I hacked up a satellite link to..." → "As an engineer who built the comm equipment of that satellite,.." → "As the astronaut who launched the satellite from ICS,..", etc.
- chamomeal 10mo agoDoes datomic have built in cloning functionality? I’ve been wanting to try datomic out but haven’t felt like putting in the work to make a real app lol
- majodev 10mo agoUff, I had no idea that Postgres v15 introduced WAL_LOG and changed the defaults from FILE_COPY. For (parallel CI) test envs, it make so much sense to switch back to the FILE_COPY strategy ... and I previously actually relied on that behavior. Raised an issue in my previous pet project for doing concurrent integration tests with real PostgreSQL DBs (https://github.com/allaboutapps/integresql https://github.com/allaboutapps/integresql) as well.
- christophilus 10mo agoAs an aside, I just jumped around and read a few articles. This entire blog looks excellent. I’m going to have to spend some time reading it. I didn’t know about Postgres’s range types.
- pak9rabid 10mo agoRange types are a godsend when you need to calculate things like overlapping or intersecting time/date ranges.
- zachrip 10mo agoCan you give a real world example?
- christophilus 10mo agoI think the examples here are pretty good: https://boringsql.com/posts/beyond-start-end-columns/ https://boringsql.com/posts/beyond-start-end-columns/
- pak9rabid 10mo agoThis is kind of a complicated example, but here goes: Say we want to create a report that determines how long a machine has been down, but we only want to count time during normal operational hours (aka operational downtime). Normally this would be as simple as counting the time between when the machine was first reported down, to when it was reported to be back up. However, since we're only allowed to count certain time ranges within a day as operational downtime, we need a way to essentially "mask out" the non-operational hours. This can be done efficiently by finding the intersection of various time ranges and summing the duration of each of these intersections. In the case of PostgreSQL, I would start by creating a tsrange (timestamp range) that encompases the entire time range that the machine was down. I would then create multiple tsranges (one for each day the machine was down), limited to each day's operational hours. For each one of these operational hour ranges I would then take the intersection of it against the entire downtime range, and sum the duration of each of these intersecting time ranges to get the amount of operational downtime for the machine. PostgreSQL has a number of range functions and operators that can make this very easy and efficient. In this example I would make use of the '*' operator to determine what part of two time ranges intersect, and then subtract the upper-bound (using the upper() range function) of that range intersection with its lower-bound (using the lower() range function) to get the time duration of only the "overlapping" parts of the two time ranges. Here's a list of functions and operators that can be used on range types: https://www.postgresql.org/docs/9.3/functions-range.html https://www.postgresql.org/docs/9.3/functions-range.html Hope this helps.
- elitan 10mo agoFor those who can't wait for PG18 or need full instance isolation: I built Velo, which does instant branching using ZFS snapshots instead of reflinks. Works with any PG version today. Each branch is a fully isolated PostgreSQL container with its own port. ~2-5 seconds for a 100GB database. https://github.com/elitan/velo https://github.com/elitan/velo Main difference from PG18's approach: you get complete server isolation (useful for testing migrations, different PG configs, etc.) rather than databases sharing one instance.
- tobase 10mo ago[flagged]
- teiferer 10mo agoYou mean you told Claude a bunch of details and it built it for you? Mind you, I'm not saying it's bad per se. But shouldn't we be open and honest about this? I wonder if this is the new normal. Somebody says "I built Xyz" but then you realize it's vibe coded.
- earthnail 10mo agoNot sure why this is downvoted. For a critical tool like DB cloning, I‘d very much appreciate if it was hand written. Simply because it means it’s also hand reviewed at least once (by definition). We wouldn’t have called it reviewed in the old world, but in the AI coding world we’re now in it makes me realise that yes, it is a form of reviewing. I use Claude a lot btw. But I wouldn’t trust it on mission critical stuff.
- deleted 10mo ago[deleted]
- renewiltord 10mo agoIf you don’t read code you execute someone is going to steal everything on your file system one day
- 10mo ago
- horse666 10mo agoThis is really cool, looking forward to trying it out. Obligatory mention of Neon (https://neon.com/ https://neon.com/) and Xata (https://xata.io/ https://xata.io/) which both support “instant” Postgres DB branching on Postgres versions prior to 18.
- oulipo2 10mo agoAssuming I'd like to replicate my production database for either staging, or to test migrations, etc, and that most of my data is either: - business entities (users, projects, etc) - and "event data" (sent by devices, etc) where most of the database size is in the latter category, and that I'm fine with "subsetting" those (eg getting only the last month's "event data") what would be the best strategy to create a kind of "staging clone"? ideally I'd like to tell the database (logically, without locking it expressly): do as though my next operations only apply to items created/updated BEFORE "currentTimestamp", and then: - copy all my business tables (any update to those after currentTimestamp would be ignored magically even if they happen during the copy) - copy a subset of my event data (same constraint) what's the best way to do this?
- gavinray 10mo agoYou can use "psql" to dump subsets of data from tables and then later import them. Something like: psql <db_url> -c "\copy (SELECT * FROM event_data ORDER BY created_at DESC LIMIT 100) TO 'event-data-sample.csv' WITH CSV HEADER" https://www.postgresql.org/docs/current/sql-copy.html https://www.postgresql.org/docs/current/sql-copy.html It'd be really nice if pg_dump had a "data sample"/"data subset" option but unfortunately nothing like that is built in that I know of.
- peterldowns 10mo agopg_dump has a few annoyances when it comes to doing stuff like this — tricky to select exactly the data/columns you want, and also the dumped format is not always stable. My migration tool pgmigrate has an experimental `pgmigrate dump` subcommand for doing things like this, might be useful to you or OP maybe even just as a reference. The docs are incomplete since this feature is still experimental, file an issue if you have any questions or trouble https://github.com/peterldowns/pgmigrate https://github.com/peterldowns/pgmigrate
- oulipo2 10mo agoIndeed, but is there a way to do it as a "point in time", eg do a "virtual checkpoint" at a timestamp, and do all the copy operations from that timestamp, so they are coherent?
- francislavoie 10mo agoIs anyone aware of something like this for MariaDB? Something we've been trying to solve for a long time is having instant DB resets between acceptance tests (in CI or locally) back to our known fixture state, but right now it takes decently long (like half a second to a couple seconds, I haven't benchmarked it in a while) and that's by far the slowest thing in our tests. I just want fast snapshotted resets/rewinds to a known DB state, but I need to be using MariaDB since it's what we use in production, we can't switch DB tech at this stage of the project, even though Postgres' grass looks greener.
- proaralyst 10mo agoYou could use LVM or btrfs snapshots (at the filesystem level) if you're ok restarting your database between runs
- francislavoie 10mo agoRestarting the DB is unfortunately way too slow. We run the DB in a docker container with a tmpfs (in-memory) volume which helps a lot with speed, but the problem is still the raw compute needed to wipe the tables and re-fill them with the fixtures every time.
- renewiltord 10mo agoI have not done this so it’s theorycrafting but can’t you do the following? 1. Have a local data dir with initial state 2. Create an overlayfs with a temporary directory 3. Launch your job in your docker container with the overlayfs bind mount as your data directory 4. That’s it. Writes go to the overlay and the base directory is untouched
- francislavoie 10mo agoBut how does the reset happen fast, the problem isn't with preventing permanent writes or w/e, it's with actually resetting for the next test. Also using overlayfs will immediately be slower at runtime than tmpfs which we're already doing.
- peterldowns 10mo agoReally interesting article, I didn't know that the template cloning strategy was configurable. Huge fan of template cloning in general; I've used Neon to do it for "live" integration environments, and I have a golang project https://github.com/peterldowns/pgtestdb https://github.com/peterldowns/pgtestdb that uses templates to give you ~unit-test-speed integration tests that each get their own fully-schema-migrated Postgres database. Back in the day (2013?) I worked at a startup where the resident Linux guru had set up "instant" staging environment databases with btrfs. Really cool to see the same idea show up over and over with slightly different implementations. Speed and ease of cloning/testing is a real advantage for Postgres and Sqlite, I wish it were possible to do similar things with Clickhouse, Mysql, etc.
- 702318464 10mo ago[flagged]
- riskable 10mo agoPostgreSQL seems to have become the be-all, end-all SQL database that does everything and does it all well. And it's free! I'm wondering why anyone would want to use anything else at this point (for SQL).
- wahnfrieden 10mo agoCan’t really run it on iOS. And its WASM story is weak
- efxhoy 10mo agoIt’s the clear OLTP winner but for OLAP it’s still not amazing out of the box.
- scottyah 10mo agoIt's heavy, I'd say sqlite3 close to the client and postgres back at the server farm is the combo to use.
- aftbit 10mo agoOnce upon a time, MySQL/InnoDB was a better performance choice for UPDATE-heavy workloads. There was a somewhat famous blog post about this from Uber[1]. I'm not sure to what extent this persists today. The other big competitor is sqlite3, which fills a totally different niche for running databases on the edge and in-product. Personally, I wouldn't use any SQL DB other that PostgreSQL for the typical "database in the cloud" use case, but I have years of experience both developing for and administering production PostgreSQL DBs, going back to 9.5 days at least. It has its warts, but I've grown to trust and understand it. 1: https://www.uber.com/blog/postgres-to-mysql-migration/ https://www.uber.com/blog/postgres-to-mysql-migration/
- vl 10mo ago“does it all well” is a stretch. Any non-trivial amount of data and you’ll run into non-trivial problems. For example, some of our pg databases got into such state, that we had to write custom migration tool because we couldn’t copy data to new instance using standard tools. We had to re-write schema to using custom partitions because perf on built-in partitioning degrades as number of partitions gets high, and so on.
- sheepscreek 10mo agoI set this up for my employer many years ago when they migrated to RDS. We kept bumping into issues on production migrations that would wreck things. I decided to do something about it. The steps were basically: 1. Clone the AWS RDS db - or spin up a new instance from a fresh backup. 2. Get the arn and from that the cname or public IP. 3. Plug that into the DB connection in your app 4. Run the migration on pseudo prod. This helped up catch many bugs that were specific to production db or data quirks and would never haven been caught locally or even in CI. Then I created a simple ruby script to automate the above and threw it into our integrity checks before any deployment. Last I heard they were still using that script I wrote in 2016!
- Tostino 10mo agoI love those "migration only fails in prod because of data quirks" bugs. They are the freaking worst. Have called off releases in the past because of it.
- QuercusMax 10mo agoYou should almost never test in prod, but sometimes testing on [a copy of] prod is useful
- leetrout 10mo agoYou should almost never stop testing in prod. https://www.honeycomb.io/blog/testing-in-production https://www.honeycomb.io/blog/testing-in-production
- tehlike 10mo agoNow i need to find a way to migrate from hydra columnar to pg_lake variants so i can upgrade to PG18.
- hmokiguess 10mo agoI’ve been a fan of Neon and it’s branching strategy, really handy thing for stuff like this.
- wayeq 10mo agowe just build the database, commit it to a container (without volumes attached), and programmatically stop and restart the container per test class (testcontainers.org). the overhead is < 5 seconds and our application recovers to the reset database state seamlessly. it's been awesome.
- eatsyourtacos 10mo agoI still cannot reliably restore any Postgres DB with the TimescaleDB extensions on it.. have tried a million things but still fails every time.
- deleted 10mo ago[deleted]
- tudorg 10mo agoThis is really cool and I love to see the interest in fast clones / branching here. We've built Xata with this idea of using copy-on-write database branching for staging and testing setups, where you need to use testing data that's close to the real data. On top of just branching, we also do things like anonymization and scale-to-zero, so the dev branches are often really cheap. Check it out at https://xata.io/ https://xata.io/ > The source database can't have any active connections during cloning. This is a PostgreSQL limitation, not a filesystem one. For production use, this usually means you create a dedicated template database rather than cloning your live database directly. This is a key limitation to be aware of. A way to workaround it could be to use pgstream (https://github.com/xataio/pgstream https://github.com/xataio/pgstream) to copy from the production database to a production replica. Pgstream can also do anonymization on the way, this is what we use at Xata.