Thread: Table Partitioning is in 8.1

Table Partitioning is in 8.1

From
Simon Riggs
Date:
Table Partitioning is in 8.1

I've just read Peter Eisentraut's presentation to the Dutch gov (very
good BTW). On the last page I read that Table Partitioning is a future
for PostgreSQL....which is strange because Constraint Exclusion is an
8.1 feature.

I've had five other people ask about it too in the last 2 weeks, and all
have been surprised that this has now been accepted for 8.1

Is it possible that the Release Notes do not fully explain the
Constraint Exclusion feature? Or is it the consensus that it works but
not quite well enough to make a song and dance about yet?

Certainly, my docs submission is overdue...

Best Regards, Simon Riggs




Re: Table Partitioning is in 8.1

From
Martijn van Oosterhout
Date:
On Wed, Sep 21, 2005 at 06:10:15PM +0100, Simon Riggs wrote:
> Table Partitioning is in 8.1
>
> I've just read Peter Eisentraut's presentation to the Dutch gov (very
> good BTW). On the last page I read that Table Partitioning is a future
> for PostgreSQL....which is strange because Constraint Exclusion is an
> 8.1 feature.

I think it depends on how closely you relate "Table Partitioning" and
"Constraint Exclusion".  While you can use the latter to implement the
former, I think people expect a database that supports "Table
Partitioning" to have a CREATE PARTITION command (or something
similar).

> Is it possible that the Release Notes do not fully explain the
> Constraint Exclusion feature? Or is it the consensus that it works but
> not quite well enough to make a song and dance about yet?

IMHO, I think Constraint Exclusion is more useful in general and once
it is in and tested future releases will have a partitioning system
based upon it.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Re: Table Partitioning is in 8.1

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.com> writes:
> Is it possible that the Release Notes do not fully explain the
> Constraint Exclusion feature? Or is it the consensus that it works but
> not quite well enough to make a song and dance about yet?

I hardly think that the existing constraint-exclusion code is enough for
us to claim we "support table partitioning".  There's too much grunt
work that the DBA still has to do to set up a partitioning arrangement.
        regards, tom lane


Re: Table Partitioning is in 8.1

From
Hannu Krosing
Date:
On K, 2005-09-21 at 18:10 +0100, Simon Riggs wrote:
> Table Partitioning is in 8.1
> 
> I've just read Peter Eisentraut's presentation to the Dutch gov (very
> good BTW). On the last page I read that Table Partitioning is a future
> for PostgreSQL....which is strange because Constraint Exclusion is an
> 8.1 feature.
>
> I've had five other people ask about it too in the last 2 weeks, and all
> have been surprised that this has now been accepted for 8.1
> 
> Is it possible that the Release Notes do not fully explain the
> Constraint Exclusion feature? Or is it the consensus that it works but
> not quite well enough to make a song and dance about yet?

It sure makes implementing Table Partitioning using inheritance much
simpler and efficient, and should be documented as such.

The release notes should certainly mention the words "Table
Partitioning" together with Constraint Exclusion so that people looking
(grepping) for partitioning will find it.

> Certainly, my docs submission is overdue...

This could be one of the reasons CE is not recognised as a feature
supporting table partitioning. ;)

-- 
Hannu Krosing <hannu@skype.net>



Re: Table Partitioning is in 8.1

From
Hannu Krosing
Date:
On K, 2005-09-21 at 15:39 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Is it possible that the Release Notes do not fully explain the
> > Constraint Exclusion feature? Or is it the consensus that it works but
> > not quite well enough to make a song and dance about yet?
> 
> I hardly think that the existing constraint-exclusion code is enough for
> us to claim we "support table partitioning".  There's too much grunt
> work that the DBA still has to do to set up a partitioning arrangement.

We can probably claim that :

"Postgres has *low-level* support for table partitioning using and
Inheritance, Rules/Triggers and Constraint Exclusion , enabling DBAs to
set up complex partitioning schemes"

There certainly is much grunt work, but then we can get a better CE
performance than just "PARTITION TABLE TTT BY XXX" would give by having
constraints on multiple fields in some/all partitions, both overlapping
and not, possibly resulting in a better overall system. That is what
"low-level" in the above statement means. 

