Re: Monthly table partitioning for fast purges?
От | Shridhar Daithankar |
---|---|
Тема | Re: Monthly table partitioning for fast purges? |
Дата | |
Msg-id | 3F2D3FE8.2497.1F0F5206@localhost обсуждение исходный текст |
Ответ на | Monthly table partitioning for fast purges? ("Roger Hand" <rhand@ragingnet.com>) |
Ответы |
Re: Monthly table partitioning for fast purges?
|
Список | pgsql-general |
On 1 Aug 2003 at 13:46, Roger Hand wrote: > In Oracle you can divide a table into partitions. We use this feature to break up the data by month. Each month we storeseveral tens of millions of rows in a particular table, and each month we drop the partition that's a year old. In otherwords, we always keep the last 12 months of data (12 partitions). This is clean and fast. Since the partition is bya timestamp column, it also gives us a certain amount of automatic indexing. > > Postgres doesn't support table partitions (correct me if I'm wrong!) so the only option appears to be to dump everythinginto one big table. What I'm worried about is the purging of the data from 12 months ago ... I'm worried that thiswill be a slow and expensive operation. Well, you need to emulate this feature on postgresql, as pointed out already. It is possible in postgresql but is not provided out of the box. Here is what you can do. * Create a empty base table. * Create a trigger the directs the select on timestamps accordingly * Write a schedule to create a new table at the start of every month and update the trigger accordingly * Create and maintain rules to insert into base table depending upon the timestamp. This is just a rough idea. There might be fair amount of work to get this working but surely it is not imposible. HTH Bye Shridhar -- Justice, n.: A decision in your favor.
В списке pgsql-general по дате отправления: