`SELECT name FROM pg_timezone_names;` has variable performance, affecting startup time

[steve-chavez]

Problem

This on an AWS instance (always north of 700 ms):

systemctl restart postgres; time psql-d postgres -c "SELECT name FROM pg_timezone_names" >> /dev/null;

real    0m0.815s
user    0m0.012s
sys     0m0.009s
systemctl restart postgres; time psql-d postgres -c "SELECT name FROM pg_timezone_names" >> /dev/null;

real    0m0.958s
user    0m0.008s
sys     0m0.011s
systemctl restart postgres; time psql-d postgres -c "SELECT name FROM pg_timezone_names" >> /dev/null;

real    0m0.775s
user    0m0.011s
sys     0m0.012s
systemctl restart postgres; time psql-d postgres -c "SELECT name FROM pg_timezone_names" >> /dev/null;

real    0m0.964s
user    0m0.010s
sys     0m0.009s

Locally:

systemctl restart postgres; time psql -d postgres -c "SELECT name FROM pg_timezone_names" >> /dev/null;

real    0m0.060s
user    0m0.006s
sys     0m0.003s

systemctl restart postgres; time psql -d postgres -c "SELECT name FROM pg_timezone_names" >> /dev/null;

real    0m0.060s
user    0m0.006s
sys     0m0.000s

systemctl restart postgres; time psql -d postgres -c "SELECT name FROM pg_timezone_names" >> /dev/null;

real    0m0.062s
user    0m0.006s
sys     0m0.005s

As previously mentioned on #4751 (comment), pg_timezone_names actually does a lookup on the filesystem. This varies: can be slow when using network storage, can be fast on local NVMe.

Another problem is that this is not a common use case and can be done in other ways:

The thing is that timezone is a special use case, not everyone needs it. As per #2799, it can already be done via roles. On hindsight it was a mistake adding it without a config flag, it should have been done like Prefer: tx.
#4751 (comment)

Solution

Switch to db-timezone-enabled = false by default.

[wolfgangwalther]

So you're proposing to change a default value for us, because AWS is slow to read some files from disk?

I'd prefer people to optimize their config for performance if they have certain specific setups, compared to people having to enable every single feature individually.

[steve-chavez]

because AWS is slow to read some files from disk?

That's an understatement, the AWS userbase is huge. Just in my case I see hundreds of thousands deployments on AWS. But really where talking about any server with network storage.

I'd prefer people to optimize their config for performance if they have certain specific setups,

We already have to use a config for Prefer: tx because it's only needed in special cases, this is the same.

It doesn't make sense to have the majority of the userbase to lose performance because of a use case only needed by 0.001% of users.

[wolfgangwalther]

That's an understatement, the AWS userbase is huge. Just in my case I see hundreds of thousands deployments on AWS. But really where talking about any server with network storage.

I don't quite get it - are all executables and such on network storage as well? Doesn't that add latency whenever you start a new process, etc? Or is this just because many small files have to be read from disk?

This seems like a faulty deployment to me. But I have never used AWS, so I can't say.

(I'll note this doesn't even say whether this is managed PostgreSQL aka RDS or self-managed or whatever...)

It doesn't make sense to have the majority of the userbase to lose performance because of a use case only needed by 0.001% of users.

My interpretation is, that this is a problem of the deployment. PostgreSQL's deployment. It should ideally be fixed at that level. If that doesn't work, then all of these PostgREST deployments you are talking about are all through the same provider - isn't there a set of default options that you chose, deviating from some of the PostgREST defaults, but taking your environment into account? This seems like exactly the kind of thing that should be configured there.

We already have to use a config for Prefer: tx because it's only needed in special cases, this is the same.

Not quite, because db-tx-end does provide a default as well. Are you proposing to change the default to commit-allow-override? Yeah, I think that actually makes sense. There is no reason to forbid rollback by default.

Still, this is a big difference. The reason why the timezone header is not used widely is not because it's something that is not needed in production. It's because many are just not handling time correctly. Which is really bad. I'd rather not be part of the problem (having to specifically configure PostgREST to allow me to do the right thing), but part of the solution (have proper timezone support!).

The thing is that timezone is a special use case, not everyone needs it. As per #2799, it can already be done via roles.

No, doing it via roles is exactly wrong. That's because this is statically tied to the role you are using. However, mobile and web apps should be able to use the current time zone set on the user's device. So they need to look up the current timezone and (possibly) send it via header. The role on the server can't tell whether you just roamed into a different country or timezone. The device can.

[wolfgangwalther]

That being said, I believe there are other options to improve performance of this or rather reduce the impact it can have. For example we can delay the loading of timezones. Do it async, start handling requests without timezone header immediately, make requests with that header wait until they are loaded. Of course, this is not limited to timezones - I believe we could slice the schema cache that way in general, for example by schema etc.

Just turning the default off seems like a brute force solution to me, though.

[steve-chavez]

Do it async, start handling requests without timezone header immediately, make requests with that header wait until they are loaded. Of course, this is not limited to timezones - I believe we could slice the schema cache that way in general, for example by schema etc.

Do you mean loading the pieces of the schema cache needed to fulfill a request on demand? So once the Prefer: timezone comes, only then do the SELECT name FROM pg_timezone_names;?

[wolfgangwalther]

Do you mean loading the pieces of the schema cache needed to fulfill a request on demand? So once the Prefer: timezone comes, only then do the SELECT name FROM pg_timezone_names;?

That would be one way to do it, which I proposed in a different context earlier, but was not wanted.

Here, I propose to trigger the load on startup, too - but not wait until the timezones are loaded before reporting ready. I.e. load the schema cache in (possibly independent) steps, still triggered as usual (startup, reload notification etc.), but serve requests on best effort depending on which parts have already been loaded.

[steve-chavez]

That would be one way to do it, which I proposed in a different context earlier, but was not wanted.

I might have missed that, do you mean this #4364 (comment)?

Here, I propose to trigger the load on startup, too - but not wait until the timezones are loaded before reporting ready. I.e. load the schema cache in (possibly independent) steps, still triggered as usual (startup, reload notification etc.), but serve requests on best effort depending on which parts have already been loaded.

That sounds like it would work for this issue, but would that complicate the library angle? I'm worried about complicating the schema cache logic more before that's completed.