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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
