Início / user-278984

George G's questions

Martin Hope
George G
Asked: 2023-09-05 01:22:47 +0800 CST
  • 5

Estou analisando um aplicativo legado que gerencia objetos armazenados em um banco de dados PostgreSQL.

Cada objeto possui vários conjuntos de propriedades que são armazenados como objetos JSONB. Cada um desses conjuntos de propriedades descreve um aspecto diferente do objeto (pode haver de 1 a 10 conjuntos de propriedades por objeto).

Em relação a isso, diferentes departamentos geralmente se preocupam apenas com alguns desses conjuntos de propriedades (aspectos), portanto o número de conjuntos de propriedades varia dependendo de quem está envolvido durante o ciclo de vida de cada objeto.

Cada vez que algo muda em uma propriedade definida pelo aplicativo front-end (que está além do meu controle), ele é enviado de volta ao banco de dados como um novo documento JSON.

No momento, a tabela (para efeito de argumentação) está estruturada da seguinte forma:

TABLE x (
  object_id INTEGER,
  aspect_id INTEGER,
  doc_ts TIMESTAMP,
  doc JOSNB,
  PRIMARY KEY (object_id, aspect_id, doc_ts)
)

A entrada de dados é tratada por um procedimento, que armazena uma cópia do documento em uma tabela, e tem a seguinte aparência:

CREATE OR REPLACE PROCEDURE ingest.generic_object(IN in_object_id integer, IN in_aspect_id integer, IN in_doc_ts TIMESTAMP,  IN in_doc JSONB DEFAULT NULL::JSONB)
 LANGUAGE plpgsql
AS $procedure$
  BEGIN
      INSERT INTO ingest.x ( object_id, aspect_id, doc_ts, doc) 
      VALUES (in_object_id, in_aspect_id, in_doc_ts, in_doc)
      ;
  END;
$procedure$;

Agora vamos passar para o problema que estou tentando resolver. Esta tabela continua crescendo com o tempo e pode ficar muito grande, muito rapidamente. O processo de manutenção mantém APENAS um registro para cada combinação de object_id e aspecto_id (ou seja, apenas aquele com o valor doc_ts mais recente) e leva muito tempo para ser executado.

Estou pensando em dividir os dados em duas tabelas. Como:

TABLE ingest.x (
  object_id INTEGER,
  aspect_id INTEGER,
  doc_ts TIMESTAMPZ,
  doc JOSNB,
  PRIMARY KEY (object_id, aspect_id, doc_ts)
);

TABLE ingest.x_history (
  object_id INTEGER,
  aspect_id INTEGER,
  doc_ts TIMESTAMP,
  doc JOSNB,
  PRIMARY KEY (object_id, aspect_id, doc_ts)
);

E então altere a função de ingestão para algo parecido com:

CREATE OR REPLACE PROCEDURE ingest.generic_object(IN in_object_id integer, IN in_aspect_id integer, IN in_doc_ts TIMESTAMP, IN in_doc JSONB DEFAULT NULL::JSONB)
 LANGUAGE plpgsql
AS $procedure$
  BEGIN
      WITH 
        dd AS (
          DELETE FROM ingest.x
          WHERE object_id= in_object_id
            AND aspect_id = in_aspect_id
          RETURNING *
        ), 
        di AS (
          INSERT INTO ingest.x_history ( object_id, aspect_id, doc_ts, doc) 
          SELECT object_id, aspect_id, doc_ts, doc FROM dd            
        )
      INSERT INTO ingest.x ( object_id, aspect_id, doc_ts, doc) 
      VALUES (in_object_id, in_aspect_id, in_doc_ts, in_doc)
      ;
  END;
$procedure$;

Isso me permitiria simplificar SIGNIFICATIVAMENTE o processo de limpeza, até:

TRUNCATE TABLE ingest.x_history;

Minhas perguntas são as seguintes:

  1. Considerando a alteração acima, o AUTOVACUUM levará a alguma reutilização do espaço ocupado pelos registros excluídos em ingest.x?

  2. Quais parâmetros de tabela ingest.xdevo ajustar para melhorar a reutilização do espaço liberado pelos registros excluídos? - Supondo que a resposta para 1 seja SIM. Tenho em mente o FILL FACTOR juntamente com limites de AUTOVACUUM mais direcionados.

  3. Você consegue ver uma abordagem alternativa para alcançar um processo de limpeza mais fácil e eficaz?

Observação:

  • Tudo isso está rodando no PostgreSQL v15
  • Os registros de documentos podem ser redigidos dezenas de vezes durante a fase de criação – primeiros dias. Depois, uma vez a cada dois dias/semanas durante o período de revisão. Mudanças podem acontecer depois disso, mas são muito raras.
  • Os dados brutos podem envelhecer muito rapidamente, e é por isso que TRUNCAR o histórico não é um problema.

Edição 1: Os documentos JSON variam entre 100kb e 2MB. Acredito que sejam direcionados ao TOAST. Além disso, o banco de dados está em um sistema de arquivos ZFS e, como tal, a compactação PostgreSQL está desativada, aproveitando a compactação ZFS.

Atenciosamente

postgresql-15