Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 1FA5B9FA1E2 for ; Wed, 18 Oct 2006 20:00:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 30895-10 for ; Wed, 18 Oct 2006 23:00:48 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 87C099FB2E6 for ; Wed, 18 Oct 2006 20:00:48 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k9IN0jDU002652; Wed, 18 Oct 2006 19:00:46 -0400 (EDT) To: "Jim C. Nasby" cc: Graham Davis , pgsql-performance@postgresql.org Subject: Re: index growth problem In-reply-to: <20061018223307.GC56874@nasby.net> References: <4536A8A3.1050409@refractions.net> <20061018223307.GC56874@nasby.net> Comments: In-reply-to "Jim C. Nasby" message dated "Wed, 18 Oct 2006 17:33:08 -0500" Date: Wed, 18 Oct 2006 19:00:45 -0400 Message-ID: <2651.1161212445@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/388 X-Sequence-Number: 21517 "Jim C. Nasby" writes: > On Wed, Oct 18, 2006 at 03:20:19PM -0700, Graham Davis wrote: >> When I run the same command to find the size after the VACUUM, it hasn't >> changed. > That's not really a useful test to see if VACUUM is working. VACUUM can > only trim space off the end of a relation (index or table), where by > 'end' I mean the end of the last file for that relation on the > filesystem. This means it's pretty rare for VACUUM to actually shrink > files on-disk for tables. This can be even more difficult for indexes (I > think it's virtually impossible to shrink a B-tree index file). Right; IIRC, a plain VACUUM doesn't even try to shorten the physical index file, because of locking considerations. The important question is whether space gets recycled properly for re-use within the index. If the index continues to grow over time, then you might have a problem with insufficient FSM space (or not vacuuming often enough). It might be worth pointing out that VACUUM isn't intended to try to reduce the disk file to the shortest possible length --- the assumption is that you are doing vacuuming on a regular basis and so the file length should converge to a "steady state", wherein the internal free space runs out about the time you do another VACUUM and reclaim some more space for re-use. There's not really any point in being more aggressive than that; we'd just create additional disk I/O when the filesystem releases and later reassigns space to the file. Of course, this argument fails in the scenario where you make a large and permanent reduction in the amount of data in a table. There are various hacks you can use to clean up in that case --- use TRUNCATE not DELETE if you can, or consider using CLUSTER (not VACUUM FULL). Some variants of ALTER TABLE will get rid of internal free space, too. regards, tom lane