Subject: [pgjdbc/pgjdbc] issue #3811: PreparedStatement batch performance issue in a loop on v42.7.6 or later

From: "leandrokemp (@leandrokemp)" <[email-masked]>
Date: Thu, 18 Sep 2025 21:28:15 +0000

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

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Fri, 19 Sep 2025 18:25:10 +0000

What is the reason to execute `ps.toString()` within a loop?

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Sat, 20 Sep 2025 06:25:07 +0000

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

---

From: "leandrokemp (@leandrokemp)" <[email-masked]>
Date: Sun, 21 Sep 2025 20:29:00 +0000

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.