Re: Table Partitioning Advice Request

Rule is not advisable, Trigger is the best solution.

---
Thanks
Sam Jas

--- On Thu, 17/12/09, Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it> wrote:

From: Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>
Subject: [GENERAL] Table Partitioning Advice Request
To: pgsql-general(at)postgresql(dot)org
Date: Thursday, 17 December, 2009, 11:05 AM

Hi all.

I'm planning to implement table partitioning as "suggested" (among
other sources) in the official documentation.
I'm using v8.4.2 at the moment.

My case is far from the general one as:
1. I'll never UPDATE or DELETE rows from partitioned tables (only INSERTs)
2. Rows will be inserted one-by-one or, in the worse case, in bunches
of two or three
3. Partitioning will be based upon TIMESTAMP ranges
4. The "virtual" tables should approach (and possibly go past) 100M rows
5. Most (99%) of the INSERTs (and possibly SELECTs) will actually
operate on a rather small number of partitions (hardly more than 2).

My main TABLE is like the following one:

CREATE TABLE events (
  eventtype text not null,
  item_id int8 not null,
  event_date timestamp not null default now(),
  row_date timestamp not null default now(),
  event_id serial8 primary key
);

where the partitioning would happen over the values of the event_date column.
The row_date columns is to record the row creation TIMESTAMP as events
can be created relatively to the past, the future or the current time.

In my mind a solution which is simple to maintain is to add a simple
RULE ... ON INSERT for every newly created partition table.
The TRIGGER approach, in my opinion, is much more complex to maintain
as either the body of the function needs to be rewritten as new
partitions are added, or
some external TABLE lookup is needed to choose the actual table name
to be used for a (dynamically created) INSERT.

Now the questions.

1.  As the number of RULEs will grow with the time, how will change
the efficiency of the query planner while "browsing" among the RULES?
2. In the case the previous answer would lead to bad news for me, is
there any better/different approach to partitioning with TRIGGERs?
3. Is there any more general advise for such approaches?

--
Vincenzo Romano
NON QVIETIS MARIBVS NAVTA PERITVS

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/