Database Reference
In-Depth Information
Partitioning in PostgreSQL
Range partitioning can be done, for example, by record number ranges (such as
record 0 to 100 or 100 to 200) or even using date ranges (such as from 2014-11-01 to
2014-11-30).
List partitioning can be done, for example, by a list of cities (such as New York,
Boston, Chicago, and Houston) or list of departments (such as HR, inance,
administration, and so on). We will use multiple examples so that you are familiar
with both of these types.
There are ive simple steps used to create a partition in PostgreSQL, which are
as follows:
1.
Create the master table.
2.
Create multiple child tables without having an overlapped table constraint.
3.
Create indexes.
4.
Create a trigger function to insert data into child tables.
5.
Enable the constraint exclusion.
Range partition
The range partition is the partition in which we partition a table into ranges deined
by a single column or multiple columns. When deining the ranges, the user will
have to take care that ranges should be connected and not overlapping with each
other; moreover, ranges must be deined using the
<
value operator. For instance,
one can partition by date ranges or ranges of identiiers for speciic business objects.
To illustrate the range partition, we are going to create a table that contains the sales-
related record for the year 2014.
Creating the master table
So, let's start by creating a simple master table. This table will contain data on sales
stored on a daily basis. This will be done in the following manner:
warehouse_db=# CREATE TABLE sales_record
(
id NUMERIC PRIMARY KEY,
sales_amount NUMERIC,
sales_date DATE NOT NULL DEFAULT CURRENT_DATE
);
