�ݺ�ߣ

1 ? 2018 Fujitsu Australia Software Technology
��Divide and Rule��
Partitioning in PostgreSQL11
Rajni Baliyan
December 07, 2018

Partitioning?
?Subdivide a parent table into a number of smaller child tables/partitions
https://www.elephind.com
https://www.americanantiquarian.org/rece
nt-acquisitions-newspaper-department

Agenda
? Partitioning in PostgreSQL
? Partitioning benefits
? When to partition?
? Exceptions
? When not to use partitioning?
? Partitioning limitations in PG10
? Partitioning Improvements in PG11
? Useful commands
? Limitations in PG11
? What��s next? PG12��
? PG native partitioning vs pg_partman
? Questions

Partitioning in PostgreSQL
PG10 PG11
LIST RANGE
partitioning partitioning
East Region
A
B
C
West Region
D
E
F
North Region
G
H
I
2017
2018
2019
HASH
partitioning
h1
h2
h3
Add
? Inheritance partitioning prior to PG10
? Declarative from PG10

Partitioning benefits
?Easy maintenance of big tables.
?Address performance issue because of data growth over the period of time.
?Improves query performance.
?Address I/O performance issues by keeping partitions on different tablespaces.
?Address storage issues- partitions can spin across multiple tablespaces and disk file
systems.
?Transparent to application.
?Best suited for applications where data growth is enormous and only recent data is
required �C IoT etc.

Partitioning benefits
FAST
? Queries will access only relevant partitions.
? Reporting queries access most or all of the data in an entire partition
? Better I/O
FLEXIBLE
? Easy maintenance- adding and removing of partition is easy.
? Archiving of historic data.
? Easy backup and restore of partition.
? Add new table as partition of existing partitioned table.
CHEAPER
? Maintenance of INDEXES
ATTENTION!!!
identifying right partitioning type and
partitioning key

When to partition?
? Some suggestions ,when to partition-
? Table size is very big.
? Data archiving is the requirement.

When to partition?
? Better I/O- when content of the table needs to be distributed across different types of Storage
devices to achieve better I/O
Tablespace on slow DISKTablespace on SAN
DISK
Sales_archiveSales
Sales_2020 Sales_2019 Sales_2018
Ind_1 Ind_2 Ind_3
Ind_0
Sales_2017
Ind_4
Ind_0
Ind_0 Ind_0Ind_0

Exceptions
?CHECK and NOT NULL constraints-
? inherit by partitions from partitioned tables.
? CHECK constraints marked ��NO INHERIT�� are not allowed to be created on partitioned tables.
?ONLY to add or drop a constraint-
? supported on partitioned table as long as there are no partitions.
? ONLY will result in an error as adding or dropping constraints on only the partitioned table, when partitions exist, is
not supported
?TRUNCATE ONLY on a partitioned table will always return an error.
?Columns should be same in partitioned table and partitions.

When not to use partitioning?
? Table size is not too big.
? No performance issues faced.
? Application is mostly read/write intensive.
? If partitioning need matches the exceptions.
? Partitioned key is not in WHERE clause of the query.
? When INDEX manageability is the known problem.

Partitioning limitations in PG10
Sales
Sales_2017
Ind_1
PRIMARY KEY
FOREIGN KEY
INDEX
UPDATE
Sales_def
SELECT * from sales where sale_year=2017;
SalesSales_2018
Ind_1

Partitioning limitations in PG10
? No INDEX, PRIMARY key, UNIQUE constraint, or exclusion constraint spanning all partitions
automatically.
? No support for HASH partitioning.
? No default partition.
? FOREIGN keys referencing partitioned tables are not supported
? No pruning of partitions during query. Results in poor performance.
? No row movement across partitions when doing UPDATE.
? Error while using the ON CONFLICT clause with partitioned tables will cause an error
? Trigger based rules.

Partitioning Improvements in PG11
Sales
Sales_2017
Ind_1
PRIMARY KEY
FOREIGN KEY
INDEX
UPDATE
Sales_def
SELECT * from sales where sale_year=2017;
Sales

