Introduction to PostgreSQL Table Partitioning
PostgreSQL Table Partitioning means we have one largest PostgreSQL table and this table is splitting into various tables. Partitions can also be foreign tables. The PostgreSQL allows us to partition the table into different ranges where we need to ensure that the values within the different partitions should not overlap. It is necessary to define the list of key values which should be added to each partition. Also, the set of columns used for creating a different partition or the split tables should be similar to the parent. Partitioning can be performed by different methods such as range partition or list partitioning.
Syntax:
Given below is the syntax:
CREATE TABLE parent
(
Id int,
col_a varchar,
col_b varchar
);
CREATE TABLE range1() inherits (parent);
CREATE TABLE range2() inherits (parent);
CREATE TABLE range3() inherits (parent);
Explanation:
- The main table which we divide into various partitions is considered as a partitioned table.
- Each partition has the partition key which is expressions or a list of columns.
How does Table Partitioning work in PostgreSQL?
The PostgreSQL table partition defines how to divide a table into different pieces termed as partitions. The partitions should be created very carefully as it might lead to affect the execution performance of various queries. The PostgreSQL does not allow us to convert the regular or normal table into the partitioned table. Also, we cannot convert the partitioned tables into regular or normal tables. If the constraint is present in the parent table then the PostgreSQL does not allow us to drop the NOT NULL constraint on a partition’s table columns.
The table is partitioned by clearly listing which key values display in each partition. Whenever we insert the data into the partitioned table then the records get inserted into one of the partitions as per the key of partition. Each partition has its own boundaries, which restrict the data insertions and allows the partition to have data within the boundaries only. The partitioned table does not have any data directly.
The PostgreSQL supports the following types of partitioning:
- Range Partitioning: PostgreSQL allows us to partition the main table into different ranges, the range is defined by some set of columns or a key column and it needs to be ensured that the values present in the various ranges should not overlap.
- List Partitioning: In the case of List partitioning, the main table gets divided into different partitions which are created by different key values.
Examples of PostgreSQL Table Partitioning
Given below are the examples mentioned :
Let’s create a table named ‘stock_shop’.
Example #1
Creating a stock_shop table using this PostgreSQL CREATE TABLE statement having PARTITION BY RANGE clause defined. The table stock_shop will have three columns.
- stock_date: Its data type is date and having constraint not null.
- product_name: Its data type is text.
- qyt: Its data type is integer.
Code:
CREATE TABLE stock_shop (
stock_date date not null,
product_name text,
qyt integer
) PARTITION BY RANGE (stock_date);
Output:
In the above example, we are creating a stock_shop table with Partition BY RANGE for stock_date column. This will sort all records using stock_date range.
Example #2
Now there is creating partition of the stock_shop table using this query.
Code:
CREATE TABLE stock_1 PARTITION OF stock_shop FOR VALUES FROM ('2019-03-01') TO ('2019-04-01');
CREATE TABLE stock_2 PARTITION OF stock_shop FOR VALUES FROM ('2019-04-01') TO ('2019-05-01');
Output:
In this example, we have created two partitions by a range of stock_date columns.
We have created two partitioned named as ‘stock_1’ and ‘stock_2’.
- Partition ‘stock_1’: The stock_1 will have values stored within the date range (‘2019-03-01’) to (‘2019-04-01’).
- Partition ‘stock_2’: The stock_2 will have values stored within the date range (‘2019-04-01’) to (‘2019-05-01’).
Example #3
Inserting some data in the Stock_shop table.
Now we will insert some record values in stock_shop table by using the following INSERT INTO SQL statement.
Code:
INSERT INTO stock_shop
(stock_date, product_name, qyt)
VALUES
('2019-03-02', 'shirt', 65),
('2019-03-03', 't-shirt', 3),
('2019-04-02', 'jeans', 69),
('2019-04-03', 'watches', 2),
('2019-03-02', 'shoes', 70),
('2019-04-03', 'goggles', 98),
('2019-04-02', 'perfume', 17),
('2019-04-01', 'sando', 82);
Output:
Example #4
Show the content of the table.
Illustrate the content of the table named ‘stock_shop’ by using the following SQL statement and snapshot.
Code:
SELECT * FROM stock_shop WHERE stock_date BETWEEN '2019-02-01' and '2019-03-31';
Output:
Now we can see, in the table named ‘stock_shop,’ there are some records between 2019-02-01 and 2019-03-31, and only three rows available between 2019-02-01 and 2019-03-31 date range.
Recommended Articles
This is a guide to PostgreSQL Table Partitioning. Here we discuss the introduction to PostgreSQL Table Partitioning, how does it work with query examples. You may also have a look at the following articles to learn more –