-- 
Hannu Krosing <hannu@skype.net>



Re: Table Partitioning is in 8.1

From
Simon Riggs
Date:
On Wed, 2005-09-21 at 15:39 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Is it possible that the Release Notes do not fully explain the
> > Constraint Exclusion feature? Or is it the consensus that it works but
> > not quite well enough to make a song and dance about yet?
> 
> I hardly think that the existing constraint-exclusion code is enough for
> us to claim we "support table partitioning".  There's too much grunt
> work that the DBA still has to do to set up a partitioning arrangement.

So you think the DBA can do partitioning? Good.

Setting up partitioning in Oracle or SQLServer2005 requires lots of
syntax and multiple commands. There are fewer commands with PostgreSQL
and they are ISO/ANSI compliant also.

I think there is much still left to do with partitioning, so I would be
the first to say that this is only the beginning. I know you are wary of
overstating capabilities; so am I, but it looks like we differ slightly
on where to draw the line.

On reflection, the only changes I suggest are:

1) the phrase "This allows for a type of table partitioning" have the
word "basic" inserted within it to become: "This allows for a basic type
of table partitioning"

2) placing CE as a major feature of 8.1 - many people regard it at least
as highly as other optimizations, though this is subjective based upon
their application requirements

Suggestion (2) might be seen as some kind of vanity, so having raised
the issue I'll leave the floor open to others to agree or not.

Best Regards, Simon Riggs




Re: Table Partitioning is in 8.1

From
"Jim C. Nasby"
Date:
On Thu, Sep 22, 2005 at 10:11:50AM +0100, Simon Riggs wrote:
> On Wed, 2005-09-21 at 15:39 -0400, Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.com> writes:
> > > Is it possible that the Release Notes do not fully explain the
> > > Constraint Exclusion feature? Or is it the consensus that it works but
> > > not quite well enough to make a song and dance about yet?
> > 
> > I hardly think that the existing constraint-exclusion code is enough for
> > us to claim we "support table partitioning".  There's too much grunt
> > work that the DBA still has to do to set up a partitioning arrangement.
> 
> So you think the DBA can do partitioning? Good.
> 
> Setting up partitioning in Oracle or SQLServer2005 requires lots of
> syntax and multiple commands. There are fewer commands with PostgreSQL
> and they are ISO/ANSI compliant also.

Actually, IIRC it takes 2 commands; one to initially setup the
partitioning and one to create new partitions as needed. 3 commands if
you count DROP PARTITON.

It's been a while since I looked at what you've done, but I seem to
recall needing to manually maintain rules every time you create a new
partition.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: Table Partitioning is in 8.1

From
"Joshua D. Drake"
Date:
> 
> On reflection, the only changes I suggest are:
> 
> 1) the phrase "This allows for a type of table partitioning" have the
> word "basic" inserted within it to become: "This allows for a basic type
> of table partitioning"

How about just: Initial support for table partitioning. Yes it is 
non-committal but that is a good thing since nobody 100% agrees anyway.

Sincerely,

Joshua D. Drake

> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq


-- 
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


Re: Table Partitioning is in 8.1

From
Bruce Momjian
Date:
Word "basic" added.

---------------------------------------------------------------------------

Joshua D. Drake wrote:
> > 
> > On reflection, the only changes I suggest are:
> > 
> > 1) the phrase "This allows for a type of table partitioning" have the
> > word "basic" inserted within it to become: "This allows for a basic type
> > of table partitioning"
> 
> How about just: Initial support for table partitioning. Yes it is 
> non-committal but that is a good thing since nobody 100% agrees anyway.
> 
> Sincerely,
> 
> Joshua D. Drake
> 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> > 
> >                http://www.postgresql.org/docs/faq
> 
> 
> -- 
> Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
> PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
> Managed Services, Shared and Dedicated Hosting
> Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Table Partitioning is in 8.1

