Table Partitioning in PostgreSQL

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.

--Thanking You