Início / dba / Perguntas / 86779
Accepted
user4150760
Asked: 2014-12-23 07:25:37 +0800 CST

Atualize a visão materializada de forma incremental no PostgreSQL

  • 772

É possível atualizar uma visão materializada de forma incremental no PostgreSQL, ou seja, apenas para os dados que são novos ou alterados?

Considere esta tabela e visualização materializada:

CREATE TABLE graph (
   xaxis integer NOT NULL,
   value integer NOT NULL,
);

CREATE MATERIALIZED VIEW graph_avg AS 
SELECT xaxis, AVG(value)
FROM graph
GROUP BY xaxis

Periodicamente, novos valores são adicionados graphou um valor existente é atualizado. Desejo atualizar a exibição a graph_avgcada duas horas apenas para os valores que foram atualizados. No entanto, no PostgreSQL 9.3, toda a tabela é atualizada. Isso é bastante demorado. A próxima versão 9.4 permite CONCURRENTatualização, mas ainda atualiza toda a visualização. Com centenas de milhões de linhas, isso leva alguns minutos.

Qual é uma boa maneira de acompanhar os valores atualizados e novos e apenas atualizar a exibição parcialmente?

postgresql postgresql-9.3

2 respostas

  1. Best Answer

    Você sempre pode implementar sua própria tabela servindo como "visão materializada". Foi assim que fizemos antes de MATERIALIZED VIEWser implementado no Postgres 9.3.

    Você pode criar uma planície VIEW:

    CREATE VIEW graph_avg_view AS 
SELECT xaxis, AVG(value) AS avg_val
FROM   graph
GROUP  BY xaxis;

    E materialize o resultado uma vez ou sempre que precisar recomeçar:

    CREATE TABLE graph_avg AS
SELECT * FROM graph_avg_view;

    (Ou use a SELECTinstrução diretamente, sem criar um VIEW.)
    Então, dependendo dos detalhes não divulgados do seu caso de uso, você pode // DELETEalterar manualmente.UPDATEINSERT

    Uma instrução DML básica com CTEs de modificação de dados para sua tabela como está :

    Supondo que ninguém mais tente escrever simultaneamente ( a graph_avgleitura não é problema):

    WITH del AS (
   DELETE FROM graph_avg t
   WHERE  NOT EXISTS (SELECT FROM graph_avg_view WHERE xaxis = t.xaxis)
   )
, upd AS (
   UPDATE graph_avg t
   SET    avg_val = v.avg_val
   FROM   graph_avg_view v
   WHERE  t.xaxis = v.xaxis
   AND    t.avg_val <> v.avg_val
-- AND    t.avg_val IS DISTINCT FROM v.avg_val  -- alt if avg_val can be NULL
   )
INSERT INTO graph_avg t  -- no target list, whole row
SELECT v.*
FROM   graph_avg_view v
WHERE  NOT EXISTS (SELECT FROM graph_avg WHERE xaxis = v.xaxis);

    Receita básica

    • Adicione uma timestampcoluna com padrão now()à sua tabela base. Vamos chamá-lo ts.
      • Se você tiver atualizações, adicione um gatilho para definir o carimbo de data/hora atual com cada atualização que muda xaxisou value.

    • Crie uma pequena tabela para lembrar o carimbo de data/hora do seu último instantâneo. Vamos chamá-lo mv:

      CREATE TABLE mv (
   tbl text PRIMARY KEY
 , ts timestamp NOT NULL DEFAULT '-infinity'
); -- possibly more details

    • Crie este índice parcial de várias colunas:

      CREATE INDEX graph_mv_latest ON graph (xaxis, value)
WHERE  ts >= '-infinity';

    • Use o carimbo de data/hora do último instantâneo como predicado em suas consultas para atualizar o instantâneo com o uso de índice perfeito.

    • No final da transação, elimine o índice e recrie-o com o carimbo de data/hora da transação substituindo o carimbo de data/hora no predicado do índice (inicialmente '-infinity'), que você também salva em sua tabela. Tudo em uma transação.

    • Observe que o índice parcial é ótimo para cobrir INSERTe UPDATEoperações, mas não DELETE. Para cobrir isso, você precisa considerar a tabela inteira. Tudo depende dos requisitos exatos.

    • 32

  2. Atualização simultânea (Postgres 9.4)

    Embora não seja uma atualização incremental como você pediu, o Postgres 9.4 fornece um novo recurso de atualização simultânea .

    Para citar o documento…

    Antes do PostgreSQL 9.4, atualizar uma visualização materializada significava bloquear a tabela inteira e, portanto, impedir que qualquer coisa a consultasse, e se uma atualização levasse muito tempo para adquirir o bloqueio exclusivo (enquanto espera que as consultas usando-o terminem), ele por sua vez está retendo as consultas subsequentes. Isso agora pode ser mitigado com a palavra-chave CONCURRENTLY:

     postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY mv_data;

    No entanto, um índice exclusivo precisará existir na visualização materializada. Em vez de bloquear a visualização materializada, ele cria uma versão atualizada temporária dela, compara as duas versões e aplica INSERTs e DELETEs na visualização materializada para aplicar a diferença. Isso significa que as consultas ainda podem usar a visualização materializada enquanto ela está sendo atualizada. Ao contrário de sua forma não concorrente, as tuplas não são congeladas e precisam de VACUUMing devido aos DELETEs mencionados acima que deixarão as tuplas mortas para trás.

    Esta atualização simultânea ainda está executando uma nova consulta completa (não incremental). Portanto, CONCURRENTLY não economiza no tempo total de computação, apenas minimiza a quantidade de tempo que sua visualização materializada fica indisponível para uso durante sua atualização.

    • 20

relate perguntas