Fazendo Um Elefante Passar Debaixo da Porta - PGCon-BR

por Fábio Telles8 de Dezembro de 2007


Melhores Práticas Melhores práticas são diretrizes e não dogmas:● Uma pessoa sem bom senso não se preocupa com melhores práticas;● Uma pessoa com bom senso e pouca experiência procura aprender e utilizar as melhores práticas;● Uma pessoa com bom senso e muita experiência sabe quando não utilizar as melhores práticas;


ModelagemEstrutura de dados inteligentes e código burro trabalham

muito melhor que ao contrário. (Eric Raimond)

Problemas de performance quando causados por falhas na modelagem podem demorar anos para aparecer.

Quando surgem são quase insolúveis.

A parte mais importante no sucesso de uma aplicação com comunicação tipo Matriz/Filial está na modelagem e não na

replicação


Modelagem● Abuse de PK, FK, NOT NULL, CHECK;● Use domínios para melhorar a semântica dos dados;● Use seqüências para gerar números únicos;● Use visões para tornar o modelo lógico mais simples que o

modelo físico;● Evite desnormalizar o modelo de dados;● Nunca deixe de documentar DDL, DER e Dicionário de Dados;● Nunca crie campos e tabelas do tipo “FLEX”;● Cuidado com FRAMEWORKs e GERADORES DE

CÓDIGO;


Desempenho● Use funções para processar tarefas em lote;● Agregue várias consultas pequenas em uma única consulta maior;● Use o PREPARE ... EXECUTE quando não for possível agregar

consultas menores;● Use poucas transações grandes no lugar de muitas transações

pequenas;● Use índices em campos muito utilizados em cláusulas WHERE;● Evite utilizar muitos índices em ambiente transacional pesado;● Evite operações pesadas de DELETE e UPDATE;● Evite o uso indiscriminado de gatilhos;● Evite usar funções quando SQL puro resolve;


PadrõesAplicações independentes de SGDB não existem...● Use funções específicas do PostgreSQL quando:

● houver grande ganho de performance ou ● esta função for chave para a sua aplicação;

... mas a necessidade de fornecer soluções para mais de um SGDB existe!

● Use ao máximo o padrão ANSI/SQL;● Evite o uso de funções que não sejam em PL/pgSQL;● Evite ao máximo o uso de funções exóticas que não tenha

implementação similar em outro SGDB de mercado;


Desenvolvedores● Abuse da documentação oficial;● Abuse de transações com BEGIN, COMMIT e ROLLBACK;● Use proteção efetiva contra SQL Injection;● Use desconexão automática por ociosidade;● Use de tratamento de erros com tratamento especial para erros

de violação de constraints;● Use logs de erro armazenados em servidores de aplicação;● Nunca exiba mensagens de erro do banco na tela do usuário;● Nunca confie em conversões implícitas de tipo de dados;


DBAs● Abuse da documentação oficial;● Use backup periódico do seu ambiente de produção e teste;● Nunca deixe de testar, rotinas de backup e restauração;● Nunca utilize TRUST ou PASSWORD como método de

autenticação no pg_hba.conf;● Nunca deixe de acompanhar os logs de erro;● Nunca utilize codificação de caracteres SQL_ASCII;● Nunca deixe de dormir!!!


Escrevendo SQL● Abuse de editores de texto puro;● Use o psql com a opção \i● Use palavras reservadas em letra maiúscula e nome de

objetos em letra minúscula;● Use o nome do esquema em operações DML e nomes

explícitos para índices, restrições e seqüências em DDL ;● Evite realizar operações pesadas em ambiente gráfico;● Evite criar objetos em interfaces gráficas;● Antes de dizer que um SQL não funciona, teste sempre no

psql;


O PostgreSQL é case sensitive!● Ruim:CREATE TABLE FUNCIONARIO (IDFUNCIONARIO SERIAL PRIMARY KEY,NOME VARCHAR(50) NOT NULL,DEPTO INTEGER REFERENCES DEPTO(IDDEPTO));● Péssimo:Create Table Funcionario (IdFuncionario Serial Primary Key,Nome Varchar(50) Not Null,Depto Integer References Depto(IdDepto));


USE MINÚSCULAS para nome de objetos

● Bom: CREATE TABLE funcionario ( id_funcionario SERIAL PRIMARY KEY, nome VARCHAR(50) NOT NULL, id_depto INTEGER REFERENCES depto(id_depto));


Explícito > Implícito● Ótimo:CREATE SEQUENCE hr.funcionario_seq;CREATE TABLE hr.funcionario ( id_funcionario INTEGER DEFAULT NEXTVAL('hr.funcionario_seq'), nome VARCHAR(50) NOT NULL, depto INTEGER CONSTRAINTS funcionario_pk PRIMARY KEY (id_funcionario) USING INDEX TABLESPACE tbs_rh_index, funcionario_depto_fk FOREIGN KEY (id_depto) REFERENCES rh.depto(id_depto)) TABLESPACE tbs_rh_table;


