default child of partition master
От | April Lorenzen |
---|---|
Тема | default child of partition master |
Дата | |
Msg-id | 72624f530608200902x47ead3efj91db8d2858b89dc7@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: default child of partition master
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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
В списке pgsql-hackers по дате отправления: