Thread: Partitioning

Partitioning

From
Arnau
Date:
Hi all,

   I'm not sure if this question fits in the topic of this list.

   I'm interested in partitioning and it's the first time I'd use it.
There is an issue I don't know how you handle it. Lets say I'm
interested in store monthly based statistical data like the example of
http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html. What I
don't like of this approach is that the monthly tables, rules... must be
created "manually" or at least I haven't found any other option.

   My question is how do you manage this? do you have a cron task that
creates automatically these monthly elements (tables, rules, ... ) or
there is another approach that doesn't require external things like cron
  only PostgreSQL.
--
Arnau

Re: Partitioning

From
"Mikael Carneholm"
Date:
Take a look at the set of partitioning functions I wrote shortly after
the 8.1 release:

http://www.studenter.hb.se/~arch/files/part_functions.sql

You could probably work something out using those functions (as-is, or
as inspiration) together with pgAgent
(http://www.pgadmin.org/docs/1.4/pgagent.html)

/Mikael

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of Arnau
> Sent: den 5 januari 2007 12:02
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Partitioning
>
> Hi all,
>
>    I'm not sure if this question fits in the topic of this list.
>
>    I'm interested in partitioning and it's the first time I'd use it.
> There is an issue I don't know how you handle it. Lets say I'm
> interested in store monthly based statistical data like the example of
> http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html. What
I
> don't like of this approach is that the monthly tables, rules... must
be
> created "manually" or at least I haven't found any other option.
>
>    My question is how do you manage this? do you have a cron task that
> creates automatically these monthly elements (tables, rules, ... ) or
> there is another approach that doesn't require external things like
cron
>   only PostgreSQL.
> --
> Arnau
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


Re: Partitioning

From
"Jim C. Nasby"
Date:
BTW, someone coming up with a set of functions to handle partitioning
for the general 'partition by time' case would make a GREAT project on
pgFoundry.

On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
> Take a look at the set of partitioning functions I wrote shortly after
> the 8.1 release:
>
> http://www.studenter.hb.se/~arch/files/part_functions.sql
>
> You could probably work something out using those functions (as-is, or
> as inspiration) together with pgAgent
> (http://www.pgadmin.org/docs/1.4/pgagent.html)
>
> /Mikael
>
> > -----Original Message-----
> > From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-
> > owner@postgresql.org] On Behalf Of Arnau
> > Sent: den 5 januari 2007 12:02
> > To: pgsql-performance@postgresql.org
> > Subject: [PERFORM] Partitioning
> >
> > Hi all,
> >
> >    I'm not sure if this question fits in the topic of this list.
> >
> >    I'm interested in partitioning and it's the first time I'd use it.
> > There is an issue I don't know how you handle it. Lets say I'm
> > interested in store monthly based statistical data like the example of
> > http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html. What
> I
> > don't like of this approach is that the monthly tables, rules... must
> be
> > created "manually" or at least I haven't found any other option.
> >
> >    My question is how do you manage this? do you have a cron task that
> > creates automatically these monthly elements (tables, rules, ... ) or
> > there is another approach that doesn't require external things like
> cron
> >   only PostgreSQL.
> > --
> > Arnau
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Re: Partitioning

From
Erik Jones
Date:
On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
>> Take a look at the set of partitioning functions I wrote shortly after
>> the 8.1 release:
>>
>> http://www.studenter.hb.se/~arch/files/part_functions.sql
>>
>> You could probably work something out using those functions (as-is, or
>> as inspiration) together with pgAgent
>> (http://www.pgadmin.org/docs/1.4/pgagent.html)
>>
>> /Mikael
>>
Those are  some great functions.

--
erik jones <erik@myemma.com>
software development
emma(r)


Re: Partitioning

From
"Jeremy Haile"
Date:
I really wish that PostgreSQL supported a "nice" partitioning syntax
like MySQL has.

Here is an example:
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
  PARTITION BY RANGE( YEAR(purchased) ) (
  PARTITION p0 VALUES LESS THAN (1990),
  PARTITION p1 VALUES LESS THAN (1995),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (2005)
);

And to drop a partition:
ALTER TABLE tr DROP PARTITION p2;


This seems so much more intuitive and simpler than what is required to
set it up in PostgreSQL.  Does PostgreSQL's approach to table
partitioning have any advantage over MySQL?  Is a "nicer" syntax planned
for Postgres?


On Wed, 10 Jan 2007 14:20:06 -0600, "Jim C. Nasby" <jim@nasby.net> said:
> BTW, someone coming up with a set of functions to handle partitioning
> for the general 'partition by time' case would make a GREAT project on
> pgFoundry.
>
> On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
> > Take a look at the set of partitioning functions I wrote shortly after
> > the 8.1 release:
> >
> > http://www.studenter.hb.se/~arch/files/part_functions.sql
> >
> > You could probably work something out using those functions (as-is, or
> > as inspiration) together with pgAgent
> > (http://www.pgadmin.org/docs/1.4/pgagent.html)
> >
> > /Mikael
> >
> > > -----Original Message-----
> > > From: pgsql-performance-owner@postgresql.org
> > [mailto:pgsql-performance-
> > > owner@postgresql.org] On Behalf Of Arnau
> > > Sent: den 5 januari 2007 12:02
> > > To: pgsql-performance@postgresql.org
> > > Subject: [PERFORM] Partitioning
> > >
> > > Hi all,
> > >
> > >    I'm not sure if this question fits in the topic of this list.
> > >
> > >    I'm interested in partitioning and it's the first time I'd use it.
> > > There is an issue I don't know how you handle it. Lets say I'm
> > > interested in store monthly based statistical data like the example of
> > > http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html. What
> > I
> > > don't like of this approach is that the monthly tables, rules... must
> > be
> > > created "manually" or at least I haven't found any other option.
> > >
> > >    My question is how do you manage this? do you have a cron task that
> > > creates automatically these monthly elements (tables, rules, ... ) or
> > > there is another approach that doesn't require external things like
> > cron
> > >   only PostgreSQL.
> > > --
> > > Arnau
> > >
> > > ---------------------------(end of
> > broadcast)---------------------------
> > > TIP 5: don't forget to increase your free space map settings
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >        choose an index scan if your joining column's datatypes do not
> >        match
> >
>
> --
> Jim Nasby                                            jim@nasby.net
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
>
> ---------------------------(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: Partitioning

From
"Jim C. Nasby"
Date:
On Wed, Jan 10, 2007 at 03:28:00PM -0500, Jeremy Haile wrote:
> This seems so much more intuitive and simpler than what is required to
> set it up in PostgreSQL.  Does PostgreSQL's approach to table
> partitioning have any advantage over MySQL?  Is a "nicer" syntax planned
> for Postgres?

The focus was to get the base functionality working, and working
correctly. Another consideration is that there's multiple ways to
accomplish the partitioning; exposing the basic functionality without
enforcing a given interface provides more flexibility (ie: it appears
that you can't do list partitioning with MySQL, while you can with
PostgreSQL).
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Re: Partitioning

From
Scott Marlowe
Date:
On Wed, 2007-01-10 at 15:09, Jim C. Nasby wrote:
> On Wed, Jan 10, 2007 at 03:28:00PM -0500, Jeremy Haile wrote:
> > This seems so much more intuitive and simpler than what is required to
> > set it up in PostgreSQL.  Does PostgreSQL's approach to table
> > partitioning have any advantage over MySQL?  Is a "nicer" syntax planned
> > for Postgres?
>
> The focus was to get the base functionality working, and working
> correctly. Another consideration is that there's multiple ways to
> accomplish the partitioning; exposing the basic functionality without
> enforcing a given interface provides more flexibility (ie: it appears
> that you can't do list partitioning with MySQL, while you can with
> PostgreSQL).

And I don't think the mysql partition supports tablespaces either.

Re: Partitioning

From
"Jeremy Haile"
Date:
You can do list partitioning in MySQL:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html

My comment was not meant as a criticism of PostgreSQL's current state -
I'm glad that it has partitioning.  I'm simply wondering if there are
any plans of adopting a more user-friendly syntax in the future similar
to MySQL partitioning support.  Having first-class citizen support of
partitions would also allow some nice administrative GUIs and views to
be built for managing them.

Jeremy Haile


On Wed, 10 Jan 2007 15:09:31 -0600, "Jim C. Nasby" <jim@nasby.net> said:
> On Wed, Jan 10, 2007 at 03:28:00PM -0500, Jeremy Haile wrote:
> > This seems so much more intuitive and simpler than what is required to
> > set it up in PostgreSQL.  Does PostgreSQL's approach to table
> > partitioning have any advantage over MySQL?  Is a "nicer" syntax planned
> > for Postgres?
>
> The focus was to get the base functionality working, and working
> correctly. Another consideration is that there's multiple ways to
> accomplish the partitioning; exposing the basic functionality without
> enforcing a given interface provides more flexibility (ie: it appears
> that you can't do list partitioning with MySQL, while you can with
> PostgreSQL).
> --
> Jim Nasby                                            jim@nasby.net
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Re: Partitioning

From
Scott Marlowe
Date:
On Wed, 2007-01-10 at 15:15, Jeremy Haile wrote:
> You can do list partitioning in MySQL:
> http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html
>
> My comment was not meant as a criticism of PostgreSQL's current state -
> I'm glad that it has partitioning.  I'm simply wondering if there are
> any plans of adopting a more user-friendly syntax in the future similar
> to MySQL partitioning support.  Having first-class citizen support of
> partitions would also allow some nice administrative GUIs and views to
> be built for managing them.

I don't think anyone took it as a negative criticism.  Jim and I were
both more pointing out that the development process of the two projects
is somewhat different.

In MySQL a small group that doesn't necessarily interact with a large
user community sets out to implement a feature in a given time line with
a given set of requirements and they tend to ignore what they see as
esoteric requirements.

In PostgreSQL a large development community that communicates fairly
well with it's large user community put somewhat of the onus of proving
the need and doing the initial proof of concept on those who say they
need a feature, often working in a method where the chief hackers lend a
hand to someone who wants the feature so they can get a proof of concept
up and running.  And example would be the auditing / time travel in the
contrib/spi project.  After several iterations, and given the chance to
learn from the mistakes of the previous incarnations, something often
rises out of that to produce the feature needed.

Generally speaking the postgresql method takes longer, making life
harder today, but produces cleaner more easily maintained solutions,
making life easier in the future.  Meanwhile the mysql method works
faster, making life easier today, but makes compromises that might make
life harder in the future.

Something that embodies that difference is the table handler philosophy
of both databases.  PostgreSQL has the abstraction to have more than one
table handler, but in practice has exactly one table handler.  MySQL has
the ability to have many table handlers, and in fact uses many of them.

With PostgreSQL this means that things like the query parsing /
execution and the table handler are tightly coupled.  This results in
things like transactable DDL.  Sometimes this results in suggestions
being dismissed out of hand because they would have unintended
consequences.

In MySQL, because of the multiple table handlers, many compromises on
the query parsing have to be made.  The most common one being that you
can define constraints / foreign keys in a column item, and they will
simply be ignored with no error or notice.  The fk constraints have to
go at the end of the column list to be parsed and executed.

So, partitioning, being something that will touch a lot of parts of the
database, isn't gonna just show up one afternoon in pgsql.  It will
likely take a few people making proof of concept versions before a
consensus is reached and someone who has the ability codes it up.

Re: Partitioning

From
Bernd Helmle
Date:


On Wed, 10 Jan 2007 15:30:16 -0600, Scott Marlowe <smarlowe@g2switchworks.com> wrote:

[...]

>
> And I don't think the mysql partition supports tablespaces either.
>

MySQL supports distributing partitions over multiple disks via the SUBPARTITION clause [1].
I leave it to you, wether their syntax is cleaner, more powerful or easier or ....;)


Bernd

[1] http://dev.mysql.com/doc/refman/5.1/en/partitioning-subpartitions.html

Re: Partitioning

From
"Mikael Carneholm"
Date:
> On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
> >> Take a look at the set of partitioning functions I wrote shortly
after
> >> the 8.1 release:
> >>
> >> http://www.studenter.hb.se/~arch/files/part_functions.sql
> >>
> >> You could probably work something out using those functions (as-is,
or
> >> as inspiration) together with pgAgent
> >> (http://www.pgadmin.org/docs/1.4/pgagent.html)
> >>
> >> /Mikael
> >>
> Those are  some great functions.
>

Well, they're less than optimal in one aspect: they add one rule per
partition, making them unsuitable for OLTP type applications (actually:
any application where insert performance is crucial). Someone with time
and/or energy could probably fix that, I guess...patches are welcome :)

/Mikael



Re: Partitioning

From
"Jeremy Haile"
Date:
Well - whether or not MySQL's implementation of partitioning has some
deficiency, it sure is a lot easier to set up than PostgreSQL.  And I
don't think there is any technical reason that setting up partitioning
on Postgres couldn't be very easy and still be robust.

On Thu, 11 Jan 2007 13:59:20 +0100, "Mikael Carneholm"
<Mikael.Carneholm@WirelessCar.com> said:
> > On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
> > >> Take a look at the set of partitioning functions I wrote shortly
> after
> > >> the 8.1 release:
> > >>
> > >> http://www.studenter.hb.se/~arch/files/part_functions.sql
> > >>
> > >> You could probably work something out using those functions (as-is,
> or
> > >> as inspiration) together with pgAgent
> > >> (http://www.pgadmin.org/docs/1.4/pgagent.html)
> > >>
> > >> /Mikael
> > >>
> > Those are  some great functions.
> >
>
> Well, they're less than optimal in one aspect: they add one rule per
> partition, making them unsuitable for OLTP type applications (actually:
> any application where insert performance is crucial). Someone with time
> and/or energy could probably fix that, I guess...patches are welcome :)
>
> /Mikael
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

Re: Partitioning

From
"Adam Rich"
Date:
Each partition can have its own disk, without using subpartitions.

CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
PARTITION BY LIST(YEAR(adate))
(
  PARTITION p1999 VALUES IN (1995, 1999, 2003)
    DATA DIRECTORY = '/var/appdata/95/data'
    INDEX DIRECTORY = '/var/appdata/95/idx',
  PARTITION p2000 VALUES IN (1996, 2000, 2004)
    DATA DIRECTORY = '/var/appdata/96/data'
    INDEX DIRECTORY = '/var/appdata/96/idx',
  PARTITION p2001 VALUES IN (1997, 2001, 2005)
    DATA DIRECTORY = '/var/appdata/97/data'
    INDEX DIRECTORY = '/var/appdata/97/idx',
  PARTITION p2000 VALUES IN (1998, 2002, 2006)
    DATA DIRECTORY = '/var/appdata/98/data'
    INDEX DIRECTORY = '/var/appdata/98/idx'
);

Subpartitions are just a way to break (parent) partitions up into
smaller pieces.  Those of course can be moved to other disks
just like the main partitions.



-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Bernd
Helmle
Sent: Thursday, January 11, 2007 6:51 AM
To: Scott Marlowe
Cc: Jim C. Nasby; Jeremy Haile; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Partitioning





On Wed, 10 Jan 2007 15:30:16 -0600, Scott Marlowe
<smarlowe@g2switchworks.com> wrote:

[...]

>
> And I don't think the mysql partition supports tablespaces either.
>

MySQL supports distributing partitions over multiple disks via the
SUBPARTITION clause [1].
I leave it to you, wether their syntax is cleaner, more powerful or
easier or ....;)


Bernd

[1]
http://dev.mysql.com/doc/refman/5.1/en/partitioning-subpartitions.html

---------------------------(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: Partitioning

From
Bernd Helmle
Date:


On Thu, 11 Jan 2007 08:18:39 -0600, "Adam Rich" <adam.r@sbcglobal.net> wrote:

>
> Subpartitions are just a way to break (parent) partitions up into
> smaller pieces.  Those of course can be moved to other disks
> just like the main partitions.

Ah, didn't know that (i just wondered why i need a subpartition to
change the location of a partition).

Thanks for your clarification...

Bernd