Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 387605377D9 for ; Tue, 29 May 2012 14:43:00 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SZQRd-0001wQ-NC for pgsql-performance@postgresql.org; Tue, 29 May 2012 17:42:59 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q4THggXL014868; Tue, 29 May 2012 13:42:42 -0400 (EDT) To: Job cc: "pgsql-performance@postgresql.org" Subject: Re: Strong slowdown on huge tables In-reply-to: <88EF58F000EC4B4684700C2AA3A73D7AA8B1042B83@W2008DC01.ColliniConsulting.lan> References: <88EF58F000EC4B4684700C2AA3A73D7AA8B1042B83@W2008DC01.ColliniConsulting.lan> Comments: In-reply-to Job message dated "Tue, 29 May 2012 16:54:18 +0200" Date: Tue, 29 May 2012 13:42:42 -0400 Message-ID: <14867.1338313362@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201205/167 X-Sequence-Number: 46991 Job writes: > i use PostgreSQL 8.4.8 on Centos 5.x and i have some table where i load with pg_bulkload webtraffic logs, every day. > After loading new data, i delete with a query 30-days older logs. Since the deletion pattern is so predictable, you should consider setting this table up as a partitioned table, so that you can just drop the oldest partition instead of having to rely on DELETE+VACUUM to reclaim space. See http://www.postgresql.org/docs/8.4/static/ddl-partitioning.html Alternatively, look into whether including a manual VACUUM in your daily update script helps any. regards, tom lane