How to create table partition in PostgreSQL?

What is table partition in PostgreSQL?

Table partitioning is the practice of splitting a large table into smaller sub-tables and each sub-table is created using separate CREATE TABLE commands. So every time you query data, PostgreSQL scan and process a smaller subset of data instead of doing in on the large table. Therefore, query performance would be improved tremendously.

PostgreSQL supports three types of built-in partitioning types:

Range Partitioning: The data rows are distributed to partitions based on column values falling within a given range.
List Partitioning: The table is partitioned by explicitly listing which key values appear in each partition.
Hash Partitioning: Rows are evenly distributed across all partitions using a hash value of a partitioning key.

Syntax

Here is the syntax to use the PARTITION BY clause to create a table with partitions in PostgreSQL

1. To create a range partitioned table:

CREATE TABLE table_name
table_definition
PARTITION BY RANGE (expression);

Example

CREATE TABLE city (
  id int4 NOT NULL PRIMARY KEY,
  name varchar(30) NOT NULL,
  state varchar(20),
  population int4,
)
PARTITION BY RANGE (id);

Then you need to create partitions separately

CREAT TABLE city_id1 PARTITION OF city
FOR VALUES FROM (MINVALUE) TO (10);

CREAT TABLE city_id2 PARTITION OF city
FOR VALUES FROM (10) TO (20);

CREAT TABLE city_id3 PARTITION OF city
FOR VALUES FROM (20) TO (30);

CREAT TABLE city_id4 PARTITION OF city
FOR VALUES FROM (30) TO (MAXVALUE);

2. To create a list partitioned table:

CREATE TABLE table_name
table_definition
PARTITION BY LIST (expression);

Example

CREATE TABLE cities (
  city_id bigserial NOT NULL,
  name text NOT NULL,
  population bigint
)
PARTITION BY LIST (left(lower(name), 1));

Then create a partition

CREATE TABLE cities_ab PARTITION OF cities 
FOR VALUES IN ('a', 'b');

CREATE TABLE cities_cd PARTITION OF cities 
FOR VALUES IN ('c', 'd');

CREATE TABLE cities_ef PARTITION OF cities 
FOR VALUES IN ('e', 'f');

CREATE TABLE cities_gh PARTITION OF cities 
FOR VALUES IN ('g', 'h');

3. To create a hash partitioned table:

CREATE TABLE table_name
table_definition
PARTITION BY HASH (expression);

Example

CREATE TABLE orders (
  order_id bigint NOT NULL,
  cust_id bigint NOT NULL,
  status text
)
PARTITION BY HASH (order_id);

Create partitions:

CREATE TABLE orders_p1 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE orders_p2 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE orders_p3 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE orders_p4 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.