Range : The table is partitioned into "range" defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions
List : According to a key in table, ex : country, sales
Hash : The partition specifying a modulus and a reminder for each partition.
Table Inheritance
create table master (
pk INTEGER primary key ,
tag text,
parent integer
);
create table master_child() inherits (master);
ALTER TABLE public.master_child
ADD PRIMARY KEY (pk);
select * from master;
select * from master_child;
insert into master (pk, tag, parent) values (1,'pencil',0);
insert into master_child (pk, tag, parent) values (2,'pen',0);
update master set tag = 'monitor' where pk = 2;
select * from only master;
select * from only master_child;
-- error
drop table master;
drop table master cascade ;
Range Partitioning
create table employees_range (
id bigserial,
birth_date DATE NOT NULL ,
country_code VARCHAR(2) NOT NULL
) PARTITION BY RANGE (birth_date);
CREATE TABLE employee_range_y2000 PARTITION of
employees_range for values from ('2000-01-01') to ('2001-01-01');
CREATE TABLE employee_range_y2001 PARTITION of
employees_range for values from ('2001-01-01') to ('2002-01-01');
insert into employees_range (birth_date, country_code) values
('2000-01-01','US'),
('2000-01-02','US'),
('2000-12-31','US'),
('2000-01-01','US'),
('2001-01-01','US'),
('2001-01-02','US'),
('2001-12-31','US'),
('2001-01-01','US');
select * from employees_range;
select * from only employees_range; -- you will get nothing
select * from employee_range_y2000;
select * from employee_range_y2001;
explain analyze select * from employees_range
where birth_date = '2001-01-01';
List Partitioning
create table employee_list (
id bigserial,
birth_date DATE NOT NULL ,
country_code VARCHAR(2) not null
) PARTITION BY LIST ( country_code );
create table employee_list_eu PARTITION of employee_list
for values in ('UK','DE');
create table employee_list_us PARTITION of employee_list
for values in ('US','BZ');
Hash Partitioning
create table employee_hash (
id bigserial,
birth_date DATE NOT NULL,
country_code varchar(2) not null
) PARTITION BY HASH (id);
create table employee_hash_0 partition of employee_hash
for values with (modulus 3, remainder 0);
create table employee_hash_1 partition of employee_hash
for values with (modulus 3, remainder 1);
create table employee_hash_2 partition of employee_hash
for values with (modulus 3, remainder 2);
Default Partitioning
create table employee_default_part partition of employee_list default;
Multilevel Partitioning
create table employee_list_master (
id bigserial,
birth_date DATE NOT NULL ,
country_code VARCHAR(2) not null
) PARTITION BY LIST ( country_code );
create table employee_list_eu_m PARTITION of employee_list_master
for values in ('UK','DE')
partition by hash (id);
create table employee_list_us_m PARTITION of employee_list_master
for values in ('US','BZ');
create table employee_hash_0_m partition of employee_list_eu_m
for values with (modulus 3, remainder 0);
create table employee_hash_1_m partition of employee_list_eu_m
for values with (modulus 3, remainder 1);
create table employee_hash_2_m partition of employee_list_eu_m
for values with (modulus 3, remainder 2);
Attach and DeAttach Partitions
create table employees_list_sp partition of employee_list
for values in ('SP');
insert into employee_list (birth_date, country_code) values ('2001-01-01','SP');
create table employees_list_in partition of employee_list
for values in ('IN');
create table employees_list_default partition of employee_list default
ALTER TABLE employee_list detach partition employees_list_in;
Altering Partitions
create table t1
(
a int,
b int
) partition by range (a);
create table t1p1 partition of t1 for values from (0) to (1000);
create table t1p2 partition of t1 for values from (2000) to (3000);
insert into t1 (a, b)
values (1, 1);
-- detach
-- alter
-- attach
BEGIN TRANSACTION ;
ALTER TABLE T1 DETACH PARTITION t1p1;
ALTER TABLE t1 attach partition t1p1 for values FROM (0) to (200);
commit TRANSACTION;
Partition Indexes
Creating an index on the master/parent table will automatically create same indexes to every attached table partition
PostgreSQL doesnt allow a way to create a single index covering every partition of the parent table. You have to create indexes for each table
create unique index idx_u_employee_list_id_country_code on employee_list
(id); -- error
create unique index idx_u_employee_list_id_country_code on employee_list
(id,country_code); -- with paritition key