PostgreSQL/Partitioning

If you have a table with a very huge amount of data, it may be helpful to scatter the data to different physical tables which share a common data structure. In such use cases, where DML statements concern only one of those physical tables, you can get great performance benefits from partitioning. Typically this is the case, if there is any timeline or a geographical distribution of the values of a column.

Declarative-partitioning-syntax: since version 10
Postgres 10 introduced a declarative partition-defining-syntax in addition to the previous table-inheritance-syntax. With this syntax the necessity to define an additional trigger disappears, but in comparision to the previous solution the functionality stays unchanged.

First, you define a master table containing a partitioning methode which is  in this example:

Next, you create partitions with the same structure as the master and ensure, that only rows within the expected data range can be stored there. Those partitions are conventional, physical tables.

Table-inheritance-syntax
First, you define a master table, which is a conventional table.

Next, you create partitions with the same structure as the master table by using the table-inheritance mechanism. Additionally you must ensure that only rows within the expected data range can be stored in the derived tables.

You need a function, which transfers rows into the appropriate partition.

The function is called by a trigger.

Further Steps
It's a good idea to create an index.

Many DML statements like  act only on one partition and can ignore all the others. This is very helpfull especially in such cases where a full table scan becomes necessary. The query optimizer has the chance to generate execution plans which avoid scanning unnecessary partitions.

In the shown example new rows will mainly go to the newest partition. After some years you can drop old partitions as a whole. This shall be done with the command  - not with a   command. The  command is much faster than the   command as it removes the complete partition in one single step instead of touching every single row.