Minha Foto Oficial da PGBR 2013!

PgBR 2013.

Eu estava lá.

Foi muito bom! REcomendo a PgBR 2014!

Simples e Útil: Visões Materializadas no PostgreSQL!

Visões materializadas são recursos introduzidos na versão 9.3 do postgresql. Enquanto visões tradicionais reexecutam uma consulta sempre que são referenciadas, visões materializadas dispensam este esforço pelos seus dados já estarem guardados desde a sua criação ou do último refresh (atualização de visão). Pode-se dizer que uma visão materializada é um objeto que contém o resultado de uma consulta, facilitando o acesso aos dados nela contidos.

A principal justificativa para se utilizar visões materializadas é a aceleração de consultas em grandes massas de dados. É importante observar que em sistemas com pouco espaço em disco e discos lentos, visões materializadas podem ter pouco efeito ou até impacto negativo por sobrecarregar ainda mais o hardware.

Para ter ainda mais desempenho,. é possível criar índices para visões materializadas.

No postgresql, a atualização de uma visão materializada é feita através do comando REFRESH MATERIALIZED VIEW, enquanto que a mudança do código da consulta da visão é feita através do comando ALTER MATERIALIZED VIEW. A exclusão de visões materializadas é feita com o comendo DROP MATERIALIZED VIEW.

Sintaxe básica:

CREATE MATERIALIZED VIEW nome_tabela
    [ (nome_coluna [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE nome_tablespace ]
    AS consulta
    [ WITH [ NO ] DATA ]
Exemplo 1: Criação de uma visão materializada simples

CREATE MATERIALIZED VIEW lista_tabelas AS
SELECT tablename FROM PG_TABLES ORDER BY tablename;
Exemplo 2: Refresh dos dados de uma visão materializada, pelo comando REFRESH MATERIALIZED VIEW.

REFRESH MATERIALIZED VIEW lista_tabelas;

Exemplo 3: Exclusão de uma visão materializada utilizando DROP MATERIALIZED VIEW. O comando drop view não exclui visões materializadas, e sim gera erro.

DROP MATERIALIZED VIEW lista_tabelas;

Exemplo 4: Criação de uma visão materializada sem dados. Neste caso, o comando REFRESH MATERIALIZED VIEW pode ser utilizado para popular a visão armazenada.

CREATE MATERIALIZED VIEW lista_tabelas_nodata AS
SELECT tablename FROM PG_TABLES ORDER BY tablename WITH NO DATA;
Exemplo 5: Criação de uma visão materializada simples, explicitando o tablespace utilizado

CREATE MATERIALIZED VIEW lista_indices TABLESPACE pg_default AS
SELECT schemaname, tablename, indexname, tablespace FROM PG_INDEXES;
Exemplo 6: Criação de uma visão materializada simples, utilizando o storage parameter fillfactor. São aceitos todos os tipos de storage parameters de uma tabela padrão, exceto OIDs, pois visões materializadas não apresentam identificador OID para cada registro.

CREATE MATERIALIZED VIEW lista_indices_fill50 WITH (fillfactor = 50) AS
SELECT schemaname, tablename, indexname, tablespace FROM PG_INDEXES;
Exemplo 7: Para saber quantas visões armazenadas você tem no seu servidor, utilize a visão PG_MATVIEWS.

SELECT * FROM PG_MATVIEWS;

Exemplo 8: Alteração de visão materializada. O comando ALTER MATERIALIZED VIEW apresenta uma sintaxe mais elaborada, merecendo mais espaço em um texto futuro.

ALTER MATERIALIZED VIEW lista_tabelas RENAME TO lista_relacoes;

* Conclusões

Visões materializadas são uma boa opção para aumento de performance sob certas condições.

Também facilitam a importação de visões materializadas disponíveis em outros SGBDs como ORACLE e SQL SERVER.

A implementação de visões materializadas é relativamente fácil e bastante útil.

Você já utilizou esta funcionalidade nos seus projetos? Qual foi a sua opinião?

Gere uma Dimensão Temporal para o seu Data Warehouse Utilizando o Postgresql!

A dimensão temporal em um data warehouse, e também em outros tipos de aplicação, pode assumir uma multitude de formatos. Encaminho a vocês o link de uma planilha desenvolvida por Tomáš Greif, que simplesmente gera praticamente tudo o que você poderia precisar em termos de dimensão temporal para uso em data warehouse. Espero que ela facilite o seu trabalho. Baixe e use à vontade!

Não precisa de macros! Para criar a tabela, inserir e atualizar dados, basta copiar as colunas H e seguintes para um editor de texto.

A única ressalva é que a planilha ainda não suporta feriados, mas essa funcionalidade você pode adicionar :)