When you need to store large amount of indexed data in one table, you hit the problem of slow inserts caused by large B-tree needing to be balanced every time you add data. PostgreSQL provides mechanism allowing to split data across multiple sub-tables. This reduces index size considerably and makes speed of inserting new data more consistent.
As per documentation, partitioning should be considered in cases where amount of data in one table exceeds amount of RAM available.
The generali approach is to create master table without any constraints and indexes. For example:
The next step is to create sub-tables holding real data. The key to success is the INHERITS keyword. It creates relation between child and parent table allowing database to gather data from all child tables as one result:
By creating multiple tables like this you effectively create partitioned table. Issuing:
returns all data from all child tables (created with
INHERITS). It is important to create CHECK constraints as query planner takes them into consideration when selecting child tables as data sources.
Also remember not to insert values directly into master table. Place appropriate data in relevant child tables.
Automating the process
It is easy to make data insertions and child table creation process automatic with a trigger function:
And a trigger:
This creates the trigger responsible for automatic creation of child tables with all necessary constraints and indexes. With this trigger you can perform
INSERT statements directly on master table.