Fazendo Um Elefante Passar Debaixo da Porta - CONSEGI

por Fábio Telles27 de Agosto de 2008


Equilíbrio


PostgreSQL != MySQL


PostgreSQL != MySQL != Oracle != DB2


Padrões

Aplicaçõ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;


Padrões... 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 o uso de funções exóticas que não tenham

implementação similar em outro SGDB de mercado;


Flexibilidade

● Fácil de manter;● Fácil de portar/ migrar;● Fácil de entender;● Fácil de mudar;


Desempenho

● Transações/ Atualizações;● Conexões simultâneas;● Consultas complexas;● Carga e exportação de dados;● Suporte a grande volume de dados;


Segurança

● Evitar acesso não autorizado a informações confidenciais;

● Evitar alterações indesejadas;● Evitar perda de dados;● Evitar indisponibilidade;● Recuperação rápida de desastres;


Leiam a Documentação Oficial (LAPDM)

● DBAs: Leiam tudo!● Sysadmins: ao menos “III - Server Administration”● Desenvolvedores: Leiam ao menos as partes

● II - The SQL Language● IV – Client Interfaces● V – Server Programing

http://www.postgresql.org/docs/manuals

http://www.postgresql.org/docs/manuals


Monitore sempre!

● Configurando os logs do PostgreSQL você pode: ● Detectar gargalos de performance, erros nas

aplicações, falhas de segurança, etc;● Encontrar sugestões para melhorar a configuração

do seu servidor;● Você pode configurar onde, quando, como e o que

registrar no log;● Você ainda pode importar os logs para uma tabela

dentro do seu banco de dados (Novo no 8.3).


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;


Modelagem

Estrutura de dados inteligentes e código burro trabalham muito melhor que ao contrário. (Eric Raimond)

A origem de todo mal está da otimização de performance precoce. (Leandro Dutra)

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

Quando surgem são quase insolúveis. (Fábio Telles)


Modelagem x Flexibilidade

● 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;● Documentar DDL, DER e Dicionário de

Dados;


Modelagem X Segurança

● Use as restrições de integridade: PK, FK, NOT NULL, CHECK;

● Use visões e funções para proteger o acesso a informações sensíveis.

● Nunca crie campos e tabelas do tipo “FLEX”;● Documentar DDL, DER e Dicionário de

Dados;


Modelagem X Desempenho

● Evite desnormalizar o modelo de dados;● Separe o modelo físico do modelo lógico● Nunca crie campos e tabelas do tipo “FLEX”;● Cuidado com FRAMEWORKs e

GERADORES DE CÓDIGO;


SQL X 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 várias transações pequenas e repetitivas;

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


SQL X Desempenho

● 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;


SQL X Segurança

● 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;


SQL X Flexibilidade

● Só utilize editores de texto puro;● Use o psql com a opção \i● Escreva 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 ;


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 func_pk PRIMARY KEY (id_funcionario) USING INDEX func_pk_ix;ALTER TABLE rh.funcionario ADD CONSTRAINT func_depto_fk FOREIGN KEY (id_depto) REFERENCES rh.depto(id_depto);


Estruturas Lógicas e Físicas

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

● Tablespace: local físico p/ armazenamento de tabelas e índices;● Banco de Dados: conjunto de esquemas e seus objetos. ● Cluster (initdb): conjunto de arquivos que compõe uma única

instância do PostgreSQL. Esta utiliza um único conjunto de processos, memória, 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;● Crie um esquema para cada aplicação com 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 coloque objetos no information_schema, pg_catalog ou pg_toast.


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;


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 (Usar RAID);● Tabelas onde a disponibilidade é crítica (Usar RAID);


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.


Segurança

● Use transações com BEGIN, COMMIT e ROLLBACK;● Use proteção efetiva contra SQL INJECTION na aplicação;● Use desconexão automática por ociosidade;● Use de tratamento de erros especial para erros de violação de

restrições de integridade;● Nunca exiba mensagens de erro do banco na tela do usuário;● Nunca confie em conversões implícitas de tipo de dados;


Segurança

● Faça backup periódico do seu ambiente de produção e teste;● Teste todas rotinas de backup e restauração;● Nunca utilize TRUST ou PASSWORD como método de

autenticação no pg_hba.conf;● Acompanhe os logs de erro;● Não utilize codificação de caracteres SQL_ASCII;● Durma!!!


Backup e Alta Disponibilidade

● Backups:● Lógico (pg_dump)● Físico – off-line● Físico – on-line (via PITR)● Snapshot (via storage)

● Alta disponibilidade:● Stand By (via PITR);● Replicação (via PgPool-2, Slony, Skytools, etc)● Cluster (PL/Proxy + PgBouncer)


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 (LDAP, AD, Kerberos, etc);

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


Autenticação Interna

● 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;● Um usuário e senha para várias aplicações;● Se a aplicação for Cliente/Servidor, PostgreSQL não consegue

impedir o usuário de se conectar por fora da aplicação;


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

● 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 dentro da aplicação;● O ROLE da aplicação nunca pode ser mesmo que o ROLE do

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


Autenticação – Boas Práticas

● Aplicações web não corporativas com muitos usuários devem 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)


pg_hba.conf

● Sempre identifique o nome do banco de dados;● Utilize SSL para encriptar o tráfego de informações pela rede.● Utilize 'ident' apenas para conexões locais;● Utilize MD5 para conexões remotas;● 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;


pg_hba.conf

● Separe as regras por grupos (ROLES) 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;


Vários instâncias no mesmo servidor?

● Você tem que garantir a disponibilidade de recursos (Processador, Memória e Discos) para o servidor de produção;

● Uma única consulta mal feita pode comprometer a performance de todos usuários;

● Não é possível virtualizar os canais de I/O;


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 no mesmo servidor;

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


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;


OBRIGADO

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

Fernando Ike, Ulisses Castro e Fábio Telles Rodriguez,

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 - CONSEGI