Início / dba / Perguntas / 331424
Accepted
sujeet
Asked: 2023-09-21 12:34:23 +0800 CST

Adicionando uma chave primária a uma grande tabela PostgreSQL com alto tráfego

  • 772

Preciso adicionar uma chave primária a uma tabela grande do PostgreSQL (aproximadamente 2 TB) com alto tráfego. Esta é uma operação crítica e estou procurando orientação sobre como fazê-la com eficiência.

Eu já tentei os passos abaixo:

-- Step 1: Add id identity column 
ALTER TABLE users
ADD COLUMN id BIGINT GENERATED ALWAYS as IDENTITY;

-- Step 2: Add unique index on (id, user_id) concurrently
CREATE UNIQUE INDEX CONCURRENTLY users_id_user_id_idx
   ON users (id, user_id);

-- verify that step 2 is completed
-- Step 3: Add primary key
ALTER TABLE users
   ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_id_user_id_idx;

Estou enfrentando dois problemas:

  • A tabela está completamente bloqueada na própria "Etapa 1".

    Eu sei que isso é esperado, mas se houver alguma opção para evitar isso, sugira.

  • Eu recebo o erro abaixo,

ERRO: não foi possível estender o arquivo "base/16401/90996": Não há espaço restante no dispositivo DICA: Verifique o espaço livre em disco.

Mas ainda tenho espaço 600GBde armazenamento no meu servidor.

Como a tabela ficará bloqueada na "Etapa 1", e se não houver opção para evitar isso, eu poderia aproveitar o tempo de inatividade e adicionar a idcoluna primeiro e depois executar os outros dois scripts.

Não sei se isso resolveria o erro de armazenamento.

Forneça sugestões para que eu possa adicionar o PK com o menor tempo de inatividade possível.

PostgreSQL v14.6

postgresql

1 respostas

  1. Best Answer

    Por que?

    Sua etapa 1 precisaria de muito mais de 600 GB (temporariamente). A mesa tem cerca de 2 TB. Quase a mesma quantidade (menos o possível inchaço, mais 8 bytes por linha para a nova bigintcoluna) deve estar disponível pelo menos, porque essa mudança força o Postgres a reescrever a tabela inteira.

    Minimize o bloqueio E minimize a necessidade total de armazenamento

    Em vez disso, faça nesta ordem :

    violino

    Adicione uma coluna anulável idsem valor padrão, assim será nullinicialmente.

    ALTER TABLE users ADD COLUMN id bigint;

    Dessa forma, o Postgres pode se contentar com pequenas alterações nos metadados. Sem reescrita de tabela, sem bloqueio.
    Eu nomearia a coluna PK como "user_id", e não sou fã do nome "id", amplamente difundido, não descritivo e altamente duplicado. Mas mantendo o "id" para ficar alinhado com a questão.

    Crie um SEQUENCEmanualmente:

    CREATE SEQUENCE users_id_seq;

    Faça a coluna "possuir" a sequência:

    ALTER SEQUENCE users_id_seq OWNED BY users.id;

    Adicione a coluna padrão, que só entra em ação para novas linhas.

    ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('users_id_seq');

    Ver:

    Atualize linhas pré-existentes (ainda com nullvalores) em lotes de 1% do tamanho total (ou qualquer outro). Em transações separadas , para permitir que o autovacuum entre em ação e marque linhas mortas para reutilização. Dessa forma, a mesa não crescerá muito e 600 GB são espaço de manobra suficiente.

    Desde a adição de procedimentos SQL no Postgres 12, podemos COMMITcriar um bloco de código anônimo. Supondo que uma timestamptzcoluna users.inserted_at(de preferência com um índice!) algo assim funcionaria:

    DO
$do$
DECLARE
   _ts timestamptz := (SELECT COALESCE(min(inserted_at), now()) FROM users);  -- must not be NULL
   _step interval  := '7 days';  -- adjust to your data !!!
BEGIN
   LOOP
      RAISE NOTICE 'Updating rows starting from %', _ts;  -- optional
      
      UPDATE users
      SET    id = nextval('users_id_seq')
      WHERE  inserted_at >= _ts
      AND    inserted_at <  _ts + _step
      AND    id IS NULL;  -- optional

      EXIT WHEN NOT FOUND AND _ts >= now();  -- adjust to your case !!!

      COMMIT;  -- Requires Postgres 11+ !!!
      PERFORM pg_sleep(10);  -- adapt to your setup: long enough so let autovacuum kick in
      _ts := _ts + _step;
   END LOOP;
END
$do$;

    Como alternativa , faça um loop em seu cliente e execute VACUUM users;entre as iterações para garantir que o espaço seja reutilizado. ( VACUUMnão pode ser executado dentro de uma transação.)

    Ver:

    Eventualmente, todas as linhas antigas são atualizadas.

    Agora crie o índice exclusivo CONCURRENTLY, para evitar o bloqueio de inserções. Como sua etapa 2, mas apenas em (id):

    CREATE UNIQUE INDEX CONCURRENTLY users_id_idx ON users (id);

    Não vejo um bom motivo para adicionar user_idao PK. Se você precisar dele para varreduras somente de índice, considere um índice de cobertura com extensão INCLUDE (user_id). Mas isso nem sempre é benéfico em geral. Ver:

    Agora use o índice exclusivo para adicionar o novo PK sem bloquear inserções (etapa 3):

    ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_id_idx;

    Isso também definirá implicitamente a coluna NOT NULL.

    Finalmente, use a função de Peter Eisentraut upgrade_serial_to_identity(tbl regclass, col name) para converter serialem uma IDENTITYcoluna. Como superusuário :

    SELECT upgrade_serial_to_identity('users'::regclass, 'id')

    Ou fique com o serialPK, pode ser bom o suficiente.

    Relacionado:

    • 11

relate perguntas