Início / dba / Perguntas / 153755
Accepted
hank
Asked: 2016-11-01 00:48:46 +0800 CST

Extraindo 'colunas quentes' em uma tabela separada

  • 772

Eu tenho esta tabela:

CREATE TABLE fragment
(
  fragment_id integer,
  start_date timestamp without time zone,
  end_date timestamp without time zone,
  duration integer,
  -- <10+ more columns>
  revision_1 integer,
  revision_2 integer
)

É bem grande: 44 milhões de linhas, 27 GB de espaço em disco. A taxa de inserção diária é de cerca de 70 mil linhas.

Os dados nesta tabela quase nunca são atualizados, exceto para as duas últimas colunas denominadas revision_1e revision_2. Eles são atualizados por meio de gatilhos definidos em outras tabelas relacionadas. As atualizações ocorrem com muita frequência, especialmente para novas linhas na fragmenttabela. Cada linha pode ser atualizada até 50-100 vezes. Linhas antigas (digamos com 1 semana), no entanto, param de ser atualizadas, pois são consideradas 'processadas'.

Tanto quanto eu sei, UPDATEa operação no Postgres é implementada como algo como DELETE+ INSERT. Portanto, quando um valor em uma única coluna é atualizado, toda a linha é marcada como excluída e uma nova linha é criada. Acho que é por isso que minha fragmentmesa é aspirada todos os dias, o que leva várias horas.

A questão é: geralmente é uma boa ideia extrair 'colunas quentes' em uma tabela separada? Quero dizer algo assim:

CREATE TABLE fragment_revision
(
  fragment_id integer,
  revision_1 integer,
  revision_2 integer
)
database-design postgresql

1 respostas

  1. Best Answer

    O comportamento que você está descrevendo é chamado de MVCC (controle de simultaneidade de várias versões). Estritamente dizendo que não é deletar + inserir. É mais como:

    1. copiar a versão atual da linha
    2. atualize conforme solicitado
    3. anexar nova versão atual a uma lista encadeada de versão

    Em segundo plano, esse histórico está sendo limpo, dependendo da idade da sua transação mais antiga. Se você tiver transações de execução longa, o histórico pode crescer muito.

    Isso não é específico do postgresql. É um método muito comum em muitos bancos de dados para lidar com a simultaneidade. Para ver mais: MVCC no Postgresql

    Portanto , faz sentido mover essa "coluna quente" para uma tabela separada. Especialmente se o tamanho da linha for significativamente maior que o tamanho das "colunas quentes".

    Esteja ciente de que isso tem algumas implicações no desempenho selecionado:

    • Se essas colunas estiverem presentes em suas consultas para filtragem ou classificação, o tempo de consulta será atingido
    • Para recuperar essas colunas, você precisa se juntar à tabela, o que novamente tem algum impacto no tempo

    Existe um método alternativo de divisão em que sua tabela principal contém apenas as colunas pequenas, de filtragem e classificação (incluindo essas colunas também) e os "dados" são armazenados separadamente.

    É sempre melhor tentar qual funciona melhor para seu conjunto de dados e padrões de consulta. Eu fiz um benchmark há cerca de um ano comparando uma grande tabela com duas estratégias de divisão que podem ajudá-lo a começar: http://charlesnagy.info/it/postgresql/split-or-leave-frequently-updated-column-in-postgresql

    • 4

relate perguntas