James,

> I'm not sure what Oracle has to do with any of this.  If I wanted to use
> Oracle, I would buy Oracle. 

Good.  Your original post, which appeared to propose carbon-copying a number 
of features from Oracle -- I didn't necessarily read it that way, but several 
other people did, including some of the developers you will want to recruit.    
You are not the first person to put forward some of these ideas, and your 
predicessors tended to follow the line "but if Oracle does it that way, it 
must be good."   So it's a knee-jerk reaction thing.

AFAIK, Tom was just warning you that your feature proposals need to be backed 
by arguments/evidence that they will actually improve performance or expand 
capabilities for PostgreSQL in some meaningful way.   You seem prepared to do 
that, so we shouldn't have any disagreements in that way.

> In a nutshell, the features on my short list are all about heap
> management (e.g. partitioning).  This is really important when databases
> reach a certain size, but something for which Postgres has almost no
> support.  

heap management == table partitioning?

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 (btw, Gavin 
could use sponsorship on that one, I think).  Can you make your case to 
me/the list?   So far, the only arguments we've gotten on this list have been 
of the "Oracle does it that way" variety so it'd be interesting to see 
something concrete.

Now, query partitioning is something I think everyone is interested in, and 
would very much like to see someone implement.  

> I've gotten the green light (and many responses from people interested
> in doing it) to start writing up RFQs for specific features, which I
> will post to the pg-hackers list.  It is all stuff previously determined
> to be doable within the current PostgreSQL framework, and just requiring
> some work that my company is willing to help pay for.

Cool.   I look forward to seeing the fruits of this effort.

 From my perspective, the other "oracle-killer" that we're missing includes 
some isolated but difficult improvements in the Query Planner necessary to 
pass the TPC benchmarks.   I'd be happy to discuss those as well, if you 
like.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Sat, 22 Nov 2003 11:54:45 -0800
Josh Berkus <josh@agliodbs.com>
Re: Sponsoring enterprise features

Josh Berkus <josh@agliodbs.com> writes:

> 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 (btw, Gavin 
> could use sponsorship on that one, I think).  Can you make your case to 
> me/the list?   So far, the only arguments we've gotten on this list have been 
> of the "Oracle does it that way" variety so it'd be interesting to see 
> something concrete.

Well probably everyone who wants it is saying things of the form "they were a
useful with Oracle because...". Which isn't the same thing as "Oracle does it
this way". I don't particularly care how partitioned tables are *implemented*,
only the net effect. You can think of them as an abstraction over inherited
tables that let the database guarantee your data integrity and offer query
optimizations in a way it cannot if you build it by hand.  

I know for us they were an absolute godsend. The main advantages over a single
monolithic table even with partial indexes are:

1) Being able to load and unload parts of the table quickly.


   Adding and removing a partition is basically a DDL operation, not DML. It
   doesn't have to visit every tuple and mark it deleted or added. It just has
   to add or remove the entire partition to the structure.

   Partitioned tables are frequently used for aging out old data. The common
   example is of having a partition per month and keeping 3-12 months of data.
   We had a more extreme case where we had one partition per day and kept 21
   days of data. 
   
   When we implemented partitioned tables the time to archive and delete the
   old data went from taking most of the night and killing production
   performance to effectively instantaneous and we were able to run it at peak
   time.

2) Being able to do a sequential scan of a partition. 

   Sequential scans are faster than index scans. Sometimes much faster.
   Partial indexes are nice but when they cover 10-20% of your table scanning
   them is much slower than a sequential scan of a partition.


As for inherited tables. Well, I would expect a partitioned tables scheme to
be implemented using inherited tables or just using views. You could jury-rig
it today using these tools, it would just be very awkward and fragile. The
original Oracle implementation in Oracle 7 was implemented much the same way
using views. They were a complete hack and required lots of manual tweaking
though.


The point of partitioned tables is

a) The database ensures tuples go into the correct partition. If you used a
   manually constructed view or inherited tables you would always run the risk
   of inserting into the wrong partition which would break your data
   integrity.

b) The database automatically optimizes queries to query the correct
   partitions. It detects clauses in the query much like partial indexes so
   you don't have to tweak every query by hand and the database can skip
   clauses that match the partition clause exactly. Also this is a prime
   opportunity for the database to introduce parallel queries because each
   partition can be accessed independently.


-- 
greg


22 Nov 2003 17:24:08 -0500
Greg Stark <gsstark@mit.edu>
Re: Sponsoring enterprise features
On Sat, Nov 22, 2003 at 11:54:45AM -0800, Josh Berkus wrote:

> > In a nutshell, the features on my short list are all about heap
> > management (e.g. partitioning).  This is really important when databases
> > reach a certain size, but something for which Postgres has almost no
> > support.  
> 
> heap management == table partitioning?

It was just an example.  Another example could be a B-Tree sorted heap
(like an index, but with the whole tuple at the leaf pages instead of a
pointer to the heap).  There was a thread about this some time ago.
It seemed a good idea ...

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Para tener mï¿½s hay que desear menos"

Sat, 22 Nov 2003 23:39:34 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Sponsoring enterprise features