How to Migrate an Existing Postgres Table to Partitioned Table as Transparently as Possible

How to migrate an existing Postgres Table to partitioned table as transparently as possible?

In Postgres 10 "Declarative Partitioning" was introduced, which can relieve you of a good deal of work such as generating triggers or rules with huge if/else statements redirecting to the correct table. Postgres can do this automatically now. Let's start with the migration:

1. Rename the old table and create a new partitioned table

   alter table myTable rename to myTable_old;
   
   create table myTable_master(
       forDate date not null,
       key2 int not null,
       value int not null
   ) partition by range (forDate);
   

This should hardly require any explanation. The old table is renamed (after data migration we'll delete it) and we get a master table for our partition which is basically the same as our original table, but without indexes)

2. Create a function that can generate new partitions as we need them:

   create function createPartitionIfNotExists(forDate date) returns void
   as $body$
   declare monthStart date := date_trunc('month', forDate);
       declare monthEndExclusive date := monthStart + interval '1 month';
       -- We infer the name of the table from the date that it should contain
       -- E.g. a date in June 2005 should be int the table mytable_200506:
       declare tableName text := 'mytable_' || to_char(forDate, 'YYYYmm');
   begin
       -- Check if the table we need for the supplied date exists.
       -- If it does not exist...:
       if to_regclass(tableName) is null then
           -- Generate a new table that acts as a partition for mytable:
           execute format('create table %I partition of myTable_master for values from (%L) to (%L)', tableName, monthStart, monthEndExclusive);
           -- Unfortunatelly Postgres forces us to define index for each table individually:
           execute format('create unique index on %I (forDate, key2)', tableName);
       end if;
   end;
   $body$ language plpgsql;
   

This will come in handy later.

3. Create a view that basically just delegates to our master table:

   create or replace view myTable as select * from myTable_master;
   

4. Create rule so that when we insert into the rule, we'll not just update out partitioned table, but also create a new partition if needed:

   create or replace rule autoCall_createPartitionIfNotExists as on insert
       to myTable
       do instead (
           select createPartitionIfNotExists(NEW.forDate);
           insert into myTable_master (forDate, key2, value) values (NEW.forDate, NEW.key2, NEW.value)
       );
   

Of course, if you also need update and delete, you also need a rule for those which should be straight forward.

3. Actually migrate the old table:

   -- Finally copy the data to our new partitioned table
   insert into myTable (forDate, key2, value) select * from myTable_old;
   
   -- And get rid of the old table
   drop table myTable_old;
   

Now migration of the table is complete without that there was any need to know how many partitions are needed and also the view myTable will be absolutely transparent. You can simple insert and select from that table as before, but you might get the performance benefit from partitioning.

Note that the view is only needed, because a partitioned table cannot have row triggers. If you can get along with calling createPartitionIfNotExists manually whenever needed from your code, you do not need the view and all it's rules. In this case you need to add the partitions als manually during migration:

do
$$
declare rec record;
begin
    -- Loop through all months that exist so far...
    for rec in select distinct date_trunc('month', forDate)::date yearmonth from myTable_old loop
        -- ... and create a partition for them
        perform createPartitionIfNotExists(rec.yearmonth);
    end loop;
end
$$;

Postgres: Convert non-partitioned table to partitioned with a bit downtime

That should work, but you can also consider the following:

• create a new partitioned table

• add a partition for the current month

• attach the existing large table as a partition for all past data

• once all data in the existing table have expired, drop it

Is it possible to duplicate a table and transform the new table to a partitioned table

why not create your table first and then insert data into it :

-- step 1 - declare table defintion
create table table_Duplicate (
<  copy table structure from table >

) PARTITION BY RANGE (datecalcul);

-- step 2 - declare partitions 
create table tablename_2021 PARTITION OF table_Duplicate
for values from ('2021-01-01') TO ('2021-12-31');

create table tablename_2020 PARTITION OF table_Duplicate
for values from ('2020-01-01') TO ('2020-12-31');

... 

-- step 3 create indexes 

create index on  tablename_2021 (datecalcul);
create index on  tablename_2020 (datecalcul);
...

-- step 4 insert data 
insert into table_Duplicate
select * from table;

---

Related Topics