agora inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedImproving insert performance
5+ messages / 5 participants
[nested] [flat]
* Improving insert performance
@ 2026-05-04 19:01 Русинов Семен <mrpinkolik@gmail.com>
2026-05-04 20:49 ` Re: Improving insert performance Laurenz Albe <laurenz.albe@cybertec.at>
2026-05-05 03:05 ` Re: Improving insert performance Greg Sabino Mullane <htamfids@gmail.com>
2026-05-11 17:59 ` Re: Improving insert performance Merlin Moncure <mmoncure@gmail.com>
0 siblings, 3 replies; 5+ 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] 5+ messages in thread
* Re: Improving insert performance
2026-05-04 19:01 Improving insert performance Русинов Семен <mrpinkolik@gmail.com>
@ 2026-05-04 20:49 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2026-05-04 20:49 UTC (permalink / raw)
To: Русинов Семен <mrpinkolik@gmail.com>; pgsql-performance
On Tue, 2026-05-05 at 00:01 +0500, Русинов Семен wrote:
> I am trying to optimize PostgreSQL for insert performance and I think
> I've reaced the limit of my knowledge and experience.
>
> 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.
>
> full_page_writes=off
> fsync=off
Don't, unless you don't mind corrupting your database after a crash.
> 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
How many concurrent sessions are you using?
Monitor pg_stat_activity and see if there are any frequent wait_events.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ 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>
2026-05-08 03:31 ` Re: Improving insert performance Roland Müller <rolmur@gmail.com>
2 siblings, 1 reply; 5+ 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] 5+ messages in thread
* Re: Improving insert performance
2026-05-04 19:01 Improving insert performance Русинов Семен <mrpinkolik@gmail.com>
2026-05-05 03:05 ` Re: Improving insert performance Greg Sabino Mullane <htamfids@gmail.com>
@ 2026-05-08 03:31 ` Roland Müller <rolmur@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Roland Müller @ 2026-05-08 03:31 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; pgsql-performance@postgresql.org, Русинов Семен <mrpinkolik@gmail.com>
JDBC is using autocommit by default.
You can switch it off and start using explit commits.
connection.setAutoCommit(false);
Greg Sabino Mullane <htamfids@gmail.com> ezt írta (időpont: 2026. máj. 5.,
K 6:05):
> 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] 5+ messages in thread
* Re: Improving insert performance
2026-05-04 19:01 Improving insert performance Русинов Семен <mrpinkolik@gmail.com>
@ 2026-05-11 17:59 ` Merlin Moncure <mmoncure@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Merlin Moncure @ 2026-05-11 17:59 UTC (permalink / raw)
To: Русинов Семен <mrpinkolik@gmail.com>; +Cc: pgsql-performance
On Mon, May 4, 2026 at 1:01 PM Русинов Семен <mrpinkolik@gmail.com> wrote:
> 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".
For single threaded single statement insert performance, several
constraints must be considered:
1) hardware sync performance
2) network round trip
3) transactions
#1 can be worked around by fsync (or the less dangerous synchronous_commit
variant). For modern storage (SSD) etc, this is rarely the boundary in
practice for well designed applications.
#2 can often be a killer and easily overlooked. In java, the trick is
often to try and figure out how to supply statements in bulk through the
driver, or find another way to consolidate queries so that less statements
give more inserts. I suspect this is your bottleneck. We also might
consider moving logic into the database.
#3 transactions are generally the right way to work around #1, each
statement resolving shoots a hardware flush and has other database
housecleaning.
To kind of see the upper bound of what's possible, try running this script:
DO
$$
DECLARE
x INT;
BEGIN
CREATE TABLE IF NOT EXISTS tester(id int);
TRUNCATE tester;
FOR x IN 1..1000000
LOOP
INSERT INTO tester VALUES(x);
END LOOP;
END;
$$;
On my laptop, this runs in just under two seconds, yielding about 500k
inserts/sec. This is designed to give the best possible showing by ruling
out all three considerations above.
merlin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-05-11 17:59 UTC | newest]
Thread overview: 5+ messages (download: mbox 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-04 20:49 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-05-05 03:05 ` Greg Sabino Mullane <htamfids@gmail.com>
2026-05-08 03:31 ` Roland Müller <rolmur@gmail.com>
2026-05-11 17:59 ` Merlin Moncure <mmoncure@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