Início / dba / Perguntas / 331698
Accepted
Slim
Asked: 2023-09-29 16:40:38 +0800 CST

Coluna pequena separada atualizada muito de colunas jsonb grandes para otimizar o desempenho?

  • 772

Uma tabela tem a seguinte estrutura:

  • ajuda
  • b: jsonb grande
  • c: jsonb grande
  • d: carimbo de data/hora

Olhando as estatísticas em 2 semanas, veja muitas atualizações:

  • 90 mil solicitações atualizam dapenas o campo (tempo total: 25 minutos medidos no aplicativo)
  • 90 mil solicitações atualizam os campos "jsonb grande" (tempo total: 6h30 medidos no aplicativo)

Como o Postgres segue o padrão MVCC que reescreve as linhas na atualização, é interessante alterar a estrutura da tabela para ter duma tabela separada?

postgresql

2 respostas

  1. Best Answer

    Não, isso não é necessário. Os grandes valores JSON parecem estar armazenados fora de linha em uma tabela TOAST. Então a atualização dsó precisa escrever uma nova linha na tabela principal, mas os valores do TOAST permanecem os mesmos.

    Se os valores JSON fossem pequenos o suficiente para serem armazenados na tabela principal, a modificação dseria muito mais cara, porque seria necessário gravar novamente a linha inteira com os JSONs. Então pode fazer sentido considerar tal separação.

    • 1

  2. Como Laurenz aconselhou, os valores das colunas que são armazenados fora de linha em uma tabela TOAST e não são tocados na tabela UPDATEpodem permanecer como estão, economizando trabalho e armazenamento. (As versões de linha antiga e nova da relação principal apontam para a mesma entrada TOAST.)

    Mas há letras miúdas nisso.

    Você já está brindando?

    O tamanho da linha deve exceder TOAST_TUPLE_THRESHOLD(normalmente 2 KB) para ser considerado. Algumas colunas podem já estar compactadas. O mecanismo TOAST tentará então compactar mais colunas até que o tamanho caia abaixo TOAST_TUPLE_TARGET(normalmente os mesmos 2 kb). Somente se isso não acontecer, ele começará a mover os dados para a tabela TOAST, uma coluna por vez.

    O algoritmo de compactação padrão pglzcompacta jsonbos dados em torno do fator 3 a 4 em meu teste rápido. (Varia dependendo do conteúdo.) Portanto, o tamanho bruto da sua linha deve exceder cerca de 7 kb, antes que qualquer coisa seja TOAST. E então, possivelmente, apenas uma das suas duas colunas "grandes". Isso pode deixar você com tuplas de tamanho médio na relação principal de até 2 kb, o que nos traz de volta às suas considerações iniciais.

    Observe mais de perto o tamanho compactado (!) De seus jsonbvalores e o tamanho real da tupla na relação principal (mín, máximo, média, média) para chegar a uma decisão informada.

    Aqui está uma postagem no blog com perguntas úteis:

    "Inalterado"

    Sobre:

    90 mil solicitações atualizam os campos "jsonb grande" ...

    Normalmente, uma atualização apenas altera uma ou outra coluna ou atualiza uma ou outra sem alterações reais. Os valores da coluna TOASTed permanecem os mesmos se a coluna estiver "inalterada". Para ser mais preciso: se a coluna não estiver segmentada no arquivo UPDATE. Se você substituir uma coluna por um valor inalterado, isso contará como "alterado". O Postgres não verificará se o valor realmente mudou e escreverá uma nova versão de linha, incluindo TOAST. Evite essas atualizações "vazias" com um design de consulta inteligente. Ver:

    Algoritmo de compressãolz4

    Se o que você divulgou é tudo o que acontece na sua tabela, considere o algoritmo de compactação LZ4 não padrão. (Disponível desde o Postgres 14. ) É muito mais rápido, mas compacta um pouco menos. (O tamanho resultante foi aproximadamente 15% maior em meu teste rápido de jsonbdados.) Aqui está a postagem do blog com uma comparação exaustiva de quando foi lançado pela primeira vez:

    Ver:

    Em resumo , use o algoritmo de compactação apropriado, depois use consultas inteligentes, reúna estatísticas válidas sobre seus dados e decida se a divisão de uma ou outra coluna pode fazer sentido. Deve haver um benefício claro para justificar a sobrecarga.

    • 1

relate perguntas