3 ms·

Many people are unaware of a relatively recent method that allows almost instant copies of postgres databases that I use very heavily: CREATE DATABASE new_db

Many people are unaware of a relatively recent method that allows almost instant copies of postgres databases that I use very heavily: CREATE DATABASE new_db TEMPLATE templatedb STRATEGY FILE_COPY Combined with set file_copy_method = 'clone' This copies arbitrarily sized DBs sub-second - I use it on dbs larger than 1TB regularly - and has the advantage of being copy-on-write - the new db doesn't take up any extra disk space until you start writing to it in which case only the differences are used. Only supported on linux and macos AFAIK https://www.postgresql.org/docs/18/runtime-config-resource.html#GUC-FILE-COPY-METHOD https://www.postgresql.org/docs/18/runtime-config-resource.h...

brandur 2mo ago
Thanks for that — I didn't know about `clone` (and important to note it's non-default) and now intend to try it out.
- spellboots 2mo ago
  I'm actually curious if this is faster than a tmpfs clone, I suspect it might be as depending on how many underlying files it's copying, it might effectively be doing "nothing" for each file. Obviously operations after that will be a lot slower reading / writing to a normal disk
CodesInChaos 2mo ago
I assume it's only fast for large databases when running on a filesystem that supports copy-on-write? In particular, I assume it won't benefit from CoW on a tmpfs?
- spellboots 2mo ago
  I do know that if it's not supported by the underlying filesystem if falls back to the default. What I don't know is if it's faster to copy than a regular copy on a ram tmpfs - since it may be essentially be doing "nothing" - updating some pointers instead of copying stuff in ram. So if your workload is limited by copy not execution speed it might be worth testing. However at that point you're running on a regular FS so if you're trying to benefit test speed by having all your regular postgres operations on a ramdisk, that part will end up being slower.

brandur 2mo ago

Thanks for that — I didn't know about `clone` (and important to note it's non-default) and now intend to try it out.

spellboots 2mo ago
I'm actually curious if this is faster than a tmpfs clone, I suspect it might be as depending on how many underlying files it's copying, it might effectively be doing "nothing" for each file. Obviously operations after that will be a lot slower reading / writing to a normal disk

CodesInChaos 2mo ago

I assume it's only fast for large databases when running on a filesystem that supports copy-on-write? In particular, I assume it won't benefit from CoW on a tmpfs?

spellboots 2mo ago
I do know that if it's not supported by the underlying filesystem if falls back to the default. What I don't know is if it's faster to copy than a regular copy on a ram tmpfs - since it may be essentially be doing "nothing" - updating some pointers instead of copying stuff in ram. So if your workload is limited by copy not execution speed it might be worth testing. However at that point you're running on a regular FS so if you're trying to benefit test speed by having all your regular postgres operations on a ramdisk, that part will end up being slower.