Início / dba / Perguntas / 339738
Accepted
Halcyon Lykan
Asked: 2024-05-22 19:51:32 +0800 CST

Qual é a maneira mais rápida de inserir uma grande quantidade de dados em uma tabela particionada no postgresql?

  • 772

Eu tenho uma tabela particionada nativamente por data. As partições abrangem 1 mês. Tenho outra tabela muito grande (19 GB) da qual desejo copiar os dados para a tabela particionada. Eu usei pg_partmanesse processo, porém o partman.partition_data_procprocedimento levou 12 horas para mover 9 GB de dados para 60 novas partições. Para referência, estou usando o Postgres 15 no Amazon RDS (M5 Large).

Eu tentei usar partman.partition_data_procpara mover os dados. Faça as seguintes perguntas, para um caso mais concreto:

-- NOTE: Both tables have more columns, this is a minimal example
CREATE TABLE IF NOT EXISTS table1(
    id bigint not null,
    date timestamp not null,
    col_a integer,
    col_b double precision,
    col_c varchar(255)
);

-- insert some data into "table" at this step
-- for example using something like this: 
-- insert into table (
--      "id",
--  "date",
--  "col_a" ,
--  "col_b",
--  "col_c"
-- )
-- select
--  i,
--  get_random_date_between(start:='10 years', end:='1 day'),
--  random()::int,
--  (random()* 100)::numeric(10, 2),
--  'Some Text'
-- from
--  generate_series(1,300000000) s(i);

CREATE TABLE IF NOT EXISTS partitioned_table(
    id bigint not null,
    date timestamp not null,
    col_a integer,
    col_b double precision,
    col_c varchar(255)
) PARTITION BY RANGE (date);

-- NOTE: you will need to have pg_partman extension installed
-- https://github.com/pgpartman/pg_partman
SELECT partman.create_parent(
        p_parent_table => 'public.partitioned_table',
        p_control => 'date',
        p_interval => '1 month'
    );

          
-- This operation takes a very long time
call partman.partition_data_proc(
    p_parent_table := 'public.partitioned_table',
    p_interval := '1 month',
    p_source_table := 'public.table1'
);

Também tentei mover os dados com a funcionalidade de exportação/importação de dados do DBeaver (era mais lento e inseria dados na partição padrão). Existe uma maneira mais rápida de fazer isso? Gostaria de poder transferir os dados em menos de 8 horas e não precisar trocar a instância RDS por algo mais caro.

postgresql

1 respostas

  1. Best Answer

    Geração de dados de teste fixa:

    CREATE UNLOGGED TABLE IF NOT EXISTS table1(
    id bigint not null,
    date timestamp not null,
    col_a integer,
    col_b double precision,
    col_c varchar(255)
);

-- insert some data into "table" at this step
-- for example using something like this: 
insert into table1 ("id","date","col_a","col_b","col_c")
select i,
 '2000-01-01'::DATE + ('1 DAY'::INTERVAL*(random()*7200)),
 (random()*65536)::int,
 (random()* 100)::numeric(10, 2),
 'Some Text'
from generate_series(1,300000000) s(i);

INSERT 0 300000000
Time: 517982,474 ms (08:37,982)

select pg_relation_size('table1')/1e9;
 22.9682298880000000

    Crie uma tabela particionada:

    CREATE TABLE IF NOT EXISTS partitioned_table(
    id bigint not null,
    date timestamp not null,
    col_a integer,
    col_b double precision,
    col_c varchar(255)
) PARTITION BY RANGE (date);

    Crie um conjunto de testes menor para brincar com:

    CREATE UNLOGGED TABLE IF NOT EXISTS table1small AS SELECT * FROM table1 LIMIT 10000000;

    Tentando partman, com p_wait=0 caso contrário ele irá dormir depois de mover um monte de linhas, o que demora um pouco:

    call partman.partition_data_proc(
    p_parent_table := 'public.partitioned_table',
    p_interval := '1 month',
    p_source_table := 'public.table1small',
    p_wait := 0
);

    Percebo que é muito lento (cerca de 40 mil linhas/s) e move as linhas da tabela1small para a tabela particionada. A movimentação de linhas é lenta porque as linhas da tabela de origem precisam ser excluídas.

    Eu nunca usei o pgpartman antes, então talvez haja uma configuração para copiar as linhas em vez de movê-las. Isso seria muito mais rápido.

    Por exemplo:

    • Usar o pgpartman para mover as linhas de table1small para a tabela particionada levou 140 segundos, cerca de 71 mil linhas/s.

    • INSERT INTO partitioned_table SELECT * FROM table1small, levou apenas 12 segundos, cerca de 833 mil linhas/s.

    • INSERT INTO dummy_non_partitioned_table SELECT * FROM table1small, levou 3 segundos, cerca de 3,3 milhões de linhas/s.

    Portanto, se você quiser inserir rapidamente todo o conteúdo da tabela antiga em uma nova tabela particionada, seria muito mais rápido:

    • Crie a nova tabela particionada como UNLOGGED: se o servidor travar durante a operação, você sempre poderá refazê-la

    • Crie todas as partições “manualmente” (com um script)

    • INSERT INTO partitioned_table SELECT * FROM table1

    • Quando estiver satisfeito com o resultado, defina a nova tabela como LOGGED para que ela se torne à prova de falhas e elimine ou trunque a tabela antiga.

    Isso ainda não vai espremer todo o suco da sua caixa: vejo que ele está usando apenas 1 núcleo e gravando a menos de 100 MB/s. Parece que a inserção na tabela particionada é mais lenta do que a inserção em uma tabela não particionada, provavelmente devido ao gasto de muito tempo verificando todas as restrições de intervalo para descobrir em qual partição a linha deve ir.

    Se este teste rápido de velocidade for mantido, 300 milhões de linhas a 833 milhões de linhas/s, ele deverá ser concluído em cerca de 6 minutos.

    Provavelmente existe uma maneira de acelerar isso com varreduras sequenciais sincronizadas, fazendo uma consulta por partição com um script, tudo em paralelo. INSERT INTO partition SELECT * FROM table1 WHERE date >= 'start of partition' AND date < 'end of partition'ou algo assim.

    Isso exigiria um processo por partição, e há muitos deles. Então, talvez divida-o em duas etapas, divida uma vez por ano para obter uma tabela temporária por ano e, em seguida, divida cada uma por mês em partições finais. Deve ser capaz de consumir toda a CPU disponível na caixa, o que é um recurso ou um problema dependendo das circunstâncias...

    • 0

relate perguntas