А postgresql где стоит на хосте?
Я в плане разных recordsize ?

PG стоит на самом хосте. Или в контейнере. Это не принципиально - в контейнер пробрасывается каталог для базы из хоста

Понятно. Спасибо.

для СУБД (db и wal) созданы разные датасеты:

TYPE        NAME                      USED  RATIO  LUSED  AVAIL     LOGBIAS      SYNC    REC

filesystem  xpool/pg-rep-gcp/pg_wal  32.0G  1.00x  32.0G   260G     latency  standard    128K
filesystem  zpool/pg-rep-gcp/main     423G  1.77x   747G  1.27T     latency  standard     16K

TYPE        NAME                      USED  RATIO  LUSED  AVAIL     LOGBIAS      SYNC    REC

Если db на хосте, напрашивается мысль и pg на хосте?
Иначе в чем смысл pg в контейнере если база на хосте?

где-то pg на хосте (если это выделенный сервер под СУБД), а где-то pg в контейнер. Размещение в контейнере - это изоляция. Упавший контейнер не потянет за собой хост.

запустил

#pgbench -U postgres -i -s 5000 pgbench

HugePages на хосте выключены.

full_page_writes = on
synchronous_commit = on

Сергей, а есть разница zfs vs ext4 по производительности?

не делал полноценного тестирования, не могу сказать.

#pgbench -U postgres -i -s 5000 pgbench

500000000 of 500000000 tuples (100%) done (elapsed 460.93 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.

real  17m22.508s
user  2m40.988s
sys  0m3.157s

запускаю тест (сначала только SELECT)
1ый запуск:

root@hz:~# pgbench -U postgres -S -j 64 -c 64 -M prepared -T 120 -v -P 5 pgbench
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 5.0 s, 305302.2 tps, lat 0.208 ms stddev 0.186
...
progress: 105.0 s, 1004283.4 tps, lat 0.063 ms stddev 0.032
progress: 110.0 s, 1050040.0 tps, lat 0.061 ms stddev 0.032
progress: 115.0 s, 781985.1 tps, lat 0.082 ms stddev 0.101
progress: 120.0 s, 908814.1 tps, lat 0.070 ms stddev 0.074
transaction type: <builtin: select only>
scaling factor: 5000
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 89628737
latency average = 0.085 ms
latency stddev = 0.466 ms
tps = 746608.043846 (including connections establishing)
tps = 746726.621901 (excluding connections establishing)

повторный запуск:

root@hz:~# pgbench -U postgres -S -j 64 -c 64 -M prepared -T 120 -v -P 5 pgbench
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 5.0 s, 762618.6 tps, lat 0.083 ms stddev 0.046
progress: 10.0 s, 919992.5 tps, lat 0.069 ms stddev 0.039
...
progress: 115.0 s, 1072021.4 tps, lat 0.059 ms stddev 0.031
progress: 120.0 s, 1026368.6 tps, lat 0.062 ms stddev 0.038
transaction type: <builtin: select only>
scaling factor: 5000
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 119262222
latency average = 0.064 ms
latency stddev = 0.041 ms
tps = 993338.538056 (including connections establishing)
tps = 993476.650953 (excluding connections establishing)

Proxmox или centos/Ubuntu +kvm что лучше

Или не имеет значения

разницы практически нет. proxmox  это по сути обёртка к kvm

500000000 of 500000000 tuples (100%) done (elapsed 460.93 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.

real  17m22.508s
user  2m40.988s
sys  0m3.157s

root@hz:~# pgbench -U postgres -S -j 64 -c 64 -M prepared -T 120 -v -P 5 pgbench
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 5.0 s, 305302.2 tps, lat 0.208 ms stddev 0.186
...
progress: 105.0 s, 1004283.4 tps, lat 0.063 ms stddev 0.032
progress: 110.0 s, 1050040.0 tps, lat 0.061 ms stddev 0.032
progress: 115.0 s, 781985.1 tps, lat 0.082 ms stddev 0.101
progress: 120.0 s, 908814.1 tps, lat 0.070 ms stddev 0.074
transaction type: <builtin: select only>
scaling factor: 5000
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 89628737
latency average = 0.085 ms
latency stddev = 0.466 ms
tps = 746608.043846 (including connections establishing)
tps = 746726.621901 (excluding connections establishing)

root@hz:~# pgbench -U postgres -S -j 64 -c 64 -M prepared -T 120 -v -P 5 pgbench
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 5.0 s, 762618.6 tps, lat 0.083 ms stddev 0.046
progress: 10.0 s, 919992.5 tps, lat 0.069 ms stddev 0.039
...
progress: 115.0 s, 1072021.4 tps, lat 0.059 ms stddev 0.031
progress: 120.0 s, 1026368.6 tps, lat 0.062 ms stddev 0.038
transaction type: <builtin: select only>
scaling factor: 5000
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 119262222
latency average = 0.064 ms
latency stddev = 0.041 ms
tps = 993338.538056 (including connections establishing)
tps = 993476.650953 (excluding connections establishing)

Теперь полный тест TPC-B:
1ый запуск:

root@hz:~# pgbench -U postgres -j 64 -c 64 -M prepared -T 120 -v -P 5 pgbench
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 5.0 s, 32686.4 tps, lat 1.949 ms stddev 2.943
progress: 10.0 s, 35969.6 tps, lat 1.779 ms stddev 2.058
progress: 15.0 s, 38497.8 tps, lat 1.662 ms stddev 0.625
...
progress: 110.0 s, 38875.9 tps, lat 1.646 ms stddev 1.093
progress: 115.0 s, 40061.5 tps, lat 1.597 ms stddev 0.937
progress: 120.0 s, 40583.5 tps, lat 1.576 ms stddev 1.058
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 5000
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 5566544
latency average = 1.379 ms
latency stddev = 1.072 ms
tps = 46377.435469 (including connections establishing)
tps = 46384.181136 (excluding connections establishing)

2ой запуск:

root@hz:~# pgbench -U postgres -j 64 -c 64 -M prepared -T 120 -v -P 5 pgbench
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 5.0 s, 57940.8 tps, lat 1.100 ms stddev 3.716
progress: 10.0 s, 61918.6 tps, lat 1.033 ms stddev 0.545
...
progress: 110.0 s, 63380.2 tps, lat 1.009 ms stddev 0.552
progress: 115.0 s, 62976.2 tps, lat 1.016 ms stddev 0.576
progress: 120.0 s, 60301.4 tps, lat 1.061 ms stddev 1.653
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 5000
query mode: prepared
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 7571905
latency average = 1.014 ms
latency stddev = 1.054 ms
tps = 63085.371628 (including connections establishing)
tps = 63093.594319 (excluding connections establishing)

1 и 2 запуск это с full_page_writes = on
synchronous_commit = on ?

да

А с off ?

делаю

Простите за глупый вопрос - а pgbench какие именно select-ы делает?