|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
3.9.3 Managing Partitions
Normally the set of partitions established when initially defining the table are not intended to remain static. It is common to want to remove old partitions of data and periodically add new partitions for new data. One of the most important advantages of partitioning is precisely that it allows this otherwise painful task to be executed nearly instantaneously by manipulating the partition structure, rather than physically moving large amounts of data around.
The simplest option for removing old data is simply to drop the partition that is no longer necessary:
DROP TABLE measurement_y2003m02;
This can very quickly delete millions of records because it doesn't have to individually delete every record.
Another option that is often preferable is to remove the partition from the partitioned table but retain access to it as a table in its own right:
ALTER TABLE measurement_y2003m02 NO INHERIT measurement;
This allows further operations to be performed on the data before
it is dropped. For example, this is often a useful time to back up
the data using
COPY, pg_dump, or
similar tools. It can also be a useful time to aggregate data
into smaller formats, perform other data manipulations, or run
Similarly we can add a new partition to handle new data. We can create an empty partition in the partitioned table just as the original partitions were created above.
CREATE TABLE measurement_y2006m02 ( CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) ) INHERITS (measurement);
As an alternative, it is sometimes more convenient to create the new table outside the partition structure, and make it a proper partition later. This allows the data to be loaded, checked, and transformed prior to it appearing in the partitioned table.
CREATE TABLE measurement_y2006m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS); ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02 CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ); \copy measurement_y2006m02 from 'measurement_y2006m02' -- possibly some other data preparation work ALTER TABLE measurement_y2006m02 INHERIT measurement;
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|