Calculando e economizando espaço no PostgreSQL

Eu tenho uma tabela em pg assim:

CREATE TABLE t ( a BIGSERIAL NOT NULL, -- 8 b b SMALLINT, -- 2 b c SMALLINT, -- 2 b d REAL, -- 4 b e REAL, -- 4 b f REAL, -- 4 b g INTEGER, -- 4 b h REAL, -- 4 b i REAL, -- 4 b j SMALLINT, -- 2 b k INTEGER, -- 4 b l INTEGER, -- 4 b m REAL, -- 4 b CONSTRAINT a_pkey PRIMARY KEY (a) ); 

O acima adiciona até 50 bytes por linha. Minha experiência é que eu preciso de mais 40% a 50% para a sobrecarga do sistema, sem mesmo nenhum índice criado pelo usuário para o acima. Então, cerca de 75 bytes por linha. Eu terei muitas, muitas linhas na tabela, potencialmente acima de 145 bilhões de linhas, então a tabela vai empurrar 13-14 terabytes. Quais truques, se algum, eu poderia usar para compactar essa tabela? Minhas ideias possíveis abaixo …

Converta os valores real em integer . Se eles podem ser armazenados como smallint , isso é uma economia de 2 bytes por campo.

Converta as colunas b .. m em uma matriz. Não preciso pesquisar nessas colunas, mas preciso poder retornar o valor de uma coluna por vez. Então, se eu precisar da coluna g, eu poderia fazer algo como

 SELECT a, arr[5] FROM t; 

Economizaria espaço com a opção de matriz? Haveria uma penalidade de velocidade?

Alguma outra ideia?

Não vejo nada a ganhar (e algo a perder) ao armazenar vários campos numéricos em uma matriz.

O tamanho de cada tipo numérico é claramente documentado, você deve simplesmente usar o menor tamanho compatível com sua resolução de alcance desejada; e isso é tudo que você pode fazer.

Eu não acho (mas não tenho certeza) se há algum requisito de alinhamento de byte para as colunas ao longo de uma linha, nesse caso, um reordenamento das colunas poderia alterar o espaço usado – mas eu não penso assim.

BTW, há uma sobrecarga de correção por linha, cerca de 23 bytes .

“Coluna Tetris”

Na verdade, você pode fazer alguma coisa , mas isso precisa de uma compreensão mais profunda. A palavra-chave é o preenchimento de alinhamento . Todo tipo de dados possui requisitos de alinhamento específicos .

Você pode minimizar o espaço perdido no preenchimento entre as colunas , ordenando-as favoravelmente. O seguinte exemplo (extremo) desperdiçaria muito espaço em disco físico:

 CREATE TABLE t ( e int2 -- 6 bytes of padding after int2 , a int8 , f int2 -- 6 bytes of padding after int2 , b int8 , g int2 -- 6 bytes of padding after int2 , c int8 , h int2 -- 6 bytes of padding after int2 , d int8) 

Para salvar 24 bytes por linha, use em vez disso:

 CREATE TABLE t ( a int8 , b int8 , c int8 , d int8 , e int2 , f int2 , g int2 , h int2) -- 4 int2 occupy 8 byte (MAXALIGN), no padding at the end 

Como regra geral, se você colocar as colunas de 8 bytes primeiro, as colunas de 4 bytes, 2 bytes e 1 byte duram, e você não pode errar. text ou boolean não tem restrições de alinhamento como essas, outros tipos fazem. Alguns tipos podem ser comprimidos ou “torrados” (armazenados fora da linha) ou ambos.

Normalmente, você pode economizar alguns bytes por linha, na melhor das hipóteses jogando “coluna tetris” . Nada disso é necessário na maioria dos casos. Mas com bilhões de linhas pode significar alguns gigabytes facilmente.

Você pode testar o tamanho real da coluna / linha com a function pg_column_size() .
Esteja ciente de que alguns tipos de dados podem usar mais espaço na RAM do que no disco (formato compactado). Assim, você pode obter resultados maiores para constantes (formato RAM) do que para colunas de tabela (formato de disco) ao testar o mesmo valor (ou linha de valores versus linha da tabela) com pg_column_size() .

SQL Fiddle.

Sobrecarga por tupla (linha)

4 bytes por linha para o ponteiro de item – não sujeitos às considerações acima.
E pelo menos 24 bytes (23 + preenchimento) para o header da tupla. O manual no Layout de Página do Banco de Dados:

Há um header de tamanho fixo (ocupando 23 bytes na maioria das máquinas), seguido por um bitmap nulo opcional, um campo de ID de object opcional e os dados do usuário.

Para o preenchimento entre o header e os dados do usuário, você precisa saber MAXALIGN em seu servidor – geralmente 8 bytes em um sistema operacional de 64 bits (ou 4 bytes em um sistema operacional de 32 bits). Se você não tiver certeza, confira pg_controldata .

Execute o seguinte no seu diretório binário do Postgres para obter uma resposta definitiva:

 ./pg_controldata /path/to/my/dbcluster 

O manual:

Os dados reais do usuário (colunas da linha) começam no deslocamento indicado por t_hoff , que deve sempre ser um múltiplo da distância MAXALIGN para a plataforma.

Assim, você normalmente obtém o armazenamento ideal reunindo dados em múltiplos de 8 bytes.

Não há nada a ganhar no exemplo que você postou . Já está bem embalado. 2 bytes de preenchimento após o último int2 , 4 bytes no final. Você poderia consolidar o preenchimento em 6 bytes no final, o que não mudaria nada.

Sobrecarga por página de dados

O tamanho da página de dados é geralmente de 8 KB. Alguma sobrecarga / inchaço nesse nível também: Restos não grandes o suficiente para caber em outra tupla e, mais importante, linhas mortas ou uma porcentagem reservada com a configuração FILLFACTOR .

Existem alguns outros fatores para o tamanho do disco levar em conta:

  • Quantos registros posso armazenar em 5 MB de PostgreSQL no Heroku?
  • Não usar NULL no PostgreSQL ainda usa um bitmap NULL no header?
  • Configurando o PostgreSQL para desempenho de leitura

Tipos de matriz?

Com o array que você estava avaliando, você adicionaria 24 bytes de sobrecarga para o tipo de matriz. Além disso, elementos de uma matriz ocupam espaço como de costume. Nada para ganhar lá.

Intereting Posts