From
Bruce Momjian
Date:
Simon Riggs wrote:
> On Wed, 2005-09-21 at 15:39 -0400, Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.com> writes:
> > > Is it possible that the Release Notes do not fully explain the
> > > Constraint Exclusion feature? Or is it the consensus that it works but
> > > not quite well enough to make a song and dance about yet?
> > 
> > I hardly think that the existing constraint-exclusion code is enough for
> > us to claim we "support table partitioning".  There's too much grunt
> > work that the DBA still has to do to set up a partitioning arrangement.
> 
> So you think the DBA can do partitioning? Good.
> 
> Setting up partitioning in Oracle or SQLServer2005 requires lots of
> syntax and multiple commands. There are fewer commands with PostgreSQL
> and they are ISO/ANSI compliant also.
> 
> I think there is much still left to do with partitioning, so I would be
> the first to say that this is only the beginning. I know you are wary of
> overstating capabilities; so am I, but it looks like we differ slightly
> on where to draw the line.
> 
> On reflection, the only changes I suggest are:
> 
> 1) the phrase "This allows for a type of table partitioning" have the
> word "basic" inserted within it to become: "This allows for a basic type
> of table partitioning"
> 
> 2) placing CE as a major feature of 8.1 - many people regard it at least
> as highly as other optimizations, though this is subjective based upon
> their application requirements
> 
> Suggestion (2) might be seen as some kind of vanity, so having raised
> the issue I'll leave the floor open to others to agree or not.

Having heard no comments, I have moved this item up into the main
features section of the release notes, and reworded it:
  Improve performance for partitioned tables (Simon)
         The new constraint_exclusion configuration parameter avoids         lookups on child tables where constraints
indicate        that no matching rows exist in the child table.
 
         This allows for a basic type of table partitioning. If child         tables store separate key ranges and this
is        enforced using appropriate "CHECK" constraints, the optimizer         will skip child table accesses when the
       constraint guarantees no matching rows exist in the child table.
 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Table Partitioning is in 8.1

From
Simon Riggs
Date:
On Thu, 2005-09-22 at 14:37 -0500, Jim C. Nasby wrote:
> On Thu, Sep 22, 2005 at 10:11:50AM +0100, Simon Riggs wrote:
> > On Wed, 2005-09-21 at 15:39 -0400, Tom Lane wrote:
> > > Simon Riggs <simon@2ndquadrant.com> writes:
> > > > Is it possible that the Release Notes do not fully explain the
> > > > Constraint Exclusion feature? Or is it the consensus that it works but
> > > > not quite well enough to make a song and dance about yet?
> > > 
> > > I hardly think that the existing constraint-exclusion code is enough for
> > > us to claim we "support table partitioning".  There's too much grunt
> > > work that the DBA still has to do to set up a partitioning arrangement.
> > 
> > So you think the DBA can do partitioning? Good.
> > 
> > Setting up partitioning in Oracle or SQLServer2005 requires lots of
> > syntax and multiple commands. There are fewer commands with PostgreSQL
> > and they are ISO/ANSI compliant also.
> 
> Actually, IIRC it takes 2 commands; one to initially setup the
> partitioning and one to create new partitions as needed. 3 commands if
> you count DROP PARTITON.

Which is what it takes in PostgreSQL.

> It's been a while since I looked at what you've done, but I seem to
> recall needing to manually maintain rules every time you create a new
> partition.

All current implementations I am aware of require Declarative statements
each time a new partition is started.

But there is no *need* to create RULEs. You would need to do that if you
wanted automatic routing of INSERT statements. The current PostgreSQL
implementation does not do this, and yes, this would require creating a
set of Rule statements to do this - though possible to do this
automatically with a simple script. My experience with Oracle has been
that one loads into specifically identified partitions, so the loading
case for PostgreSQL is essentially identical to the common case for
Oracle. My experience may not be everybody's, so I may be off-base on
this, but I've not put a priority on solving that issue. Do people
consider that aspect essential to the wider use of partitioning?

I don't consider CE to be complete, any more than any newly introduced
major feature set, but IMHO the important first order functionality is
now available in PostgreSQL.

I'm looking at probably Tuesday to submit first draft docs.

