Extending inheritance – table partitioning

Table partitioning is an approach specific to PostgreSQL that extends inheritance to model tables that typically do not vary from each other in the available fields, but where the child tables represent logical partitioning of the data based on a variety of factors, be it time, value ranges, classifications, or, in our case, spatial relationships. The advantages of partitioning include improved query performance due to smaller indexes and targeted scans of data, bulk loads, and deletes that bypass the costs of maintenance functions like VACUUM. It can thus be used to put commonly used data on a faster and more expensive storage, and the remaining data on a slower and cheaper storage. In combination with PostGIS, we get the novel power of spatial partitioning, which is a really powerful feature for large datasets.

Getting ready

We could use many examples of large datasets that could benefit from partitioning. In our case, we will use a contour dataset...