Hello everyone!
I am trying to optimize PostgreSQL for insert performance and I think
I've reaced the limit of my knowledge and experience.
Here's what I'm trying to do:
I have a synthetic java application which simulates a real production
application. This application is using postgresql jdbc driver in
combination with jooq framework.
This app inserts records into my table called "outbox".
So far, I was able to reach RPS of 35k inserts per second. But I can't
tune it any better, neither I understand where is the bottleneck.
What I am using for PostgreSQL:
48 vCPU,
64 GB RAB,
SSD
Version: 17
Number of postgres instances: 1
Synthetic app settings per app (run on different VMs than postgres):
connection pool size: 200
insert threads: 50
transactional inserts: yes
Number of instances of synthetic apps: 5
Postgresql.conf options which differ from defaults:
```
max_connections = 1200 # change requires restart
ssl = on
shared_buffers = 8GB # min 128kB
huge_pages = on #on, off, or try
maintenance_work_mem = 4GB # min 64kB
dynamic_shared_memory_type = posix # the default is usually the
first option
effective_io_concurrency = 200 # 1-1000; 0 disables prefetching
max_worker_processes = 48 # (change requires restart)
max_parallel_workers_per_gather = 8 # limited by max_parallel_workers
max_parallel_maintenance_workers = 8 # limited by max_parallel_workers
max_parallel_workers = 48 # number of max_worker_processes
that
wal_level = logical # minimal, replica, or logical
wal_buffers = 256MB # min 32kB, -1 sets based on
shared_buffers
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0
- 1.0
max_wal_size = 32GB
min_wal_size = 4GB
shared_preload_libraries = 'pg_stat_statements,decoderbufs' # (change
requires restart)
wal_compression = on
autovacuum=off
#throuth put
commit_delay=10000 # 10ms
commit_siblings = 50
full_page_writes=off
fsync=off
random_page_cost = 1.1
effective_cache_size = 20GB
```
DDL of my table:
```
CREATE UNLOGGED TABLE ${database.defaultSchemaName}.outbox (
queue_name varchar(255) NOT NULL,
payload bytea NOT null
)
```
What I observe:
User CPU usage: ~15-20%
Idle CPU: ~80-90%
IO wait: ~0-0.5%
pg_locks is around 400-500 out of 1000 connections at all times for this
table.
So I don't see that PostgreSQL is bound to hardware since CPU is not
used at full, IO is also not the problem, RAM also seems to be fine. I
tried scaling synthetic applications but it doesn't give any RPS boost.
So I'm stuck here. Have I reached PostgreSQL performance cap? Or do I
have a bottleneck somewhere else? I don't have any ideas anymore what
can I try, I would appreciate any help
