Measuring the Throughput of Your PostgreSQL Database Server

To measure the throughput of your PostgreSQL database server, which is measured in transactions per second (TPS), you can use the pgbench tool:

Note: If your PostgreSQL database server is in the cloud of a cloud infrastructure provider and you do not have SSH access to that server, use a virtual machine or a container with PostgreSQL installed (the pgbench tool is shipped with PostgreSQL). This virtual machine or container must be able to connect to TCP port 5432 of your server. When executing the commands below, use the --host (or -h) and --username (or -U) arguments to specify the address of your PostgreSQL database server and the user name of a PostgreSQL administrator.

1. Determine the size of a test database that will be used for measurement. To obtain integral measurements that are based on the computational power, memory, and disk storage of your server, multiply the amount of the memory of your server by 2 and use the result as the size of that database.
2. Calculate the pgbench scale factor for the size of your test database. To do this, you can use this open source tool.

3. Create the test database and populate it with test data using the scale factor that you calculated. For example, to create a test database of 20 GiB, execute these commands:

   # su - postgres
   $ createdb testdb
   $ /usr/pgsql-11/bin/pgbench -s1500 -i testdb
   ...

4. Measure the throughput using read-write transactions. For example:

   $ /usr/pgsql-11/bin/pgbench -T60 -j8 -c32 -P10 -r testdb
   ...
   tps = 285484 (excluding connections establishing)
   statement latencies in milliseconds:
            0.001  \set aid random(1, 100000 * :scale)
            0.111  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
   ...

5. Measure the throughput using read-only transactions. For example:

   $ /usr/pgsql-11/bin/pgbench -T60 -j8 -c32 -P10 -r -S testdb
   ...
   tps = 497564 (excluding connections establishing)
   ...

As a result, you will measure the throughput of your PostgreSQL database server. If the throughput for read-write transactions is less than 2000 TPS, or the throughput for read-only transactions is less than 4000 TPS, allocate more resources to your server.