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.
In the below example , we will show how a list partition works
Create a new table called transaction in a separate tablespace
CREATE TABLE IF NOT EXISTS netbnk_schm.transaction
(
transaction_id integer NOT NULL,
transaction_date date,
card_type varchar(100),
banks varchar(100)
) partition by list(card_type); TABLESPACE new_tbs
CREATE TABLE
netbank=# \d transaction
Partitioned table "netbnk_schm.transaction"
Column | Type | Collation | Nullable | Default
------------------+------------------------+-----------+----------+---------
transaction_id | integer | | not null |
transaction_date | date | | |
card_type | character varying(100) | | |
banks | character varying(100) | | |
Partition key: LIST (card_type)
Number of partitions: 2 (Use \d+ to list them.)
Table is created successfully , now create two new tablespace and corresponding two new where we need to store the data based on the list partition .
[root@parweztestinstance ~]# mkdir -p /var/oled/test_tbs2
[root@parweztestinstance ~]# chown -R postgres:postgres /var/oled/test_tbs2
[postgres@parweztestinstance ~]$ psql
psql (14.4)
Type "help" for help.
postgres=# \c netbank
You are now connected to database "netbank" as user "postgres".
netbank=# CREATE TABLESPACE new_tbs_2 owner olap LOCATION '/var/oled/test_tbs2' ;
CREATE TABLESPACE
List the new tablespace created
netbank=# \db
List of tablespaces
Name | Owner | Location
------------+----------+---------------------
new_tbs_2 | olap | /var/oled/test_tbs2
new_tbs_3 | olap | /var/oled/test_tbs3
Create two new partition table
netbank=# create table netbnk_schm.transaction_part2 partition of netbnk_schm.transaction_part for values IN('mastercard') TABLESPACE new_tbs_3;
CREATE TABLE
netbank=# create table netbnk_schm.transaction_part partition of netbnk_schm.transaction_part for values IN('VISA') TABLESPACE new_tbs_2;
CREATE TABLE
Check all tables
netbank=# set search_path=netbnk_schm;
SET
netbank=#
netbank=#
netbank=#
netbank=# \dt
List of relations
Schema | Name | Type | Owner
-------------+-------------------+-------------------+----------
netbnk_schm | transaction | partitioned table | olap
netbnk_schm | transaction_part | table | olap
netbnk_schm | transaction_part2 | table | olap
(3 rows)
Now insert some data based on list of partition key it will send to the corresponding partition table
netbank=# insert into netbnk_schm.transaction values(12,'1/12/2001','mastercard', 'sbi'); insert into netbnk_schm.transaction values(13,'1/13/2001', 'mastercard', 'al rajhi'); insert into netbnk_schm.transaction values(14,'1/14/2001', 'mastercard', 'bny'); insert into netbnk_schm.transaction values(15,'1/15/2001', 'mastercard', 'jp morgan'); insert into netbnk_schm.transaction values(16,'1/16/2001', 'mastercard', 'al jazeera'); INSERT 0 1
select and check
netbank=# select * from netbnk_schm.transaction_part2 ;
transaction_id | transaction_date | card_type | banks
----------------+------------------+------------+------------
12 | 2001-01-12 | mastercard | sbi
13 | 2001-01-13 | mastercard | al rajhi
14 | 2001-01-14 | mastercard | bny
15 | 2001-01-15 | mastercard | jp morgan
16 | 2001-01-16 | mastercard | al jazeera
17 | 2001-01-17 | mastercard | sabb
19 | 2001-01-18 | mastercard | hsbc
18 | 2001-01-19 | mastercard | hsbc
20 | 2001-01-20 | mastercard | sbi
(9 rows)
similarly insert for another value in key
insert into netbnk_schm.transaction values(2,'1/2/2001', 'VISA', 'hsbc'); insert into netbnk_schm.transaction values(3,'1/3/2001', 'VISA', 'hsbc'); insert into netbnk_schm.transaction values(4,'1/4/2001', 'VISA', 'sbi'); insert into netbnk_schm.transaction values(5,'1/5/2001', 'VISA', 'al rajhi'); insert into netbnk_schm.transaction values(6,'1/6/2001', 'VISA', 'bny'); insert into netbnk_schm.transaction values(7,'1/7/2001', 'VISA', 'jp morgan'); INSERT 0 1
netbank=# select * from transaction_part;
transaction_id | transaction_date | card_type | banks
----------------+------------------+-----------+------------
11 | 2001-01-11 | VISA | hsbc
2 | 2001-01-02 | VISA | hsbc
2 | 2001-01-02 | VISA | hsbc
3 | 2001-01-03 | VISA | hsbc
4 | 2001-01-04 | VISA | sbi
5 | 2001-01-05 | VISA | al rajhi
6 | 2001-01-06 | VISA | bny
7 | 2001-01-07 | VISA | jp morgan
8 | 2001-01-08 | VISA | al jazeera
9 | 2001-01-09 | VISA | sab
Check the tables and tablespace details
netbank=# \dt
List of relations
Schema | Name | Type | Owner
-------------+----------------------------+-------------------+----------
netbnk_schm | transaction_par2 | table | olap
netbnk_schm | transaction_part | table | olap
netbnk_schm | transaction | partitioned table | olap
netbank=# select tablename,tablespace from pg_tables where schemaname='netbnk_schm' ;
tablename | tablespace
----------------------------+------------
transaction |new_tbs
transaction_part |new_tbs_2
transaction_part2 |new_tbs_3
netbank=# \d+ transaction
Partitioned table "netbnk_schm.transaction"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
transaction_id | integer | | not null | | plain | | |
transaction_date | date | | | | plain | | |
card_type | character varying(100) | | | | extended | | |
banks | character varying(100) | | | | extended | | |
Partition key: LIST (card_type)
Partitions: transaction_part FOR VALUES IN ('VISA'),
transaction_part2 FOR VALUES IN ('mastercard')
Range partioning examples
netbank=# CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
PARTITION BY RANGE (logdate);
CREATE TABLE
netbank=# CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE
netbank=# CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
CREATE TABLE
netbank=# CREATE TABLE measurement_y2007m11 PARTITION OF measurement FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
CREATE TABLE
netbank=# CREATE TABLE measurement_y2007m12 PARTITION OF measurement FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
TABLESPACE olap_tbs;
CREATE TABLE
Create an index on PARTITION column it will create index on all PARTITION table
netbank=# CREATE INDEX ON measurement (logdate);
CREATE INDEX
netbank=# \d+ measurement
Partitioned table "netbnk_schm.measurement"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
city_id | integer | | not null | | plain | | |
logdate | date | | not null | | plain | | |
peaktemp | integer | | | | plain | | |
unitsales | integer | | | | plain | | |
Partition key: RANGE (logdate)
Indexes:
"measurement_logdate_idx" btree (logdate)
Partitions: measurement_y2006m02 FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'),
measurement_y2006m03 FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'),
measurement_y2007m11 FOR VALUES FROM ('2007-11-01') TO ('2007-12-01'),
measurement_y2007m12 FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
Insert some values according to dates, it will send the entries for respective dates into their own par Partition tables .
netbank=# insert into netbnk_schm.measurement values(1,'2006-02-18',33,5) ;
INSERT 0 1
netbank=# insert into netbnk_schm.measurement values(1,'2006-02-28',33,5) ;
INSERT 0 1
netbank=# insert into netbnk_schm.measurement values(1,'2006-02-11',33,5) ;
INSERT 0 1
netbank=# insert into netbnk_schm.measurement values(1,'2007-11-01',33,5) ;
INSERT 0 1
netbank=# insert into netbnk_schm.measurement values(1,'2007-11-22',33,5) ;
INSERT 0 1
netbank=# insert into netbnk_schm.measurement values(12,'2007-12-3',32,4) ;
INSERT 0 1
Check the data now
netbank=# select * from measurement ;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
1 | 2006-02-18 | 33 | 5
1 | 2006-02-28 | 33 | 5
1 | 2006-02-11 | 33 | 5
1 | 2007-11-01 | 33 | 5
1 | 2007-11-22 | 33 | 5
12 | 2007-12-03 | 32 | 4
(6 rows)
netbank=# select * from measurement_y2006m02 ;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
1 | 2006-02-18 | 33 | 5
1 | 2006-02-28 | 33 | 5
1 | 2006-02-11 | 33 | 5
(3 rows)
netbank=# select * from measurement_y2007m12 ;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
12 | 2007-12-03 | 32 | 4
(1 row)
netbank=# select * from measurement_y2007m11 ;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
1 | 2007-11-01 | 33 | 5
1 | 2007-11-22 | 33 | 5
(2 rows)
1,011 total views, 3 views today