Best Regards, Simon Riggs






R: Table Partitioning is in 8.1

From
"Paolo Magnoli"
Date:
Hi, I seem to recall that in Oracle you load into specific partitions
without specifically naming them in insert statements (in other words you
insert into table, the engine redirects data to the corrisponding
partition), I quickly looked at postgresql partitioning and it seems to me
that you need to insert into a specified derived table (unless you set up
rules).
It would be good to have an insert behaviour similar to Oracle by default.
Also I see that the original table is always scanned, partition exclusion
happens only on the derived tables, is this correct?
Regards

paolo

> -----Messaggio originale-----
> Da: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]Per conto di Simon Riggs
> Inviato: venerdì 23 settembre 2005 11.51
> A: Jim C. Nasby
> Cc: Tom Lane; pgsql-hackers@postgresql.org
> Oggetto: Re: [HACKERS] Table Partitioning is in 8.1
>
>
> On Thu, 2005-09-22 at 14:37 -0500, Jim C. Nasby wrote:
> > On Thu, Sep 22, 2005 at 10:11:50AM +0100, Simon Riggs wrote:
> > > On Wed, 2005-09-21 at 15:39 -0400, Tom Lane wrote:
> > > > Simon Riggs <simon@2ndquadrant.com> writes:
> > > > > Is it possible that the Release Notes do not fully explain the
> > > > > Constraint Exclusion feature? Or is it the consensus that
> it works but
> > > > > not quite well enough to make a song and dance about yet?
> > > >
> > > > I hardly think that the existing constraint-exclusion code
> is enough for
> > > > us to claim we "support table partitioning".  There's too much grunt
> > > > work that the DBA still has to do to set up a partitioning
> arrangement.
> > >
> > > So you think the DBA can do partitioning? Good.
> > >
> > > Setting up partitioning in Oracle or SQLServer2005 requires lots of
> > > syntax and multiple commands. There are fewer commands with PostgreSQL
> > > and they are ISO/ANSI compliant also.
> >
> > Actually, IIRC it takes 2 commands; one to initially setup the
> > partitioning and one to create new partitions as needed. 3 commands if
> > you count DROP PARTITON.
>
> Which is what it takes in PostgreSQL.
>
> > It's been a while since I looked at what you've done, but I seem to
> > recall needing to manually maintain rules every time you create a new
> > partition.
>
> All current implementations I am aware of require Declarative statements
> each time a new partition is started.
>
> But there is no *need* to create RULEs. You would need to do that if you
> wanted automatic routing of INSERT statements. The current PostgreSQL
> implementation does not do this, and yes, this would require creating a
> set of Rule statements to do this - though possible to do this
> automatically with a simple script. My experience with Oracle has been
> that one loads into specifically identified partitions, so the loading
> case for PostgreSQL is essentially identical to the common case for
> Oracle. My experience may not be everybody's, so I may be off-base on
> this, but I've not put a priority on solving that issue. Do people
> consider that aspect essential to the wider use of partitioning?
>
> I don't consider CE to be complete, any more than any newly introduced
> major feature set, but IMHO the important first order functionality is
> now available in PostgreSQL.
>
> I'm looking at probably Tuesday to submit first draft docs.
>
> Best Regards, Simon Riggs
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>



Re: R: Table Partitioning is in 8.1

From
Simon Riggs
Date:
On Fri, 2005-09-23 at 12:30 +0200, Paolo Magnoli wrote:

> It would be good to have an insert behaviour similar to Oracle by default.

OK, thanks.

> Also I see that the original table is always scanned, partition exclusion
> happens only on the derived tables, is this correct?

Yes, though if you avoid placing any rows in that table it is quick.

I expect to remove that restriction in 8.2 by declarative SQL.

Best Regards, Simon Riggs




Re: R: Table Partitioning is in 8.1

From
Bricklen Anderson
Date:
Paolo Magnoli wrote:
> Hi, I seem to recall that in Oracle you load into specific partitions
> without specifically naming them in insert statements (in other words you
> insert into table, the engine redirects data to the corrisponding
> partition), 

This is correct
-- 
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________