Partitioning refers to splitting what is logically one large
table into smaller physical pieces. Partitioning can provide
several benefits:

Query performance can be improved dramatically in
certain situations, particularly when most of the heavily
accessed rows of the table are in a single partition or a
small number of partitions. The partitioning substitutes
for leading columns of indexes, reducing index size and
making it more likely that the heavily-used parts of the
indexes fit in memory.

When queries or updates access a large percentage of a
single partition, performance can be improved by taking
advantage of sequential scan of that partition instead of
using an index and random access reads scattered across the
whole table.

Bulk loads and deletes can be accomplished by adding or
removing partitions, if that requirement is planned into
the partitioning design. ALTER
TABLE is far faster than a bulk operation. It also
entirely avoids the VACUUM
overhead caused by a bulk DELETE.

Seldom-used data can be migrated to cheaper and slower
storage media.

The benefits will normally be worthwhile only when a table
would otherwise be very large. The exact point at which a table
will benefit from partitioning depends on the application,
although a rule of thumb is that the size of the table should
exceed the physical memory of the database server.

Currently, PostgreSQL
supports partitioning via table inheritance. Each partition
must be created as a child table of a single parent table. The
parent table itself is normally empty; it exists just to
represent the entire data set. You should be familiar with
inheritance (see Section 5.8)
before attempting to set up partitioning.

The following forms of partitioning can be implemented in
PostgreSQL:

Range Partitioning

The table is partitioned into "ranges" defined by a key column or set of
columns, with no overlap between the ranges of values
assigned to different partitions. For example one might
partition by date ranges, or by ranges of identifiers for
particular business objects.

List Partitioning

The table is partitioned by explicitly listing which
key values appear in each partition.

Create the "master" table,
from which all of the partitions will inherit.

This table will contain no data. Do not define any check
constraints on this table, unless you intend them to be
applied equally to all partitions. There is no point in
defining any indexes or unique constraints on it,
either.

Create several "child" tables
that each inherit from the master table. Normally, these
tables will not add any columns to the set inherited from
the master.

We will refer to the child tables as partitions, though
they are in every way normal PostgreSQL tables.

Add table constraints to the partition tables to define
the allowed key values in each partition.

This is wrong since it is not clear which partition the
key value 200 belongs in.

Note that there is no difference in syntax between range
and list partitioning; those terms are descriptive
only.

For each partition, create an index on the key
column(s), as well as any other indexes you might want.
(The key index is not strictly necessary, but in most
scenarios it is helpful. If you intend the key values to be
unique then you should always create a unique or
primary-key constraint for each partition.)

Optionally, define a trigger or rule to redirect data
inserted into the master table to the appropriate
partition.

Ensure that the constraint_exclusion
configuration parameter is not disabled in postgresql.conf. If it is, queries will not
be optimized as desired.

For example, suppose we are constructing a database for a
large ice cream company. The company measures peak temperatures
every day as well as ice cream sales in each region.
Conceptually, we want a table like:

We know that most queries will access just the last week's,
month's or quarter's data, since the main use of this table
will be to prepare online reports for management. To reduce the
amount of old data that needs to be stored, we decide to only
keep the most recent 3 years worth of data. At the beginning of
each month we will remove the oldest month's data.

In this situation we can use partitioning to help us meet
all of our different requirements for the measurements table.
Following the steps outlined above, partitioning can be set up
as follows:

We want our application to be able to say INSERT INTO measurement ... and have the
data be redirected into the appropriate partition table. We
can arrange that by attaching a suitable trigger function
to the master table. If data will be added only to the
latest partition, we can use a very simple trigger
function:

The trigger definition is the same as before. Note that
each IF test must exactly match
the CHECK constraint for its
partition.

While this function is more complex than the
single-month case, it doesn't need to be updated as often,
since branches can be added in advance of being needed.

Note: In practice it might be best to check
the newest partition first, if most inserts go into
that partition. For simplicity we have shown the
trigger's tests in the same order as in other parts of
this example.