Explícito > Implícito● Excelente:SET DEFAULT_TABLESPACE = tbs_rh_table;CREATE SEQUENCE rh.funcionario_seq;CREATE TABLE rh.funcionario ( id_funcionario INTEGER DEFAULT NEXTVAL('rh.funcionario_seq'), nome VARCHAR(50) NOT NULL, depto INTEGER );SET DEFAULT_TABLESPACE = tbs_rh_index;ALTER TABLE rh.funcionario ADD CONSTRAINT funcionario_pk PRIMARY KEY (id_funcionario) USING INDEX funcionario_pk_ix;ALTER TABLE rh.funcionario ADD CONSTRAINT funcionario_depto_fk FOREIGN KEY (id_depto) REFERENCES rh.depto(id_depto);


Inteligência da Aplicação no Banco de Dados

Vantagens:● Maior controle por parte do DBA;● Maior velocidade em operações que envolvem um

grande volume de dados e um número limitado de cálculos;

● Acesso padronizado para diversas aplicações;● Facilidade de manutenção;● Baixa curva de aprendizado;



Desvantagens:● Menor controle por parte do desenvolvedor;● PL = Procedural Language, ou seja não é orientado a objeto;● Dificuldade em migrar aplicação para outros SGDBs;● Não existe COMMIT ou ROLLBACK dentro de uma função;● Código não pode ser ofuscado;● Alguns servidores de aplicação escalam processamento

melhor que o PostgreSQL;● Concentração de carga de processamento no SGDB;



Boas Práticas:● Confie no PostgreSQL para reforçar restrições!● Utilize funções para cálculos em lote;● Utilize gatilhos para auditar tabelas chave;● Utilize funções e visões para aumentar a segurança no

acesso a informações sensíveis;● Utilize gatilhos, funções e visões para integrar dados de

diferentes aplicações;


Ambientes● Produção: utilizado por todos usuários da

aplicação;● Homologação: aceite de novas versões pelo

usuário, testes de performance;● Teste: desenvolvimento de aplicações;● Laboratório: teste de novas versões, patches e

funcionalidades do SGDB.


Autenticando Aplicações no PostgreSQL

● Autenticação Interna: um usuário do PostgreSQL por usuário da Aplicação;

● Autenticação Externa: um usuário do PostgreSQL por usuário da Aplicação com autenticação externa;

● Autenticação via Aplicação: um usuário do PostgreSQL para todos usuários da aplicação;


Autenticando Aplicações no PostgreSQLAutenticação Interna:● PostgreSQL é capaz de distinguir quais usuários estão conectados;● Auditoria consistente;● Uso de ROLEs para agrupar privilégios em objetos;● DBA precisa criar usuários no banco de dados manualmente;● Aplicação deve trocar senha do usuário na primeira vez em que ele se

conectar;● Se a aplicação for Cliente/Servidor, PostgreSQL não consegue

impedir o usuário de se conectar por fora da aplicação;● Não é possível fazer pool de conexões.



Autenticação Externa:● Tem as mesmas características da Autenticação

Interna com as seguintes diferenças:● Administração de senhas fica a cargo do

Administrador de Sistemas;● Se integra com os demais usuários da rede;● É mais complexo para ser configurado;




Autenticação pela Aplicação:● O PostgreSQL não é capaz de distinguir qual usuário está conectado;● Auditoria deve ser implementada pela aplicação;● Cadastro de usuários, senhas e permissões é de inteira responsabilidade

da aplicação;● Senha de acesso ao PostgreSQL deve ficar em posse da aplicação;● A senha da aplicação deve ser trocada com freqüência;● O ROLE da aplicação deve ter os menores privilégios possíveis;● O ROLE da aplicação nunca pode ser mesmo que o ROLE do

desenvolvedor ou o dono dos objetos da aplicação;



Autenticando Aplicações no PostgreSQLBoas Práticas:● Aplicações web não corporativas com muitos usuários

devem ser utilizar autenticação pela aplicação;● Aplicações que precisam de pool de conexões devem

utilizar autenticação pela aplicação;● Aplicações corporativas com 3 ou mais camadas devem

preferir devem preferir autenticação externa;● Mude o pg_hba.conf conforme o ambiente (produção,

homologação, teste e laboratório)


Autenticando Aplicações no PostgreSQLpg_hba.conf:● Sempre identifique o nome do banco de dados;● Utilize SSL se você se preocupar com o tráfego de informações

pela rede sem encriptação;● Limite a faixa de Ips ao máximo:

● No caso aplicações em 3 ou mais camadas, limite aos IPs dos servidores de aplicação;

● No caso de aplicações Cliente/Servidor, limite a rede local que eles utilizam;


Autenticando Aplicações no PostgreSQLpg_hba.conf:● Separe as regras por grupos de usuários:

● DBAs;● Desenvolvedores;● Aplicações (autenticação pela aplicação)● Aplicações (autenticação interna)● Aplicações (autenticação externa)● Usuários especiais;

