Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id F37702E003A for ; Tue, 8 Apr 2008 06:49:09 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 05706-01 for ; Tue, 8 Apr 2008 06:49:01 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ti-out-0910.google.com (ti-out-0910.google.com [209.85.142.184]) by developer.postgresql.org (Postfix) with ESMTP id 65E542E0038 for ; Tue, 8 Apr 2008 06:49:06 -0300 (ADT) Received: by ti-out-0910.google.com with SMTP id y6so3161377tia.5 for ; Tue, 08 Apr 2008 02:49:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=8rwRtXRoOpdArxP0WqaDJe01lV9mTcTmlGLUgIQEJFk=; b=FqTOpWGpmSifZL0NdKLUVlDJoAZLMg233CskvhJQWA3cp46q1K5rOdhi9SS5D47OuvI4XJvnL3zUqY6eOZlF8X1zY3DYDiU9+2DXCL8w6cN+743ZsRIaQvN7eEzliB44wVUKv64oOltdxZ+Dmf92VTYj1sk15JskE7WexgbmDqk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=KvTmubE84UFWnEuZ1stAMMWFw8930fzm991QltoLr2KiTZsi/PKnCR5N5cAnexEcCMHTglRiZ6oX5PG8kI20943cD+COrKIGiHUxgLkR3q/ese1WQdzz2wfzaIuRv74vfwygYJBcWVe00U5Pa0kgkfv6R66wg5Ky6fpAHnWZkz4= Received: by 10.110.63.6 with SMTP id l6mr1498954tia.36.1207648144075; Tue, 08 Apr 2008 02:49:04 -0700 (PDT) Received: by 10.110.40.17 with HTTP; Tue, 8 Apr 2008 02:49:04 -0700 (PDT) Message-ID: Date: Tue, 8 Apr 2008 19:49:04 +1000 From: "Shoaib Mir" To: "Johann Spies" , pgsql-admin@postgresql.org Subject: Re: Handling large volumes of data In-Reply-To: <20080408094233.GG13409@sun.ac.za> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14223_10860932.1207648144075" References: <20080408094233.GG13409@sun.ac.za> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/68 X-Sequence-Number: 28832 ------=_Part_14223_10860932.1207648144075 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Tue, Apr 8, 2008 at 7:42 PM, Johann Spies wrote: > Apparently the best approach is not to have very large tables. I am > thinking of making (as far as the firewall is concerned) a different > table for each day and then drop the older tables as necessary. > > Any advice on how to best handle this kind of setup will be > appreciated. > > Table paritioning is what you need --> http://www.postgresql.org/docs/current/static/ddl-partitioning.html and then I will also advise distribute your tables across different disks through tablespaces. Tweak the shared buffers and work_mem settings as well. -- Shoaib Mir Fujitsu Australia Software Technology shoaibm@fast.fujitsu.com.au ------=_Part_14223_10860932.1207648144075 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On Tue, Apr 8, 2008 at 7:42 PM, Johann Spies <jspies@sun.ac.za> wrote:
Apparently the best approach is not to have very large tables.  I am
thinking of making (as far as the firewall is concerned) a different
table for each day and then drop the older tables as necessary.

Any advice on how to best handle this kind of setup will be
appreciated.

 
Table paritioning is what you need --> http://www.postgresql.org/docs/current/static/ddl-partitioning.html and then I will also advise distribute your tables across different disks through tablespaces. Tweak the shared buffers and work_mem settings as well. 
 
 
--
Shoaib Mir
Fujitsu Australia Software Technology
------=_Part_14223_10860932.1207648144075--