As we can see, a complex partitioning scheme could require a
substantial amount of DDL. In the above example we would be
creating a new partition each month, so it might be wise to
write a script that generates the required DDL
automatically.

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_y2006m02;

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_y2006m02 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
might also be a useful time to aggregate data into smaller
formats, perform other data manipulations, or run reports.

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:

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:

Without constraint exclusion, the above query would scan
each of the partitions of the measurement table. With constraint exclusion
enabled, the planner will examine the constraints of each
partition and try to prove that the partition need not be
scanned because it could not contain any rows meeting the
query's WHERE clause. When the planner
can prove this, it excludes the partition from the query
plan.

You can use the EXPLAIN command to
show the difference between a plan with constraint_exclusion on and a plan with it off.
A typical unoptimized plan for this type of table setup is:

Some or all of the partitions might use index scans instead
of full-table sequential scans, but the point here is that
there is no need to scan the older partitions at all to answer
this query. When we enable constraint exclusion, we get a
significantly cheaper plan that will deliver the same
answer:

Note that constraint exclusion is driven only by CHECK constraints, not by the presence of
indexes. Therefore it isn't necessary to define indexes on the
key columns. Whether an index needs to be created for a given
partition depends on whether you expect that queries that scan
the partition will generally scan a large part of the partition
or just a small part. An index will be helpful in the latter
case but not the former.

The default (and recommended) setting of constraint_exclusion
is actually neither on nor off, but an intermediate setting called
partition, which causes the technique
to be applied only to queries that are likely to be working on
partitioned tables. The on setting
causes the planner to examine CHECK
constraints in all queries, even simple ones that are unlikely
to benefit.

A rule has significantly more overhead than a trigger, but
the overhead is paid once per query rather than once per row,
so this method might be advantageous for bulk-insert
situations. In most cases, however, the trigger method will
offer better performance.

Be aware that COPY ignores rules.
If you want to use COPY to insert
data, you'll need to copy into the correct partition table
rather than into the master. COPY does
fire triggers, so you can use it normally if you use the
trigger approach.

Another disadvantage of the rule approach is that there is
no simple way to force an error if the set of rules doesn't
cover the insertion date; the data will silently go into the
master table instead.

Partitioning can also be arranged using a UNION ALL view, instead of table inheritance.
For example,

CREATE VIEW measurement AS
SELECT * FROM measurement_y2006m02
UNION ALL SELECT * FROM measurement_y2006m03
...
UNION ALL SELECT * FROM measurement_y2007m11
UNION ALL SELECT * FROM measurement_y2007m12
UNION ALL SELECT * FROM measurement_y2008m01;

However, the need to recreate the view adds an extra step to
adding and dropping individual partitions of the data set. In
practice this method has little to recommend it compared to
using inheritance.

There is no automatic way to verify that all of the
CHECK constraints are mutually
exclusive. It is safer to create code that generates
partitions and creates and/or modifies associated objects
than to write each by hand.

The schemes shown here assume that the partition key
column(s) of a row never change, or at least do not change
enough to require it to move to another partition. An
UPDATE that attempts to do that
will fail because of the CHECK
constraints. If you need to handle such cases, you can put
suitable update triggers on the partition tables, but it
makes management of the structure much more
complicated.

If you are using manual VACUUM
or ANALYZE commands, don't forget
that you need to run them on each partition individually. A
command like:

ANALYZE measurement;

will only process the master table.

The following caveats apply to constraint exclusion:

Constraint exclusion only works when the query's
WHERE clause contains constants. A
parameterized query will not be optimized, since the
planner cannot know which partitions the parameter value
might select at run time. For the same reason, "stable" functions such as CURRENT_DATE must be avoided.

Keep the partitioning constraints simple, else the
planner may not be able to prove that partitions don't need
to be visited. Use simple equality conditions for list
partitioning, or simple range tests for range partitioning,
as illustrated in the preceding examples. A good rule of
thumb is that partitioning constraints should contain only
comparisons of the partitioning column(s) to constants
using B-tree-indexable operators.

All constraints on all partitions of the master table
are examined during constraint exclusion, so large numbers
of partitions are likely to increase query planning time
considerably. Partitioning using these techniques will work
well with up to perhaps a hundred partitions; don't try to
use many thousands of partitions.