Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 88A499FB48A; Fri, 9 Mar 2007 06:25:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 70631-01; Fri, 9 Mar 2007 06:25:06 -0400 (AST) X-Greylist: delayed 00:19:41.313049 by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from charon.microstep-mis.sk (charon.microstep-mis.sk [194.160.63.7]) by postgresql.org (Postfix) with ESMTP id EC75E9F9C9E; Fri, 9 Mar 2007 06:25:09 -0400 (AST) Received: from [192.168.145.5] (nod5.mstep [192.168.145.5]) by charon.microstep-mis.sk (Postfix) with ESMTP id 80F683D1387; Fri, 9 Mar 2007 11:05:13 +0100 (CET) Message-ID: <45F1315A.1020906@microstep-mis.com> Date: Fri, 09 Mar 2007 11:05:14 +0100 From: Monika Cernikova User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-general@postgresql.org, pgsql-admin@postgresql.org Subject: index bloat problem Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Microstep-MIS-MailScanner-Information: MicroStep-Mis s.r.o. X-Microstep-MIS-MailScanner: clean X-Microstep-MIS-MailScanner-SpamCheck: not spam (whitelisted), SpamAssassin (score=-4.368, required 8, autolearn=not spam, ALL_TRUSTED -1.80, AWL 0.03, BAYES_00 -2.60) X-MailScanner-From: monika@microstep-mis.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1 tagged_above=0 required=5 tests=BAYES_60 X-Spam-Level: * X-Archive-Number: 200703/593 X-Sequence-Number: 110783 I use Postgres 8.1 on linux I have several tables to which I need insert about 200-500 records per minute. Records contains timestamp (actual time), and this timestamp is part of primary key and index. I need to keep data for 1 month. I daily delete data older than 1 month and than run vacuum analyze. Size of primary keys and indexes still grows. (After 2 months database size is about 35G and vacuum runs about 20 hours. When I dumped and restored database, new size was 25G. Indexes and constraints had half size) Can you help me how to stop index growing or reindex database if I CAN'T STOP writing records? Thanx, Monika Here are my settings in postgresql.conf, that differs from defaults shared_buffers = 2000 max_fsm_pages = 400000 (it isn't much, but there isn't logged in vacuum log that it needs more) vacuum_cost_delay = 100 vacuum_cost_page_hit = 6 vacuum_cost_limit = 100 wal_buffers = 16 checkpoint_segments = 8 autovacuum = off