Início / dba / Perguntas / 339603
Accepted
Sotosoul
Asked: 2024-05-17 18:58:20 +0800 CST

A maneira mais eficiente de ATUALIZAR um bilhão de linhas no AWS Aurora PostgreSQL?

  • 772

Estou usando uma instância Aurora PostgreSQL v15 gerenciada pela AWS como catálogo para um grande número de objetos S3. A level1_datasettabela possui cerca de 2 bilhões de linhas e seu esquema possui uma metadatacoluna JSONB. Um bug de software antigo fazia com que a string nullfosse gravada na metadatacoluna (em vez de deixá-la vazia) quando nenhum metadado deveria ser gravado. Cerca de um bilhão de linhas contêm a picada nulle quero limpá-la com:

UPDATE public.level1_dataset
SET "metadata" = NULL
WHERE "metadata"::text = 'null';

O banco de dados está hospedado em um sistema db.r6g.2xlargecom 8 núcleos vCPU e 64 GB de memória. Com essa configuração, e deixando todos os ajustes nos padrões, estou obtendo cerca de 42 segundos/milhão de linhas. É possível alterar temporariamente os núcleos da CPU e a memória para esta tarefa de limpeza.

Qual é a maneira mais eficiente de proceder?

ATUALIZAÇÃO: Uma das abordagens de @laurenz-albe é fazer isso em lotes. Foi assim que fiz porque, no meu caso, “id” é UUID, não inteiro. O SELECT tem uma penalidade de 10% no meu caso de uso.

UPDATE public.level1_dataset
SET "metadata" = NULL
WHERE "id" IN (SELECT "id"
    FROM public.level1_dataset
    WHERE "metadata"::text = 'null'
    LIMIT 10000000);
postgresql

1 respostas

  1. Best Answer

    A maneira mais rápida é provavelmente

    CREATE TABLE xy AS
SELECT NULLIF(metadata, 'null') AS metadata, ...
FROM level1_dataset;

DROP TABLE level1_dataset;

ALTER TABLE xy RENAME TO level1_dataset;

    Mas isso exige que você reserve um tempo.

    Fora isso, atualize em lotes e VACUUMentre eles:

    UPDATE public.level1_dataset
SET "metadata" = NULL
WHERE "metadata"::text = 'null'
AND id BETWEEN 1 AND 10000000;

VACUUM public.level1_dataset;

UPDATE public.level1_dataset
SET "metadata" = NULL
WHERE "metadata"::text = 'null'
AND id BETWEEN 10000001 AND 20000000;

VACUUM public.level1_dataset;

...
    • 4

relate perguntas