default child of partition master

From: "April Lorenzen" <outboundindex(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: default child of partition master
Date: 2006-08-20 16:02:12
Message-ID: 72624f530608200902x47ead3efj91db8d2858b89dc7@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I'm using partitioned tables a lot and loving it. I have a suggestion
that I believe would make it easier to bring the performance and
maintenance advantages of partitions to more applications and users:

As I understand it, at present I can select records from sales_master
and get data returned from all tables that inherit sales_master:

sales_2006_q1
sales_2006_q2
sales_2006_q3
...

But INSERT and certain other operations must specify the destination
table. (Only makes sense.)

My suggestion is to allow specifying a default destination table in
the master partition table definition. This default destination table
could be changed with ALTER TABLE.

This would make it simpler to adapt existing applications to use
partitions. Instead of having to edit the table name in every location
in every application that accesses a particular table - the partition
master can be created with the name all those applications expect.

The existing data is then placed into partitions split by date - and
the applications continue to function as expected.

A scheduled process creates a new table that inherits the master as
needed: sales_2006_q4, sales_2007_q1 etc - inheriting from
sales_master. And each time, sales_master is altered to set the
default table to, for instance sales_2006_q4.

The need I have for this right now is for dbmail - a mail store in SQL
that "supports" both mysql and postgresql. It's pretty good - I've
been using it for some years - but they don't seem to know much about
postgresql. The table that holds the message blocks has a huge amount
of deletes and inserts. They do have a maintenance util which runs on
a cron frequently but... this is a live mail system - one cannot do
operations that prevent mail being delivered - and the run times for
full vacuums on the whole message blocks table or whole db are huge.

This table would be extremely enhanced by partitions split on date.
However it is extermely unlikely that I could get the dbmail
developers (and everyone else who has written related apps that access
the dbmail tables, including me) to alter their code so that it
selects from a master table but inserts to an *ever-changing other
table*.

For one thing, every type of maintenance could be done on the older
tables without affecting mail delivery into the current table. Users
who access only recent mail wouldn't suffer due to the size of the
table caused by users who keep a lot of mail on the server. Etc.

If the "specify a default table for INSERTs" (and other operations
that can't be sent to the partition master) feature were added - it
would allow users of postgresql and dbmail - (and many other apps in
similar situations) to independently choose to run partitions.

Thank you for your consideration,

- April Lorenzen

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-08-20 16:05:57 Re: DROP OWNED BY doesn't work
Previous Message Andrew Dunstan 2006-08-20 15:53:05 Re: Coverity reports looking good