● Utilize 'ident' apenas para os usuários DBAs, localmente e MD5 para conexões remotas, limitadas aos Ips/Redes locais dos DBAs;


Autenticando Aplicações no PostgreSQLpg_hba.conf● Utilize 'md5' para e o nome dos ROLEs da aplicação para

autenticação pelo PostgreSQL ou pela aplicação;● Utilize 'ldap', 'gss' ou 'sspi' para autenticação externa;● Desenvolvedores devem utilizar 'reject' no ambiente de

produção;● Usuários de aplicação autenticados pelo PostgreSQL ou

externamente devem ter 'reject' no ambiente de teste e homologação;

● Somente usuários especiais devem ter acesso ao ambiente de teste e homologação;


Esquema, Tablespace, Banco de Dados e Cluster

● Esquema: estrutura lógica onde os objetos são criados. Todo objeto é criado em um esquema;

● Tablespace: local físico de armazenamento de tabelas e índices;● Banco de Dados: conjunto de esquemas e seus objetos. Pode

compartilhar (padrão) ou não os ROLES de outros bancos de dados do cluster;

● Cluster (initdb): conjunto de arquivos que compõe uma única instância do PostgreSQL. Esta utiliza um único conjunto de processos, shared buffers e porta de rede, mas pode conter vários bancos de dados;


Um Esquema Por Aplicação● PostgreSQL não acessa nativamente outros bancos de dados;● Compartilhe dados entre as aplicações;● Crie um esquema para cada aplicação;● Cada esquema de aplicação deve possuir seu próprio dono;● Utilize um esquema separado para auditoria de vários sistemas;● Utiliza um esquema separado para monitoramento do DBA;● Utilize o esquema public apenas para compartilhar dados entre diversas aplicações;●Não utilize os esquemas information_schema, pg_catalog, pg_toast para criar, alterar ou excluir nenhum objeto;


Dois tablespaces por aplicação● Utilizar no mínimo um tablespace para índices e um para tabelas;● Mais fácil identificar o espaço físico ocupado pela aplicação;● Mais fácil identificar arquivos de backup físico;● Mais fácil identificar uso e volume de índices ou tabelas;● Mais fácil fazer ajuste de I/O e desempenho;● Uma camada a mais de segurança na criação de objetos;


Na Prática:

● No bash do Linux:#mkdir /postgresql#chown postgres /postgresql#su postgres$cd /postgresql$mkdir /postgresql/tbs_hr_index$mkdir /postgresql/tbs_hr_table$psql pgcon


Na Prática:● No psql do PostgreSQL:pgcon=#REVOKE ALL ON TABLESPACE pg_default,pg_global FROM public;pgcon=#REVOKE ALL ON SCHEMA public FROM public;pgcon=#CREATE ROLE hr NOLOGIN;pgcon=#CREATE TABLESPACE hr_index OWNER hr LOCATION '/postgresql/tbs_hr_index';pgcon=#CREATE TABLESPACE hr_table OWNER hr LOCATION '/postgresql/tbs_hr_table';CREATE SCHEMA AUTHORIZATION hr;


Quando utilizar novos TABLESPACES?

Só faz sentido utilizar muitos tablespaces se você tem ou pretende ter vários discos!

● Tablespace temporário em ambiente com muitas consultas pesadas (novo no 8.3!);

● Separar dados históricos e partições de tabelas pouco utilizadas em discos mais baratos;

● Tabelas e índices onde o desempenho é crítico;● Tabelas onde a disponibilidade é crítica;


Quando utilizar mais de um banco de dados no mesmo cluster?

● Você quer aproveitar os processos do cluster existente mas precisa comparar uma nova versão dos mesmos objetos;

● Você tem aplicações que precisam utilizar diferentes codificações de caracteres;

● NUNCA coloque um ambiente de teste e produção no mesmo cluster!


Quando utilizar mais de um cluster no mesmo Sistema Operacional?

● Você precisa utilizar um LC_COLLATE diferente;● Você precisa utilizar diferentes versões do PostgreSQL

ao mesmo tempo;● NUNCA coloque um ambiente de teste e produção no

mesmo SO!


Quando virtualizar o SO?

Sempre procure utilizar discos distintos para cada VM!● Você precisa testar uma nova versão ou funcionalidade do

PostgreSQL e não tem um ambiente de laboratório separado;● Você precisa manter o ambiente de produção junto com o

ambiente de homologação ou teste no mesmo servidor físico.● Você deseja ter múltiplos ambientes de laboratório,

homologação ou teste no mesmo servidor físico.


Equilíbrio


OBRIGADO

Dúvidas, sugestões, correções, indignações e cervejas são bem vindas!

Fábio Telles Rodriguez, Consultoria em PostgreSQL, Oracle e MySQL

SAVEPOINT: http://www.midstorm.org/~telles e-mail: [email protected]

http://www.midstorm.org/~telles

mailto:[email protected]

Technology

Fazendo Um Elefante Passar Debaixo da Porta - PGCon-BR