Message-ID: <4B48CFDF.7090406@zhukcity.ru>
Date: Sat, 09 Jan 2010 21:50:07 +0300
From: Nickolay <nitro@zhukcity.ru>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: =?UTF-8?B?UGllcnJlIEZyw6lkw6lyaWMgQ2FpbGxhdWQ=?= <lists@peufeu.com>
CC: pgsql-performance@postgresql.org
Subject: Re: PG optimization question
References: <4B485975.70506@zhukcity.ru>
	<2f4958ff1001090418i78dee992t5ff2d78a54187c07@mail.gmail.com>
	<4B4879A0.2060703@zhukcity.ru>
	<20100109124646.GA8123@it.is.rice.edu>
	<4B487D9C.9020507@zhukcity.ru> <op.u59u0rgecke6l8@soyouz>
In-Reply-To: <op.u59u0rgecke6l8@soyouz>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Okay, I see your point with staging table. That's a good idea!
The only problem I see here is the transfer-to-archive-table process. As 
you've correctly noticed, the system is kind of a real-time and there 
can be dozens of processes writing to the staging table, i cannot see 
how to make the transfer/flush process right and clear...

Pierre Frédéric Caillaud wrote:
>>>> Oh, btw, 95% of queries are searching rows for current date (last 
>>>> 24 hours).
>>>>
>>>
>>> You may want to use a daily staging table and then flush to the 
>>> monthly archive tables at the end of the day.
>
>     If the rows in the archive tables are never updated, this strategy 
> means you never need to vacuum the big archive tables (and indexes), 
> which is good. Also you can insert the rows into the archive table in 
> the order of your choice, the timestamp for example, which makes it 
> nicely clustered, without needing to ever run CLUSTER.
>
>     And with partitioning you can have lots of indexes on the staging 
> table (and current months partition) (to speed up your most common 
> queries which are likely to be more OLTP), while using less indexes on 
> the older partitions (saves disk space) if queries on old partitions 
> are likely to be reporting queries which are going to grind through a 
> large part of the table anyway.
>
>