- Improved performance – data operations (CRUD) can be performed on a smaller volume of data, for example, in case of collecting data overtime, putting old data in separate partition might help with performance.
- Bulk create and delete can be efficient by adding or removing separate partitions.
- Time based partition can be helpful in cleaning old seldom-used data i.e. month based partition we can simply set a cron job for cleaning 12 month old partition, without effecting the table portion heavily in use for ADD, UPDATE, etc.
- Improved scalability – In case of very large tables, you can partition and have them hosted on a separate server.
- Horizontal partitioning (Sharding) – a table is split horizontally, such that each partition is a subset of the table, having the same schema (i.e. number of fields/columns).
- Vertical partitioning – a table is split on the fields/columns, such that each subset has separate schema. A common use-case for vertical partitioning is to partition table fields on the basis of pattern of use i.e. frequently accessed fields are to be grouped together, and the less frequently accessed are put in a separate partition.
- Create table, or select an existing one.
- Execute partitioning function or procedure
- Table trigger – to call the partition procedure.
- View for parent-child tables (optional)
Step 1 – Create Table
Step 2 – Trigger function
partition_date := to_char(NEW.created_datetime,'YYYY_MM');
partition := TG_RELNAME || '_' || partition_date;
IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
RAISE NOTICE 'A partition has been created %',partition;
EXECUTE 'CREATE TABLE ' || partition || ' () INHERITS (' || TG_RELNAME || ');';
EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING id;';
LANGUAGE plpgsql VOLATILE
Step 3 – Table trigger
AFTER INSERT ON partition_test
FOR EACH ROW EXECUTE PROCEDURE test_partition_function();
Step 4 – Viewing the partition (optional)
SELECT nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
nmsp_child.nspname AS child_schema,
child.relname AS child
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
WHERE parent.relname='partition_test' ;
Step 5 – Verification (optional)
insert in partition_test
Few more inserts for the same month
insert into partition_test values (3, '2018-01-29');
Insert for next month
For Django ORM users there’s a very good suite ‘Architect‘ for enhancing the ORM capability for complex database task (i.e. partitioning). Architect is simple to use, updated, and currently supports MySQL and PostgreSQL.
Updated 10-2020 – there’s a new package django-postgres-extra,
- PostgreSQL 10 or newer.
- Django 2.0 or newer (including 3.0, 3.1).
- Python 3.6 or newer.