Document JDBC reWriteBatchedInserts as best practice

[robert-s-lee]

@drewdeally found this option which improved customer's performance by over 3x.

https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters

reWriteBatchedInserts = boolean

This will change batch inserts from insert into foo (col1, col2, col3) values (1,2,3) into insert into foo (col1, col2, col3) values (1,2,3), (4,5,6) this provides 2-3x performance improvement

[robert-s-lee]

In order for this to kick in:

• use prepared statement
• works with "INSERT INTO accounts (id, balance) VALUES (?, 1000)"
• works with "INSERT INTO accounts (id, balance) VALUES (?, 1000) in conflict (id) do update set balance=excluded.balance"
• does NOT work with "INSERT INTO accounts (id, balance) VALUES (?, 1000) returning id,balance;"

The return code int[] count = stmt.executeBatch(); is also different:

• 0 is returned with regular batch
• -2 aka SUCCESS_NO_INFO is returned when reWriteBatchedInserts is enabled

attached is a sample java code:

javac BasicSample.java
java -cp ~/Downloads/postgresql-42.2.4.jar:./ BasicSample "jdbc:postgresql://127.0.0.1:26257/bank?sslmode=disable&user=maxroach&ApplicationName=BasicSample&reWriteBatchedInserts=true"

• query fingerprint show batch of 1,000 being converted into 7 that has 100 rows, and other sizes.

root@:26257/defaultdb> select key, count from crdb_internal.node_statement_statistics where application_name='BasicSample';
+--------------------------------------------------------------------------+-------+
|                                   key                                    | count |
+--------------------------------------------------------------------------+-------+
| CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)    |     1 |
| TRUNCATE TABLE accounts                                                  |     1 |
| INSERT INTO accounts(id, balance) VALUES ($1, _), (__more100__) ON       |     7 |
| CONFLICT (id) DO UPDATE SET balance = excluded.balance                   |       |
| INSERT INTO accounts(id, balance) VALUES ($1, _), (__more60__) ON        |     1 |
| CONFLICT (id) DO UPDATE SET balance = excluded.balance                   |       |
| INSERT INTO accounts(id, balance) VALUES ($1, _), (__more30__) ON        |     1 |
| CONFLICT (id) DO UPDATE SET balance = excluded.balance                   |       |
| INSERT INTO accounts(id, balance) VALUES ($1, _), (__more7__) ON         |     1 |
| CONFLICT (id) DO UPDATE SET balance = excluded.balance                   |       |
+--------------------------------------------------------------------------+-------+
(6 rows)

Time: 2.844919ms

on OSX, we can watch the packet to confirm

tshark -i lo -f "tcp port 26257" -d tcp.port==26257,pgsql -O pgsql -Y pgsql

BasicSample.java.txt

[jseldess]

@awoods187, in our discussion on third-party tools, we might want to consider more detailed guidance/tips for JDBC/Hibernate.

[awoods187]

yeah i think that makes sense--we will probably need docs pages for each tool that show tips/tricks etc that are needed to make things work well

[robert-s-lee]

https://vladmihalcea.com/9-postgresql-high-performance-performance-tips/ had details on hibernate.

[rmloveland]

@jseldess I'm reassigning this to me - believe it will be part of accomplishing #2464

(shout if you disagree or are already working on this!)

[robert-s-lee]

reWriteBatchedInserts technique was added to opensource YCSB brianfrankcooper/YCSB#1220 there are results of single, batch, batchrewrite performance comparison that may be useful.

[rmloveland]

Fixed by #4855

[nirao-dd]

Does the reWriteBatchedInserts kick in for upsert into <table> <columns> <values>? @rmloveland @robert-s-lee @jseldess