Hello Guys, In this blog, I am going to explain you about table partitioning in PostgreSQL
Table Partitioning in PostgreSQL
Partitioning – Partitioning involves splitting large tables into smaller ones according to some attribute (like time ranges, regions, or even user ID groups) but can still be treated as one table from the application. This is a logical step, and it can significantly increase your performance.
A table can be partitioned according to the conditions you specify. Therefore, it is necessary to set optimum conditions considering the data characteristics and the purpose of use.
Step: 1- Connect with root login and type.
sudo -i -u Postgres
Connect PostgreSQL Instance
Step: 2- Type psql to connect to Postgres.
Types of Partitioning-
Ø List Partition
Ø Range Partition
Ø Hash Partition
1. LIST PARTITION
· Data is partitioned according to the specified discrete values.
· Effective when you want to group discrete data, such as regions and departments with arbitrary values.
· Example: Split by region, job title, etc.
Step: 1- First, you need to create a database
Type – create database database_name;
Click Enter.
Step: 2- If you want to get the list of all databases then type
\l
Step: 3- Select the database for table partitioning
Type \c database_name for use that database.
Step: 4- create a table.
postgres=# CREATE TABLE customer_tbl (id INTEGER, status TEXT,
arr NUMERIC) PARTITION BY LIST(status);
CREATE TABLE
Step: 5- Now, create first partition table of customer table i.e., cust_active
postgres=# CREATE TABLE cust_active PARTITION OF
customer_tbl FOR VALUES IN (‘ACTIVE’);
CREATE TABLE
Step: 6- create second partition table of customer table i.e., cust_archived
postgres=# CREATE TABLE cust_archived PARTITION OF
customer_tbl FOR VALUES IN
(‘EXPIRED’);
CREATE TABLE
Step: 7- create a third partition table of customer table i.e., default.
postgres=# CREATE TABLE cust_others PARTITION OF customer_tbl DEFAULT;
CREATE TABLE
Step: 8- Now retrieve the data of partition table of customers.
Type \d+ table_name
Here, cust_active, cust_archived and cust_others are partitioned tables of customers.
Step: 9- To check the details of cust_active table.
Type \d+ cust_active
Step: 10- Now, Add data into customer table.
INSERT INTO customers VALUES (1,’ACTIVE’,100), (2,’RECURRING’,20), (3,’EXPIRED’,38), (4,’REACTIVATED’,144);
Step: 11- Now, retrieve the tableoid data.
SELECT tableoid::regclass,* FROM customer_tbl;
2. RANGE PARTITION
Ø Data is partitioned according to the specified range.
Ø Effective when you want to access time-series data, by specifying date such as year and month.
Ø Example: Split by sale date, admission date, etc.
Step: 1- create a table for Range Partition.
CREATE TABLE customer_range (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY RANGE(arr);
Step: 2- create a table cust_arr_small for the partition of customer_range
table.
Range between minvalue to 25.
CREATE TABLE cust_arr_small PARTITION OF customer_range FOR
VALUES FROM (MINVALUE) TO (25);
Step: 3- create another table cust_arr_medium for the partition of
customer_range.
Range between 25 to 75.
CREATE TABLE cust_arr_medium PARTITION OF customer_range
FOR VALUES FROM (25) TO (75);
Step: 4- create another table cust_arr_large for the partition of
customer_range.
Range between 75 to max_value.
CREATE TABLE cust_arr_large PARTITION OF customer_range FOR
VALUES FROM (75) TO (MAXVALUE);
Step: 5- To retrieve customer_range table data.
Type \d+ customer_range
Step: 6- To retrieve partition table cust_arr_small data.
Type \d+ cust_arr_small
Step: 7- For adding data into customer_range table
INSERT INTO customer_range VALUES (1,’ACTIVE’,100),
(2,’RECURRING’,20), (3,’EXPIRED’,38), (4,’REACTIVATED’,144);
Step: 8- Retrieve tableoid data from partition_range table.
Leave a Reply