PostgreSQL Benchmarks: Optimizing Database Performance with Tuning

EDB Postgres Advanced Server (EPAS) and PostgreSQL ship with a set of default configurations designed to perform well on conservatively sized hardware. There’s plenty of room for improvement by customizing the settings and configuration for your specific workload and hardware environment. You might be surprised by how much of a performance boost you can get by just tuning a few database parameter configurations. 

In a recent blog published by my colleague, Vik Fearing, “PostgreSQL TPROC-C Benchmarks: PostgreSQL 12 vs. PostgreSQL 13 Performance,” EIGHT times as many transactions per minute were achieved on both 12.4 and 13.0 PostgreSQL when it was properly tuned vs. not tuned (i.e. default). This work was using HammerDB, a TPC-C like benchmark. 
 

Test results on 12.4

It’s important to note that the significant performance gain holds steady as the number of virtual users increases.

Test results on 13

The following table lists the tuneable database parameters and their respective settings Vik used in his benchmark testing. All other settings remained at the default values. The testing was done on an Amazon m5.metal instance running Red Hat Enterprise Linux release 8.2 with 96 vCPUs and 393216 MiB memory. The data directory was on eight disks configured in RAID 10 and the size of the data directory was 2000 GiB. It is worth noting that tuning effects vary on different workloads and hardware, and the values chosen in this case were specific to the hardware being used for the benchmark. What Vik performed was considered a ‘starter’ tuning configuration (i.e. one that we would suggest that users use as a starting point for their own deployments) for the specific server and workload that he chose. Further fine-tuning the settings could potentially yield even higher performance gains.