PostgreSQL 13 Upgrade and Performance Check on Ubuntu/Debian: 1.6GB/s random reads

PostgreSQL 13 was released last week. I'm excited about this one, as the more mature partitioning plus logical replication features allow some long-requested deployment architectures. I ran 13 through my usual 144 test quick spin to see if everything was working as expected. Mainly boring stuff, but I was pleased to see that with the simple 128 client/4X RAM benchmark workload, Postgres 13 is driving 1.6GB/s of random read traffic requests to my PCI-e 4.0 NVM-e SSD. It keeps up with a whole RAID volume of the old SATA SSDs! (I checked)

Hardware is standard enthusiast stuff, AMD Ryzen 9 3950X with 32 cores running clients on the server itself, delivering 140K reads/s. Motherboard is a Asus Pro WS X570-ACE. The SSD is Microcenter's Inland brand, the "Performance 2TB", using the popular Phison PCIe 4.0 controller that legit delivers 5GB/s sequential reads. (I checked)

Upgrading to PG13 on Ubuntu/Debian

I use a lot of Ubuntu or Debian Linux systems mainly because I like their wrapper scripts for managing multiple versions of PostgreSQL. This is a weird use case rather than something I expect people to worry about. You can run Postgres on any Linux distribution if you're willing to put the work into securing it.

PostgreSQL gives a simple guide to upgrading to new versions. It really was just 4 lines on my Ubuntu 20.04 system:

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql-13 postgresql-doc-13

I already had PG10 (standard from Ubuntu 18.04) and PG12 (from Ubuntu 20.04) on here, and this mess is why I like the upstream Debian provided pg_lsclusters tool:

$ pg_lsclusters
Ver Cluster Port Status Owner     Data directory              Log file
10  main    5434 online  /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
12  main    5432 online  /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
13  main    5433 online postgres  /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

One quick hack here...if you ever wonder where the port numbers are being assigned when doing this, it's not in a single config file. The wrapper assembles that information by reading the postgresql.conf files. It looks like this:

gsmith@rising:/etc/postgresql$ cat 13/main/postgresql.conf | grep ^port
port = 5433                   # (change requires restart)
gsmith@rising:/etc/postgresql$ cat 10/main/postgresql.conf | grep ^port
port = 5434                   # (change requires restart)
gsmith@rising:/etc/postgresql$ cat 12/main/postgresql.conf | grep ^port
port = 5432                   # (change requires restart)

Cluster setup

Once the database is installed you probably need to import whatever standard configuration changes are appropriate for your environment. At a minimum, as the postgres user you have to edit /etc/postgresql/13/main/pg_hba.conf to use whatever password/secret system makes sense for you. Mine is but a humble home lab so everything is trust. Don't judge me! I do benchmarks, other people here do security. There are two incantations to restart the database or simply reload a simple configuration change on this system:

gsmith@rising:~$ sudo systemctl restart postgresql@13-main
postgres@rising:~$ pg_ctlcluster 13 main reload

After many accidents over the years I always test out that my configuration on each port matches my expectations, like this:

$ psql -p 5433 -c "SELECT current_setting('shared_buffers'),version()"
 current_setting |                                                          version
-----------------+----------------------------------------------------------------------------------------------------------------------------
 4GB             | PostgreSQL 13.0 (Ubuntu 13.0-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit

Another quick tip: if you want to test drive a different version for a bit at the console, you can easily change the default port all the Postgres command line tools use with the PGPORT environment variable:

$ psql -c "select version()"
 PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
$ export PGPORT=5433
$ psql -c "select version()"
 PostgreSQL 13.0 (Ubuntu 13.0-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit

Benchmarking

The first benchmarks I run for every Postgres release are simple ones aiming to find surprise basic performance regressions in the code. The first one uses lots of simple SELECT statements hitting the database, using a grid of various database sizes and client loads. Crunchy gave me a long break this year to finally automate the whole mess with my pgbench-tools toolkit. Let me disclaim this first by saying it's normal for a few percent drift between versions, which can be anything from compiler changes to real optimizations that penalize trivial benchmark workloads. What I mainly look for are big jumps or the scaling curves moving around. So far PG13 is very close to 12, with a mild regression on this test only above 32 clients:

That thin gap only above 32 clients is 3% high and only shows up over 350K TPS, which is a benchmark rate for this class of setup--not a real-world one.

This is really down in the noise of this test, so I'm personally curious but in no way consider this is a real regression. There are so many times I've seen this before and it's rarely been the core database that's to blame. Here's just a few possibilities:

• pgbench itself is notoriously picky about how Linux schedules it. I've seen gaps like this that were 100% the fault of the pgbench client and Linux fighting, going back to 2008.
• I just added the PostgreSQL repo to this Ubuntu system at the beginning, the release is days old, and that new version gives me slightly different config options plus a bunch of things to set up. Early adopter problems are often weird.
• I ran the 13 tests after lots of 12 ones. For all I know my SSD is 2% slower this month because I burned out reserve flash or something. Latest gen hardware is picky; just the passage of time means I'm not quite comparing apples to apples. I may accept the headache of re-running the 12 tests then the 13 ones again, which is what it takes just to check for the tests getting slower over time problem.

It's back into the weeds for me, but I don't suggest you wait for that. PostgreSQL 13 on Ubuntu is perfectly fine. I checked!

To go out on the high note, how about that disk read graph again? Try doing that in the cloud! That was a 256GB database swapping into 64GB of RAM, and latency for individual disk reads were under 20ms the whole time too. Not bad for a home lab server with admittedly sub-par security. This is the same 1 minute test I started with: