Horton

Thread

  1. Improving insert performance

    Русинов Семен <mrpinkolik@gmail.com> — 2026-05-04T19:01:42Z

    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

  2. Re: Improving insert performance

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-05-04T20:49:33Z

    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

  3. Re: Improving insert performance

    Greg Sabino Mullane <htamfids@gmail.com> — 2026-05-05T03:05:11Z

    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

  4. Re: Improving insert performance

    Roland Müller <rolmur@gmail.com> — 2026-05-08T03:31:14Z

    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
>
>

  5. Re: Improving insert performance

    Merlin Moncure <mmoncure@gmail.com> — 2026-05-11T17:59:54Z

    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