A inserção em ordem tem o mesmo efeito que o agrupamento?

As linhas serão processadas na ordem garantida, mas isso não significa que após serem inseridas elas ficarão localizadas uma ao lado da outra. Isso só será possível se os registros nunca forem excluídos ou atualizados em sua tabela. Depois de atualizar ou excluir algumas linhas, após a limpeza, você provavelmente terá espaço livre no meio da tabela, para onde irão os próximos registros inseridos.

Alguns detalhes extras nesta questão .

O Postgres escreve fisicamente as tuplas sequencialmente como INSERTed. Se você fizer isso em uma nova tabela ou em uma tabela sem tuplas mortas, obterá exatamente o mesmo resultado que obteria CLUSTERem um índice com a mesma ordem de classificação que seu arquivo INSERT. O efeito de CLUSTERse deteriora com gravações posteriores na tabela da mesma maneira (e permanece intacto enquanto você nunca DELETEou UPDATE- ou INSERTquebra a ordem desejada).

Algumas respostas se concentram nesses efeitos de gravações posteriores e perdem o ponto da pergunta. A resposta para sua pergunta é basicamente:

SIM , inserir em ordem tem o mesmo efeito que agrupar.

com base em algumas condições:

as consultas nesta tabela serão filtradas por uma coluna de ID de chave estrangeira.

Isso significa que você acessa a linha com o mesmo ID de uma só vez, não um intervalo de IDs sequenciais. Então tudo que você precisa é de tuplas agrupadas por ID, a ordem física entre os IDs não tem sentido e é irrelevante.

E:

gerando esses dados em pedaços relacionados à chave estrangeira

Ou seja, "pedaços" incluem todas as linhas para o mesmo ID em sequência. Não há outras linhas para o mesmo ID inserido mais cedo ou mais tarde. Então algo como:

INSERT INTO big_table (source_table1_id,a,b,c)
SELECT s1.source_table1_id, ... 
FROM   source_table1 s1
...
WHERE s1.source_table1_id BETWEEN 123 and 125 -- example
ORDER BY s1.source_table1_id

E:

Eu nunca vou precisar UPDATEdele em uso normal; partes podem ser deletadas e regeneradas.

A parte sobre DELETEé a única parte levemente problemática. Se você nunca excluísse também, estaria feito aqui. Se por "porções" você quer dizer todas as linhas com disse IDde uma vez, você ainda é bom, principalmente . Ao excluir e inserir na mesma transação, não há fragmentação nos IDs. (As tuplas excluídas ainda não estão "mortas" e não são substituídas na mesma transação.)

Tuplas mortas começam a inchar a tabela e inserções posteriores podem começar a preencher buracos físicos, que é onde a fragmentação pode começar. O inchaço com tuplas mortas tem vários efeitos ruins acumulados, mas o acesso ao índice em todas as linhas para um determinado ID não é afetado.

Mas tudo isso é ortogonal à sua pergunta, pois as mesmas considerações se aplicam a CLUSTER.

Você já considerou pg_repack , que pode fazer o mesmo que CLUSTER, só que sem bloqueio exclusivo na tabela. Eles acabaram de adicionar o Postgres 9.6 à lista de versões suportadas esta semana.

Relacionado:

• Configurando o PostgreSQL para desempenho de leitura
• Otimizando consultas em um intervalo de carimbos de data/hora (duas colunas)

Não, não.

• O clustering é feito sobre um índice.
• Sem um ORDER BY, o pedido de inserção pode ser totalmente aleatório
• Com um ORDER BYan INSERTainda pode deixar lacunas porque não reescreve a tabela.
• Cluster força uma reescrita de tabela.

Considere isto..

CREATE TABLE foo AS
SELECT random() AS bar, random() AS quz
FROM generate_series(1,100) AS t(x);

Por implementação, isso insere as linhas na ordem em que foram geradas. Não é garantido, mas atualmente tem. O SQL não garante nada da ordem de retorno sem um ORDER BY. No entanto, os dados não professam nenhuma ordem útil quando gerados dessa forma, portanto, isso é meramente trivial e não significativo para o desempenho da consulta. Agora podemos fazer isso..

CREATE INDEX foo_bar_idx ON foo(bar);
CLUSTER foo USING foo_bar_idx;

Agora as linhas fooestão ordenadas por bar, isso pode tornar certas operações mais rápidas que usam foo_bar_idx.

O que acontece se essas linhas já estiverem nessa ordem. O que acontece se o índice se alinhar com a linha e o clustering não reordenar nada? Então nada acontece. Mas esse não é um caso de uso típico, mesmo sem INSERTe DELETE. No PostGIS, inserimos dados o tempo todo e agrupamos tabelas complexas de geometrias por sua caixa delimitadora . As comparações de caixa delimitadora são abstratas, mas tornam as coisas que as usam substancialmente mais rápidas.

Se a tabela nunca for atualizada ou excluída, as linhas inseridas serão ordenadas fisicamente em sua ordem de inserção cronológica. Mas se ele for excluído ou atualizado, a limpeza da tabela criará buracos de espaço livre na tabela, e as linhas recém-inseridas podem ser espalhadas onde quer que se encaixem nesses buracos. Isso seria um problema menor se as exclusões ocorrerem em grandes conjuntos de dados especificados por um intervalo na mesma coluna pela qual você deseja classificar. Nesse caso, páginas inteiras de dados serão excluídas juntas, liberando esse espaço para serem reutilizados em conjunto.

É improvável que seu INSERT INTO...SELECT...ORDER BY seja eficaz, porque a ordenação ocorrerá apenas em partes. A menos que seus pedaços sejam muito grandes, ou os próprios pedaços sejam processados ​​em ordem, bem como ordenados dentro de cada pedaço, é improvável que a ordenação de pedaços seja muito boa.

Você pode olhar para o intervalo de particionamento de sua tabela na chave de classificação. Isso pode resolver o problema apenas mantendo valores semelhantes juntos. Caso contrário, pelo menos faria com que o CLUSTER de cada partição separada levasse muito menos tempo do que o CLUSTER de uma tabela gigante, o que pode tornar mais fácil agendá-los.