Partitioning

CREATE TABLE USERS_PART (
   user_id serial,
   username VARCHAR(50),
   user_role VARCHAR(1),
   created_date DATE DEFAULT CURRENT_DATE,
   last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY LIST (user_role);
 
-- Inserts would currently fail as we haven't defined any partitions for this table
 
CREATE TABLE USERS_PART_A
PARTITION OF USERS_PART 
FOR VALUES IN ('A');
 
-- Inserts will now only work with a user_role of A

CREATE TABLE USERS_PART (
   user_id serial,
   username VARCHAR(50),
   user_role VARCHAR(1),
   created_date DATE DEFAULT CURRENT_DATE,
   last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (created_date);
 
-- Inserts would currently fail as we haven't defined any partitions for this table
 
CREATE TABLE USERS_PART_2021
PARTITION OF USERS_PART
FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');
 
-- Inserts will now only work when the created date is in 2021

From Postgres 11 it is possible to create a default partition.

CREATE TABLE USERS_PART_DF PARTITION OF USERS_PART DEFAULT;

It is possible to show which partitioned table your select or insert query has used.

SELECT tableoid, tableoid::regclass, * FROM users_part
 WHERE user_role = 'A';

INSERT INTO users_part (username, user_role) 
VALUES ('bloggs01', 'A') 
RETURNING tableoid::regclass, user_id;

Partitioning Example