Take a look at the set of partitioning functions I wrote shortly after
the 8.1 release:

http://www.studenter.hb.se/~arch/fil..._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
[mailtogsql-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/s...titioning.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

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/fil..._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
> [mailtogsql-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/s...titioning.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

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/fil..._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)


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/fil..._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
> > [mailtogsql-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/s...titioning.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

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

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)

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

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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

You can do list partitioning in MySQL:
http://dev.mysql.com/doc/refman/5.1/...ning-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)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/...ning-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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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/...artitions.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

> 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/fil..._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