Compreendendo o uso do disco do PostgreSQL

Question

Estou usando o 9.1 e tenho uma tabela chamada dpi em um banco de dados com codificação UTF-8 atualmente em 18.628 linhas. Há uma coluna TEXT chamada foto que contém as cadeias B64 que representam um arquivo de imagem exclusivo.

Estou tentando descobrir quanto espaço em disco uma linha ocupa. Há 18050 linhas com um valor não NULL para foto e length() para todas elas é 87384.

O que eu não entendo é isso. Executando select * from pgstattuple('dpi'); retornos:

-[ RECORD 1 ]------+--------
table_len          | 5890048
tuple_count        | 18628
tuple_len          | 5656063
tuple_percent      | 96.03
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 92752
free_percent       | 1.57

Rodando

select pg_size_pretty(pg_relation_size(c.oid)) AS "size"
from pg_class c
where relname = 'dpi';

Eu recebo o seguinte:

  size
---------
 5752 kB
(1 row)

O que exatamente está acontecendo aqui? Por que esta mesa é tão pequena?

Edit: Além disso, verifiquei /var/lib/postgresql/9.1/main e mede 574,5 MiB. Executando:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 3;

rendimentos:

           relation            |  size
-------------------------------+---------
 pg_toast.pg_toast_17048       | 501 MB
 public.dpi                    | 5752 kB
 pg_toast.pg_toast_17048_index | 5752 kB
(3 rows)

Editar 2: Usando select pg_total_relation_size('dpi'); , recebo 539262976 bytes. Isso significa que eu posso dividir isso por 18628 e isso me dará um tamanho médio por linha (28 KiB)? Esse tamanho por linha muda com o tempo (por causa de índices ou similar)?

Mesmo assim, 28 KiB por linha parece excepcionalmente pequeno. Um valor foto sozinho deve ser 85 KiB (assumindo apenas caracteres de 1 byte).

A resposta é que o mecanismo PostgreSQL TOAST, por padrão, não apenas fragmenta colunas muito grandes, dependendo do tipo de dados que ele realmente tenta compactá-las primeiro. Bem legal.

Fonte: link

    

Answer 1

O Postgresql não possui "tipo de blobs". O que acontece é que ele divide automaticamente "colunas grandes" (ou seja, maiores que 1 página, 8k) em tabelas "brindadas".

Procure aqui as informações sobre o uso do disco no postgres

e aqui para função admin postgresql onde você aprenderá que as funções de interesse porque você é: pg_table_size e pg_total_relation_size

Ambos levam em conta o espaço do brinde. O primeiro não conta o espaço do índice, o segundo.