Which instance do you use for your own Postgresql

Hello all,

we are just trying to figure out which instance type to use for running our own Postgres. We are only looking into Dedicated and High Mem instances.

High mem plans are really attractive, the only "flaw" for our use case is the limited SSD, which would mean we'd need to resort to Block storage (not excited about it:).

For all of you who are running your own postgres on Linode, we would greatly appreciate if you could share your experience. Thank you!

We did some very elementary testing with pgbench, the results are below.

Each instance has archive mode on (basic cp to local drive + wal-g to s3).

In addition we did some very basic tuning using https://pgtune.leopard.in.ua/#/ according to the instance resources (cpu/mem)

The results:
8Gb/4core

postgres@local:~$ pgbench -c 10 -j 2 -t 100000 -M prepared -r example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: prepared
number of clients: 10
number of threads: 2
number of transactions per client: 100000
number of transactions actually processed: 1000000/1000000
latency average = 2.010 ms
tps = 4976.349484 (including connections establishing)
tps = 4976.410952 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random(1, 100000 * :scale)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.001  \set delta random(-5000, 5000)
         0.125  BEGIN;
         0.202  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.162  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.196  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.247  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.161  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.825  END;

8Gb/4core

postgres@bleyk-pg-au:~$ pgbench -c 10 -j 4 -t 100000 -M prepared -r example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: prepared
number of clients: 10
number of threads: 4
number of transactions per client: 100000
number of transactions actually processed: 1000000/1000000
latency average = 1.913 ms
tps = 5226.255682 (including connections establishing)
tps = 5226.344317 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random(1, 100000 * :scale)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.001  \set delta random(-5000, 5000)
         0.107  BEGIN;
         0.176  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.146  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.174  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.223  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.144  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.810  END;

4GB/2core

postgres@local:~$ pgbench -c 10 -j 2 -t 100000 -M prepared -r example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: prepared
number of clients: 10
number of threads: 2
number of transactions per client: 100000
number of transactions actually processed: 1000000/1000000
latency average = 3.072 ms
tps = 3255.672750 (including connections establishing)
tps = 3255.706820 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random(1, 100000 * :scale)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.001  \set delta random(-5000, 5000)
         0.101  BEGIN;
         0.121  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.118  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.133  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.192  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.120  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         1.581  END;

20GB/2core ssd storage

postgres@local:~$ pgbench -c 10 -j 2 -t 100000 -M prepared -r example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: prepared
number of clients: 10
number of threads: 2
number of transactions per client: 100000
number of transactions actually processed: 1000000/1000000
latency average = 3.067 ms
tps = 3260.367278 (including connections establishing)
tps = 3260.410641 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random(1, 100000 * :scale)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.001  \set delta random(-5000, 5000)
         0.078  BEGIN;
         0.098  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.094  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.110  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.166  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.096  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         1.629  END;

20GB/2core block storage

postgres@local:~$ pgbench -c 10 -j 2 -t 100000 -M prepared -r example
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: prepared
number of clients: 10
number of threads: 2
number of transactions per client: 100000
number of transactions actually processed: 1000000/1000000
latency average = 7.135 ms
tps = 1401.639332 (including connections establishing)
tps = 1401.646247 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random(1, 100000 * :scale)
         0.001  \set bid random(1, 1 * :scale)
         0.000  \set tid random(1, 10 * :scale)
         0.000  \set delta random(-5000, 5000)
         0.058  BEGIN;
         0.055  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.044  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.112  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.612  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.045  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         5.677  END;