visitas
O
postgreSQL é um banco de dados de verdade(ao
contrário do MySQL) que implementa ACID.
Possui características de bancos de dados
corporativos como Oracle, a facilidade de uso como MSSQL e uma boa
velocidade como MySQL. O MySQL pode até ser mais
rápido,
mas não garante a consistência dos seus dados nem
suporta as funcionalidades corporativas do PostgreSQL.
Poderíamos até compará-lo com o
Access...  |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| DBLink e Visões
Materializadas (Materialized View / Snapshot) no PostgreSQL 30 Abr 06
1. Apresentação do problema e
motivação[índice]
No
momento da migração
de aplicações do
Oracle para o PostgreSQL, nos deparamos com
situações em
que precisamos dos mesmos dados nos dois servidores de bancos de dados.
A situação seria "facilmente" resolvida se
pudéssemos criar "database links" ou dblinks entre um
servidor
PostgreSQL e um servidor Oracle. Aí surge o problema.2.
Soluções existentes[índice]
Hoje temos algumas soluções,
como DBLink e
DBI-Link. A
primeira cria visões que retornam o resultado da
execução de uma função que,
por sua vez,
faz uma consulta "query" no servidor remoto. A segunda funciona quase
da mesma forma, mas cria toda uma
estrutura de tabelas, tipos, visões e
funções de
forma a tornar transparente o manuseio de tabelas remotas.3.
Problemas apresentados[índice]
Comparando as duas abordagens, devemos evidenciar que
o DBI-Link
é muito mais elegante e elaborado. No entanto, as duas
abordagens sofrem de um grave problema: trazem todo o resultado da
consulta remota para o servidor local. Vejamos o seguinte
exemplo:Supondo que temos uma tabela em um banco de dados Oracle contendo uma lista de pessoas (tabela PESSOA). A chave primária dessa tabela é o número da identidade e ela contém vários outros campos contendo dados de pessoas e um total de 250.000 registros. O tamanho aproximado dessa tabela no sistema de arquivos é 122Mb. Se executarmos a seguinte consulta no SQLPlus do Oracle: SELECT nome FROM pessoa WHERE identidade='012345678'; (1) o banco pesquisará o índice (PK) pela identidade fornecida e retornará imediatamente (tempo < 1 segundo) o nome da pessoa que possui a identidade 012345678. Agora nós queremos que uma nova aplicação, que está sendo criada para PostgreSQL, acesse de forma transparente essa tabela no Oracle, ou seja, acesse essa tabela através do servidor PostgreSQL(como se fosse nativa). Usando o DBLink poderíamos criar uma visão chamada "PESSOA" que conteria uma chamada à função de query do dblink passando "SELECT * FROM PESSOA" (2). Usando o DBI-Link poderíamos criar um espelho da estrutura do esquema remoto e, com isso, no nosso esquema local existiria uma visão chamada "PESSOA", que invoca uma função que executa uma consulta remota [2], além de tipos e funções associados. Se executarmos a mesma consulta que antes havia sido executada no Oracle [1], teremos uma demora considerável e um gasto computacional absurdo (no meu teste, tempo ~ 5 minutos e consumo de memória do processo ~ 1Gb). O que inviabiliza essas soluções para qualquer projeto corporativo. 4.
Funcionamento interno das soluções e origem
dos problemas[índice]
Nas duas soluções, a
requisição que originou a
execução das funções foi:SELECT nome FROM pessoa WHERE identidade='012345678'; (1) No entanto, devemos notar que ambas as funções foram criadas para executar a seguinte consulta remota: SELECT * FROM PESSOA; (2)
Funcionamento
do DBLink e
DBI-Link
O que acontece de fato é que a consulta presente nas funções[2] é sempre executada, independente das cláusulas de filtro presentes na requisição que originou a execução da função[1]. O resultado das funções[2] é, então, processado pelo PostgreSQL (nesse caso filtrado pela coluna identidade) de forma sequencial e o resultado final é retornado(3). Uma forma, não muito simples, seria interceptar o planejador do PostgreSQL e obter informações sobre a consulta originária da execução da função e passar, de forma dinâmica, parâmetros adicionais ao banco de dados remoto. Isso, infelizmente, ainda não existe. No entanto, podemos utilizar um outro meio, quando o acesso à informação não precisa ser feito em tempo real. Nesses casos, podemos criar uma cópia da tabela remota no banco de dados local. O problema anterior estaria resolvido, certo ? Errado. Como esta cópia seria feita? Utilizando-se a conexão DBLink ou DBI-Link poderíamos executar um CREATE TABLE PESSOA AS SELECT * FROM PESSOA_REMOTO, o que criaria uma cópia da tabela remota no banco de dados local. Com isso, qualquer consulta executada no PostgreSQL utilizaria a tabela local, o que seria muito mais rápido e eficiente. Porém essa abordagem sofre de um dos problemas relatados acima, o consumo excessivo de memória ao realizar a chamada da função (no meu teste, o tamanho do processo foi ~ 1Gb). Apesar dessa operação ser executada apenas algumas vezes por dia, o consumo excessivo de memória tornou essa alternativa pouco prática. 5.
Solução utilizando Visões
Materializadas: o PostgreSQL::Snapshots[índice]
Foi pelos motivos acima que criei o
PostgreSQL::Snashots, como uma forma eficiente e corporativa
para remediar a incapacidade das soluções DBLink
atuais. Essa solução
foi baseada e inspirada no projeto DBI-LINK.As funcionalidades implementadas incluem: CREATE DBLINK Esse comando, presente no banco de dados Oracle e ausente no PostgreSQL, cria uma ligação entre banco de dados, utilizando um usuário e uma senha, além da localização do servidor na rede. No nosso caso, usamos uma função em PL/Perl (create_dblink) que recebe o nome do DBLink, a string de conexão do Perl, o nome do usuário, a senha e algum atributo adicional necessário ao estabelecimento da conexão. O nome da tabela onde estes dados são armazenados é pg_dblink e vale lembrar que essa deve ser acessível apenas ao DBA (usuário postgres), apesar de ser criada no esquema public. Antes de inserir o registro na tabela, verificamos se conseguimos estabelecer, com suceso, uma conexão com o banco remoto utilizando os parâmetros passados. DROP DBLINK Esse comando, presente no banco de dados Oracle e ausente no PostgreSQL, remove uma ligação entre banco de dados, utilizando apenas o nome do DBLink. No nosso caso, usamos uma função em PL/Perl (drop_dblink) que recebe o nome do DBLink e remove a entrada da tabela. A chave estrangeira presente impede a remoção se algum snapshot fizer referência ao DBLink em questão. CREATE SNAPSHOT Esse comando, presente no banco de dados Oracle e ausente no PostgreSQL, cria uma visão materializada (MATERIALIZED VIEW ou SNAPSHOT) baseada em um consulta. Essa consulta pode, ou não, estar referenciando um DBLink. No nosso caso, usamos uma função em PL/Perl (create_snapshot) que recebe o nome do Esquema, o nome do Snapshot, a consulta, o nome do DBLink e o método de atualização. O nome do DBLink é opcional e, quando não for fornecido(NULL), criará um snapshot baseado em uma consulta ao banco de dados local. O método de atualização pode ser:
A tabela não é preenchida por esse comando. DROP SNAPSHOT Esse comando, presente no banco de dados Oracle e ausente no PostgreSQL, remove uma visão materializada (MATERIALIZED VIEW ou SNAPSHOT) utilizando apenas o nome do Snapshot. No nosso caso, usamos uma função em PL/Perl (drop_snapshot) que recebe o nome do Esquema e o nome do Snapshot e remove o objeto e a entrada na tabela pg_snapshots. CREATE SNAPSHOT LOG Esse comando, presente no banco de dados Oracle e ausente no PostgreSQL, cria uma tabela de log de visão materializada(MATERIALIZED VIEW LOG ou SNAPSHOT LOG) acoplada a uma outra tabela(chamada de master). No momento em que for criado um snapshot fazendo referência a essa tabela master, será possível a utilização de atualizações rápidas (REFRESH FAST) baseadas no log. Isso permite, por exemplo, que, no momento da atualização do snapshot(REFRESH), apenas os registros deletados, atualizados ou incluídos sejam trazidos, aumentando consideravelmente a performance e o tempo de atualização. No nosso caso, usamos uma função em PL/Perl (create_snapshot_log) que recebe o nome do Esquema, o nome da Tabela Master e a lista de campos (separados por vírgulas) sobre os quais será feito o filtro do log. Essa lista de campos pode contem uma palavra-chave como "primary key" ou "oid" ou apenas nomes de campos. Para implementar o "snapshot log", essa função cria uma tabela de log com o prefixo "mlog$_" e uma trigger de código dinâmico que monitora quaisquer modificações na tabela master e grava as informações necessárias na tabela de log. Finalmente, uma entrada na tabela de snapshot logs (pg_mlogs) é criada, juntamente com as entradas necessárias na tabela de colunas de snapshot logs (pg_mlog_refcols). DROP SNAPSHOT LOG Esse comando, presente no banco de dados Oracle e ausente no PostgreSQL, remove um log de visão materializada (MATERIALIZED VIEW LOG ou SNAPSHOT LOG) utilizando apenas o esquema e o nome da tabela master. No nosso caso, usamos uma função em PL/Perl (drop_snapshot_log) que recebe o nome do Esquema e o nome da Tabela Master e remove a tabela de log de visão materializada, juntamente com a trigger na tabela master, a entrada na tabela pg_mlogs e as entradas correspondentes na tabela pg_mlog_refcols. REFRESH SNAPSHOT Esse comando, presente no banco de dados Oracle como uma "Stored Procedure" e ausente no PostgreSQL, atualiza os dados de uma visão materializada (MATERIALIZED VIEW ou SNAPSHOT) utilizando apenas o nome do Snapshot. No nosso caso, usamos uma função em PL/Perl (refresh_snapshot) que recebe o nome do Esquema e o nome do Snapshot e o preenche com o resultado da consulta de criação do mesmo. O segredo é a utilização de conexões distintas para comunicação SPI com o backend, para inserção de dados no Snapshot e leitura de dados remota. O procedimento de inserção de dados é feito em transações de 1000 registros e utiliza Prepared Statements para agilizar o processo (no meu teste com a tabela PESSOA, a taxa foi de ~ 650 registros/segundo). É também nessa função que verificamos o método de atualização e fazemos atualizações rápidas quando determinado(e possível). As atualizações rápidas dependem do método escolhido, da criação de log de visões materializadas (MATERIALIZED VIEW LOG), da quantidade de registros nesse log e da quantidade de registros retornados pela consulta. Para tabelas grandes que sofrem pequenas alteraçoes/inclusões, o método FAST pode fazer com que uma atualização ocorra em apenas alguns segundos. A atualização rápida (REFRESH FAST) também só é possível em bancos de dados que possuem drivers implementados (atualmente, apenas para PostgreSQL e Oracle) uma vez que são necessárias operações em tabelas internas de sistema no banco de dados master (onde a consulta será executada). No Oracle, por exemplo, tabelas como SLOG$, MLOG$, MLOG_REFCOL$, etc. devem ser acessadas diretamente e a tabela SLOG$ deve ser acessada para modificações. 6.
Conclusão[índice]
Com o PostgreSQL::Snapshots, as funcionalidades
básicas de Visões Materializadas são
implementadas, o que não impede uma futura
associação com uma solução
eficiente de DBLink. A utilização de
Visões Materializadas não se restringe a
cópias de tabelas de outros bancos de dados, muito pelo
contrário, podem ser utilizadas como forma de persistir
resultados de consultas muito complexas e demoradas, garantindo, assim,
responsividade dos sistemas front-end. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
