From: Nickolay <nitro@zhukcity.ru>
To: Pierre Frédéric Caillaud <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PG optimization question
Date: Sat, 09 Jan 2010 21:50:07 +0300
Message-ID: <4B48CFDF.7090406@zhukcity.ru> (raw)
In-Reply-To: <op.u59u0rgecke6l8@soyouz>
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>
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.
>
>
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: nitro@zhukcity.ru, lists@peufeu.com
Subject: Re: PG optimization question
In-Reply-To: <4B48CFDF.7090406@zhukcity.ru>
* 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