What is the fastest way to insert a large amount of data into a partitioned table in postgresql?

Question

I have a table, natively partitioned by date. The partitions encompass 1 month. I have another very large table (19GB), from which I want to copy data into the partitioned table. I have used pg_partman for this process, however the partman.partition_data_proc procedure took 12 hours to move 9GB of data, into 60 new partitions. For reference, I am using Postgres 15 on Amazon RDS (M5 Large).

I have attempted to use partman.partition_data_proc to move the data. Take the following queries, for a more concrete case:

-- 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'
);

I have also attempted to move the data with DBeaver export/import data functionality (it was both slower and inserted data into the default partition). Is there a faster way to do this ? I would like to be able to transfer the data in less than 8 hours and, not have to bump up the RDS instance to something more expensive.

Answer 1

Fixed test data generation:

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

Create partitioned table:

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);

Create smaller test set to toy with:

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

Trying partman, with p_wait=0 otherwise it will sleep after moving a bunch of rows, which takes a while:

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

I notice it's pretty slow (about 40k rows/s) and it moves the rows from table1small into the partitioned table. Moving rows is slow, because source table rows need to be deleted.

I've never used pgpartman before, so maybe there is a setting to make it copy the rows instead of moving them. This would be much faster.

For example:

• Using pgpartman to move the rows from table1small to the partitioned table took 140 seconds, about 71k rows/s.

• INSERT INTO partitioned_table SELECT * FROM table1small, it only took 12 seconds, about 833k rows/s.

• INSERT INTO dummy_non_partitioned_table SELECT * FROM table1small, took 3 seconds, about 3.3M rows/s.

So if you want to insert the whole contents of the old table into a new partitioned table quickly, it would be much faster to:

• Create the new partitioned table as UNLOGGED: if the server crashes during the operation, you can always redo it

• Create all the partitions "manually" (with a script)

• INSERT INTO partitioned_table SELECT * FROM table1

• Once satisfied with the result, set the new table to LOGGED so it becomes crash-proof, and drop or truncate the old table.

This is still not going to squeeze all the juice from your box: I see it is only using 1 core, and writing at less than 100 MB/s. It looks like inserting into the partitioned table is slower than inserting into a non-partitioned table, most likely due to spending a lot of time checking all the range contraints to figure out into which partition the row should go.

If this quick speed test holds, 300M rows at 833M rows/s, it should complete in about 6 minutes.

There's probably a way to speed it up with synchronized seq scans, by doing one query per partition with a script, all in parallel. INSERT INTO partition SELECT * FROM table1 WHERE date >= 'start of partition' AND date < 'end of partition' or something like that.

That would require one process per partition, and there are a lot of them. So maybe split it into two steps, split once by year to get one temp table per year, then split each by month into final partitions. It should be able to consume all the available CPU in the box, which is either a feature or a problem depending on circumstances...