Message-ID: <c3bbe654-939b-467a-9626-8c0938b7347d@gmail.com>
Date: Tue, 5 May 2026 00:01:42 +0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
From: =?UTF-8?B?0KDRg9GB0LjQvdC+0LIg0KHQtdC80LXQvQ==?= <mrpinkolik@gmail.com>
Subject: Improving insert performance
Autocrypt: addr=mrpinkolik@gmail.com; keydata=
 xsFNBGDdbP4BEACxHgtdqUgOwTW811f80STub1LWcpVtvfkq45y9UTCf4JkjpnplzRCsHagY
 8rWzfubqGoYhryMBmtEBVQsqqxf2K4YJbra2rPbWcKl11y3GciVHWpqCXBlhREwSfannN16i
 VOPi4x+yGVo0FNoMie0ZSNu2ld8dbl9FZOsNFcqHzuokKhyw30VROaek6ybjtJ689RypU/EL
 bQ4j1ONt+rJgZfYTT43A8ZG+16JuUzcofgzY03cxFqHov0sQKEFjr482orAtSrUNmUFlXCbm
 68xaCuZJdO22KHro6/icWwJOYlBxOJNyrk8f7Ks2OY0R2qiM3/41R5McObTOeU51iqx+ezK9
 X0rMlZqgeJf2aEHrqQe7j3zIC8tqGlWwTT1k+eu2vd/uvRwkN2HFHaRvSB/Z5nKLW2Uq0G+f
 Ub6asJW4tCUNVyjQa52rSAyudiMxNlbunJ/RTzqRl3vacwariQkjSA35299gYzaM5QqxblPB
 fkLxaH7GXPvR2e8JDLr6dJmgXhsGcRgFbyfUObOm6T1Ls6ScRV+xQ6rCUSolGszKW5pIqTvv
 k1pGQ2tR2CaVceZlEwQBmXNmuhS17dahtytnxC6X3YFNcGuR3DulxuVNLxEXSDkZ6prVnpS8
 NqapVL+rwLtolYyGMd+r4C3YdcmnVzV56dGy/aeEl8shybXRdQARAQABzSRTaW1vbiBSdXNp
 bm92IDxtcnBpbmtvbGlrQGdtYWlsLmNvbT7CwY4EEwEIADgCGwMFCwkIBwIGFQoJCAsCBBYC
 AwECHgECF4AWIQRSC4v2d3/2ZLWzVSqgvJe+hzUr9QUCZJ52rQAKCRCgvJe+hzUr9YXaD/4p
 iEEVk0t6ab8TfdSojSIRB2oSTDab2SVXjsmTL+rU3AJwJz+X8wxZmyNc/I5gS8wkrzWHQGhf
 X5I2Ghdcf17OrQVt+Ui7H3mFA0xB+F3io/qluGiU7Rtun7C+HLWaSWL98Y+omwh41IYtxiQv
 OwWvFJVNA+IsYjvnvokWb9SYaNHtVCbzp2vi3nu5fM14kwTAyq9Xd+kN67SQm4qXCpnwtJ7c
 Bl8gKnTDubsDn8TXoIKunJZBxHWCvtOTOnur7zS/xqwxZAubzauYoD13al4/O+V/lOhgRsbs
 sHA2Iosnme1yZUSLYr0IBQhabq1Cri+LuOR1FPAlpXqPPUbdMo9FxmFf3R2K18P/rlOksDSI
 U2Uf5D2gddj3MNs4mb1Vb9efUsy60/O7er8BXl//lIojVQOtj2aULvqvmyCG44gu8/MXN1mp
 mo7zqdKYCq1miRGGZgxNa4kyoANsQVeiv9xK14k/ogXn/fwa86PUBcY8czAosi8TFxERaZhU
 1YTKWC88dWNvsnrDRJJq6wc/u3Z/zdRGBG79el00tcjIP7zT+2LaY6OkxRwMQS1x4oPmGLXQ
 VFr2pF8gWPKvUUIjqIlpHG1c1DJmOE0qkTqm6aTuBdBTbbFEWISoHo9F8MIlvfThQiR5ULEk
 5FsCGQNC1L9AXJN19RAtOTC0Kftwg/ZN4c7BTQRg3Wz+ARAA04jDNh5J2WbvJnpzfiGuCAT4
 H9pumaNFakdI5z8MEk/5AiIXuR8Tc+QUy7mt689vwzKerbgnwVnTE8P86zL/zxN9DEhewc0U
 F4TMgN6GQS7SXAcMVEQp6byWx+J6uHHlaPZMjAm+U7NHsNeu19hmPK2GgEbE0tR8lXqQLgio
 +N0sRDtDnmNXNtvdm1sVW2JyTRLqJ/mBS6cf7x3dYCr6uD/NHmCuF6uCyV7sTg5gHULaVbYc
 FqXs5eQMAvIA2/yjpQsL8+JLKP92GM9oLYmFdJhPs6w7WL8TXgTUbzsBPWtxQCDhE2EXhnw7
 KtBMSmjCuRSZpIzo838uBCSEzizNl5GllFL/Hf020oKAk9Kt18Ah7616OnJQNKg0AkZvgCyu
 8bm5K/KBZZXKB+V3EWxol39VcXvH78UQ/huyDZ0YNCrpqKWkuSdTVvSTGPsYZS4X+RDf+Vyc
 JIjZ1xabGR+ajDVL5EFqkBOtTbhwyBCvutNaI1hXNYaGdi6/NobOQjrmgfhQsoyfAMCTab/B
 loq7CkFFGs09xlWdOSxpyxdltiYJDwiMoQXrUEYMQ0bW4b1OI0EU8rLA4gNpTRHYJp6Z6hl8
 kgBO8z+l/PtBCbGUO5G6zbFTMj1RMOgMcY0OVIJanySTwKprDc2cVRjML0IZlLp5/Dtvry6F
 pBxTQRfVeKkAEQEAAcLBdgQYAQgAIAIbDBYhBFILi/Z3f/ZktbNVKqC8l76HNSv1BQJknncP
 AAoJEKC8l76HNSv1XrEP/1+0MJVUafU6ZsQHJ232lKhZSWOsD7wR8hy3jz4j+1nBtIMz2Djr
 gU2L/9wOv/O3cKRYw+Rqg5LC3FuVTRRvSw7RedkBbfJdvTA3avyqrpjS4HEOugyPJ+T9jcuq
 2RGfHPuy177z2hop2W38TD4wRmwlxALJ4a8vpzeAWV6NTDcRVU1cnKOAm0IqUNxskLyMAN+v
 vVda5RzlyHGwr63DYJEEteOtBmWK4kh0xUNtDZRtZ6Fe6mMeJOk3YGSA+5orLoM7zF79fp6g
 02WDQppXF+Ujz+HYnvQ9VTJeuzd9AuuQiAF6tr0penhyYRQXlEsOKd9hlTpxW8pyFuhpNbWV
 2GKUh2vt276WiYhEKMwnsygSLipYdLuzskdxYI9rnGq0xLOVCqxDwq0Icq8S5HC2Xams3hDB
 aMmw/IHCYgY6Wtley9GD5EDOL9oHqaLKav1qgWXcugJ27D42B2dypWSchcqhwXKI1G/m0Y72
 GtcQxGB7byaQgRJgFvIfkG3kB0eBrOdj2lbEDem7/LxEJHka/6wD3TpWMDO8mvASLJHiOumO
 yAdoFVXSWtcJLO7l4rPEt+uOn21+XXhBgI9aqodKMwKb9aRDtw6z8nj+/KB7MJMYZB6aKWie
 1QATY2m4Us8KGNc1LozBEw8VcAezEZFlP+iSnrX6HtdXNxXuVqmOjLzY
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/c3bbe654-939b-467a-9626-8c0938b7347d%40gmail.com>
Precedence: bulk

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