What is Partition in PostgreSQL?<\/h2>\n
We will be discussing the table partitioning in PostgreSQL 11.2. The partitioning method used before PostgreSQL 10 was very manual and problematic. Imagine that before version 10, Trigger was used to transfer data to the corresponding partition. Imagine how old it is.<\/p>\n
Partitioning the table according to certain criteria is called partitioning. The main table we partitioned is called master and each partition are called child.<\/p>\n
What are the advantages of Table Partitioning in PostgreSQL?<\/h2>\n\n- Improves query performance. (Since the queries read the data only from the relevant partition, query result will be faster.)<\/li>\n
- Index cost and Size are decreasing. We reduce the size of our indexes and decrease the index fragmentation by creating an index in the relevant partition only.<\/li>\n
- We will be able to manage our Bulk operations healthier and faster.<\/li>\n<\/ul>\n
Partition Types in PostgreSQL<\/h2>\n
You can find the partition types in postgresql below.<\/p>\n
LIST PARTITION in PostgreSQL<\/h3>\n
The table is partitioned according to the key value of the partition column. For Example, suppose that you have a table that contains person name and country information and you want to create a partition according to the country column’s value. You can perform this operation by using LIST PARTITION.<\/p>\n
PostgreSQL List Partition Example<\/h4>\n
First execute the command \\x<\/code> for user friendly screen.<\/p>\nCREATE TABLE person\r\n(\r\n personname varchar(100),\r\n country varchar(100)\r\n)\r\nPARTITION BY LIST(country);\r\nCREATE TABLE EUROPE PARTITION of person FOR VALUES IN('GERMANY','SPAIN');\r\nCREATE TABLE ASIA PARTITION of person FOR VALUES IN('TURKEY','INDIA');\r\nCREATE TABLE AFRICA PARTITION of person FOR VALUES IN('ANGOLA','BENIN');\r\nCREATE TABLE NORTHAMERICA PARTITION of person FOR VALUES IN('US','MEXICO');\r\nCREATE TABLE SOUTHAMERICA PARTITION of person FOR VALUES IN('ARGENTINA','ECUADOR');\r\nCREATE TABLE AUSTRALIA PARTITION of person FOR VALUES IN('AUSTRALIA','FIJI');<\/pre>\nThen check partitions created successfully; Write your table name instead of person in the below script if your table name is different.<\/p>\n
select pt.relname as partition_name,\r\n pg_get_expr(pt.relpartbound, pt.oid, true) as partition_expression\r\nfrom pg_class base_tb \r\n join pg_inherits i on i.inhparent = base_tb.oid \r\n join pg_class pt on pt.oid = i.inhrelid\r\nwhere base_tb.oid = 'public.person'::regclass;<\/pre>\n![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/12\/Ads\u0131z-26.png\")
<\/p>\n
Insert new records to ASIA partition. The following data will be inserted to ASIA partition. Because the values TURKEY and INDIA is in the ASIA partition.<\/p>\n
INSERT INTO person VALUES ('Nurullah CAKIR', 'TURKEY');\r\nINSERT INTO person VALUES ('Ahmad ANSAR', 'INDIA');<\/pre>\nGet Row Count in Each Partititions<\/h4>\nSELECT tableoid::regclass AS source, count(*) FROM person GROUP BY source;<\/pre>\n![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/12\/Ads\u0131z-27.png\")
<\/h2>\n
Then insert new records to other partitions to see the distribution.<\/p>\n
INSERT INTO person VALUES ('John TRAVOLTA', 'US');\r\nINSERT INTO person VALUES ('Diego Maradona', 'ARGENTINA');<\/pre>\n![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/12\/Ads\u0131z-28.png\")
<\/h2>\nRANGE PARTITION in PostgreSQL<\/h2>\n
Table partitioning is performed according to a range according to the specified criteria. For example, we can create a range partition according to a specific date range, or we can create a range partition using a range according to other data types.<\/p>\n
PostgreSQL Range Partition Example<\/h4>\n
Let’s create our demo table at first.<\/p>\n
CREATE TABLE sales (\r\n id serial,\r\n sales_count int,\r\n sales_date date not null\r\n) PARTITION BY RANGE (sales_date);<\/pre>\nWe have specified partition type and partition column above. PARTITION BY RANGE (sales_date)<\/p>\n
Now let’s create our Partitions. Since we will create partitions monthly, we divide our table into 12 for the last 1 year.<\/p>\n
CREATE TABLE sales_2019_01 PARTITION OF sales\r\nFOR VALUES FROM ('2019-01-01') TO ('2019-02-01');\r\n \r\nCREATE TABLE sales_2019_02 PARTITION OF sales\r\nFOR VALUES FROM ('2019-02-01') TO ('2019-03-01');\r\n \r\nCREATE TABLE sales_2019_03 PARTITION OF sales\r\nFOR VALUES FROM ('2019-03-01') TO ('2019-04-01');\r\n \r\nCREATE TABLE sales_2019_04 PARTITION OF sales\r\nFOR VALUES FROM ('2019-04-01') TO ('2019-05-01');\r\n \r\nCREATE TABLE sales_2019_05 PARTITION OF sales\r\nFOR VALUES FROM ('2019-05-01') TO ('2019-06-01');\r\n \r\nCREATE TABLE sales_2019_06 PARTITION OF sales\r\nFOR VALUES FROM ('2019-06-01') TO ('2019-07-01');\r\n \r\nCREATE TABLE sales_2019_07 PARTITION OF sales\r\nFOR VALUES FROM ('2019-07-01') TO ('2019-08-01');\r\n \r\nCREATE TABLE sales_2019_08 PARTITION OF sales\r\nFOR VALUES FROM ('2019-08-01') TO ('2019-09-01');\r\n \r\nCREATE TABLE sales_2019_09 PARTITION OF sales\r\nFOR VALUES FROM ('2019-09-01') TO ('2019-10-01');\r\n \r\nCREATE TABLE sales_2019_10 PARTITION OF sales\r\nFOR VALUES FROM ('2019-10-01') TO ('2019-11-01');\r\n \r\nCREATE TABLE sales_2019_11 PARTITION OF sales\r\nFOR VALUES FROM ('2019-11-01') TO ('2019-12-01');\r\n \r\nCREATE TABLE sales_2019_12 PARTITION OF sales\r\nFOR VALUES FROM ('2019-12-01') TO ('2020-01-01');<\/pre>\nCheck Partitions in PostgreSQL<\/h4>\n
After creating our partitions, let’s have a chek without inserting data.<\/p>\n
We can check the partitions we created with the help of the below script.<\/p>\n
SELECT nmsp_parent.nspname AS parent_schema,\r\n parent.relname AS parent,\r\n nmsp_child.nspname AS child_schema,\r\n child.relname AS child\r\nFROM pg_inherits\r\nJOIN pg_class parent ON pg_inherits.inhparent = parent.oid\r\nJOIN pg_class child ON pg_inherits.inhrelid = child.oid\r\nJOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace\r\nJOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace\r\nWHERE parent.relname='sales' ;<\/pre>\n![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/12\/Ads\u0131z-29.png\")
<\/p>\n
After completing our checks, let’s insert data to our table.<\/p>\n
INSERT INTO sales (sales_count, sales_date) VALUES (50 , '2019-04-12');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (80 , '2019-12-14');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (70 , '2019-07-15');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (75 , '2019-08-17');<\/pre>\nNow let’s execute a query and check if our query brings data from the relevant partition.<\/p>\n
select sales_count from sales where sales_date between '2019-04-01' and '2019-05-01';<\/pre>\nNow let’s check which partitions it use with EXPLAIN<\/strong>.<\/p>\nexplain select sales_count from sales where sales_date between '2019-04-01' and '2019-05-01';<\/pre>\nWhen you execute the query, we see that it uses the sales_2019_04 and sales_2019_05 partitions.<\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/12\/Ads\u0131z-30.png\")
<\/p>\n
Get Row Count in Each Partition<\/h4>\nSELECT tableoid::regclass AS source, count(*) FROM person GROUP BY source;<\/pre>\n![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/12\/Ads\u0131z-31.png\")
<\/p>\n
Hash PARTITION in PostgreSQL<\/h2>\n
In Hash Partition, data is transferred to partition tables according to the hash value of Partition Key(column you specified in PARTITION BY HASH statement). You can specify a single column or multiple columns when specifying the Partition Key. In order to distribute the data equally to partitions, you should take care that partition key is close to unique. It is created similar to the RANGE and LIST partition.<\/p>\n
There are MODULUS and REMAINDER concepts during the creation of partitions tables. The MODULUS value indicates how many partition tables we have. It is fixed for all partition tables and does not change.<\/p>\n
Since there are 10 partitions, REMAINDER can have a value from 0 to 9. If you do not specify the modulus and remainder values correctly, you will receive the below error.<\/p>\n
ERROR: every hash partition modulus must be a factor of the next larger modulus<\/em><\/span><\/p>\nThe hash value of the partition key used for the HASH partition is divided into MODULUS value and the data is transferred to the REMAINDER table pointed to by the remaining value.<\/p>\n
For Example, suppose that the hash value is 102. It divides 102 by 10. Here, the remaining value is 2. So, the data will go to the REMANDER 2 table.<\/p>\n
PostgreSQL Hash Partition Example<\/h4>\n
In this example, we will use the same table structure as the List Partition Example. But the partition column will be PersonName. But do not use name column as hash partition column in your production environment. Because names are often not unique. Therefore, data is not evenly distributed across partitions.<\/p>\n
Note:<\/strong> Do not forget person table we have created for previous example.<\/p>\nCREATE TABLE person\r\n(\r\n personname varchar(100), \r\n country varchar(100)\r\n)\r\nPARTITION BY HASH(PersonName);<\/pre>\nCREATE TABLE hashpartition_0 PARTITION OF person FOR VALUES WITH (MODULUS 5, REMAINDER 0);\r\nCREATE TABLE hashpartition_1 PARTITION OF person FOR VALUES WITH (MODULUS 5, REMAINDER 1);\r\nCREATE TABLE hashpartition_2 PARTITION OF person FOR VALUES WITH (MODULUS 5, REMAINDER 2);\r\nCREATE TABLE hashpartition_3 PARTITION OF person FOR VALUES WITH (MODULUS 5, REMAINDER 3);\r\nCREATE TABLE hashpartition_4 PARTITION OF person FOR VALUES WITH (MODULUS 5, REMAINDER 4);<\/pre>\nYou can check partition is created with the command \\d+ person<\/code><\/p>\n![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/12\/Ads\u0131z-32.png\")
<\/p>\n
Insert Into data to the table. Note that we insert 3 row and the names of the 2 rows are the same. This will cause the data not to be evenly distributed across partition tables.<\/p>\n
INSERT INTO person (personname, country) VALUES ('Nurullah CAKIR', 'TURKEY');\r\nINSERT INTO person (personname, country) VALUES ('Nurullah CAKIR', 'INDIA');\r\nINSERT INTO person (personname, country) VALUES ('John TRAVOLTA', 'US');<\/pre>\nSelect * from the main table and partition tables as below. You will see that there are no rows in the main table. Two rows will be on a partition because of two rows name value is the same and the other row will be in different partition.<\/p>\n
select * from only person;\r\nselect * from only hashpartition_0;\r\nselect * from only hashpartition_1;\r\nselect * from only hashpartition_2;\r\nselect * from only hashpartition_3;\r\nselect * from only hashpartition_4;<\/pre>\nIf you select maint table without only, you can see all the rows;<\/p>\n
select * from person;<\/pre>\nYou can see the distribution with the below query;<\/p>\n
SELECT tableoid::regclass AS source, count(*) FROM person GROUP BY source;<\/pre>\n![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/12\/Ads\u0131z-33.png\")
<\/p>\n
Sub-Partitioning in PostgreSQL<\/h2>\n
With Sub Partition, we can divide the partitions of the tables into sub-partitions. For example, suppose you have a partitioned table by years. It means a partition for each year. But you may also want to make partitions by months.<\/p>\n
PostgreSQL Sub Partition Example<\/h4>\n
In this example, we will use the same table structure as the Range Partition Example.<\/p>\n
Note:<\/strong> Do not forget sales table we have created for previous example.<\/p>\nCREATE TABLE sales (\r\n id serial,\r\n sales_count int,\r\n sales_date date not null\r\n) PARTITION BY RANGE (sales_date);<\/pre>\nCreate partition for 2018-2019-2020.<\/p>\n
CREATE TABLE sales_2018 PARTITION OF sales\r\nFOR VALUES FROM ('2018-01-01') TO ('2019-01-01');\r\n \r\nCREATE TABLE sales_2019 PARTITION OF sales\r\nFOR VALUES FROM ('2019-01-01') TO ('2020-01-01');\r\n \r\nCREATE TABLE sales_2020 PARTITION OF sales\r\nFOR VALUES FROM ('2020-01-01') TO ('2021-01-01');<\/pre>\nInsert below records to table.<\/p>\n
INSERT INTO sales (sales_count, sales_date) VALUES (50 , '2018-04-12');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (80 , '2019-12-14');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (70 , '2020-01-01');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (75 , '2020-12-31');<\/pre>\nGet Row Count in Each Partition<\/h4>\nSELECT tableoid::regclass AS source, count(*) FROM sales GROUP BY source;<\/pre>\n![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/12\/Ads\u0131z-34.png\")
<\/p>\n
Suppose that your needs has changed and you need also sub partitions for new year. To perform this we will create a partition for sales_2021, and subpartitions for each month in 2021.<\/p>\n
create table sales_2021 partition of sales\r\nfor values from ('2021-01-01') to ('2022-01-01') partition by range(sales_date);<\/pre>\ncreate table sales_2021_01 partition of sales_2021\r\nfor values from ('2021-01-01') to ('2021-02-01');\r\ncreate table sales_2021_02 partition of sales_2021\r\nfor values from ('2021-02-01') to ('2021-03-01');\r\ncreate table sales_2021_03 partition of sales_2021\r\nfor values from ('2021-03-01') to ('2021-04-01');\r\ncreate table sales_2021_04 partition of sales_2021\r\nfor values from ('2021-04-01') to ('2021-05-01');\r\ncreate table sales_2021_05 partition of sales_2021\r\nfor values from ('2021-05-01') to ('2021-06-01');\r\ncreate table sales_2021_06 partition of sales_2021\r\nfor values from ('2021-06-01') to ('2021-07-01');\r\ncreate table sales_2021_07 partition of sales_2021\r\nfor values from ('2021-07-01') to ('2021-08-01');\r\ncreate table sales_2021_08 partition of sales_2021\r\nfor values from ('2021-08-01') to ('2021-09-01');\r\ncreate table sales_2021_09 partition of sales_2021\r\nfor values from ('2021-09-01') to ('2021-10-01');\r\ncreate table sales_2021_10 partition of sales_2021\r\nfor values from ('2021-10-01') to ('2021-11-01');\r\ncreate table sales_2021_11 partition of sales_2021\r\nfor values from ('2021-11-01') to ('2021-12-01');\r\ncreate table sales_2021_12 partition of sales_2021\r\nfor values from ('2021-12-01') to ('2022-01-01');<\/pre>\nThe last partition structure of our table is as follows.<\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/12\/Ads\u0131z-36.png\")
<\/p>\n
if you want to see the sub partitions you should execute the \\d+ sales_2021<\/code> command.<\/p>\n![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/12\/Ads\u0131z-37.png\")
<\/p>\n
<\/div>\n
Partition Types in PostgreSQL<\/h2>\n
You can find the partition types in postgresql below.<\/p>\n
LIST PARTITION in PostgreSQL<\/h3>\n
The table is partitioned according to the key value of the partition column. For Example, suppose that you have a table that contains person name and country information and you want to create a partition according to the country column’s value. You can perform this operation by using LIST PARTITION.<\/p>\n
PostgreSQL List Partition Example<\/h4>\n
First execute the command Then check partitions created successfully; Write your table name instead of person in the below script if your table name is different.<\/p>\n Insert new records to ASIA partition. The following data will be inserted to ASIA partition. Because the values TURKEY and INDIA is in the ASIA partition.<\/p>\n Then insert new records to other partitions to see the distribution.<\/p>\n Table partitioning is performed according to a range according to the specified criteria. For example, we can create a range partition according to a specific date range, or we can create a range partition using a range according to other data types.<\/p>\n Let’s create our demo table at first.<\/p>\n We have specified partition type and partition column above. PARTITION BY RANGE (sales_date)<\/p>\n Now let’s create our Partitions. Since we will create partitions monthly, we divide our table into 12 for the last 1 year.<\/p>\n After creating our partitions, let’s have a chek without inserting data.<\/p>\n We can check the partitions we created with the help of the below script.<\/p>\n After completing our checks, let’s insert data to our table.<\/p>\n Now let’s execute a query and check if our query brings data from the relevant partition.<\/p>\n Now let’s check which partitions it use with EXPLAIN<\/strong>.<\/p>\n When you execute the query, we see that it uses the sales_2019_04 and sales_2019_05 partitions.<\/p>\n In Hash Partition, data is transferred to partition tables according to the hash value of Partition Key(column you specified in PARTITION BY HASH statement). You can specify a single column or multiple columns when specifying the Partition Key. In order to distribute the data equally to partitions, you should take care that partition key is close to unique. It is created similar to the RANGE and LIST partition.<\/p>\n There are MODULUS and REMAINDER concepts during the creation of partitions tables. The MODULUS value indicates how many partition tables we have. It is fixed for all partition tables and does not change.<\/p>\n Since there are 10 partitions, REMAINDER can have a value from 0 to 9. If you do not specify the modulus and remainder values correctly, you will receive the below error.<\/p>\n ERROR: every hash partition modulus must be a factor of the next larger modulus<\/em><\/span><\/p>\n The hash value of the partition key used for the HASH partition is divided into MODULUS value and the data is transferred to the REMAINDER table pointed to by the remaining value.<\/p>\n For Example, suppose that the hash value is 102. It divides 102 by 10. Here, the remaining value is 2. So, the data will go to the REMANDER 2 table.<\/p>\n In this example, we will use the same table structure as the List Partition Example. But the partition column will be PersonName. But do not use name column as hash partition column in your production environment. Because names are often not unique. Therefore, data is not evenly distributed across partitions.<\/p>\n Note:<\/strong> Do not forget person table we have created for previous example.<\/p>\n You can check partition is created with the command Insert Into data to the table. Note that we insert 3 row and the names of the 2 rows are the same. This will cause the data not to be evenly distributed across partition tables.<\/p>\n Select * from the main table and partition tables as below. You will see that there are no rows in the main table. Two rows will be on a partition because of two rows name value is the same and the other row will be in different partition.<\/p>\n If you select maint table without only, you can see all the rows;<\/p>\n You can see the distribution with the below query;<\/p>\n With Sub Partition, we can divide the partitions of the tables into sub-partitions. For example, suppose you have a partitioned table by years. It means a partition for each year. But you may also want to make partitions by months.<\/p>\n In this example, we will use the same table structure as the Range Partition Example.<\/p>\n Note:<\/strong> Do not forget sales table we have created for previous example.<\/p>\n Create partition for 2018-2019-2020.<\/p>\n Insert below records to table.<\/p>\n Suppose that your needs has changed and you need also sub partitions for new year. To perform this we will create a partition for sales_2021, and subpartitions for each month in 2021.<\/p>\n The last partition structure of our table is as follows.<\/p>\n if you want to see the sub partitions you should execute the \\x<\/code> for user friendly screen.<\/p>\nCREATE TABLE person\r\n(\r\n personname varchar(100),\r\n country varchar(100)\r\n)\r\nPARTITION BY LIST(country);\r\nCREATE TABLE EUROPE PARTITION of person FOR VALUES IN('GERMANY','SPAIN');\r\nCREATE TABLE ASIA PARTITION of person FOR VALUES IN('TURKEY','INDIA');\r\nCREATE TABLE AFRICA PARTITION of person FOR VALUES IN('ANGOLA','BENIN');\r\nCREATE TABLE NORTHAMERICA PARTITION of person FOR VALUES IN('US','MEXICO');\r\nCREATE TABLE SOUTHAMERICA PARTITION of person FOR VALUES IN('ARGENTINA','ECUADOR');\r\nCREATE TABLE AUSTRALIA PARTITION of person FOR VALUES IN('AUSTRALIA','FIJI');<\/pre>\nselect pt.relname as partition_name,\r\n pg_get_expr(pt.relpartbound, pt.oid, true) as partition_expression\r\nfrom pg_class base_tb \r\n join pg_inherits i on i.inhparent = base_tb.oid \r\n join pg_class pt on pt.oid = i.inhrelid\r\nwhere base_tb.oid = 'public.person'::regclass;<\/pre>\n
<\/p>\nINSERT INTO person VALUES ('Nurullah CAKIR', 'TURKEY');\r\nINSERT INTO person VALUES ('Ahmad ANSAR', 'INDIA');<\/pre>\nGet Row Count in Each Partititions<\/h4>\n
SELECT tableoid::regclass AS source, count(*) FROM person GROUP BY source;<\/pre>\n
<\/h2>\nINSERT INTO person VALUES ('John TRAVOLTA', 'US');\r\nINSERT INTO person VALUES ('Diego Maradona', 'ARGENTINA');<\/pre>\n<\/h2>\nRANGE PARTITION in PostgreSQL<\/h2>\n
PostgreSQL Range Partition Example<\/h4>\n
CREATE TABLE sales (\r\n id serial,\r\n sales_count int,\r\n sales_date date not null\r\n) PARTITION BY RANGE (sales_date);<\/pre>\n
CREATE TABLE sales_2019_01 PARTITION OF sales\r\nFOR VALUES FROM ('2019-01-01') TO ('2019-02-01');\r\n \r\nCREATE TABLE sales_2019_02 PARTITION OF sales\r\nFOR VALUES FROM ('2019-02-01') TO ('2019-03-01');\r\n \r\nCREATE TABLE sales_2019_03 PARTITION OF sales\r\nFOR VALUES FROM ('2019-03-01') TO ('2019-04-01');\r\n \r\nCREATE TABLE sales_2019_04 PARTITION OF sales\r\nFOR VALUES FROM ('2019-04-01') TO ('2019-05-01');\r\n \r\nCREATE TABLE sales_2019_05 PARTITION OF sales\r\nFOR VALUES FROM ('2019-05-01') TO ('2019-06-01');\r\n \r\nCREATE TABLE sales_2019_06 PARTITION OF sales\r\nFOR VALUES FROM ('2019-06-01') TO ('2019-07-01');\r\n \r\nCREATE TABLE sales_2019_07 PARTITION OF sales\r\nFOR VALUES FROM ('2019-07-01') TO ('2019-08-01');\r\n \r\nCREATE TABLE sales_2019_08 PARTITION OF sales\r\nFOR VALUES FROM ('2019-08-01') TO ('2019-09-01');\r\n \r\nCREATE TABLE sales_2019_09 PARTITION OF sales\r\nFOR VALUES FROM ('2019-09-01') TO ('2019-10-01');\r\n \r\nCREATE TABLE sales_2019_10 PARTITION OF sales\r\nFOR VALUES FROM ('2019-10-01') TO ('2019-11-01');\r\n \r\nCREATE TABLE sales_2019_11 PARTITION OF sales\r\nFOR VALUES FROM ('2019-11-01') TO ('2019-12-01');\r\n \r\nCREATE TABLE sales_2019_12 PARTITION OF sales\r\nFOR VALUES FROM ('2019-12-01') TO ('2020-01-01');<\/pre>\nCheck Partitions in PostgreSQL<\/h4>\n
SELECT nmsp_parent.nspname AS parent_schema,\r\n parent.relname AS parent,\r\n nmsp_child.nspname AS child_schema,\r\n child.relname AS child\r\nFROM pg_inherits\r\nJOIN pg_class parent ON pg_inherits.inhparent = parent.oid\r\nJOIN pg_class child ON pg_inherits.inhrelid = child.oid\r\nJOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace\r\nJOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace\r\nWHERE parent.relname='sales' ;<\/pre>\n
<\/p>\nINSERT INTO sales (sales_count, sales_date) VALUES (50 , '2019-04-12');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (80 , '2019-12-14');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (70 , '2019-07-15');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (75 , '2019-08-17');<\/pre>\n
select sales_count from sales where sales_date between '2019-04-01' and '2019-05-01';<\/pre>\n
explain select sales_count from sales where sales_date between '2019-04-01' and '2019-05-01';<\/pre>\n
<\/p>\nGet Row Count in Each Partition<\/h4>\n
SELECT tableoid::regclass AS source, count(*) FROM person GROUP BY source;<\/pre>\n
<\/p>\nHash PARTITION in PostgreSQL<\/h2>\n
PostgreSQL Hash Partition Example<\/h4>\n
CREATE TABLE person\r\n(\r\n personname varchar(100), \r\n country varchar(100)\r\n)\r\nPARTITION BY HASH(PersonName);<\/pre>\n
CREATE TABLE hashpartition_0 PARTITION OF person FOR VALUES WITH (MODULUS 5, REMAINDER 0);\r\nCREATE TABLE hashpartition_1 PARTITION OF person FOR VALUES WITH (MODULUS 5, REMAINDER 1);\r\nCREATE TABLE hashpartition_2 PARTITION OF person FOR VALUES WITH (MODULUS 5, REMAINDER 2);\r\nCREATE TABLE hashpartition_3 PARTITION OF person FOR VALUES WITH (MODULUS 5, REMAINDER 3);\r\nCREATE TABLE hashpartition_4 PARTITION OF person FOR VALUES WITH (MODULUS 5, REMAINDER 4);<\/pre>\n
\\d+ person<\/code><\/p>\n<\/p>\nINSERT INTO person (personname, country) VALUES ('Nurullah CAKIR', 'TURKEY');\r\nINSERT INTO person (personname, country) VALUES ('Nurullah CAKIR', 'INDIA');\r\nINSERT INTO person (personname, country) VALUES ('John TRAVOLTA', 'US');<\/pre>\nselect * from only person;\r\nselect * from only hashpartition_0;\r\nselect * from only hashpartition_1;\r\nselect * from only hashpartition_2;\r\nselect * from only hashpartition_3;\r\nselect * from only hashpartition_4;<\/pre>\n
select * from person;<\/pre>\n
SELECT tableoid::regclass AS source, count(*) FROM person GROUP BY source;<\/pre>\n
<\/p>\nSub-Partitioning in PostgreSQL<\/h2>\n
PostgreSQL Sub Partition Example<\/h4>\n
CREATE TABLE sales (\r\n id serial,\r\n sales_count int,\r\n sales_date date not null\r\n) PARTITION BY RANGE (sales_date);<\/pre>\n
CREATE TABLE sales_2018 PARTITION OF sales\r\nFOR VALUES FROM ('2018-01-01') TO ('2019-01-01');\r\n \r\nCREATE TABLE sales_2019 PARTITION OF sales\r\nFOR VALUES FROM ('2019-01-01') TO ('2020-01-01');\r\n \r\nCREATE TABLE sales_2020 PARTITION OF sales\r\nFOR VALUES FROM ('2020-01-01') TO ('2021-01-01');<\/pre>\nINSERT INTO sales (sales_count, sales_date) VALUES (50 , '2018-04-12');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (80 , '2019-12-14');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (70 , '2020-01-01');\r\nINSERT INTO sales (sales_count, sales_date) VALUES (75 , '2020-12-31');<\/pre>\n
Get Row Count in Each Partition<\/h4>\n
SELECT tableoid::regclass AS source, count(*) FROM sales GROUP BY source;<\/pre>\n
<\/p>\ncreate table sales_2021 partition of sales\r\nfor values from ('2021-01-01') to ('2022-01-01') partition by range(sales_date);<\/pre>\ncreate table sales_2021_01 partition of sales_2021\r\nfor values from ('2021-01-01') to ('2021-02-01');\r\ncreate table sales_2021_02 partition of sales_2021\r\nfor values from ('2021-02-01') to ('2021-03-01');\r\ncreate table sales_2021_03 partition of sales_2021\r\nfor values from ('2021-03-01') to ('2021-04-01');\r\ncreate table sales_2021_04 partition of sales_2021\r\nfor values from ('2021-04-01') to ('2021-05-01');\r\ncreate table sales_2021_05 partition of sales_2021\r\nfor values from ('2021-05-01') to ('2021-06-01');\r\ncreate table sales_2021_06 partition of sales_2021\r\nfor values from ('2021-06-01') to ('2021-07-01');\r\ncreate table sales_2021_07 partition of sales_2021\r\nfor values from ('2021-07-01') to ('2021-08-01');\r\ncreate table sales_2021_08 partition of sales_2021\r\nfor values from ('2021-08-01') to ('2021-09-01');\r\ncreate table sales_2021_09 partition of sales_2021\r\nfor values from ('2021-09-01') to ('2021-10-01');\r\ncreate table sales_2021_10 partition of sales_2021\r\nfor values from ('2021-10-01') to ('2021-11-01');\r\ncreate table sales_2021_11 partition of sales_2021\r\nfor values from ('2021-11-01') to ('2021-12-01');\r\ncreate table sales_2021_12 partition of sales_2021\r\nfor values from ('2021-12-01') to ('2022-01-01');<\/pre>\n<\/p>\n\\d+ sales_2021<\/code> command.<\/p>\n<\/p>\n