Daniel Lyons's questions

Estou executando o PostgreSQL 9.1. Percebi que índices realmente ruins estão ocupando muito espaço no meu servidor, por exemplo:

evlampts=# SELECT 
  pg_size_pretty(pg_relation_size('mcdata_2011_07')) AS relation_size, 
  pg_size_pretty(pg_total_relation_size('mcdata_2011_07')) AS total_relation_size, 
  pg_size_pretty(pg_indexes_size('mcdata_2011_07')) AS indexes_size;

 relation_size | total_relation_size | indexes_size
---------------+---------------------+--------------
 43 GB         | 100 GB              | 57 GB
(1 row)

Posso ver que, embora boa parte disso seja a chave primária, tenho 11 GB em um índice e 16 em outro:

evlampts=# SELECT
  pg_size_pretty(pg_relation_size('mcdata_2011_07_timestamp_idx')) AS timestamp_idx_size, 
  pg_size_pretty(pg_relation_size('mcdata_2011_07_host_idx')) AS host_idx_size;

 timestamp_idx_size | host_idx_size
--------------------+---------------
 11 GB              | 16 GB
(1 row)

Se eu continuar eliminando esses índices, não vejo nenhuma melhoria dramática no espaço em disco no sistema operacional:

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              1.8T  1.8T   32G  99% /export/home/lando

$ psql evlampts
evlampts=# drop index mcdata_2011_07_host_idx;
DROP INDEX
evlampts=# \q

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              1.8T  1.8T   32G  99% /export/home/lando

Se o espaço fosse liberado para o sistema operacional, esperaria que houvesse outros 16 GB disponíveis. Existe uma VACUUMetapa que estou perdendo? Ajustei o autovacuum para executar com menos frequência e posso ter ido longe demais, mas não vejo nada na documentação dizendo que VACUUMé necessário liberar espaço no índice. O que não estou fazendo que deveria estar fazendo?

Acho isso meio misterioso. O custo de obter o valor máximo apenas da tabela pai da partição (que está vazia) é muito barato:

evlampts=# explain select max(timestamp) from only mcdata;
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Result  (cost=0.15..0.16 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.15 rows=1 width=8)
           ->  Index Scan Backward using mcdatatime_idx on mcdata  (cost=0.00..19.20 rows=130 width=8)
                 Index Cond: ("timestamp" IS NOT NULL)
(5 rows)

Da mesma forma, o custo de obter o valor máximo apenas da partição mais nova é muito barato.

evlampts=# explain select max(timestamp) from mcdata_2011_09;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=3.17..3.18 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..3.17 rows=1 width=8)
           ->  Index Scan Backward using mcdata_2011_09_timestamp_idx on mcdata_2011_09  (cost=0.00..560255403.07 rows=176936192 width=8)
                 Index Cond: ("timestamp" IS NOT NULL)
(5 rows)

Aqui está a coisa estranha. Obter o máximo de toda a tabela particionada é muito caro. O PostgreSQL não parece produzir subplanos eficientes, embora haja apenas duas tabelas aqui (a master e uma partição) e ambas possam ser consultadas separadamente de forma eficiente.

evlampts=# explain select max(timestamp) from mcdata;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=4663394.03..4663394.04 rows=1 width=8)
   ->  Append  (cost=0.00..4221053.22 rows=176936322 width=8)
         ->  Seq Scan on mcdata  (cost=0.00..11.30 rows=130 width=8)
         ->  Seq Scan on mcdata_2011_09 mcdata  (cost=0.00..4221041.92 rows=176936192 width=8)
(4 rows)

Existe uma configuração ajustável que eu perdi ou esta é uma limitação que eu não entendo?

Obrigado!

Um dos meus servidores PostgreSQL hospeda vários (1-3) bancos de dados que recebem um fluxo constante de dados. Os dados não são particularmente estruturados, equivalem ao tempo atual e a uma variedade de dados observados para aquele instante específico. A taxa de dados é bastante alta; ele chega a cerca de um gigabyte por dia para um banco de dados, cerca de um décimo disso para outro. Não espero que essa taxa aumente. O desempenho de leitura é uma prioridade muito menor e atualmente é aceitável.

Nos logs tenho esta mensagem:

LOG:  checkpoints are occurring too frequently (15 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".

Esse valor está atualmente definido como 16, cortesia de pgtune.

Quais são as configurações que devo considerar para melhorar o desempenho de gravação? Eu preferiria manter o máximo de segurança possível. Considerando o volume de dados que chegam, eu poderia aceitar perder alguns dados recentes em uma falha, desde que a maior parte dos dados estivesse intacta.

Edit: Estou usando o PostgreSQL 9.0 por enquanto, mas pretendo atualizar para 9.1. Não estou postando os detalhes de hardware porque embora reconheça sua importância, no final das contas precisarei fazer essa otimização em várias máquinas com hardwares muito diversos. Se o hardware for essencial para a resposta, por favor, forneça as informações gerais para que eu possa aplicar a resposta em máquinas com diferentes configurações de hardware.