Blogmark

These kinds of performance / stress tests of PostgreSQL are always fascinating to me. There are many factors you need to consider about the machine’s stats, you have to set up multiple databases, maybe multiple clusters, and then after running a tool like pgbench, you have to make sure you’ve observed and captured useful data that you can draw conclusions from.

To try to quantify the crossover point, I ran a small test. I initialized a 8GB pgbench database that fits into shared buffers, then I set up WAL archiving and took a backup. Next, I ran 30min of pgbench with synchronous_commit=off to generate some WAL. On a 24 thread workstation, this generated 70GB of WAL containing 66M pgbench transactions, with an average speed of 36.6k tps. Finally, I configured Postgres to run recovery on the backup. This recovery was able to complete in 372 seconds, or 177k tps.

Here are some notes from AWS on benchmarking Postgres with pgbench: https://docs.aws.amazon.com/whitepapers/latest/optimizing-postgresql-on-ec2-using-ebs/postgresql-benchmark-observations-and-considerations.html