I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to suggestions:

  1. The existing table is the master and children inherit from it. Over time move data from master to child, but there will be a period of time where some of the data is in the master table and some in the children.

  2. Create a new master and children tables. Create copy of data in existing table in child tables (so data will reside in two places). Once child tables have most recent data, change all inserts going forward to point to new master table and delete existing table.

  • 1
    Here my ideas: if tables have datetime column -> create new master + new child -> insert new data to NEW + OLD (ex: datetime = 2015-07-06 00:00:00) -> copy from OLD to NEW base on time column (where: datetime < 2015-07-06 00:00:00) -> rename table -> change insert to NEW else -> create "partition trigger" for insert/update on master (insert/update new data -> move to childs, so new data will be inserted to childs) -> update master , trigger will move data to childs.
    – Luan Huynh
    Jul 6, 2015 at 2:06
  • @Innnh, so you are suggesting the second option, but then once the data is copied over, delete the old table and rename the new table to have the same name as the old table. Is that right? Jul 6, 2015 at 2:18
  • 2
    For just few million rows I don't think partitioning is actually necessary. Why do you think you need it? What problem are you trying to solve? Jul 6, 2015 at 6:30
  • 1
    @a_horse_with_no_name I looked again and it is actually closer to 10m rows. I do quite a bit of calls retrieving data over past 30 days and these calls have gotten slower over time. Seems like an ideal situation for a partition Jul 6, 2015 at 12:27
  • 2
    @EvanAppleby DELETE FROM ONLY master_table is the solution.
    – dezso
    Jul 6, 2015 at 13:40

2 Answers 2


Since #1 requires copying data from the master to the child while it is in an active production environment, I personally went with #2 (creating a new master). This prevents disruptions to the original table while it is actively in use and if there are any issues, I can easily delete the new master without issue and continue using the original table. Here are the steps to do it:

  1. Create new master table.

    CREATE TABLE new_master (
        id          serial,
        counter     integer,
  2. Create children that inherit from master.

    CREATE TABLE child_2014 (
        CONSTRAINT pk_2014 PRIMARY KEY (id),
        CONSTRAINT ck_2014 CHECK ( dt_created < DATE '2015-01-01' )
    ) INHERITS (new_master);
    CREATE INDEX idx_2014 ON child_2014 (dt_created);
    CREATE TABLE child_2015 (
        CONSTRAINT pk_2015 PRIMARY KEY (id),
        CONSTRAINT ck_2015 CHECK ( dt_created >= DATE '2015-01-01' AND dt_created < DATE '2016-01-01' )
    ) INHERITS (new_master);
    CREATE INDEX idx_2015 ON child_2015 (dt_created);
  3. Copy all historical data to new master table

    INSERT INTO child_2014 (id,counter,dt_created)
    SELECT id,counter,dt_created
    from old_master
    where dt_created < '01/01/2015'::date;
  4. Temporarily pause new inserts/updates to production database

  5. Copy most recent data to new master table

    INSERT INTO child_2015 (id,counter,dt_created)
    SELECT id,counter,dt_created
    from old_master
    where dt_created >= '01/01/2015'::date AND dt_created < '01/01/2016'::date;
  6. Rename tables so that new_master becomes the production database.

    ALTER TABLE old_master RENAME TO old_master_backup;
    ALTER TABLE new_master RENAME TO old_master;
  7. Add function for INSERT statements to old_master so that data gets passed to correct partition.

        IF ( NEW.dt_created >= DATE '2015-01-01' AND
             NEW.dt_created < DATE '2016-01-01' ) THEN
            INSERT INTO child_2015 VALUES (NEW.*);
        ELSIF ( NEW.dt_created < DATE '2015-01-01' ) THEN
            INSERT INTO child_2014 VALUES (NEW.*);
            RAISE EXCEPTION 'Date out of range';
        END IF;
        RETURN NULL;
    LANGUAGE plpgsql;
  8. Add trigger so that function is called on INSERTS

    CREATE TRIGGER tr_insert BEFORE INSERT ON old_master
  9. Set constraint exclusion to ON

    SET constraint_exclusion = on;
  10. Re-enable UPDATES and INSERTS on production database

  11. Set up trigger or cron so that new partitions get created and function gets updated to assign new data to correct partition. Reference this article for code examples

  12. Delete old_master_backup

  • 1
    Nice writeup. It would be interesting if that actually makes your queries faster. 10 million still isn't that many rows that I would think about partitioning. I wonder if your degrading performance was maybe caused with vacuum not catching up or being prevented due to "idle in transaction" sessions. Jul 7, 2015 at 14:17
  • @a_horse_with_no_name, so far it hasn't made the queries significantly better :( I use Heroku which has auto-vacuum settings and it appears to happen daily for this large table. Will look more into that tho. Jul 7, 2015 at 14:38
  • Shouldn't the inserts in step 3 and 5 be to table new_master and let postgresql choose the right child table/partition?
    – pakman
    Jun 20, 2017 at 17:14
  • @pakman the function to assign the right child doesn't get added until step 7 Jun 20, 2017 at 19:52
  • Hello, (Noob alert) the solution suggested addresses the problem. But here we aren't technically creating any partition here, are we? For partitioned table we can see partitioned by in their schema.
    – Surya
    Jan 11, 2021 at 6:22

There is a new tool called pg_pathman (https://github.com/postgrespro/pg_pathman) that would do this for you automatically.

So something like the following would do it.

SELECT create_range_partitions('master', 'dt_created', 
   '2015-01-01'::date, '1 day'::interval);
  • 4
    NOTE: this project is not under development anymore: pg_pathman ... won't be ported to 13 and later release ... no new development is going to happen here
    – cdalxndr
    Aug 22, 2020 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.