Slowness observed creating/restoring snapshots in Postgres 17 with large DB

[ash-plunkett-alliance]

G'day,
This might not be the right spot but I'm hoping you'll either have an idea, or be able to point me in the right direction.

I use DSLR pretty extensively on a lot of my projects and it works like a charm. Super quick to take & restore snapshots while making the whole thing easy to manage.
I have one particular DB which is significantly larger than the rest, some 130GB+.

I have two versions of postgres running on my local machine, 13 & 17. (95% of my time is on v17 projects)

Loading the dump of the 130GB+ DB from the production server (which is running postgres 12 currently) to my local machine via pg_restore takes some 80-95 minutes, which is fine. It's around the same time regardless of if I load it into v13 or v17, which is good.

Taking or restoring a snapshot of the DB via DSLR in v13 takes ~80 seconds, which is fantastic and as expected for a DB of this size.

However, taking or restoring a snapshot of the same DB via DSLR in v17 takes ~36 minutes, which is less than great.
(if I trim some old data out of the DB and get it down to ~82GB, it reduces the snapshot/restore time to ~24 minutes)

I've had a squiz through what DSLR's actually doing under the hood and there isn't anything I could spy here that would be the culprit so it'll be something at the postgres level - but I can't find any clue on exactly what it might be. I haven't noticed this at all in any of my other projects (all others are running v17).

Do you know how I could dig into this deeper and figure out what's going on?
The config of my local postgres installations are off-the-shelf (nothing custom tweaked in either installation).

At the end of the day it's still significantly faster than pg_dump/pg_restore, but seeing the huge difference of ~80 seconds in v13 compared to 24-36 minutes in v17 with the same database dump has me a bit alarmed. (if it was slower in v13 but faster in v17 then I wouldn't mind 😁)

[ash-plunkett-alliance]

Figured it out. Postgres 15 introduced strategy to CREATE DATABASE: https://www.postgresql.org/docs/15/sql-createdatabase.html#CREATE-DATABASE-STRATEGY

In <15 it used FILE_COPY under the hood, but in 15+ it exposes the arg and defaults to WAL_LOG.

If I create a template using the FILE_COPY strategy in Postgres 17, the snapshot above which used to take ~24-36 minutes is now down to ~2 minutes.

If you could expose strategy in dslr snapshot ... that would be fantastic 😁

[ash-plunkett-alliance]

On top of that, maybe you could check the size of the source DB in advance and if over x GB have a confirmation akin to

Your database is x GB.
It may be slow using the default creation strategy.
Consider --strategy FILE_COPY when creating a snapshot of a large database.
Do you want to continue using the default strategy? y/n

I haven't done incremental testing to see where the tipping point is between WAL_LOG or FILE_COPY though, so unsure what x could be set at.

(it's only an arg in Postgres 15+ though, so you'd also need a check if someone is running DSLR in <15 to not do any of it)

[mixxorz]

Interesting. I did a bit of digging and I don't know if we'll be able to pin down the threshold at which point one becomes better than the other. It's looking like it's probably going to be database/environment dependent.

So perhaps it's worth just letting the user decide through --strategy.

Perhaps, if snapshot/restore takes more than 3 minutes, DSLR could output a message suggesting to try --strategy FILE_COPY.

[ash-plunkett-alliance]

Sorry for the slow response!

Perhaps, if snapshot/restore takes more than 3 minutes, DSLR could output a message suggesting to try --strategy FILE_COPY.

One qualm I have with that approach is if your snapshot/restore is already taking a long time, you can't easily interrupt the process without making matters worse for yourself.

It would probably be frowned upon, but perhaps, make --strategy mandatory to be explicitly passed by the user.

ie just doing dslr snapshot foo wouldn't work - you'd need to do dslr snapshot foo --strategy <strategory> (and likewise for dslr restore ...).

Not exactly a great solution though - I'd assume the majority of users would just be able to use the default strategy and not have any issues one way or the other. So suddenly being forced to pass in the --strategy arg would just be annoying for those users.

If there was some safe and easy way to interrupt the import if it's taking too long, that'd be great. Then I'd be much more keen for your suggestion of:

Perhaps, if snapshot/restore takes more than 3 minutes, DSLR could output a message suggesting to try --strategy FILE_COPY.

Otherwise maybe an approach could just be to check the size of the DB in advance - if it's greater than say 10gb* then when a user does a command without specifying a strategy (eg dslr snapshot foo) it would give them a confirmation prompt asking for the strategy, eg:

$ dslr snapshot foo
Your database 'foo' is greater than 10gb.
It may be slow to use the default creation strategy.
For more details, see: https://www.postgresql.org/docs/15/sql-createdatabase.html#CREATE-DATABASE-STRATEGY.
Would you like to use the FILE_COPY strategy? (Y/n): 

(but if you did dslr snapshot foo --strategy WAL or dslr snapshot foo --strategy FILE_COPY then the warning/confirmation wouldn't come up at all, regardless of DB size)

*I haven't fiddled around with thresholds so it might be more appropriate to be a higher number than 10gb.
Probably better to be conservative than accurate though if it's difficult to nail down a reasonably consistent size without being too dependent on OS/database version.

[mixxorz]

Here's a proposal.

1. Expose a --strategy flag. Using it will explicitly use the specified strategy.
2. When --strategy is not specified, we don't pass anything to progress either. So you will end up using the database defaults.
3. We can add a default strategy to the config as well so that you can choose to always use one or the other for your particular project.

That way, you can at least use FILE_COPY if you need to, such as in your case.

For my use case though, and I expect a lot of people, their local test databases are small enough for this to not matter.

[ash-plunkett-alliance]

Yeah that's probably, realistically, the best viable option here 👍
Being able to add it to the config file would be handy.

I'm not sure how other users have their environments set up, but my structure is along the lines of:

~/dev/
    project-b/
        dslr.toml  # points to DB for this project
        code/
            ... project repo here ...
    project-a/
        dslr.toml  # points to DB for this project
        code/
            ... project repo here ...

And of all the projects I do work on, there's only one in particular that has this issue. So if I can add a strategy = 'FILE_COPY' to the dslr.toml for that particular project, that'd be nice. 😁

But I'd still be happy without it - being able to do --strategy FILE_COPY works as well. I just need to remember which project is which, or I'll have a bad time. :p