Medir o tamanho de uma linha da tabela PostgreSQL

Q2:way to measure page size

O PostgreSQL fornece várias funções de tamanho de objeto de banco de dados . Empacotei os mais interessantes nesta consulta e adicionei algumas funções de acesso a estatísticas na parte inferior. (O módulo adicional pgstattuple ainda fornece funções mais úteis.)

Isso vai mostrar que diferentes métodos para medir o "tamanho de uma linha" levam a resultados muito diferentes. Tudo depende do que você quer medir, exatamente.

Esta consulta requer o Postgres 9.3 ou posterior . Para versões mais antigas, veja abaixo.

Usando uma VALUESexpressão em uma LATERALsubconsulta , para evitar soletrar cálculos para cada linha.

Substitua public.tblpor seu nome de tabela opcionalmente qualificado pelo esquema para obter uma visualização compacta das estatísticas de tamanho de linha coletadas. Você pode envolver isso em uma função plpgsql para uso repetido, entregar o nome da tabela como parâmetro e usar EXECUTE...

SELECT l.metric, l.nr AS bytes
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.tbl t                     -- provide table name *once*
   ) x
CROSS  JOIN LATERAL (
   VALUES
     (true , 'core_relation_size'               , pg_relation_size(tbl))
   , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
   , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
   , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
   , (true , 'indexes_size'                     , pg_indexes_size(tbl))
   , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
   , (true , 'live_rows_in_text_representation' , txt_len)
   , (false, '------------------------------'   , NULL)
   , (false, 'row_count'                        , ct)
   , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
   , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, metric, nr);

Resultado:

              métrica | bytes | bytes_pretty | bytes_per_row
-----------------------------------+----------+--- -----------+---------------
 core_relation_size | 44138496 | 42MB | 91
 visibilidade_mapa | 0 | 0 bytes | 0
 free_space_map | 32768 | 32kB | 0
 table_size_incl_toast | 44179456 | 42MB | 91
 índices_tamanho | 33128448 | 32MB | 68
 total_size_incl_toast_and_indexes | 77307904 | 74MB | 159
 live_rows_in_text_representation | 29987360 | 29MB | 62
 ------------------------------ | | |
 linha_conta | 483424 | |
 live_tuples | 483424 | |
 dead_tuples | 2677 | |

Para versões mais antigas ( Postgres 9.2 ou anterior ):

WITH x AS (
   SELECT count(*)               AS ct
        , sum(length(t::text))   AS txt_len  -- length in characters
        , 'public.tbl'::regclass AS tbl      -- provide table name as string
   FROM   public.tbl t                       -- provide table name as name
   ), y AS (
   SELECT ARRAY [pg_relation_size(tbl)
               , pg_relation_size(tbl, 'vm')
               , pg_relation_size(tbl, 'fsm')
               , pg_table_size(tbl)
               , pg_indexes_size(tbl)
               , pg_total_relation_size(tbl)
               , txt_len
             ] AS val
        , ARRAY ['core_relation_size'
               , 'visibility_map'
               , 'free_space_map'
               , 'table_size_incl_toast'
               , 'indexes_size'
               , 'total_size_incl_toast_and_indexes'
               , 'live_rows_in_text_representation'
             ] AS name
   FROM   x
   )
SELECT unnest(name)                AS metric
     , unnest(val)                 AS bytes
     , pg_size_pretty(unnest(val)) AS bytes_pretty
     , unnest(val) / NULLIF(ct, 0) AS bytes_per_row
FROM   x, y

UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;

Mesmo resultado.

Q1:anything inefficient?

Você pode otimizar a ordem das colunas para economizar alguns bytes por linha, atualmente desperdiçados no preenchimento de alinhamento:

integer                  | not null default nextval('core_page_id_seq'::regclass)
integer                  | not null default 0
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
text                     | default '{}'::text
text                     |
timestamp with time zone |
timestamp with time zone |
integer                  |
integer                  |

Isso economiza entre 8 e 18 bytes por linha. Eu chamo de Coluna Tetris . Ver:

• Configurando o PostgreSQL para desempenho de leitura
• Calculando e economizando espaço no PostgreSQL

Considere também:

• A pesquisa de índice seria visivelmente mais rápida com char vs varchar quando todos os valores são 36 caracteres

Uma aproximação do tamanho de uma linha, incluindo o conteúdo do TOAST 'ed, é fácil de obter consultando o comprimento da representação TEXT de toda a linha:

SELECT octet_length(t.*::text) FROM tablename AS t WHERE primary_key=:value;

Esta é uma aproximação do número de bytes que serão recuperados do lado do cliente durante a execução:

SELECT * FROM tablename WHERE primary_key=:value;

...assumindo que o chamador da consulta está solicitando resultados em formato de texto, que é o que a maioria dos programas faz (o formato binário é possível, mas não vale a pena na maioria dos casos).

A mesma técnica pode ser aplicada para localizar as Nlinhas "maiores no texto" de tablename:

SELECT primary_key, octet_length(t.*::text) FROM tablename AS t
   ORDER BY 2 DESC LIMIT :N;

Existem algumas coisas que podem estar acontecendo. Em geral, duvido que o comprimento seja o problema proximal. Suspeito que, em vez disso, você tenha um problema relacionado ao comprimento.

Você diz que os campos de texto podem chegar a alguns k. Uma linha não pode ultrapassar 8k no armazenamento principal e é provável que seus campos de texto maiores tenham sido torrados ou movidos do armazenamento principal para um armazenamento estendido em arquivos separados. Isso torna seu armazenamento principal mais rápido (portanto, select id é realmente mais rápido porque menos páginas de disco para acessar), mas select * torna-se mais lento porque há mais E/S aleatória.

Se os tamanhos totais de linha ainda estiverem bem abaixo de 8k, você pode tentar alterar as configurações de armazenamento. Eu, no entanto, aviso que você pode fazer coisas ruins acontecerem ao inserir um atributo superdimensionado no armazenamento principal, então é melhor não tocar nisso se você não precisar e, se precisar, defina limites apropriados por meio de restrições de verificação. Portanto, o transporte provavelmente não é a única coisa. Pode estar agrupando muitos campos que exigem leituras aleatórias. Grandes números de leituras aleatórias também podem causar faltas de cache, e grandes quantidades de memória necessárias podem exigir que as coisas sejam materializadas em disco e um grande número de linhas largas, se uma junção estiver presente (e houver uma se o TOAST estiver envolvido) pode exigir mais custos juntar padrões, etc.

A primeira coisa que eu faria é selecionar menos linhas e ver se isso ajuda. Se isso funcionar, você pode tentar adicionar mais RAM ao servidor também, mas eu começaria e veria onde o desempenho começa a cair devido a alterações de plano e falhas de cache primeiro.

Usando as funções de tamanho de objeto de banco de dados mencionadas acima:

SELECT primary_key, pg_column_size(tablename.*) FROM tablename;

Se uma média do tamanho das linhas atuais for desejada, você poderá usar pg_column_size:

SELECT SUM(pg_column_size(table_name.*))/COUNT(*) FROM tablename;

Usando por coluna:

SELECT SUM(pg_column_size(table_name.column_name))/COUNT(*) FROM tablename;