Matheus de Oliveira
Matheus de Oliveira
<matheus.oliveira@dextra.com.br>
DBA PostgreSQL – consultorias e suportes 24x7/8x5
Mais de 15 anos trabalhando de suporte e consultoria PostgreSQL
http://www.dextra.com.br/ “Crafting Software, Transforming Business”
Instrutor dos treinamentos PostgreSQL
Concep¸c˜ao, desenvolvimento e suporte `a produ¸c˜ao
Matheus de Oliveira
<matheus.oliveira@dextra.com.br>
Mais de 15 anos trabalhando de suporte e consultoria PostgreSQL
http://www.dextra.com.br/ “Crafting Software, Transforming Business” 10 anos e mais de 10 mil profissionais
capacitados em todo o Brasil. http://www.dextraining.com.br/
Mais de 15 anos trabalhando de suporte e consultoria PostgreSQL
http://www.dextra.com.br/ “Crafting Software, Transforming Business” 10 anos e mais de 10 mil profissionais
capacitados em todo o Brasil. http://www.dextraining.com.br/
“Aprenda com quem faz na pr´atica”
SaaS para an´alise e monitoramento PostgreSQL
http://www.pganalytics.com.br/ “Facilitando a vida do DBA”
Database
Refactoring
“A simple change to a database schema that improves its design while retaining both its behavioral and informational semantics – in other words, you can neither add new functionality nor break existing
functionality, nor can you add new data nor change the meaning of existing data.”
Scott Ambler and Pramod Sadalage (2006). Refactoring databases: Evolutionary database design. Addison-Wesley.
Database Refactoring
• Segue a mesma ideia e princ´ıpios de refatora¸c˜ao de c´odigo, s´o que ´
e considerado um pouco mais dif´ıcil.
• Escrever e evoluir o c´odigo de forma disciplinada:
◦ nem t˜ao importante durante o desenvolvimento inicial (antes de entrar em produ¸c˜ao);
◦ essencial (pra todos?) ap´os ter entrado em produ¸c˜ao.
• Desenvolvedores, DBAs, sysadmins... DevOps... Todos
Estrat´egia:
Database Refactoring
Podemos dividir os tipos de refatora¸c˜ao em 6 categorias:
• Estrutural (structural);
• Qualidade dos dados (data quality);
• Integridade referencial (referential integrity);
• Arquitetural (architectural); • M´etodos/fun¸c˜oes (method);
• Transforma¸c˜ao (non-refactoring transformation).
Database Refactoring – exemplos
SELECT ... FROM m e n s a g e n s m
LEFT JOIN u s u a r i o _ l o c a l l
ON l . u s u a r i o _ i d = m . d e _ i d
AND m . d a t a _ h o r a BETWEEN l . d a t a _ i n i
AND l . d a t a _ f i m
Database Refactoring – exemplos
Em alguns casos, como no exemplo, n˜ao h´a necessidade de
sincroniza¸c˜ao:
• adicionar colunas `a tabelas, desde que essas possam ser NULL ou
tenham um valor DEFAULT definido;
• adicionar novas tabelas, vis˜oes ou fun¸c˜oes ao modelo;
Mudar a estrutura j´a ´e mais complicado:
Database Refactoring – sincroniza¸c˜
ao
Para muitos casos, como renomear uma coluna, mover de uma tabela para outra, remover uma tabela, entre outros; ´e necess´ario um mecanismo de sincroniza¸c˜ao. Estes podem ser feitos via:
• gatilhos (triggers); • vis˜oes (views);
Database Refactoring – dicas
• Tenha diversos ambientes (sandboxes para desenvolvedores,
ambiente de teste de carga, demonstra¸c˜ao, QA, etc.);
• Use ambientes de Integra¸c˜ao Cont´ınua (Continuous Integration);
• Pequenas modifica¸c˜oes s˜ao mais f´aceis de aplicar e testar (tente dividir uma grande tarefa e v´arias menores);
• Automatize tudo;
• Fa¸ca as pazes entre desenvolvedores e DBAs (dica:
pair-programming entre ambos sempre que poss´ıvel)... =) ;
• Teste...
•
Teste...
•TESTE...
Database Refactoring – dicas
• Tenha diversos ambientes (sandboxes para desenvolvedores,
ambiente de teste de carga, demonstra¸c˜ao, QA, etc.);
• Mantenha o versionamento da base num controle de vers˜oes, e
tamb´em a informa¸c˜ao da vers˜ao atual na pr´opria base (use
ferramentas como Sqitch, dbdeploy, Flyway, etc... ou fa¸ca a sua);
• Use ambientes de Integra¸c˜ao Cont´ınua (Continuous Integration);
• Pequenas modifica¸c˜oes s˜ao mais f´aceis de aplicar e testar (tente dividir uma grande tarefa e v´arias menores);
• Automatize tudo;
• Fa¸ca as pazes entre desenvolvedores e DBAs (dica:
pair-programming entre ambos sempre que poss´ıvel)... =) ;
• Teste...
•
Teste...
•TESTE...
Database Refactoring – dicas
• Tenha diversos ambientes (sandboxes para desenvolvedores,
ambiente de teste de carga, demonstra¸c˜ao, QA, etc.);
• Mantenha o versionamento da base num controle de vers˜oes, e
tamb´em a informa¸c˜ao da vers˜ao atual na pr´opria base (use
ferramentas como Sqitch, dbdeploy, Flyway, etc... ou fa¸ca a sua);
• Use ambientes de Integra¸c˜ao Cont´ınua (Continuous Integration);
Automatize tudo;
• Fa¸ca as pazes entre desenvolvedores e DBAs (dica:
pair-programming entre ambos sempre que poss´ıvel)... =) ;
• Teste...
•
Teste...
•TESTE...
Database Refactoring – dicas
• Tenha diversos ambientes (sandboxes para desenvolvedores,
ambiente de teste de carga, demonstra¸c˜ao, QA, etc.);
• Mantenha o versionamento da base num controle de vers˜oes, e
tamb´em a informa¸c˜ao da vers˜ao atual na pr´opria base (use
ferramentas como Sqitch, dbdeploy, Flyway, etc... ou fa¸ca a sua);
• Use ambientes de Integra¸c˜ao Cont´ınua (Continuous Integration);
• Pequenas modifica¸c˜oes s˜ao mais f´aceis de aplicar e testar (tente dividir uma grande tarefa e v´arias menores);
• Automatize tudo;
• Fa¸ca as pazes entre desenvolvedores e DBAs (dica:
pair-programming entre ambos sempre que poss´ıvel)... =) ;
• Teste...
•
Teste...
•TESTE...
Database Refactoring – dicas
• Tenha diversos ambientes (sandboxes para desenvolvedores,
ambiente de teste de carga, demonstra¸c˜ao, QA, etc.);
• Mantenha o versionamento da base num controle de vers˜oes, e
tamb´em a informa¸c˜ao da vers˜ao atual na pr´opria base (use
ferramentas como Sqitch, dbdeploy, Flyway, etc... ou fa¸ca a sua);
• Use ambientes de Integra¸c˜ao Cont´ınua (Continuous Integration);
• Pequenas modifica¸c˜oes s˜ao mais f´aceis de aplicar e testar (tente dividir uma grande tarefa e v´arias menores);
• Automatize tudo;
Teste...
•
Teste...
•TESTE...
Database Refactoring – dicas
• Tenha diversos ambientes (sandboxes para desenvolvedores,
ambiente de teste de carga, demonstra¸c˜ao, QA, etc.);
• Mantenha o versionamento da base num controle de vers˜oes, e
tamb´em a informa¸c˜ao da vers˜ao atual na pr´opria base (use
ferramentas como Sqitch, dbdeploy, Flyway, etc... ou fa¸ca a sua);
• Use ambientes de Integra¸c˜ao Cont´ınua (Continuous Integration);
• Pequenas modifica¸c˜oes s˜ao mais f´aceis de aplicar e testar (tente dividir uma grande tarefa e v´arias menores);
• Automatize tudo;
• Fa¸ca as pazes entre desenvolvedores e DBAs (dica:
• Teste...
•
Teste...
•TESTE...
Database Refactoring – dicas
• Tenha diversos ambientes (sandboxes para desenvolvedores,
ambiente de teste de carga, demonstra¸c˜ao, QA, etc.);
• Mantenha o versionamento da base num controle de vers˜oes, e
tamb´em a informa¸c˜ao da vers˜ao atual na pr´opria base (use
ferramentas como Sqitch, dbdeploy, Flyway, etc... ou fa¸ca a sua);
• Use ambientes de Integra¸c˜ao Cont´ınua (Continuous Integration);
• Pequenas modifica¸c˜oes s˜ao mais f´aceis de aplicar e testar (tente dividir uma grande tarefa e v´arias menores);
• Automatize tudo;
• Fa¸ca as pazes entre desenvolvedores e DBAs (dica:
pair-programming entre ambos sempre que poss´ıvel)... =) ;
• Teste...
Database Refactoring – dicas
• Tenha diversos ambientes (sandboxes para desenvolvedores,
ambiente de teste de carga, demonstra¸c˜ao, QA, etc.);
• Mantenha o versionamento da base num controle de vers˜oes, e
tamb´em a informa¸c˜ao da vers˜ao atual na pr´opria base (use
ferramentas como Sqitch, dbdeploy, Flyway, etc... ou fa¸ca a sua);
• Use ambientes de Integra¸c˜ao Cont´ınua (Continuous Integration);
• Pequenas modifica¸c˜oes s˜ao mais f´aceis de aplicar e testar (tente dividir uma grande tarefa e v´arias menores);
• Automatize tudo;
• Fa¸ca as pazes entre desenvolvedores e DBAs (dica:
ambiente de teste de carga, demonstra¸c˜ao, QA, etc.);
• Mantenha o versionamento da base num controle de vers˜oes, e
tamb´em a informa¸c˜ao da vers˜ao atual na pr´opria base (use
ferramentas como Sqitch, dbdeploy, Flyway, etc... ou fa¸ca a sua);
• Use ambientes de Integra¸c˜ao Cont´ınua (Continuous Integration);
• Pequenas modifica¸c˜oes s˜ao mais f´aceis de aplicar e testar (tente dividir uma grande tarefa e v´arias menores);
• Automatize tudo;
• Fa¸ca as pazes entre desenvolvedores e DBAs (dica:
pair-programming entre ambos sempre que poss´ıvel)... =) ;
• Teste...
•
Teste...
•TESTE...
Zero Downtime
Upgrades
• Existe um mito que qualquer atualiza¸c˜oes em bancos de dados relacionais ´e extremamente lenta.
• E fato que opera¸´ c˜oes DDL (Data Definition Language) muitas
vezes bloqueiam opera¸c˜oes DML (Data Manipulation Language),
mas ´e poss´ıvel fazer com que este bloqueio seja extremamente
r´apido na grande maioria dos casos.
Zero Downtime Upgrades – ALTER TABLE
• Sem d´uvida o comando que gera maiores d´uvidas e dores de
cabe¸ca.
• Para executar um ALTER TABLE o banco de dados necessita de um
bloqueio exclusivo da tabela (em muitos casos bloqueando at´e
consultas), mas existem basicamente dois mecanismos para um ALTER TABLE atualizar o esquema:
◦ com necessidade de reescrita – a tabela ´e atualizada completamente (in-place ou usando arquivos tempor´arios) + atualiza¸c˜ao de cat´alogo; ◦ sem necessidade de reescrita – apenas atualiza¸c˜ao de cat´alogo.
Adicionar coluna, sem valor DEFAULT:
ALTER TABLE u s u a r i o s
ADD lat NUMERIC;
PostgreSQL : sem reescrita;
Oracle : sem reescrita;
MySQL : com reescrita (mas a partir da vers˜ao 5.6 ´e poss´ıvel reescrita in-place e sem bloqueio de opera¸c˜oes DML – exceto para auto increment);
Zero Downtime Upgrades – ALTER TABLE
Adicionar coluna, com valor DEFAULT:
ALTER TABLE u s u a r i o s
ADD a t i v o BOOLEAN DEFAULT t r u e;
PostgreSQL : com reescrita (devido `a necessidade do valor DEFAULT);
Oracle : com reescrita (devido `a necessidade do valor DEFAULT);
ALTER TABLE u s u a r i o s
ADD a t i v o BOOLEAN DEFAULT t r u e NOT NULL;
PostgreSQL : com reescrita (devido `a necessidade do valor DEFAULT);
Oracle : sem reescrita (a partir da vers˜ao 11g, anteriormente a reescrita era necess´aria);
MySQL : com reescrita (mesmas considera¸c˜oes do anterior);
Para adi¸c˜ao de colunas com valor DEFAULT (exceto MySQL 5.6+), uma pr´atica comum ´e adicionar a coluna sem DEFAULT, definir o
DEFAULT em outra opera¸c˜ao e fazer um UPDATE em grupos (batches) e/ou em paralelo para aplicar `as linhas antigas.
Zero Downtime Upgrades – ALTER TABLE
Remover coluna:
ALTER TABLE u s u a r i o s
DROP f o n e _ c a s a ;
PostgreSQL : sem reescrita;
Oracle : sem reescrita se usando o comando:
ALTER TABLE usuarios ALTER fone_casa SET UNUSED
;
Alterar tipo de uma coluna:
ALTER TABLE u s u a r i o s
ALTER lat TYPE b i g i n t;
PostgreSQL : com reescrita;
Oracle : com reescrita;
MySQL : com reescrita;
Zero Downtime Upgrades – ALTER TABLE
Alterar limite de uma coluna:
ALTER TABLE u s u a r i o s
ALTER n o m e TYPE v a r c h a r( 1 0 0 ) ;
PostgreSQL : a partir da vers˜ao 9.2, sem reescrita somente se o limite estiver aumentando (ou remo¸c˜ao de limite);
Oracle : sem reescrita somente se o limite estiver aumentando;
Cria¸c˜ao de ´ındices.
PostgreSQL : diminui o bloqueio se usado CREATE INDEX CONCURRENTLY ...;
Oracle : diminui o bloqueio se usado CREATE INDEX ... ONLINE;
MySQL : n˜ao bloqueia para ´ındices secund´arios, exceto FULLTEXT (InnoDB 5.1+, demais 5.6+).
Zero Downtime Upgrades – dicas
• Nunca se preocupe com a posi¸c˜ao das colunas numa tabela,
mapeia as posi¸c˜oes l´ogicas no seu c´odigo/aplica¸c˜ao;
• Nunca utilize o famigerado SELECT * ;
• Tente executar essas migra¸c˜oes em momentos de menor
atividade, mesmo que sejam sem reescrita;
• Execute scripts de migra¸c˜ao parte a parte, algumas vezes um
´
unico script de migra¸c˜ao pode levar dias;
• Automatize tudo que for poss´ıvel, mas acompanhe a execu¸c˜ao
quando for em produ¸c˜ao.
• Teste...
•
Teste...
•TESTE...
Zero Downtime Upgrades – dicas
• Nunca se preocupe com a posi¸c˜ao das colunas numa tabela,
mapeia as posi¸c˜oes l´ogicas no seu c´odigo/aplica¸c˜ao;
• Nunca utilize o famigerado SELECT * ;
• Execute scripts de migra¸c˜ao parte a parte, algumas vezes um
´
unico script de migra¸c˜ao pode levar dias;
• Automatize tudo que for poss´ıvel, mas acompanhe a execu¸c˜ao
quando for em produ¸c˜ao.
• Teste...
•
Teste...
•TESTE...
Zero Downtime Upgrades – dicas
• Nunca se preocupe com a posi¸c˜ao das colunas numa tabela,
mapeia as posi¸c˜oes l´ogicas no seu c´odigo/aplica¸c˜ao;
• Nunca utilize o famigerado SELECT * ;
• Tente executar essas migra¸c˜oes em momentos de menor
atividade, mesmo que sejam sem reescrita;
• Execute scripts de migra¸c˜ao parte a parte, algumas vezes um
´
unico script de migra¸c˜ao pode levar dias;
• Automatize tudo que for poss´ıvel, mas acompanhe a execu¸c˜ao
quando for em produ¸c˜ao.
• Teste...
•
Teste...
•TESTE...
Zero Downtime Upgrades – dicas
• Nunca se preocupe com a posi¸c˜ao das colunas numa tabela,
mapeia as posi¸c˜oes l´ogicas no seu c´odigo/aplica¸c˜ao;
• Nunca utilize o famigerado SELECT * ;
• Tente executar essas migra¸c˜oes em momentos de menor
atividade, mesmo que sejam sem reescrita;
• Execute scripts de migra¸c˜ao parte a parte, algumas vezes um
´
unico script de migra¸c˜ao pode levar dias;
• Teste...
•
Teste...
•TESTE...
Zero Downtime Upgrades – dicas
• Nunca se preocupe com a posi¸c˜ao das colunas numa tabela,
mapeia as posi¸c˜oes l´ogicas no seu c´odigo/aplica¸c˜ao;
• Nunca utilize o famigerado SELECT * ;
• Tente executar essas migra¸c˜oes em momentos de menor
atividade, mesmo que sejam sem reescrita;
• Execute scripts de migra¸c˜ao parte a parte, algumas vezes um
´
unico script de migra¸c˜ao pode levar dias;
• Automatize tudo que for poss´ıvel, mas acompanhe a execu¸c˜ao
quando for em produ¸c˜ao.
• Teste...
•
Teste...
•TESTE...
Zero Downtime Upgrades – dicas
• Nunca se preocupe com a posi¸c˜ao das colunas numa tabela,
mapeia as posi¸c˜oes l´ogicas no seu c´odigo/aplica¸c˜ao;
• Nunca utilize o famigerado SELECT * ;
• Tente executar essas migra¸c˜oes em momentos de menor
atividade, mesmo que sejam sem reescrita;
• Execute scripts de migra¸c˜ao parte a parte, algumas vezes um
´
unico script de migra¸c˜ao pode levar dias;
• Automatize tudo que for poss´ıvel, mas acompanhe a execu¸c˜ao
quando for em produ¸c˜ao.
• Teste...
Zero Downtime Upgrades – dicas
• Nunca se preocupe com a posi¸c˜ao das colunas numa tabela,
mapeia as posi¸c˜oes l´ogicas no seu c´odigo/aplica¸c˜ao;
• Nunca utilize o famigerado SELECT * ;
• Tente executar essas migra¸c˜oes em momentos de menor
atividade, mesmo que sejam sem reescrita;
• Execute scripts de migra¸c˜ao parte a parte, algumas vezes um
´
unico script de migra¸c˜ao pode levar dias;
• Automatize tudo que for poss´ıvel, mas acompanhe a execu¸c˜ao
quando for em produ¸c˜ao.
• Nunca se preocupe com a posi¸c˜ao das colunas numa tabela, mapeia as posi¸c˜oes l´ogicas no seu c´odigo/aplica¸c˜ao;
• Nunca utilize o famigerado SELECT * ;
• Tente executar essas migra¸c˜oes em momentos de menor
atividade, mesmo que sejam sem reescrita;
• Execute scripts de migra¸c˜ao parte a parte, algumas vezes um
´
unico script de migra¸c˜ao pode levar dias;
• Automatize tudo que for poss´ıvel, mas acompanhe a execu¸c˜ao
quando for em produ¸c˜ao.
• Teste...
•