public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedImproving insert performance
2+ messages / 2 participants
[nested] [flat]
* Improving insert performance
@ 2026-05-04 19:01 Русинов Семен <mrpinkolik@gmail.com>
2026-05-05 03:05 ` Re: Improving insert performance Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Русинов Семен @ 2026-05-04 19:01 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Improving insert performance
2026-05-04 19:01 Improving insert performance Русинов Семен <mrpinkolik@gmail.com>
@ 2026-05-05 03:05 ` Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Sabino Mullane @ 2026-05-05 03:05 UTC (permalink / raw)
To: Русинов Семен <mrpinkolik@gmail.com>; +Cc: pgsql-performance
You can definitely do better than 35k rps. Setting the table as unlogged is
a great start. Are you using prepared queries? Have you tried COPY? What is
the size of the typical rows going in? Have you tried version 18?
By using small string values and COPY, I can easily get over 1 million rows
per second on a single thread, on an underpowered and fairly busy laptop,
to give you a rough idea of potential. Checking wait_events as Laurenz says
is a great idea. I'd also do 10 inserts with log_statement='all' on so you
can see exactly what the driver and application are really doing for those
inserts. Play around with the thread sizes to find the best combo.
> transactional inserts: yes
This is worrisome - are you committing after every insert? That's gonna
hurt performance.
Cheers,
Greg
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-05-05 03:05 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-04 19:01 Improving insert performance Русинов Семен <mrpinkolik@gmail.com>
2026-05-05 03:05 ` Greg Sabino Mullane <htamfids@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox