I suggest to get acquainted with the transcript of the report of the beginning of 2016 by Vladimir Sitnikov "PostgreSQL and JDBC squeeze all the juices"
! . 10 NetCracker. . , Java, , SQL – , .
, , PostgreSQL . Java. , , Java. , .
:
. .
. 20 .
20 – . 100 , , , . . .
, . .
– . ? , , .
https://github.com/pgjdbc/pgjdbc/pull/478
, , . , , . .
Super extended query – , . , , - , - , . . , , . , .
, , – simple query extended query.
?
. . , , . . - .
Extended query – . , . - . . . .
. . Java . .
statement. . close. ? ? . . . , .
, . ? «close». , – . «PARSE EXECUTE DEALLOCATE».
statements? . PreparedStatement , , . , .
, , . statement, . – . REST statement id. .
?
– statements. : «prepare» «execute».
, , - - . , . , . statement. - , , .
, . . , . , .
? ?
statements. , , .
PostgreSQL . , .
.
.
– , PgSQL. . . . . 100500 . . – . -.
– . , . -. , .
https://github.com/pgjdbc/pgjdbc/pull/319
2015 . . . , . PgSQL, . . , .
Server-prepared statements 5- , .
– ? ? , .
, OLTP- - 20 . 0,5 , 20 . – 10 , 170 . OLTP . 1, 5, 10 , .
20 . 0 . .
? Java, .
- , – ? , , PL 8 LibPQ, , , . ? .
, , - . . , , , .
. . - , SQL-.
? , .
hashCode. CPU – hash’ . – . - . .
. . ORM, , NULL, -. Int, setInt. NULL, VARCHAR . NULL? . .
. , , , VARCHAR, Server-prepared statements. statement.
, , . NULL. , , PreparedStatements
, . , , . . .
? ? , – . . . . , , . , , - , . .
https://gist.github.com/vlsi/df08cbef370b2e86a5c1
, , . NULL . 20 . , .
, . . «?» «$1» , ?
https://gist.github.com/vlsi/df08cbef370b2e86a5c1
– . – . - . --. – - . , . , , , ?
? ? . generic . , , - generic , , , , . , . - , .
? , , - . , . +0, OFFSET 0. , . «+0» . .
– . , 6 «explain analyze». 5, .
– pgsql-hackers . , , – .
https://gist.github.com/vlsi/df08cbef370b2e86a5c1
– , . «+0». . , . . . «+0» , , .
6- explain’. 6 , . , . . .
, ? , . .
. , . . – . ? . . – , .
: «?». , , , , «search_path», , . , .
? , server-prepared statements , search_path - . . - .
, , . , . 9.1 . , .
Set search_path + server-prepared statements =
cached plan must not change result type
? – . search_path . , .
, . . , , . , , , - , : «, . , statements , ?». , - statements .
– , Java. PL/ pgSQL . .
. -. . . . Java- 128 .
, , . . . resultSet . ? ? - ? , Postgres . . OutOFMemory? OutOfMemory? ? - .
, . OFFSET/LIMIT. ? , autoCommit’ ?
, , . autoCommit, . ? .
, Postgres, . PgJDBC , .
FetchSize, . . statement , , , 10, 50. , autoCommit. autoCommit – .
setFetchSize – . , .
. . ? , , , 10 , . .
, , , : defaultRowFetchSize .
. – , . , INSERT, VALUES. . «INSERT SELECT». . .
, Batch statement, , . Postgres – COPY , . . .
, . , ? .
TCP . , , . , , , .
. , .
, . , 200 , . .
https://github.com/pgjdbc/pgjdbc/pull/380
, 10 . . ? , - . «128» – batching.
Java microbenchmark harness
, . , . , , .
. InsertBatch . InsertBatch , . . , values . . , , , COPY.
COPY.
. User default type, INSERT .
: pgjdbc/ubenchmsrk/InsertBatch.java, GitHub. , . .
. , , batch – . batching , . . .
. . . ? , . COPY, , .
. , VALUES, VALUES, VALUES 10 . . 1, 2, 4, 128. , Batch Insert, , . . . , , , VALUES . EXECUTE.
COPY – . . , . . COPY.
COPY , . . , , . Batch , COPY – .
What do we do next? They measured it. We understand that we must use either structures or the ingenious bacth, combining several values.
What needs to be learned from today's report?
- PreparedStatement is our everything. It gives a lot for performance. It gives a big barrel of tar.
- And you need to do EXPLAIN ANALYZE 6 times.
- And you need to dilute OFFSET 0, and tricks like +0 in order to edit the percentage of our problematic queries that remained there.