Thread: Re: Database creation performance drop going from pg 14 to pg 15+
I had some more time/mood to mess around with this. Interestingly enough, psql CLI seems to be consistently slower than the Swift client (PostgresNIO: https://github.com/vapor/postgres-nio) that I was using. The new psql-CLI packet captures are attached.
Though it does appear that psql uses SimpleQuery by default, which makes sense considering it's a CLI tool. PostgresNIO uses ExtendedQuery by default which can explain the differences. PostgresNIO does not support SimpleQuery yet; I'm awaiting a review for a PR that adds that support: https://github.com/vapor/postgres-nio/pull/505.
The other notable thing is that I re-ran the scripts but with using psql CLI, and although things are slower (per the packet captures), they are at least consistent. I can't see any significant performance differences between Postgres versions when using psql CLI, which might mean that this performance degradation only happens under ExtendedQuery and not SimpleQuery.
Here's how I ran the commands that seem to be at fault when using PostgresNIO. I only ran the drop-db create-db commands, not the whole chain, which should still be sufficient:
```
PGPASSWORD=xxx psql -U spi_test -h 127.0.0.1 postgres -c "DROP DATABASE IF EXISTS spi_test WITH (FORCE);" -c "CREATE DATABASE spi_test;"
```
For checking this against multiple postgres versions and different auth mechanisms like in the original repo in the `run-perf-test.sh` script, I ran the commands through Docker CLI (podman to be precise, docker is an alias to podman here):
```
```
docker exec spi_test bash -c 'COMMAND_ABOVE'
```
For packet captures, I opened up a shell into the container and ran the psql command while having tcpdump capturing the packets.
Here's an image of an overview of the packet captures. The 2 on the left are the old ones with PostgresNIO, the 2 on the right are the new ones using psql. The postgres versions are mentioned in the Wireshark window names up there or down below.
For packet captures, I opened up a shell into the container and ran the psql command while having tcpdump capturing the packets.
Here's an image of an overview of the packet captures. The 2 on the left are the old ones with PostgresNIO, the 2 on the right are the new ones using psql. The postgres versions are mentioned in the Wireshark window names up there or down below.
As you can see in the image, the psql ones consistently take 63ms compared to the 37/51ms of when using PostgresNIO.
The StartupMessage is set as the time reference for all captures.
For clarity, in the image only the first connection is filtered-in from the PostgresNIO captures.
For clarity, in the image only the first connection is filtered-in from the PostgresNIO captures.