PostgreSQL for Data Architects

Partitioned tables

We saw that materialized views can be used to capture summary or preaggregated data so that instead of scanning large volumes of data in huge tables, we can scan a small table to get the data we want.

Table partitioning involves breaking a huge table into a number of small tables so that fewer blocks need to be scanned to retrieve data. When we create partitions, we use constraints to ensure that only specific datasets are stored in a partition. PostgreSQL's query planner can use these constraints to eliminate scanning some partitions. This feature is called constraint exclusion (check constraint on a table/partition tells the PostgreSQL planner that specific values will not be there in the table/partition. So, the planner can ...