Início / dba / Perguntas / 344499
Accepted
Purushottam Nawale
Asked: 2025-01-02 15:04:41 +0800 CST

Criando índices e chaves primárias de forma eficiente em uma tabela grande com 70 milhões de registros

  • 772

Estou trabalhando com uma tabela que contém aproximadamente 70 milhões de registros. Preciso criar uma chave primária e vários índices nesta tabela. As consultas SQL que estou usando são as seguintes:

BEGIN;
ALTER TABLE table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY (uniqueid);
CREATE INDEX IF NOT EXISTS table_name_column1_idx ON table_name (column1);
CREATE INDEX IF NOT EXISTS table_name_column2_idx ON table_name (column2);
CREATE INDEX IF NOT EXISTS table_name_column3_idx ON table_name (column3);
CREATE INDEX IF NOT EXISTS table_name_column4_idx ON table_name (column4);
CREATE INDEX IF NOT EXISTS table_name_column5_idx ON table_name (column5);
COMMIT;

No entanto, executar essas consultas leva um tempo considerável. Não tenho nenhuma restrição de recursos, então estou procurando maneiras de otimizar esse processo. Especificamente, estou considerando executar essas consultas SQL em paralelo ou simultaneamente a partir de um aplicativo Python Django.

Minhas perguntas são:

1. Quais estratégias posso usar para minimizar bloqueios e tornar o processo de indexação mais rápido?
2. Existem práticas recomendadas ou ferramentas que podem ajudar a gerenciar a criação de vários índices em uma tabela grande de forma eficiente?
3. Usar BEGIN; COMMIT; durante a criação do índice traz algum benefício de desempenho?

Qualquer conselho ou recomendação será muito apreciado.

postgresql

2 respostas

  1. No caso de CREATE INDEX, é bem simples: não escreva CREATE INDEX, sempre escreva CREATE INDEX CONCURRENTLY. CREATE INDEXé mais rápido e requer menos recursos, mas bloqueia a tabela para escrita, então, em produção, CONCURRENTLY é sempre usado.

    Mas vou anotar separadamente: sempre verifique o código de retorno do comando. Um CREATE INDEX CONCURRENTLY interrompido por qualquer motivo deixará um índice inválido que não pode ser usado em consultas, mas repetido CREATE INDEX IF NOT EXISTSnão completará sua criação.

    No caso de adicionar chave primária, há uma possibilidade de usar CONCURRENTLY, mas é menos óbvio. É necessário dividir o comando em partes.

    ALTER TABLE table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY (uniqueid);

    isso será funcionalmente equivalente sem bloqueios longos:

    create unique index concurrently table_name_new_pkey on table_name(uniqueid);
begin;
set local statement_timeout to '1s';
alter table table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY USING INDEX table_name_new_pkey;
commit;

    Observe que a chave primária requer implicitamente um índice exclusivo e que todos os campos de chave primária sejam declarados NOT NULL. Verifique a definição da tabela antes de migrar. Se necessário e você tiver o postgresql 12 ou mais recente, então NOT NULL sem um long lock pode ser definido dessa forma .

    Usar BEGIN; COMMIT; durante a criação do índice traz algum benefício de desempenho?

    Não, pelo contrário, não permitirá o uso de nenhuma abordagem para minimizar o bloqueio.

    • 4
  2. Best Answer

    A criação de índices normalmente bloqueia todas as gravações na tabela de destino. Para evitar esses bloqueios, você pode usar a CONCURRENTLYcláusula, que permite gravações simultâneas durante a criação do índice. Embora esse método exija mais esforço e leve mais tempo para ser concluído em comparação à abordagem padrão, é a escolha preferida em ambientes de produção.

    CREATE INDEX CONCURRENTLY IF NOT EXISTS table_name_column1_idx ON table_name (column1);

    Para reduzir o tempo gasto na criação do índice, considere aumentar o maintenance_work_meme max_parallel_maintenance_workers. Essa configuração pode ser ajustada no nível do servidor ou no nível da sessão. No entanto, criar vários índices simultaneamente pode sobrecarregar os recursos de E/S e memória do seu sistema, então é melhor criá-los sequencialmente.

    Definir explicitamente uma instrução de criação de índice é desnecessário, pois o CONCURRENTLYmétodo manipula inerentemente múltiplas transações para evitar bloqueios.

    Considere também criar compound indexesou covering indexessubstituir um índice individual se for adequado para as cargas de trabalho de consulta.

    • 1

relate perguntas