Re: Sponsoring enterprise features

Zeugswetter Andreas SB SD kirjutas E, 24.11.2003 kell 13:16:
> 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 with
> a fast loading utility (e.g. loading without index, 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

Partial indexes don't solve the basic clustering problem (need for
reading too many pages).

Using inherited tables for this somehow seems conceptually wrong, though
this may solve the clustering problem. Also, indexes don't currently
cover child tables, but this may be thought of as an implementation
deficiency.

But some mechanisms used for inheritance could likely be used as basis
for partitioning.

> 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])

We kind of have these - a unique index can nicely be built over table
stored in several 1GB files. Now we must just make sure that seqscans
know about the gaps if files are not full 1GB in size.

BTW, does VACUUM FULL shrink individual files or will it move tuples
between files when shrinking the storage ?

Are interfaces to sparse files portable enough that we could use these ?

> - partition elimination (imho we should use check constraints for that)

or partial indexes

or smart clustering which can make use of several partitions depending
on the possition of the tuple in the index(es) used for clustering.

> - physical backups :-)

Do you mean read-only partitions which can come and go?

> - tablespaces ?

Tablespaces should be an orthogonal feature, mainly about efficient
storage. We could like to spread partitions of the same table over
several tablespaces.

> Note, that these would all be useful for non partitioning
> use cases also.

True.

---------------
Hannu