Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id F3FF26336F9
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sat,  9 Jan 2010 08:46:43 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 23119-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sat,  9 Jan 2010 12:46:33 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from is.rice.edu (is.rice.edu [128.42.42.24])
	by mail.postgresql.org (Postfix) with ESMTP id 8F08163384C
	for <pgsql-performance@postgresql.org>;
	Sat,  9 Jan 2010 08:46:33 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by is.rice.edu (Postfix) with ESMTP id 33AB54181C;
	Sat,  9 Jan 2010 06:46:53 -0600 (CST)
Received: from is.rice.edu ([127.0.0.1])
	by localhost (it.is.rice.edu [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id 01771-02; Sat,  9 Jan 2010 06:46:46 -0600 (CST)
Received: by is.rice.edu (Postfix, from userid 18612)
	id 9557F41819; Sat,  9 Jan 2010 06:46:46 -0600 (CST)
Date: Sat, 9 Jan 2010 06:46:46 -0600
From: Kenneth Marshall <ktm@rice.edu>
To: Nickolay <nitro@zhukcity.ru>
Cc: Grzegorz Ja??kiewicz <gryzman@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: PG optimization question
Message-ID: <20100109124646.GA8123@it.is.rice.edu>
References: <4B485975.70506@zhukcity.ru>
	<2f4958ff1001090418i78dee992t5ff2d78a54187c07@mail.gmail.com>
	<4B4879A0.2060703@zhukcity.ru>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4B4879A0.2060703@zhukcity.ru>
User-Agent: Mutt/1.5.16 (2007-06-09)
X-Virus-Scanned: by amavis-2.2.1 at is.rice.edu
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.589 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188
X-Spam-Level: 
X-Archive-Number: 201001/163
X-Sequence-Number: 37043

On Sat, Jan 09, 2010 at 03:42:08PM +0300, Nickolay wrote:
> I do not see any way to normalize this table anymore. it's size is 4Gig for 
> ~4M rows, i.e. 1Kb per row, i think it's ok.
> Also there are 2 indexes: by date_time and by a couple of service fields 
> (total index size is 250Mb now).
> I think i'll be going to partition by months (approx. 1M rows or 1Gig per 
> month), so it would be like 60 partitions for 5 years. Is that OK for 
> postgres?

Not a problem. We have a log server that has 64 daily partitions.

> 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.

Ken