Optimizing storage size of Azure DB for PostgreSQL

This article describes how storage size affects the result of benchmarking, pgbench.

The storage size of Azure DB for PostgreSQL is 100 GB by default. It can be enough for your data, but you should know that IOPS is defined with storage size by 3 IOPS per GB. And you should understand the fact that pgbench is just a benchmark as well. It doesn’t reflect the real querying pattern from your app. The queries from pgbench are too simple, so the result will be very sensitive for latency. Another I need to mention here is that the minimum IOPS for Azure DB for PostgreSQL is 100 regardless of storage size. Both 5GB and 33GB show a similar result of benchmarking.

I tested with following settings.
PostgreSQL: General Purpose, 4 vCore(s)
Client: Standard F16s (16 vcpus, 32 GiB memory), Ubuntu 18.04

Firstly, inserted 10M records.

$ pgbench -i "host=riopostgres.postgres.database.azure.com port=5432 dbname=postgres user=rifujita@riopostgres password={password} sslmode=require" -s 100

Checked that about 1.5GB data existed after inserting.

postgres=> select datname, pg_size_pretty(pg_database_size(datname)) from pg_database where datname='postgres';
 datname  | pg_size_pretty 
----------+----------------
 postgres | 1503 MB
(1 row)

pgbench command executed

$ pgbench -c 10 -t 1000 "host=riopostgres.postgres.database.azure.com port=5432 dbname=postgres user=rifujita@riopostgres password={password} sslmode=require"

Common parameters through benchmarks

transaction type: 
scaling factor: 100
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000

33 GB(100 IOPS)
latency average = 91.464 ms
tps = 109.332645 (including connections establishing)
tps = 109.800127 (excluding connections establishing)
50 GB(150 IOPS)
latency average = 60.443 ms
tps = 165.445682 (including connections establishing)
tps = 166.176647 (excluding connections establishing)
100 GB(300 IOPS)
latency average = 34.883 ms
tps = 286.669194 (including connections establishing)
tps = 288.647951 (excluding connections establishing)
200 GB(600 IOPS)
latency average = 29.285 ms
tps = 341.474144 (including connections establishing)
tps = 344.244699 (excluding connections establishing)
400 GB(1,200 IOPS)
latency average = 28.352 ms
tps = 352.708873 (including connections establishing)
tps = 355.236614 (excluding connections establishing)
800 GB(2,400 IOPS)
latency average = 28.632 ms
tps = 349.256644 (including connections establishing)
tps = 352.543669 (excluding connections establishing)

Conclusion: From the performance standpoint, the best storage size is 200GB with this querying pattern, and data size, 1.5GB.

関連