Início / dba / Perguntas / 174659
Accepted
brauliobo
Asked: 2017-05-27 03:06:21 +0800 CST

Alternativa ao CLUSTER sem bloqueio de mesa

  • 772

Estou enfrentando degradação de desempenho e uso crescente de armazenamento como resultado de registros novos e atualizados frequentes, causando fragmentação de índice e armazenamento.

VÁCUO não ajuda muito.

Infelizmente CLUSTER não é uma opção, pois causa tempo de inatividade e pg_repack não está disponível para AWS RDS.

Estou procurando alternativas hacky para CLUSTER. Um que parece funcionar bem nos meus testes locais é:

begin;

create temp table tmp_target as select * from target;

delete from target;
insert into target select * from tmp_target order by field1 asc, field2 desc;

drop table tmp_target;

commit;

A ordenação dos ctidlooks está correta com:

select ctid, field1, field2 from target order by ctid;

A questão é: isso parece bom? Vai bloquear a targettabela para SELECTconsultas que causam tempo de inatividade nos aplicativos? Existe uma maneira de listar os bloqueios envolvidos na transação?

Relacionado a consultas somente leitura de bloqueio de comando de cluster na réplica

postgresql locking

2 respostas

  1. Best Answer

    Se é apenas sobre o inchaço da mesa, VACUUM FULLé a ferramenta para fazê-lo. ( VACUUMsó reduz o arquivo físico se ele puder truncá-lo oportunamente no final.) No entanto, VACUUM FULLtambém recebe um bloqueio exclusivo na tabela, assim como CLUSTER.

    Para fazer o mesmo sem bloqueio exclusivo pg_repack é a ferramenta que você procura. É lamentável que a Amazon não pareça permitir isso.

    As soluções manuais introduzem todos os tipos de curvas e condições de corrida. Você precisaria especificar sua tabela e possíveis padrões de acesso exatamente antes de podermos discutir a melhor solução.

    Sua solução atual não é boa de qualquer maneira. Você faz a operação de classificação após excluir todas as linhas (bloqueando-as), o que não diminuirá o tempo de inatividade (a duração do bloqueio) para SELECTconsultas.

    Se você não tiver acesso de gravação simultâneo e nenhum objeto dependente , isso pode ser melhor:

    BEGIN;

CREATE TEMP TABLE tmp_target AS TABLE target ORDER BY field1, field2 DESC;

TRUNCATE target;
INSERT INTO target TABLE tmp_target;

-- DROP TABLE tmp_target;  -- optional; dropped at end of session automatically

COMMIT;

    1. Classifique em segundo plano antes de pegar o cadeado.
      Estritamente falando, INSERTsem ORDER BYé livre para escrever linhas em qualquer ordem física. Praticamente, porém, ele copiará a ordem física atual da tabela temporária com um simples SELECT * FROM(ou TABLEabreviado).

    2. Use TRUNCATEem vez de DELETE, que é mais rápido para tabelas grandes. Esteja ciente de algumas implicações como TRUNCATEnão funciona com restrições FK. Leia o manual para cobertura completa.

    Você pode querer descartar os índices existentes logo antes TRUNCATEe recriar depois INSERTpara tornar isso mais rápido.

    Relacionado:

    • 5

  2. O pg_repack já está disponível no RDS.

    • 2

relate perguntas