Tuning PostgreSQL with pgbench

The recommended size of shared_buffer is 25%. We have dedicated database server with 312G, a recommended starting value of shared_buffer is 25% of the memory in your system.
Larger settings for shared_buffers usually require a corresponding increase in checkpoint_segments, in order to spread out the process of writing large quantities of new or changed data over a longer period of time.

I have updated the parameter shared_buffer in postgresql.conf and restarted the service.

Before parameter change # 

A.  1000 transactions with 10 sessions using two threads

-bash-4.2$ ./pgbench -c 10 -j 2 -t 10000 db2019_pt

starting vacuum...end.

transaction type: <builtin: TPC-B (sort of)>

scaling factor: 50

query mode: simple

number of clients: 10

number of threads: 2

number of transactions per client: 10000

number of transactions actually processed: 100000/100000

latency average = 5.159 ms

tps = 1938.401955 (including connections establishing)

tps = 1938.558540 (excluding connections establishing)

After parameter change#

A.. After parameter(shared_buffer)  changes 1000 transactions with 10 client using 2 threads

-bash-4.2$ ./pgbench -c 10 -j 2 -t 10000 db2019_pt

starting vacuum...end.

transaction type: <builtin: TPC-B (sort of)>

scaling factor: 50

query mode: simple

number of clients: 10

number of threads: 2

number of transactions per client: 10000

number of transactions actually processed: 100000/100000

latency average = 4.548 ms

tps = 2198.851379 (including connections establishing)

tps = 2199.039680 (excluding connections establishing)

-bash-4.2$

Our transactions per sec went from 1938 to 2199 with 10% increase in performance, the throughput increased after increasing the value of shared_buffer.