Updated May 22, 2023
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 that we should add to each partition. Also, the set of columns used for creating a different partition or the split tables should be similar to the parent. We can perform partitioning in PostgreSQL using different methods, such as range partitioning 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, divided into various partitions, is considered 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. When inserting data into a partitioned table in PostgreSQL, the system assigns the records to one of the partitions based on the partition key. 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
We create a stock_shop table in PostgreSQL using the CREATE TABLE statement, which includes the PARTITION BY RANGE clause. The table stock_shop will have three columns.
- stock_date: Its data type is the date and having a constraint, not null.
- product_name: Its data type is text.
- qyt: Its data type is an 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 the stock_date column. This will sort all records using the stock_date range.
Example #2
Now there is creating a 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 the 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
We hope that this EDUCBA information on “PostgreSQL Table Partitioning” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