Partitioning Improvements in PG11
? Support for PRIMARY KEY, FOREIGN KEY, indexes, and triggers on partitioned tables.
? Parent INDEX automatically applicable to partitioned tables.
? Allow a DEFAULT partition for non-matching rows.
? Partition by a hash key- hash partitioning.
? Row movement across partitions on UPDATE
? Improve SELECT performance through enhanced partition elimination strategies during query planning
and execution
? No more trigger functions to be created.
In general, partitions now have most of the capabilities of ordinary tables.

Let��s start with partitioning- RANGE
? Continuous data distribution based on predicted range of values.
? Create table ��emp�� and five partition by ��RANGE�� and insert some rows.
? CREATE TABLE emp ( emp_id int, emp_name text, joining_date date not null )
PARTITION BY RANGE (emp_id);
? CREATE TABLE emp_1000 PARTITION OF emp FOR VALUES FROM (1000) TO (3000);
? CREATE TABLE emp_3000 PARTITION OF emp FOR VALUES FROM (3000) TO (5000);
? INSERT INTO EMP (emp_id,emp_name,joining_date) VALUES (1001,'AA','2016-09-30');
? INSERT INTO EMP (emp_id,emp_name,joining_date) VALUES (3501,'AAB','2017-07-5');

? Sub-partitioning- adding new table with partitions to previously created partitioned table.
? Create parent table-
CREATE TABLE emp_1100(LIKE EMP) PARTITION BY RANGE(EMP_ID);
? Create two partitions-
CREATE TABLE emp_1100_11 PARTITION OF emp_1100 FOR VALUES FROM (11000) TO
(13000);
CREATE TABLE emp_1100_13 PARTITION OF emp_1100 FOR VALUES FROM (13000) TO
(15000);

? In PG11-
? PG10-

PG11- Automatic Index Creation
PG10 PG11
Sales_2019
Sales
Sales_2017 Sales_2018
Ind_1 Ind_2 Ind_3
Ind_0
Sales
Sales_2017 Sales_2018 Sales_2019
Ind_1 Ind_2 Ind_3
Ind_0
Sales_2020
Ind_4
Ind_0
Ind_0 Ind_0Ind_0

PG11- Automatic Index Creation
? PG10-
? Manual on each partition :
? fails on parent table :
? PG11-
? Query performance will improve as it has
to smaller data set having partition key in WHERE
Clause.
? CREATE INDEX now also possible
on parent.
? Cascade to each existing and new
partitions
? Attach Index- If same INDEX already exist.
? Create Index- if no INDEX .

PG11- Foreign Key Support
? PG10- No column as FOREIGN KEY in partitioned table
? PG11: FOREIGN KEYs are allowed. But no FK reference to the partitioned master table.

PG11- Row migration on UPDATE
? UPDATE statements can move a row across partition boundaries.
? This occurs when the update happens to affect a column that participates in defining the
boundaries.
? Frequently doing so might defeat the purpose of partitioning.
? Not available in PG10. Give error-
? In PG11- moves rows across partitions.

PG11- Hash partitioning
? Hash partitioning is a method to separate out information in a randomized way rather than
putting the data in the form of groups unlike RANGE partitioning.
? Divide rows (more or less) equally into multiple partitions
? Much useful for data ware house kind of application.
? Partitioning is based on module and remainder.
? An INSERT statement that does not match the hash value will fail when storing tuples directly on
a partition.

? SYNTAX:
CREATE TABLE <table_name> (col1 numeric, col2 text) PARTITION BY hash (hash_key);
CREATE TABLE part1_name PARTITION OF <table_name> FOR VALUES WITH (MODULUS 4,
REMAINDER 0);
? Note:
? MODULUS is number of partitions, and REMAINDER is number, 0 or more, but less than MODULUS
? MODULUS clause value > REMAINDER clause
? Number of partitions >= MODULUS value, else Insert error

? Advantages of HASH partitioning over RANGE partitioning.
? Not aware beforehand how much data will map into a given range.
? The sizes of range partitions would differ quite substantially or would be difficult to balance manually
? Avoid data skew in partitions.
? Performance features such as parallel DML, partition pruning, and partition-wise joins are important
? Maximize I/O throughput.
? Partition pruning and partition-wise joins on a partitioning key are important.

PG11- DEFAULT partition
? Default partition should exist prior to insert rows else result in error.
? With default partition- rows can be inserted outside of the defined range.
? It can be called as "catch all" partition
? Syntax:
? CREATE TABLE emp_default PARTITION OF emp DEFAULT;
? INSERT INTO EMP (emp_id,emp_name,joining_date) VALUES (940305,'AAS','2014-03-11');

