Thread: 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
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
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)
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)
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
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)
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.
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)
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.
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
> 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
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
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
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