Database Reference
In-Depth Information
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
wherethechildtablesrepresentlogicalpartitioningofthedatabasedonavarietyof
factors,beittime,valueranges,classifications,or,inourcase,spatialrelationships.
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
dataonafasterandmoreexpensivestorage,andtheremainingdataonaslowerand
cheaperstorage.IncombinationwithPostGIS,wegetthenovelpowerofspatialpar-
titioning, which is a really powerful feature for large datasets.
Getting ready
Wecouldusemanyexamplesoflargedatasetsthatcouldbenefitfrompartitioning.In
ourcase,wewilluseacontourdataset.Contoursareusefulwaystorepresentterrain
data, as they are well established in use, and thus commonly interpreted. Contours
can also be used to compress terrain data into linear representations, thus allowing
them to be shown in conjunction with other data easily.
Theproblemis,thestorageofcontourdatacanbequiteexpensive.Two-footcontours
forasingleUScountycantake20to40GB,andstoringsuchdataforalargerarea
such as a region or nation can become quite prohibitive from the standpoint of ac-
cessing the appropriate portion of the dataset in an efficient way.
How to do it...
Thefirststepinthiscasemaybetopreparethedata.Ifwehadamonolithiccontour
tablecalled cuy_contours_2 ,wecouldchoosetoclipthedatatoaseriesofrect-
anglesthatwouldserveasourtablepartitions;inthiscase, chp02.contour_clip ,
using the following query:
Search WWH ::




Custom Search