? Trying to create partition for inserted row- will fail
? Create new partition for inserted rows �C
? Detach default? Create new? Move data? ? Attach new ? Reattach default
? ALTER TABLE emp DETACH PARTITION emp_default;
? CREATE TABLE emp_def_pr1 (like emp);
? INSERT INTO emp_def_pr1 (SELECT * FROM emp_default);

? Attach newly created partition to the parent ��emp�� table.
? ALTER TABLE emp ATTACH PARTITION emp_def_pr1 FOR VALUES FROM (800000) TO (1000000);
? New data inserted for defined bounds will move to new partition.
? INSERT INTO EMP (emp_id,emp_name,joining_date) VALUES (890305,'AAT','2018-07-21');
? SELECT * FROM emp;

? Re-attach default partition for future use.
? ALTER TABLE emp ATTACH PARTITION emp_default DEFAULT;
? d+ emp
? Test: INSERT INTO EMP (emp_id,emp_name,joining_date) VALUES (890305,'AAT','2018-07-21');

PG11- Partition pruning
? Partition pruning is a query optimization technique that improves performance for declaratively
partitioned tables.
? Significantly cheaper plan when enabled.
? This is possible by using parameter ��enable_partition_pruning��
? Can be set at session level-
? SET enable_partition_pruning=on; -- on�� by default

? With partition pruning enabled, the planner will examine the definition of each partition and
will include partitions meeting the query's WHERE clause.
? When enable_partition_pruning=on

? Without partition pruning, same query will scan each partition.
? When enable_partition_pruning=off

Useful commands
? Check table and partition description
? d+ name
? DETACH an existing partition.
? ALTER TABLE name DETACH PARTITION partition_name;
? ATTACH a new partition.
? ALTER TABLE name ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec |
DEFAULT };
? How rows are distributed?
? SELECT * FROM emp, LATERAL (SELECT relname FROM pg_class WHERE pg_class.oid =
emp.tableoid) AS table_name (emp) GROUP BY emp, emp_id, emp_name;
? Which partition contains row?
? SELECT relname FROM pg_class WHERE oid = (SELECT tableoid FROM <tablename> where
<condition>);

Limitations in PG11
? There is no way to create an exclusion constraint spanning all partitions- possible on individual partitions.
? FOREIGN keys referencing partitioned tables are not supported.
? When an UPDATE causes a row to move from one partition to another, there is a chance that another
concurrent UPDATE or DELETE misses this row.
? BEFORE ROW triggers, if necessary, must be defined on individual partitions, not the partitioned table.
? Mixing temporary and permanent relations in the same partition tree is not allowed.

What��s next? PG12��
? There��s still more to do here in the future.
? At the moment execution-time pruning only performs pruning of Append nodes. May be in
future we can have pruning for MergeAppend or for ModifyTable nodes (UPDATE/DELETE)
? Enhancement in partition pruning performance and views.
Never-the-less what we have for PG11 is a significant improvement over PG10!

PG native partitioning vs pg_partman
Feature 9.6 PG 10 PG 11 pg_partman
Declarative partitioning ? ? ? ?* RANGE
INSERT- auto routing ? ? ? ?* using triggers
UPDATE �Cauto routing ? ? ? ?
Foreign Key ? ? ?* ?
Default partitioning ? ? ? ?* on parent table
Hash partitioning ? ? ? ?
Partition level aggregation
/joins
? ? ?*
constraint should match both sides
?
Partition pruning ? ? ? ?
Trigger based ? ? ? ?
Automatic child creation ? ? ? ?* using triggers
Automatic privileges transfer
to
new and existing child
? ? ? ?* using separate
function

Your speaker
Rajni Baliyan
Database Support Manager
Fujitsu Enterprise Postgres / PostgreSQL
+61 2 9452 9017
rajnib@fast.au.fujitsu.com
postgesql.fastware.com
twitter.com/fujitsupostgres
linkedin.com/showcase/fujitsu-enterprtise-postgres

Thank you

Questions

�ݺ�ߣ

Divide and Rule partitioning in pg11

More Related Content

Divide and Rule partitioning in pg11