PostgreSQL 10 Built-in Partitioning

Table partitioning is like table inheritance and reuses much of the
existing infrastructure, but there are some important differences.
The parent is called a partitioned table and is always empty; it may
not have indexes or non-inherited constraints, since those make no
sense for a relation with no data of its own. The children are called
partitions and contain all of the actual data. Each partition has an
implicit partitioning constraint. Multiple inheritance is not
allowed, and partitioning and inheritance can't be mixed. Partitions
can't have extra columns and may not allow nulls unless the parent
does. Tuples inserted into the parent are automatically routed to the
correct partition, so tuple-routing ON INSERT triggers are not needed.
Tuple routing isn't yet supported for partitions which are foreign
tables, and it doesn't handle updates that cross partition boundaries.

Currently, tables can be range-partitioned or list-partitioned. List
partitioning is limited to a single column, but range partitioning can
involve multiple columns. A partitioning "column" can be an
expression.

Because table partitioning is less general than table inheritance, it
is hoped that it will be easier to reason about properties of
partitions, and therefore that this will serve as a better foundation
for a variety of possible optimizations, including query planner
optimizations. The tuple routing based which this patch does based on
the implicit partitioning constraints is an example of this, but it
seems likely that many other useful optimizations are also possible.

Amit Langote, reviewed and tested by Robert Haas, Ashutosh Bapat,
Amit Kapila, Rajkumar Raghuwanshi, Corey Huinker, Jaime Casanova,
Rushabh Lathia, Erik Rijkers, among others. Minor revisions by me.
CREATE TABLE table_name ( ... )
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
CREATE TABLE table_name
PARTITION OF parent_table [ (
{ column_name [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ] FOR VALUES partition_bound_spec

and partition_bound_spec is:

{ IN ( expression [, ...] ) |
FROM ( { expression | UNBOUNDED } [, ...] ) TO ( { expression | UNBOUNDED } [, ...] ) }
CREATE TABLE measurement (
logdate date not null,
peaktemp int default 1,
unitsales int
) PARTITION BY RANGE (logdate);

ALTER TABLE measurement ADD CHECK (peaktemp > 0);
CREATE TABLE measurement_y2016m07
PARTITION OF measurement (
unitsales DEFAULT 0
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

keith@keith=# \d+ measurement
Table "public.measurement"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
logdate | date | | not null | | plain | |
peaktemp | integer | | | 1 | plain | |
unitsales | integer | | | | plain | |
Partition key: RANGE (logdate)
Check constraints:
"measurement_peaktemp_check" CHECK (peaktemp > 0)
Partitions: measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')

keith@keith=# \d measurement_y2016m07
Table "public.measurement_y2016m07"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
logdate | date | | not null |
peaktemp | integer | | | 1
unitsales | integer | | | 0
Partition of: measurement FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')
Check constraints:
"measurement_peaktemp_check" CHECK (peaktemp > 0)
CREATE TABLE cities (
city_id bigserial not null,
name text not null,
population int
) PARTITION BY LIST (initcap(name));

CREATE TABLE cities_west
PARTITION OF cities (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('Los Angeles', 'San Francisco');

keith@keith=# \d+ cities
Table "public.cities"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+-----------------------------------------+----------+--------------+-------------
city_id | bigint | | not null | nextval('cities_city_id_seq'::regclass) | plain | |
name | text | | not null | | extended | |
population | integer | | | | plain | |
Partition key: LIST (initcap(name))
Partitions: cities_west FOR VALUES IN ('Los Angeles', 'San Francisco')

keith@keith=# \d+ cities_west
Table "public.cities_west"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+-----------------------------------------+----------+--------------+-------------
city_id | bigint | | not null | nextval('cities_city_id_seq'::regclass) | plain | |
name | text | | not null | | extended | |
population | integer | | | | plain | |
Partition of: cities FOR VALUES IN ('Los Angeles', 'San Francisco')
Check constraints:
"city_id_nonzero" CHECK (city_id <> 0)
keith@keith=# insert into cities (name, population) values ('Baltimore', 2003);
2016-12-09 17:36:57.999 EST [15839] ERROR: no partition of relation "cities" found for row
2016-12-09 17:36:57.999 EST [15839] DETAIL: Failing row contains (1, Baltimore, 2003).
2016-12-09 17:36:57.999 EST [15839] STATEMENT: insert into cities (name, population) values ('Baltimore', 2003);
ERROR: no partition of relation "cities" found for row
DETAIL: Failing row contains (1, Baltimore, 2003).
Time: 0.641 ms
keith@keith=# insert into cities (name, population) values ('Los Angeles', 2003);
INSERT 0 1
Time: 3.127 ms
keith@keith=# select * from cities;
city_id | name | population
---------+-------------+------------
2 | Los Angeles | 2003
(1 row)

Time: 0.525 ms
CREATE TABLE cities_west
PARTITION OF cities (
CONSTRAINT city_id_nonzero CHECK (city_id != 0)
) FOR VALUES IN ('Los Angeles', 'San Francisco') PARTITION BY RANGE (population);

CREATE TABLE cities_west_10000_to_100000
PARTITION OF cities_west FOR VALUES FROM (10000) TO (100000);

keith@keith=# \d+ cities
Table "public.cities"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+-----------------------------------------+----------+--------------+-------------
city_id | bigint | | not null | nextval('cities_city_id_seq'::regclass) | plain | |
name | text | | not null | | extended | |
population | integer | | | | plain | |
Partition key: LIST (initcap(name))
Partitions: cities_west FOR VALUES IN ('Los Angeles', 'San Francisco')

keith@keith=# \d+ cities_west
Table "public.cities_west"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+-----------------------------------------+----------+--------------+-------------
city_id | bigint | | not null | nextval('cities_city_id_seq'::regclass) | plain | |
name | text | | not null | | extended | |
population | integer | | not null | | plain | |
Partition of: cities FOR VALUES IN ('Los Angeles', 'San Francisco')
Partition key: RANGE (population)
Check constraints:
"city_id_nonzero" CHECK (city_id <> 0)
Partitions: cities_west_10000_to_100000 FOR VALUES FROM (10000) TO (100000)

keith@keith=# \d+ cities_west_10000_to_100000
Table "public.cities_west_10000_to_100000"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+-----------------------------------------+----------+--------------+-------------
city_id | bigint | | not null | nextval('cities_city_id_seq'::regclass) | plain | |
name | text | | not null | | extended | |
population | integer | | not null | | plain | |
Partition of: cities_west FOR VALUES FROM (10000) TO (100000)
Check constraints:
"city_id_nonzero" CHECK (city_id <> 0)
keith@keith=# CREATE TABLE fk_test (unitsales integer);
CREATE TABLE
Time: 3.818 ms
keith@keith=# CREATE TABLE measurement (
keith(# logdate date not null,
keith(# peaktemp int default 1,
keith(# unitsales int REFERENCES fk_test (unit_sales)
keith(# ) PARTITION BY RANGE (logdate);
2016-12-12 11:34:14.485 EST [5051] ERROR: foreign key constraints are not supported on partitioned tables at character 122
2016-12-12 11:34:14.485 EST [5051] STATEMENT: CREATE TABLE measurement (
logdate date not null,
peaktemp int default 1,
unitsales int REFERENCES fk_test (unit_sales)
) PARTITION BY RANGE (logdate);
ERROR: foreign key constraints are not supported on partitioned tables
LINE 4: unitsales int REFERENCES fk_test (unit_sales)
^


keith@keith=# CREATE TABLE fk_test (unitsales integer REFERENCES measurement (unitsales));
2016-12-12 11:06:51.741 EST [5051] ERROR: cannot reference partitioned table "measurement"
2016-12-12 11:06:51.741 EST [5051] STATEMENT: CREATE TABLE fk_test (unitsales integer REFERENCES measurement (unitsales));
ERROR: cannot reference partitioned table "measurement"
Time: 1.794 ms
keith@keith=# drop table cities cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table cities_west
drop cascades to table cities_west_10000_to_100000
DROP TABLE

--

--

--

Database Admin w/ OmniTI & Player of Way Too Many Video Games

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Keith Fiske

Keith Fiske

Database Admin w/ OmniTI & Player of Way Too Many Video Games

More from Medium

Setup hot standby PostgreSQL

PostgreSQL EXPLAIN — What are the Query Costs?

Using an Amazon Simple Queue Service to invoke a Lambda function on LocalStack

Be careful about the number of JOIN in PostgreSQL