From: Mark Thornton <mthornton@optrak.com>
To: pgsql-performance@postgresql.org
Subject: Re: Table partitioning
Date: Sat, 05 Mar 2011 09:57:48 +0000
Message-ID: <4D72091C.8030809@optrak.com> (raw)
In-Reply-To: <AANLkTin7ZbOU+Z=bCjTbCUsUC=pYq-A2yM5=15gyRgZN@mail.gmail.com>
References: <AANLkTin7ZbOU+Z=bCjTbCUsUC=pYq-A2yM5=15gyRgZN@mail.gmail.com>
On 05/03/2011 09:37, Tobias Brox wrote:
> Sorry for not responding directly to your question and for changing
> the subject ... ;-)
>
> On 4 March 2011 18:18, Landreville<landreville@deadtreepages.com> wrote:
>> That is partitioned into about 3000 tables by the switchport_id (FK to
>> a lookup table), each table has about 30 000 rows currently (a row is
>> inserted every 5 minutes into each table).
> Does such partitioning really make sense? My impression is that the
> biggest benefit with table partitioning is to keep old "inactive" data
> out of the caches. If so, then it doesn't seem to make much sense to
> split a table into 3000 active partitions ... unless, maybe, almost
> all queries goes towards a specific partitioning.
If your partitions a loosely time based and you don't want to discard
old data, then surely the number of partitions will grow without limit.
You could have partitions for say the last 12 months plus a single
partition for 'ancient history', but then you have to transfer the
content of the oldest month to ancient each month and change the
constraint on 'ancient'.
Mark
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: mthornton@optrak.com
Subject: Re: Table partitioning
In-Reply-To: <4D72091C.8030809@optrak.com>
* 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