Re: Feature suggestions (long)

On Sat, May 17, 2003 at 04:04:11AM -0700, Don Baccus wrote:
> On Saturday 17 May 2003 10:51 am, Matthew Kirkwood wrote:
> > On Sat, 17 May 2003, Alvaro Herrera wrote:
> > > > > 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?
> > >
> > > Maybe one can put different partitions in different tablespaces?
> >
> > One can.  The tablespace a partition is in can even be
> > offline if Oracle can prove that a query doesn't require
> > that partition.
> 
> People use this feature for warehousing old data that they don't want to purge 
> from the database.   For very large databases (of course that definition 
> changes with each new generation of computer) this can greatly improve the 
> performance of queries on the active portion of the data.
This would be very useful for http://stats.distributed.net. Right now
I'm trying to 'partition' our main (120M row) table by project_id.
Sybase handled this very well, because it has good support for
clustered indexes/index organized tables. PGSQL lacks this, which means
large reads from that table are much more painful than on sybase,
because you either tablescan (ugh) or go though the overhead of an index
read for every row retrieved (not much better).

Partitioning would fit the bill here perfectly (though in my case
good/true clustering would work just as well).
-- 
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"