Como posso obter o agrupamento de linhas sem o bloqueio exclusivo e a sobrecarga de registro do comando `cluster`?

Você pode fazer isso sem usar o clustercomando e ter a tabela bloqueada ou gerar WAL para a tabela inteira. O custo é que você precisa verificar a tabela regularmente.

A ideia básica é:

1. desligue o aspirador automático da mesa
2. verifique cada bloco para determinar o grau de agrupamento
3. exclua e insira novamente todas as linhas de blocos abaixo de um limite de agrupamento
4. aspirar manualmente para liberar esses blocos (completos)
5. repita os passos 2-4 tão regularmente quanto necessário

dados de amostra do esquema de teste inicialmente 'parte-agrupados':

create schema stack;
set search_path=stack;
create type t_tid as (blkno bigint, rowno integer);
create table foo(host_id integer, bar text default repeat('a',400)) with (autovacuum_enabled=false);
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g order by mod(g,10);
insert into foo(host_id) select mod(g,10) from generate_series(1,500000) g;
create index nu_foo on foo(host_id);

estatísticas iniciais de agrupamento:

select cn, count(*)
from ( select count(*) cn
       from (select distinct (ctid::text::t_tid).blkno, host_id from foo) z
       group by blkno ) z
group by cn
order by cn;
/*
 cn | count
----+-------
  1 | 27769  <---- half clustered
  2 |     8
  5 |     1
 10 | 27778  <---- half un-clustered
*/
select count(distinct (ctid::text::t_tid).blkno) from foo where host_id=1;
/*
 count
-------
 30558  <--------- lots of blocks to read for `host_id=1`
*/

análise inicial ( 2146,503 ms ):

explain analyze select count(bar) from foo where host_id=1;
/*
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=15097.30..15097.31 rows=1 width=32) (actual time=2146.157..2146.158 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=95.17..15084.80 rows=5000 width=32) (actual time=21.586..2092.379 rows=100000 loops=1)
         Recheck Cond: (host_id = 1)
         Rows Removed by Index Recheck: 286610
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..93.92 rows=5000 width=0) (actual time=19.232..19.232 rows=100000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 2146.503 ms
*/

exclua e insira novamente as linhas não agrupadas:

with w as ( select blkno
            from (select distinct (ctid::text::t_tid).blkno, host_id from foo) z
            group by blkno
            having count(*)>2 )
   , d as ( delete from foo
            where (ctid::text::t_tid).blkno in (select blkno from w)
            returning * )
insert into foo(host_id,bar) select host_id,bar from d order by host_id;
--
vacuum foo;

novas estatísticas de agrupamento:

select cn, count(*)
from ( select count(*) cn
       from (select distinct (ctid::text::t_tid).blkno, host_id from foo) z
       group by blkno ) z
group by cn
order by cn;
/*
 cn | count
----+-------
  1 | 55541  <---- fully clustered
  2 |    16
*/
select count(distinct (ctid::text::t_tid).blkno) from foo where host_id=1;
/*
 count
-------
  5558  <--------- far fewer blocks to read for `host_id=1`
*/

nova análise ( 48,804 ms ):

explain analyze select count(bar) from foo where host_id=1;
/*
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16110.64..16110.65 rows=1 width=32) (actual time=48.760..48.761 rows=1 loops=1)
   ->  Bitmap Heap Scan on foo  (cost=131.18..16098.14 rows=5000 width=32) (actual time=8.402..32.439 rows=100000 loops=1)
         Recheck Cond: (host_id = 1)
         ->  Bitmap Index Scan on nu_foo  (cost=0.00..129.93 rows=5000 width=0) (actual time=7.636..7.636 rows=100000 loops=1)
               Index Cond: (host_id = 1)
 Total runtime: 48.804 ms
*/

limpar:

drop schema stack cascade;

O acima é viável agora, mas é um pouco peculiar (precisa desligar o auto-vácuo para a mesa) e requer varredura completa regular da mesa. Acho que algo semelhante sem as desvantagens poderia ser incorporado ao postgres. Você precisaria de:

1. Um índice com eficiência de espaço para agrupar (isso está chegando em 9.4 com compactação GIN, ou melhor ainda em 9.5 com o novo tipo de índice BRIN)
2. Um processo 'semelhante a vácuo' que examinaria esse índice para detectar quais blocos precisam ser excluídos/reinserido (o ideal seria reinserir as linhas em novos blocos para que o vácuo automático possa ser deixado como padrão)