Re: Feature suggestions (long)

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > I'm going suggest a feature like what Oracle calls "partitions" and later on
> > something with indexes. The idea is to generate some discussion to see if
> > they are worthy of being added to the TODO list.
>
> Why bother? Make partial indexes corresponding to what you are calling
> the partitions of the table, and (I claim) you can get every possible
> benefit of a partitioning scheme. Plus more, because there's nothing
> constraining the partial indexes to be nonoverlapping, so you can get
> efficient plans for sets of queries that no partitioning scheme would
> win for.

Partial indexes get some of the advantages of partitioned tables, but
certainly not all.

A big advantage of partitioned tables is being able to manage the data in
whole chunks very efficiently.

. Archive all records for a single year becomes a seqential scan instead of an
index scan.

. Delete all records for a single year becomes a truncate table instead of an
update.

This will become even more important when postgres gets some other features
like being able to set the physical storage location of each table and the
equivalent of what oracle calls "transportable tablespaces".

Then you can move individual partitions to slow read-only media and keep the
currently active partition on the fast read-write media. Or back up the old
partition and drop it but load it on the DSS system using a simple binary
copy.

This isn't theoretical. I've done exactly this before. We had a table that
grew by about a million records per day. When we used dml statements to
archive the old records to the DSS system it took over a day and frequently
failed. When we switched to partitioned tables we were able to run it reliably
daily during prime time without impacting performance and have up-to-date data
in the DSS system.

--
greg