Aumentando performance nos INSERTS e UPDATES PostgreSQL

• Fazer vários INSERTs e UPDATEs em apenas uma consulta, simultaneamente
• Fazer vários INSERTs e UPDATEs usando o "Batch Update"
• Usar o comando COPY ao invés de INSERT
• Usar Prepared Statements no servidor
• Usar Prepared Statements no cliente
• Ter o mínimo de índices possíveis
• Ao fazer um UPDATEs, alterar apenas colunas que não são indexadas (Chamado de hot update! Apenas no PostgreSQL, o MySQL InnoDB não sofre dessa penalidade)
• Particionar a tabela
• Criar tabela com UNLOGGED (cuidado!)
• Fazer INSERTs e UPDATEs em paralelo (rodando as várias consultas em várias conexões diferentes ao mesmo tempo, um connection pool ajuda muito)
• Desabilite o fsync (perigoso!)
• Desabilite o synchronous_commit (cuidado!)
• Não tenha mais de 1000 tabelas (thumb rule)

Usando Prepared Statements no servidor

Os PREPARED STATEMENTS são consultas SQL pré preparadas, um esqueleto de uma consulta sem seus valores. Um prepared statement tem interrogações nestes parâmetros, mais ou menos com essa cara:

INSERT INTO tabela (id, nome, idade)VALUES (?, ?, ?)

Ou mais ou menos essa cara:

Os PREPARED STATEMENTS são muito conhecidos como forma de impedir que SQL injections aconteçam no banco, mas o seu maior objetivo não é esse, pelo menos não para os prepared statements no Servidor.

Normalmente, ao enviar uma query ao banco de dados, o driver envia ao banco um textão contendo a consulta inteira. O banco por sua vez faz o parse desse texto, confere se há algum erro de sintaxe, pra só então executar o comando.

Fazendo prepared statements no servidor implicitamente

Fazendo prepared statements no servidor explicitamente

Com o prepared statement no servidor, é preciso primeiro enviar a consulta desejada ao servidor, mas com "?" nos valores a definir, conforme o exemplo abaixo:

-- Exemplo de INSERTINSERT INTO tabela (id, nome, idade)VALUES (?, ?, ?);-- Exemplo com SELECTSELECT *FROM tabelaWHERE idade BETWEEN ? AND ?;

Após o envio do "esqueleto" da consulta, você poderá substituir os ? por valores e finalmente enviar a consulta ao banco. Por trás dos panos o cliente enviará apenas os valores, e não a consulta inteira como de costume. Isso alivia o banco em 2 pontos: reduz o processamento gasto com parse do texto para código e reduz o tráfego entre o cliente o servidor.

Isso significa que o Prepared Statement no servidor ajuda apenas quando temos consultas repetidas que usam um mesmo esqueleto. É um caso perfeito para INSERTS em massa e também SELECTS.

Prós:
Reduz o tráfego de rede entre o cliente e o servidor
Reduz o processamento de parse
Promove proteção contra SQL Injection

Contras:
Os Prepared Statements ocupam um pedaço na memória RAM
Criar Prepared Statements a toda hora prejudica a performance

Descubra todos os Prepared Statements abertos na conexão atual com:

SELECT *FROM pg_prepared_statements;

ATENÇÃO - Não é possível verificar os prepared statements de outras conexões. Essa informação não pode ser obtida.

Os Prepared Statements são uma boa ferramenta de otimização nos casos em que a consulta/inserção/atualização é sempre igual, mudando apenas os parâmetros, por exemplo:

INSERT INTO tabela (x, y, z)VALUES (?, ?, ?);

Confira aqui a documentação oficial sobre prepared statements.
https://www.postgresql.org/docs/current/sql-prepare.html

Como o JDBC lida com Prepared Statements?

O JDBC cria automaticamente prepared statements no servidor se a conexão preparar a mesma consulta 5 vezes (ou um número de vezes definido no parâmetro prepareThreshold).

Fazendo cache de Prepared Statements no JDBC com Hikari Connection Pool

Estou procurando formas de fazer cache de Prepared Statements no PostgreSQL com o Hikari.

O HikariCP por si só não faz cache de Statements.

https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration

https://stackoverflow.com/questions/21716839/prepared-statement-cache-with-mysql-jdbc

Implicit Caching JDBC
https://stackoverflow.com/questions/12167108/using-oracle-jdbc-driver-implicit-caching-feature

HikariCP Prepared Statement
https://stackoverflow.com/questions/45546531/hikaricp-prepared-statement-cache

Referências

Oracle JDBC Developer Guide - Statement Caching
https://docs.oracle.com/cd/B10501_01/java.920/a96654/stmtcach.htm

Melhorando performance de INSERTs
https://github.com/vitaly-t/pg-promise/wiki/Performance-Boost

Melhorando performance de INSERTs - Discussão no Stack Overflow
https://stackoverflow.com/questions/41074557/how-to-improve-postgresql-performance-on-insert

Melhorando performance de INSERTs
https://www.petefreitag.com/item/454.cfm

Tunando seu PostgreSQL
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Exemplo de configuração do HikariCP
https://pt.stackoverflow.com/questions/55607/hikaricp-unable-to-get-driver-for-jdbc-url

Parâmetros de configuração do JDBC com Prepared Statements
https://stackoverflow.com/q/32286518/2789895

Considerações sobre Prepared Statement com o JDBC. Server side ou Client side?
https://stackoverflow.com/questions/32297503/whats-the-life-span-of-a-postgresql-server-side-prepared-statement

Artigo sensacional sobre performance de INSERTs no PostgreSQL e MySQL
https://medium.com/@jerolba/persisting-fast-in-database-jdbc-76912ec2ef42

Aumentando performance do JDBC com batches
https://dzone.com/articles/performant-batch-inserts-using-jdbc

Dicas para aumentar performance de insert no PostgreSQL
https://dzone.com/articles/13-tips-to-improve-postgresql-insert-performance

Discussão sobre inserts eficientes com o JDBC
https://stackoverflow.com/questions/3784197/efficient-way-to-do-batch-inserts-with-jdbc/17062396

Discussão sobre inserts eficientes com o JDBC (mysql)
https://stackoverflow.com/questions/2993251/jdbc-batch-insert-performance

Documentação oficial sobre o comando PREPARE
https://www.postgresql.org/docs/current/sql-prepare.html

Documentação sobre server prepared statements do PGJDBC
https://jdbc.postgresql.org/documentation/head/server-prepare.html

Lições aprendidas ao escalar PostgreSQL em 5 anos
https://onesignal.com/blog/lessons-learned-from-5-years-of-scaling-postgresql/