Início / dba / Perguntas / 334547
Accepted
KaliTheGreat
Asked: 2024-01-04 19:17:52 +0800 CST

Migração de banco de dados - Convertendo restrição exclusiva em chave primária

  • 772

Eu tenho uma tabela que possui índice exclusivo:

CREATE UNIQUE INDEX IF NOT EXISTS tb_sha256_key_idx ON blob_props (sha256, key);

Quero alterá-lo para chave primária durante a migração do banco de dados e há duas maneiras:

  1. ALTER TABLE tb ADD CONSTRAINT tb_pkey PRIMARY KEY USING INDEX tb_sha256_key_idx;

  2. DROP INDEX tb_sha256_key_idxe entãoALTER TABLE tb ADD PRIMARY KEY (sha256, key);

sha256e keynão são colunas nulas (portanto, deve funcionar bem).

Tenho algumas preocupações em relação às duas abordagens e estou debatendo qual é a abordagem correta durante uma migração de banco de dados:

A segunda abordagem é melhor porque, ao descartar/criar, melhoraremos drasticamente o fator de agrupamento do índice e seu tamanho devido à fragmentação. É preferível do ponto de vista do desempenho. Mas criar um índice em uma tabela grande causaria arquivos temporários grandes (é por isso que usamos REINDEX na maioria das vezes e criamos novos índices apenas como último recurso).

Deseja aprofundar a discussão e obter algumas reivindicações e/ou mais opiniões sobre esse cenário.

postgresql

1 respostas

  1. Best Answer

    Nos sistemas de produção, o tempo de inatividade é mais importante. Na maioria dos casos, você não pode bloquear completamente (para inserir/atualizar/excluir e até mesmo selecionar) uma tabela grande por várias horas enquanto um novo btree é construído. Portanto, a alteração da chave primária é realizada apenas por meio de um índice exclusivo criado anteriormente.

    -- make sure all columns in the new primary key are NOT NULL

CREATE UNIQUE INDEX CONCURRENTLY :new_primary_key_name ON :relname(:new_colname);

begin;
SET LOCAL statement_timeout TO '1s';
ALTER TABLE :relname DROP CONSTRAINT :primary_key_name;
ALTER TABLE :relname ADD CONSTRAINT :primary_key_name PRIMARY KEY USING INDEX :new_primary_key_name;
commit;

    Nesse caso, ao construir um novo btree, não bloqueamos consultas normais de seleção/inserção/atualização/exclusão.

    Para mesas pequenas ou quando você não se importa com o tempo de execução - não importa. Sinta-se à vontade para comandar DROP CONSTRAINT + ADD PRIMARY KEY para simplificar.

    sha256 e key são colunas anuláveis

    Todas as partes da chave primária não devem ser nulas. ADD PRIMARY KEYtentarei adicionar not nullimplicitamente. A migração retornará um erro se isso não for verdade para alguma tupla.

    Mas criar um índice em uma tabela grande causaria arquivos temporários grandes (é por isso que usamos REINDEX na maioria das vezes e criamos novos índices apenas como último recurso).

    Isso não faz nenhum sentido. O comando reindex usa o mesmo código de construção de índice que o comando create index. Efetivamente, o índice de reindexação é criar índice + eliminar índice em um comando.

    • 3

relate perguntas