Date: Sat, 09 Jan 2010 16:37:29 +0100
To: Nickolay <nitro@zhukcity.ru>, "Kenneth Marshall" <ktm@rice.edu>
Subject: Re: PG optimization question
From: =?utf-8?Q?Pierre_Fr=C3=A9d=C3=A9ric_Caillau?= =?utf-8?Q?d?=
	<lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <4B485975.70506@zhukcity.ru>
	<2f4958ff1001090418i78dee992t5ff2d78a54187c07@mail.gmail.com>
	<4B4879A0.2060703@zhukcity.ru> <20100109124646.GA8123@it.is.rice.edu>
	<4B487D9C.9020507@zhukcity.ru>
Content-Transfer-Encoding: 7bit
Message-ID: <op.u59u0rgecke6l8@soyouz>
In-Reply-To: <4B487D9C.9020507@zhukcity.ru>
User-Agent: Opera Mail/9.62 (Linux)


> That may help with the queries speed (not a problem now), but we'll then  
> have to add UNION statement for daily staging table for other 5% of  
> requests, right? And there would be a moment when daily message is in  
> archive table AND in daily table (while transferring from daily table to  
> archive).
> Our main problem is in blocking when doing DELETE (app sometimes freezes  
> for a long time), and also we have to do VACUUM on live table, which is  
> not acceptable in our app.
>
> Thanks for your reply, I was kinda worried about number of partitions  
> and how this would affect PG query execution speed.
>
> Kenneth Marshall 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.