On Mon, May 4, 2026 at 1:01 PM Русинов Семен <[email protected]> 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
