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 avoid scanning that table).

Compared to the indexes on the big single table, indexes on the partitions will be smaller, increasing the chances that they fit into memory.

We can also use partitioning to implement a tiered storage. Data that is less frequently...