PostgreSQL 10 – table partitioning – first impressions

PostgreSQL 10 comes with nice new native partitioning. This new partitioning does not need any triggers. It distributes inserted records automatically to the proper partitions. Here are some first impressions from tests:

1. Create parent table:

   create table public.test (id bigserial not null, inserted timestamp not null, rowvalue text) partition by range (inserted);

2. Create first partition:

   create table public.test_20170612 partition of public.test for values from ('2017.06.12 00:00:00') to ('2017.06.12 23:59:59.999999');

3. If you try to insert outside of the boundaries of this first partition

   insert into public.test (inserted, rowvalue) values ('2017-06-13 01:01:01','test1');

   you will get error message

   ERROR: no partition of relation "test" found for row
   SQL state: 23514
   Detail: Partition key of the failing row contains (inserted) = (2017-06-13 01:01:01).

4. If you want to have one partition for all older records you can specify UNBOUNDED boundary:

   create table public.test_past partition of metrics.test for values from (UNBOUNDED) to ('2017.06.11 23:59:59.999999');

5. Command checks for overlaps. If you try to create following partition:

   create table public.test_overlap partition of metrics.test for values from ('2017.06.12 00:00:00') to ('2017.06.13 23:59:59.999999');

   you will get error:

   ERROR: partition "test_overlap" would overlap partition "test_20170612"
   SQL state: 42P17