From: Adam Rich <adam.r@sbcglobal.net>
To: 'Bernd Helmle' <mailings@oopsware.de>
To: 'Scott Marlowe' <smarlowe@g2switchworks.com>
Cc: 'Jim C. Nasby' <jim@nasby.net>
Cc: 'Jeremy Haile' <jhaile@fastmail.fm>
Cc: pgsql-performance@postgresql.org
Subject: Re: Partitioning
Date: Thu, 11 Jan 2007 08:18:39 -0600
Message-ID: <045101c7358b$644554a0$6400a8c0@dualcore> (raw)
In-Reply-To: <6b147815b01253f9c9a22f0e896a8aa7@oopsware.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adam.r@sbcglobal.net, mailings@oopsware.de, smarlowe@g2switchworks.com, jim@nasby.net, jhaile@fastmail.fm
Subject: Re: Partitioning
In-Reply-To: <045101c7358b$644554a0$6400a8c0@dualcore>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox