Main needs partitioning is useful for:
- partition elimination for queries (e.g. seq scans only scan relevant partitions)
- deleting/detaching huge parts of a table in seconds
- attaching huge parts to a table in seconds (that may have been loaded witha fast loading utility (e.g. loading
withoutindex, prebuilding indexes,attaching table + prebuilt partitioned indexes))
- achieving [heap and index] per page data locality (for better cache rates)
- allowing partial restores (for defect disks) while the rest of the db is still online
- in pg, allowing partial vacuums (only partitions that see changes)
People needing those features usually have data with more than 10-50 Gb per
partition.
> I'm a little unclear, personally, about what can be accomplished through table
> partitioning that we can't currently do through partial indexes and inherited
> tables, especially after Gavin finishes his tablespaces patch
Well, sure the goal needs to be to make use of what already exists,
but a few things are still missing, e.g.:- unique indexes, that span the hierarchy (and do not contain the partitioning
column[s])-partition elimination (imho we should use check constraints for that)- physical backups :-)- tablespaces ?
Note, that these would all be useful for non partitioning
use cases also.
Andreas
