Slow connect with PostgreSQL 14 image

[pimeys]

We've been witnessing a regression when integrating the PostgreSQL 14 docker image to our CI setup. We run about 18000 tests with PostgreSQL Docker images from 9 to 13, and adding the version 14 to our test setup showed a huge slowdown on how fast our tests finish. Typically on CI a PostgreSQL test run takes about 15 minutes, but with version 14 the tests were completed after over an hour.

I could minimize our test setup to a simple test project, with benchmarks for the following:

• Connect with versions 13 and 14. TLS off, the default configurations from both images.
• Opening a connection first, then running SELECT 1 on both versions using the same connection.

We see the following results

pg14 full connect       time:   [2.4987 ms 2.5077 ms 2.5174 ms]
pg13 full connect       time:   [1.2523 ms 1.2607 ms 1.2690 ms]
pg14 select 1           time:   [100.03 us 103.47 us 107.37 us]
pg13 select 1           time:   [106.56 us 111.29 us 116.64 us]

As seen from the results, forming a new connection to version 14 docker image takes quite a bit longer compared to 13. Of course the first thing to check is changing the password_encryption setting from scram-sha-256 to md5, but this has no effect.

The interesting thing here is, that by running the databases without docker, with two different nix configurations:

services.postgresql = {
  enable = true;
  package = pkgs.postgresql_14;
  enableTCPIP = true;
  authentication = pkgs.lib.mkOverride 10 ''
    local all all trust
    host  all all 127.0.0.1 255.255.255.255 trust
    host  all all ::1/128 trust
  '';
  initialScript = pkgs.writeText "backend-initScript" ''
    ALTER USER postgres WITH PASSWORD 'prisma';
  '';
};

And the other one with package = pkgs.postgresql_13, actually do not show any difference with connection times, which are hovering between 0.8 and 0.9ms. I'm interested is this due to some configuration in the PostgreSQL 14 docker image, or is it a regression in Docker itself that is somehow visible with some changes in the database.

> docker --version
Docker version 20.10.9, build v20.10.9

Issue for rust-postgres: sfackler/rust-postgres#829

[yosifkit]

this release now makes the standards-compliant SCRAM-SHA-256 password management and authentication system the default on new PostgreSQL instances

https://www.postgresql.org/about/news/postgresql-14-released-2318/

So, it seems that with the swap to scram-sha-256 as the default, then initdb uses that unless specified otherwise. So for the initial user created (postgres), the password is SCRAM encrypted.

To ease transition from the md5 method to the newer SCRAM method, if md5 is specified as a method in pg_hba.conf but the user's password on the server is encrypted for SCRAM (see below), then SCRAM-based authentication will automatically be chosen instead.

https://www.postgresql.org/docs/14/auth-password.html

And with that, even though pg_hba.conf contains md5, it is actually using the newer method since that is what the initial user was created with.

---

So running your tests by adding the following to the environment of the postgres-13 service makes postgres:13 just as slow as 14:

      POSTGRES_HOST_AUTH_METHOD: "scram-sha-256"
      POSTGRES_INITDB_ARGS: "--auth-host=scram-sha-256"

pg14 full connect       time:   [7.9032 ms 7.9241 ms 7.9454 ms]
pg13 full connect       time:   [7.9242 ms 7.9444 ms 7.9648 ms]

Alternatively, adding the following to the environment of the postgres-14 service makes postgres:14 just as fast as 13. This will use the "less secure challenge-response mechanism".

      POSTGRES_INITDB_ARGS: "--auth-host=md5"

pg14 full connect       time:   [1.4247 ms 1.4323 ms 1.4394 ms]
pg13 full connect       time:   [1.4347 ms 1.4446 ms 1.4538 ms]

Note: we will likely change the default "POSTGRES_HOST_AUTH_METHOD" in the 14 images to match upstream's change, so you'd also need to set POSTGRES_HOST_AUTH_METHOD: "md5" as well to use the older method.

[yosifkit]

I've added #897 to auto adjust the value that the entrypoint puts into pg_hba.conf to reflect the password encryption method. It should make POSTGRES_INITDB_ARGS: "--auth-host=md5" (or scram-sha-256) just work for all versions 😄

[pimeys]

Yay. I'll give this a spin later this weeks. Thank you!

[pimeys]

Works like a charm. Thank you.