pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3811: PreparedStatement batch performance issue in a loop on v42.7.6 or later
4+ messages / 2 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3811: PreparedStatement batch performance issue in a loop on v42.7.6 or later
@ 2025-09-18 21:28 "leandrokemp (@leandrokemp)" <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: leandrokemp (@leandrokemp) @ 2025-09-18 21:28 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
**Describe the issue**
The app I work on run a loop of inserts and add those inserts to a PreparedStatement batch.
There are more than 100 thousand inserts, which are added to the batch in the loop.
On postgresql 42.7.5 and earlier versions the loop takes a few seconds to add all inserts to the ps batch.
From version 42.7.6, the loop hangs and never finishes. I've tested it for more than 30 minutes and it didn't finish. Nothing has changed in my app, except the postgresql library upgrade.
**Driver Version?**
42.7.6
**Java Version?**
21
**OS Version?**
MacOS 26 and AWS Lambda container
**PostgreSQL Version?**
15
**To Reproduce**
```
import java.sql.*;
import java.util.List;
import java.util.Properties;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
public class TestBatchLoop {
public static void main(String []args) throws Exception {
String url = "jdbc:postgresql://localhost:5432/test";
Properties props = new Properties();
props.setProperty("user", "test");
props.setProperty("password", "test");
// generate a list with 100 thousand values
List<String> values = IntStream.rangeClosed(1, 100_000)
.mapToObj(i -> "value" + i)
.collect(Collectors.toList());
try ( Connection conn = DriverManager.getConnection(url, props) ){
try ( PreparedStatement ps = conn.prepareStatement("insert into table(column) values(?)") ) {
// on version 42.7.5 and earlier this finishes in a few seconds
// on version 42.7.6 and later this didn't finish until I stopped it after about 30 minutes
for(String value : values) {
ps.setString(1, value);
String stm = ps.toString();
ps.addBatch();
}
ps.executeBatch();
conn.commit();
}
}
}
}
```
**Expected behaviour**
It should complete the loop in a few seconds
**Logs**
There are no logs. It just hangs in the loop
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3811: PreparedStatement batch performance issue in a loop on v42.7.6 or later
@ 2025-09-19 18:25 ` "vlsi (@vlsi)" <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: vlsi (@vlsi) @ 2025-09-19 18:25 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
What is the reason to execute `ps.toString()` within a loop?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3811: PreparedStatement batch performance issue in a loop on v42.7.6 or later
@ 2025-09-20 06:25 ` "vlsi (@vlsi)" <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: vlsi (@vlsi) @ 2025-09-20 06:25 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@leandrokemp , Previously, `ps.toString();` rendered only the the last value, and 0a88ea425e86dce691a96d6aa7023c20ac887b98 probably fixed it, so now `ps.toString()` renders all the values in a batch. Now `ps.toString()` properly displays all the values it is about to send to the database, and it is far `toString()` to take longer time as the list of values grows. This makes `ps.toString(); ps.addBatch()` loop behave with O(N^2) properties, thus it effectively hangs.
If you want to log the executed statement, you'd better move the logging out of the loop just before `executeBatch`.
I am leaning towards to closing the issue with "working as expected, won't fix" resolution unless you justify the business case behind calling `ps.toString(); ps.addBatch();` within a loop.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3811: PreparedStatement batch performance issue in a loop on v42.7.6 or later
@ 2025-09-21 20:29 ` "leandrokemp (@leandrokemp)" <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: leandrokemp (@leandrokemp) @ 2025-09-21 20:29 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Thank you pointing that out @vlsi.
I've tested moving ps.toString() outside of the loop and it worked, as you mentioned.
In my program, there is no specific reason for toString() to be inside the loop. So, this resolves the issue for me.
Thank you.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-09-21 20:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-18 21:28 [pgjdbc/pgjdbc] issue #3811: PreparedStatement batch performance issue in a loop on v42.7.6 or later "leandrokemp (@leandrokemp)" <[email protected]>
2025-09-19 18:25 ` "vlsi (@vlsi)" <[email protected]>
2025-09-20 06:25 ` "vlsi (@vlsi)" <[email protected]>
2025-09-21 20:29 ` "leandrokemp (@leandrokemp)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox