cancel
Showing results for 
Search instead for 
Did you mean: 

Wow!! Native Partition in PostgreSQL 10

EDB Team Member

Wow!! Native Partition in PostgreSQL 10

Thanks to the author for such a nice native feature in PostgreSQL Native Partitioning. 

Commit reference:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f0e44751d7175fa3394da2c8f85e3ceb3...

Native Partitioning
In 10, partitioning tables is now an attribute of the table:

CREATE TABLE table_name ( ... )
   [ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) }

CREATE TABLE table_name
   PARTITION OF parent_table [ (
   ) ] FOR VALUES partition_bound_spec

Example
Before:

CREATE TABLE padre (
      id                 serial,
      pais             integer,
      fch_creado  timestamptz not null
   );

CREATE TABLE hija_2017 (
     CONSTRAINT pk_2017 PRIMARY KEY (id),
     CONSTRAINT ck_2017 CHECK (fch_creado < DATE '2015-01-01' )) INHERITS (padre);

CREATE INDEX idx_2017 ON hija_2017 (fch_creado);

Today:

CREATE TABLE padre (
     id             serial not null,
     nombre         text not null,
     fch_creado     timestamptz not null
)   PARTITION BY RANGE ( id );

CREATE TABLE hijo_0
      partition of padre (id, primary key (id), unique (nombre))
      for values from (unbounded) to (9);

CREATE TABLE hijo_1
      partition of padre (id, primary key (id), unique (nombre))
      for values from (10) to (unbounded);

This means that users no longer need to create triggers for routing data; it's all handled by the system. Cool

Regards

Raghav

8 REPLIES
EDB Team Member

Re: Wow!! Native Partition in PostgreSQL 10

This is a huge step forward for Postgres

PostgreSQL Core Team - EDB

Re: Wow!! Native Partition in PostgreSQL 10

We're also expecting initial support for PG10's partitioned tables in pgAdmin 4 v1.6.

EDB Team Member

Re: Wow!! Native Partition in PostgreSQL 10

When do we expect pgAdmin 4 v1.6.?

PostgreSQL Core Team - EDB

Re: Wow!! Native Partition in PostgreSQL 10

It was released last week: https://www.pgadmin.org/

Adventurer

Re: Wow!! Native Partition in PostgreSQL 10

When is V10 release?

Level 3 Traveller

Re: Wow!! Native Partition in PostgreSQL 10

Is this feature only for range partitioning or was Interval partioning also implemented? And Will the partitions be automatically migrated using the tool?

Community Manager

Re: Wow!! Native Partition in PostgreSQL 10

Hi Krishna, 

 

The first Release Candidate for V10 was released September 21st.  Based on the feedback from users for that release, that will determine when the final GA is released, but @rhaas@pgSnake or @VibhorKumar may know better on the subsequent timing.

 

Thanks!

 

Jamie

EDB Team Member

Re: Wow!! Native Partition in PostgreSQL 10

Barring unexpected problems, PostgreSQL 10 is expected to be released this Thursday (October 5th).

 

Range and list partitioning are supported, but not hash partitioning, interval partitioning, or reference partitioning.  Hash partitioning is however supported in Advanced Server and has been for several years.