Início / dba / Perguntas / 286640
Accepted
ldrg
Asked: 2021-03-08 14:41:46 +0800 CST

A ordem de coluna otimizada para uma tabela PostgreSQL sempre tem tipos de comprimento variável no final?

  • 772

Há um post de blog popular e aparentemente oficial chamado On Rocks and Sand sobre como otimizar o tamanho das tabelas PostgreSQL para eliminar o preenchimento interno reordenando o comprimento da coluna. Eles explicam como os tipos de comprimento variável incorrem em algum preenchimento extra se não estiverem no final da tabela:

Isso significa que podemos encadear colunas de comprimento variável o dia todo sem introduzir preenchimento, exceto no limite direito. Conseqüentemente, podemos deduzir que as colunas de comprimento variável não apresentam inchaço, desde que estejam no final de uma listagem de colunas.

E no final do post, resumindo:

Classifique as colunas pelo comprimento do tipo, conforme definido em pg_type.

Existe uma biblioteca que se integra ao ActiveRecord do Ruby para reordenar automaticamente as colunas para reduzir o preenchimento chamado pg_column_byte_packer . Você pode ver o README em que o repositório cita a postagem do blog acima e, em geral, faz a mesma coisa que a postagem do blog descreve.

No entanto, o pg_column_byte_packernão retorna resultados consistentes com a postagem do blog que cita. A postagem do blog é extraída do interno do PostgreSQL, pg_type.typelenque coloca colunas de comprimento variável sempre no final por meio de um alinhamento de -1. pg_column_byte_packerdá-lhes um alinhamento de 3.

pg_column_byte_packertem um comentário explicativo :

    # These types generally have an alignment of 4 (as designated by pg_type
    # having a typalign value of 'i', but they're special in that small values
    # have an optimized storage layout. Beyond the optimized storage layout, though,
    # these small values also are not required to respect the alignment the type
    # would otherwise have. Specifically, values with a size of at most 127 bytes
    # aren't aligned. That 127 byte cap, however, includes an overhead byte to store
    # the length, and so in reality the max is 126 bytes. Interestingly TOASTable
    # values are also treated that way, but we don't have a good way of knowing which
    # values those will be.
    #
    # See: `fill_val()` in src/backend/access/common/heaptuple.c (in the conditional
    # `else if (att->attlen == -1)` branch.
    #
    # When no limit modifier has been applied we don't have a good heuristic for
    # determining which columns are likely to be long or short, so we currently
    # just slot them all after the columns we believe will always be long.

O comentário parece não estar errado, pois as colunas de texto têm um pg_type.typalignde 4, mas também têm um pg_type.typlende -1, que a postagem do blog argumenta que obtém a embalagem mais ideal quando no final da tabela.

Portanto, no caso de uma tabela que tenha uma integercoluna, uma textcoluna e uma smallintcoluna, pg_column_byte_packercolocará as colunas de texto entre as duas. Eles até têm um teste de unidade para afirmar que isso sempre acontece.

Minha pergunta aqui é: qual ordem de colunas realmente é compactada para espaço mínimo? O comentário de pg_column_byte_packerparece não estar errado, pois as colunas de texto têm um pg_type.typalignde 4, mas também têm um pg_type.typlende -1.

postgresql

1 respostas

  1. Best Answer

    O comentário de pg_column_byte_packerparece não estar errado, pois as colunas de texto têm um pg_type.typalignde 4, mas também têm um pg_type.typlende -1.

    Fiquei intrigado também, quando pesquisei alguns anos atrás. typlen = -1apenas indica varlenaarmazenamento, que nominalmente tem typalign = 'i'(alinhamento de inteiro, precisa começar com deslocamento de 4 bytes). Mas essa não é toda a história. Eventualmente, encontrei a explicação em uma nota no código-fonte:

    Observe também que permitimos que o alinhamento nominal seja violado ao armazenar varlenas "empacotadas"; o mecanismo TOAST cuida de esconder isso da maioria dos códigos.

    Portanto, um varlenadatum < 127 bytes (após a possível compactação) adiciona apenas 1 byte de sobrecarga (significando seu comprimento) e não requer preenchimento de alinhamento "no disco" . (Dificilmente existem "discos" mais nos dias de hoje.)

    Ver:

    Respondendo a pergunta do título:

    A ordem de coluna otimizada para uma tabela PostgreSQL sempre tem tipos de comprimento variável no final?

    Verdadeiro. Datums que excedem 127 bytes não podem ser armazenados de forma otimizada e voltam a exigir "alinhamento inteiro". Se não sabemos que uma coluna do tipo varlena ficará abaixo desse limite (na maioria das vezes), não podemos dizer com certeza.

    Além disso, há considerações adicionais para armazenamento otimizado. Com muitas colunas em uma linha, é mais barato calcular os deslocamentos de armazenamento de tupla com NOT NULLcolunas de tamanho fixo primeiro. Colocar as colunas acessadas com frequência primeiro também gera uma pequena vantagem. Tudo isso é ainda mais complicado pelo mecanismo TOAST e acesso index(-only).

    Mas todos esses efeitos são tipicamente minúsculos. E perder 3 bytes para o preenchimento de alinhamento é insignificante em comparação quando uma coluna ocupa 200 bytes. Então, principalmente, não vale a pena. A regra geral cobre a maior parte disso:

    Classifique as colunas pelo alinhamento necessáriotypalign :
    d--> i--> s--> c.
    Mas typlen = -1("varlena") por último (tipicamente), embora formalmente typalign = 'i'.

    O manual:

    c = char alignment, i.e., no alignment needed.
s = short alignment (2 bytes on most machines).
i = int alignment (4 bytes on most machines).
d = double alignment (8 bytes on many machines, but by no means all).

    Seu exemplo

    Portanto, no caso de uma tabela que tenha uma integercoluna, uma textcoluna e uma smallintcoluna, pg_column_byte_packercolocará as colunas de texto entre as duas.

    pg_column_byte_packerestá fazendo jus ao seu nome. int--> text--> smallinté o mais apertado possível.

    Para o caso típico de strings curtas, a única decisão relevante é colocar em intprimeiro lugar. smallintpode forçar no máximo 1 byte adicional de preenchimento de alinhamento em deslocamentos de bytes ímpares. Como o espaço da tupla é sempre alocado em múltiplos de 8 bytes, isso nunca pode resultar em uma tupla maior.

    Strings que excedem o limite de 127 bytes no disco (incluindo 1 byte de comprimento inicial), mudam para 4 bytes de comprimento inicial e exigem o integeralinhamento nominal. É aí que colocar textantes smallintpode proteger 8 bytes de forma eficaz. Com comprimento de string aleatório que acontece em 25% dos casos, então 2 bytes em média para tuplas de pelo menos 144 bytes.

    Há isso. Mas colocar o smallintprimeiro normalmente tem pequenas vantagens, e a maioria das textcolunas fica bem abaixo do limite de comprimento.

    A coisa a lembrar é não intercalar múltiplas smallinte textcolunas. Vários deslocamentos podem somar neste caso.

    • 2

relate perguntas