Skip site navigation (1) Skip section navigation (2)

Re: Partitioning an existing table

From: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
To: Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>
Cc: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Partitioning an existing table
Date: 2011-04-30 19:46:48
Message-ID: m2vcxvwmhj.fsf@2ndQuadrant.fr (view raw or flat)
Thread:
Lists: pgsql-general
disclaimer : I didn't read the presentation paper Greg Smith talked
about yet, nor his partitioning chapter yet, so it might be about the
same trick.

Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> writes:
> How about doing this with existing massive tables? (Over 120 million rows)
>
> I could create a new parent table with child tables, and then INSERT
> all these millions of rows to put them into the right partition. But
> is that recommended?

If you're partitioning by date, for example, then what I regularly do is
to consider the existing table to be the first partition with data from
origin to now.

Then what I do is to create a new parent table and is children, prepare
the trigger(s), etc.  The switch is then a light transaction which only
renames the current table to say "name_past_201104", have it inherits
the parent table, and finally rename the new parent table to the "name".

Later on you still can rejigger your data around if you wish.  With time
based partitioning it's best to wait until the old partition is not the
target of INSERTs or UPDATEs any more.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

In response to

pgsql-general by date

Next:From: Dimitri FontaineDate: 2011-04-30 19:56:48
Subject: Re: [HACKERS] PostgreSQL Core Team
Previous:From: Dimitri FontaineDate: 2011-04-30 19:35:00
Subject: Re: converting databases form SQL_ASCII to UTF8

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group