PostgreSQL 9.4: O que vem por aí
timbira
A empresa brasileira de PostgreSQL
03/03/2015
Sobre este material
timbira
• Copyright © 2015 Timbira. Todos os direitos reservados.
• Embora este material tenha sido elaborado com toda
precaução, os autores não assumem quaisquer
responsabilidades por erros, omissões ou danos resultantes da
utilização das informações aqui contidas.
• É expressamente proibido copiar, modificar, publicar,
distribuir, transmitir ou vender este material.
• Se você encontrar qualquer erro, por favor reporte-o a
[email protected]
timbira
Resumo
1 Introdução
2 Performance
3 Desenvolvimento
4 Administração
5 Replicação e Recuperação
6 Extensões
7 Migração
8 9.5
9 Conclusão
Timbira - A empresa brasileira de PostgreSQL
1 / 39
Ciclo de Desenvolvimento
timbira
• início: branch 9.3
• Commit fest 1: jun/2013
• Commit fest 2: set/2013
• Commit fest 3: nov/2013
• Commit fest 4: jan/2014
• Beta 1: 15 mai/2014
• Beta 2: 24 jul/2014
• Beta 3: 09 out/2014
• RC1: 20 nov/2014
• lançamento: 18 dez/2014
• versão atual: 9.4.1
Estatísticas
2732 files changed, 293801 insertions(+), 241440 deletions(-)
Timbira - A empresa brasileira de PostgreSQL
2 / 39
timbira
Resumo
1 Introdução
2 Performance
3 Desenvolvimento
4 Administração
5 Replicação e Recuperação
6 Extensões
7 Migração
8 9.5
9 Conclusão
Timbira - A empresa brasileira de PostgreSQL
3 / 39
Melhorias de Performance
timbira
• reduzir bloqueios de alguns comandos ALTER TABLE
• visões materializadas pode ser atualizadas sem bloquear
leituras
• acelerar agregações que usam tipo NUMERIC
• escrever somente a parte modificada de registros atualizados
no WAL
• múltiplas conexões inserindo simultaneamente nos buffers do
WAL
• reduzir memória alocada em blocos DO
• aumentar limite da quantidade de memória usada por
ordenação durante criação de índice
Timbira - A empresa brasileira de PostgreSQL
4 / 39
timbira
Índices GIN
• reduzir tamanho dos índices GIN
• acelerar buscas em índices de múltiplas colunas
Timbira - A empresa brasileira de PostgreSQL
5 / 39
timbira
EXPLAIN
• saída do EXPLAIN ANALYZE mostra o tempo gasto com
planejamento da consulta
t i m b i r a=# EXPLAIN (ANALYZE, BUFFERS) SELECT bid ,
b b a l a n c e FROM pgbench_branches WHERE b i d > 4 ;
Seq Scan on pgbench_branches ( c o s t = 0 . 0 0 . . 1 . 0 6 rows=1
w i d t h =8) ( a c t u a l time = 0 . 0 1 2 . . 0 . 0 1 3 rows=1 l o o p s =1)
F i l t e r : ( b i d > 4)
Rows Removed by F i l t e r : 4
B u f f e r s : s h a r e d h i t =1
P l a n n i n g time : 0 . 1 5 6 ms
E x e c u t i o n time : 0 . 0 3 9 ms
(6 r e g i s t r o s )
Timbira - A empresa brasileira de PostgreSQL
6 / 39
timbira
Resumo
1 Introdução
2 Performance
3 Desenvolvimento
4 Administração
5 Replicação e Recuperação
6 Extensões
7 Migração
8 9.5
9 Conclusão
Timbira - A empresa brasileira de PostgreSQL
7 / 39
tipos de dados
timbira
• tipo de dados line
• tipo de dados pg_lsn
• tipo de dados jsonb
• aceita mesma entrada do que tipo json
• entrada é mais lenta
• suporta indexação
• não preserva espaços, ordem das chaves e chaves duplicadas
Timbira - A empresa brasileira de PostgreSQL
8 / 39
jsonb: Ilustrando
timbira
t i m b i r a=# c r e a t e t a b l e t e s t e j s o n ( a j s o n b ) ;
t i m b i r a=# i n s e r t i n t o t e s t e j s o n v a l u e s ( ’ {” c p f ” :
”123456789 −01” , ”nome ” : ” E u l e r T a v e i r a ” , ” empresa ” : ”
T i m b i r a ” , ” c i d a d e ” : [ ” Palmas ” , ” G o i â n i a ” , ” Porto
Alegre ”]} ’ ) ;
t i m b i r a=# i n s e r t i n t o t e s t e j s o n v a l u e s ( ’ {” c p f ” :
”111333555 −01” , ”nome ” : ” John Doe ” , ” empresa ” : ”
Google ” , ” c i d a d e ” : [ ” Rio de J a n e i r o ” , ” N a t a l ” ] } ’ ) ;
Timbira - A empresa brasileira de PostgreSQL
9 / 39
jsonb: Ilustrando consultas
timbira
t i m b i r a=# s e l e c t a−> ’ c p f ’ as cpf , a−> ’ nome ’ as nome from
t e s t e j s o n where a @> ’ {” empresa ” : ” Google ”} ’ ;
cpf
|
nome
−−−−−−−−−−−−−−−−+−−−−−−−−−−−−
” 111333555 −01 ” | ” John Doe”
(1 r e g i s t r o )
t i m b i r a=# s e l e c t a−> ’ c p f ’ as cpf , a−> ’ nome ’ as nome from
t e s t e j s o n where a −> ’ c i d a d e ’ ? ’ Porto A l e g r e ’ ;
cpf
|
nome
−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−
” 123456789 −01 ” | ” E u l e r T a v e i r a ”
” 123789456 −01 ” | ” F a b r i z i o M e l l o ”
(2 r e g i s t r o s )
Timbira - A empresa brasileira de PostgreSQL
10 / 39
jsonb: Ilustrando indexação
timbira
t i m b i r a=# c r e a t e i n d e x t e s t e j s o n i d x on t e s t e j s o n u s i n g
g i n ( ( a −> ’ c i d a d e ’ ) ) ;
t i m b i r a=# s e t e n a b l e _ s e q s c a n t o o f f ;
t i m b i r a=# e x p l a i n a n a l y z e s e l e c t a−> ’ c p f ’ as cpf , a−> ’
nome ’ as nome from t e s t e j s o n where a −> ’ c i d a d e ’ ? ’
Porto A l e g r e ’ ;
Bitmap Heap Scan on t e s t e j s o n ( c o s t = 8 . 0 0 . . 1 2 . 0 2 rows=1
w i d t h =32) ( a c t u a l time = 0 . 0 3 3 . . 0 . 0 3 6 rows=2 l o o p s =1)
Recheck Cond : ( ( a −> ’ c i d a d e ’ : : t e x t ) ? ’ Porto A l e g r e ’
: : text )
Heap B l o c k s : e x a c t=1
−> Bitmap Index Scan on t e s t e j s o n i d x ( c o s t
= 0 . 0 0 . . 8 . 0 0 rows=1 w i d t h =0) ( a c t u a l time
= 0 . 0 1 4 . . 0 . 0 1 4 rows=2 l o o p s =1)
Index Cond : ( ( a −> ’ c i d a d e ’ : : t e x t ) ? ’ Porto
Alegre ’ : : text )
P l a n n i n g time : 0 . 1 0 6 ms
E x e c u t i o n time : 0 . 0 7 7 ms
(7 r e g i s t r o s )
Timbira - A empresa brasileira de PostgreSQL
11 / 39
timbira
json
• novas funções para permitir construir documentos complexos
JSON
t i m b i r a=# s e l e c t * from j s o n _ t o _ r e c o r d ( ’ {” s o f t w a r e ” : ”
PostgreSQL ” , ” empresa ” : ” T i m b i r a ” , ” p a í s ” : ” B r a s i l ”} ’
) as t ( s o f t w a r e t e x t , empresa t e x t , c o u n t r y t e x t ) ;
s o f t w a r e | empresa | c o u n t r y
−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−
PostgreSQL | T i m b i r a | �
(1 r e g i s t r o )
t i m b i r a=# s e l e c t j s o n _ b u i l d _ o b j e c t ( ’ s o f t w a r e ’ , ’
PostgreSQL ’ , ’ empresa ’ , ’ T i m b i r a ’ , ’ p a í s ’ , ’ B r a s i l ’ ) ;
{” s o f t w a r e ” : ” PostgreSQL ” , ” empresa ” : ” T i m b i r a ” , ”
p a í s ” : ” B r a s i l ”}
(1 r e g i s t r o )
Timbira - A empresa brasileira de PostgreSQL
12 / 39
timbira
FILTER
• cláusula FILTER em agregações
• esqueça o CASE THEN!
t i m b i r a=# SELECT bid , count ( * ) t o t a l , count ( * ) FILTER (
WHERE d e l t a > 0) d e p o s i t o FROM p g b e n c h _ h i s t o r y GROUP
BY b i d ;
bid | t o t a l | deposito
−−−−−+−−−−−−−+−−−−−−−−−−
6 |
483 |
228
14 |
457 |
216
8 |
506 |
259
12 |
417 |
230
1 |
425 |
202
...
9 |
434 |
229
7 |
474 |
254
(15 r e g i s t r o s )
Timbira - A empresa brasileira de PostgreSQL
13 / 39
Agregação de conjunto ordenado
timbira
• nova classe de agregações
• cláusula WITHIN GROUP
•
t i m b i r a=# s e l e c t bid , mode ( ) w i t h i n group ( o r d e r by t i d )
from p g b e n c h _ h i s t o r y group by b i d ;
b i d | mode
−−−−−+−−−−−−
1 | 126
2 |
84
3 |
82
4 |
14
5 |
22
6 |
6
...
13 |
92
14 |
90
15 |
45
(15 r e g i s t r o s )
Timbira - A empresa brasileira de PostgreSQL
14 / 39
timbira
PL/pgSQL
• armazenar pilha de chamadas de uma função
• opção print_strict_params fornece parâmetros passados a
consultas que geram erros STRICT
• opções para fornecer erros (extra_errors) e avisos
(extra_warnings) adicionais de uma função
Timbira - A empresa brasileira de PostgreSQL
15 / 39
timbira
Gatilhos
• gatilhos em tabelas externas
• gatilhos de eventos em PL/Perl e PL/Tcl
Timbira - A empresa brasileira de PostgreSQL
16 / 39
timbira
Resumo
1 Introdução
2 Performance
3 Desenvolvimento
4 Administração
5 Replicação e Recuperação
6 Extensões
7 Migração
8 9.5
9 Conclusão
Timbira - A empresa brasileira de PostgreSQL
17 / 39
background workers
timbira
• background workers foi adicionado na 9.3
• pode ser registrado, iniciado e terminado dinamicamente
• memória compartilhada pode ser alocada dinamicamente
Timbira - A empresa brasileira de PostgreSQL
18 / 39
ALTER SYSTEM
timbira
• parâmetros em um outro arquivo postgresql.auto.conf
• os parâmetros são aplicados após postgresql.conf
• DEFAULT ou RESET removem o parâmetro do
postgresql.auto.conf
• os parâmetros tem efeito somente após o reload
• somente para super-usuários
• não é permitido dentro de um bloco de transação
Timbira - A empresa brasileira de PostgreSQL
19 / 39
ALTER SYSTEM: exemplo
timbira
t i m b i r a=# a l t e r syste m s e t work_mem t o ’ 5MB’ ;
t i m b i r a=# show work_mem ;
work_mem
−−−−−−−−−−
4MB
(1 r e g i s t r o )
t i m b i r a=# s e l e c t p g _ r e l o a d _ c o n f ( ) ;
LOG : SIGHUP r e c e b i d o , r e c a r r e g a n d o a r q u i v o s de
configuração
LOG : p a r â m e t r o ”work_mem” mudou p a r a ” 5MB”
Timbira - A empresa brasileira de PostgreSQL
20 / 39
pg_stat_archiver
timbira
t i m b i r a=# s e l e c t * from p g _ s t a t _ a r c h i v e r ;
−[ RECORD 1 ]−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
archived_count
| 13
l a s t _ a r c h i v e d _ w a l | 00000001000000000000000D
l a s t _ a r c h i v e d _ t i m e | 2015−03−02 15:38:05.272208 −03
failed_count
| 40
last_failed_wal
| 000000010000000000000001
last_failed_time
| 2015−03−01 05:38:00.048389 −03
stats_reset
| 2015−03−01 00:20:57.526647 −03
Timbira - A empresa brasileira de PostgreSQL
21 / 39
timbira
autovacuum
• novo parâmetro autovacuum_work_mem
• padrão é utilizar maintenance_work_mem (comportamento
antigo)
• controle independente de memória utilizada pelo autovacuum
Timbira - A empresa brasileira de PostgreSQL
22 / 39
timbira
tablespaces
• CREATE TABLESPACE ... WITH ( ... )
• ALTER [ TABLE | INDEX | MATERIALIZED VIEW ] ALL IN
TABLESPACE foo SET TABLESPACE bar
• move todos os objetos de uma tablespace para outra
Timbira - A empresa brasileira de PostgreSQL
23 / 39
timbira
Bloqueios
• informação detalhada sobre quem está esperando o que e em
qual tabela
LOG :
p r o c e s s o 15370 a i n d a e s p e r a p o r ShareLock em
t r a n s a ç ã o 1009 após 1 0 0 0 . 1 4 6 ms
DETALHE: P r o c e s s h o l d i n g t h e l o c k : 1 5 3 0 6 . Wait queue :
15370.
CONTEXTO: enquanto a t u a l i z a v a t u p l a ( 0 , 5 ) na r e l a ç ã o ”
foo ”
COMANDO: update f o o s e t b = ’ t e s t 2 ’ where a = 1 ;
Timbira - A empresa brasileira de PostgreSQL
24 / 39
timbira
Parâmetros
• log_connections mostra informação sobre SSL
• parâmetro huge_pages para utilizar huge pages no Linux
• aumento dos valores padrão:
• work_mem
• maintenance_work_mem
• effective_cache_size
• permitir unidades em TB
Timbira - A empresa brasileira de PostgreSQL
25 / 39
timbira
Opções
• createuser: opção -g especifica role da qual será membro
• pg_restore: opções -I, -P, -T e -n podem ser especificadas
várias vezes
• pg_dump, pg_dumpall e pg_restore: opção --if-exists
• adicionar IF EXISTS ao remover objetos antigos
• deve especificar opção --clean também
• pg_basebackup: opção --xlogdir
• pg_basebackup: opção --max-rate
• pg_basebackup: opção --tablespace-mapping
• vacuumdb: opção --analyze-in-stages
Timbira - A empresa brasileira de PostgreSQL
26 / 39
timbira
Resumo
1 Introdução
2 Performance
3 Desenvolvimento
4 Administração
5 Replicação e Recuperação
6 Extensões
7 Migração
8 9.5
9 Conclusão
Timbira - A empresa brasileira de PostgreSQL
27 / 39
Replicação e Recuperação
timbira
• novo parâmetro no recovery.conf recovery_min_apply_delay
para configurar atraso na replicação
• opção immediate do parâmetro recovery_target para
recuperação assim que estado consistente for atingido
• adicionado entradas (slots) de replicação
• adicionado novo wal_level chamado logical
• parâmetro REPLICA IDENTITY para controlar replicação
lógica
• opção user_catalog_table para identificar tabelas do usuário
envolvidas na replicação lógica
• decodificação lógica via walsender
• pg_recvlogical: receber dados da decodificação lógica
Timbira - A empresa brasileira de PostgreSQL
28 / 39
recovery_min_apply_delay: exemplo
timbira
standby_mode = ’ on ’
p r i m a r y _ c o n n i n f o = ’ h o s t = 1 2 7 . 0 . 0 . 1 ’ u s e r= ’ r e p l i c a c a o ’ ”
r e c o v e r y _ t i m e _ d e l a y = 30000
• tempo em milisegundos
• replicação sincrona não é afetada
Timbira - A empresa brasileira de PostgreSQL
29 / 39
timbira
Resumo
1 Introdução
2 Performance
3 Desenvolvimento
4 Administração
5 Replicação e Recuperação
6 Extensões
7 Migração
8 9.5
9 Conclusão
Timbira - A empresa brasileira de PostgreSQL
30 / 39
timbira
Extensões
• pgcrypto: função gen_random_uuid
• pg_prewarm: carregar dados de relações na cache do
PostgreSQL
• auto_explain: opção para incluir tempo de execução de
gatilhos
• pgbench: opção --progress
• pgbench: opção --rate (taxa de transações)
• pg_stat_statements: usar arquivo ao invés da memória
compartilhada para armazenar consultas
• pg_stat_statements: especificar um hash como identificador
da consulta
• pg_stat_statements: especificar parâmetro para omitir texto
da consulta
Timbira - A empresa brasileira de PostgreSQL
31 / 39
timbira
Resumo
1 Introdução
2 Performance
3 Desenvolvimento
4 Administração
5 Replicação e Recuperação
6 Extensões
7 Migração
8 9.5
9 Conclusão
Timbira - A empresa brasileira de PostgreSQL
32 / 39
Imcompatibilidades
timbira
• EXPLAIN ANALYZE: “total time” -> “execution time”
• DISCARD ALL: descarta estado de sequências
• autenticação krb5 removida: use GSSAPI
• usa o último parâmetro recovery_target_* especificado
• suporte ao sistema operacional IRIX
• última versão ago/2006
• suporte até dez/2013
• leia as notas de lançamento!
Timbira - A empresa brasileira de PostgreSQL
33 / 39
timbira
Resumo
1 Introdução
2 Performance
3 Desenvolvimento
4 Administração
5 Replicação e Recuperação
6 Extensões
7 Migração
8 9.5
9 Conclusão
Timbira - A empresa brasileira de PostgreSQL
34 / 39
timbira
9.5
• gatilho de eventos: COMMENT, SECURITY LABEL e
GRANT/REVOKE
• gatilho de eventos: reescrita de tabela
• ddl_command_start e ddl_command_end
• substituir checkpoint_segments por min_wal_size e
max_wal_size
• vacuumdb: modo paralelo
• REINDEX SCHEMA
• políticas de segurança a nível de registros (RLS)
• cláusula SKIP LOCKED para bloqueios em registros
• ALTER TABLE ... SET LOGGED / UNLOGGED
• IMPORT FOREIGN SCHEMA
Timbira - A empresa brasileira de PostgreSQL
35 / 39
timbira
9.5
• UPDATE foo SET (col1, col2, ...) = (SELECT ...), ...
• índice BRIN (Block Range Index)
• utilizar abreviação de chaves para ordenação rápida de tipo
text
• acelerar cálculo do CRC usando algoritmo slicing-by-8
• escrita do WAL
• aplicação do wAL
• novo formato do WAL
• ações do servidor em espera ao fim da recuperação
• action_at_recovery_target = pause | promote | shutdown
• pg_dump: opção --snapshot
Timbira - A empresa brasileira de PostgreSQL
36 / 39
timbira
Resumo
1 Introdução
2 Performance
3 Desenvolvimento
4 Administração
5 Replicação e Recuperação
6 Extensões
7 Migração
8 9.5
9 Conclusão
Timbira - A empresa brasileira de PostgreSQL
37 / 39
timbira
Referências
• pgsql-announce: PostgreSQL Weekly News
• http://www.postgresql.org/docs/current/
• http://www.postgresql.org/docs/devel/static
• http://www.depesz.com
• http://michael.otacoo.com
Timbira - A empresa brasileira de PostgreSQL
38 / 39
timbira
Treinamentos
• PostgreSQL Backup e Restore:
https://www.eventick.com.br/pgsql-bkp-201503
• PostgreSQL Tuning:
https://www.eventick.com.br/pgsql-tuning-201504
• PostgreSQL Monitoramento:
https://www.eventick.com.br/pgsql-mon-201505
• PostgreSQL Replicação Nativa:
https://www.eventick.com.br/pgsql-replicacao-201505
Timbira - A empresa brasileira de PostgreSQL
39 / 39