From: Pierre Frédéric Caillaud <lists@peufeu.com>
To: Nickolay <nitro@zhukcity.ru>
To: Kenneth Marshall <ktm@rice.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: PG optimization question
Date: Sat, 09 Jan 2010 16:37:29 +0100
Message-ID: <op.u59u0rgecke6l8@soyouz> (raw)
In-Reply-To: <4B487D9C.9020507@zhukcity.ru>
References: <4B485975.70506@zhukcity.ru>
<2f4958ff1001090418i78dee992t5ff2d78a54187c07@mail.gmail.com>
<4B4879A0.2060703@zhukcity.ru>
<20100109124646.GA8123@it.is.rice.edu>
<4B487D9C.9020507@zhukcity.ru>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, nitro@zhukcity.ru, ktm@rice.edu
Subject: Re: PG optimization question
In-Reply-To: <op.u59u0rgecke6l8@soyouz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox