Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 3A6271FE6210 for ; Sun, 15 Jan 2012 08:10:09 -0400 (AST) Received: from moutng.kundenserver.de ([212.227.17.8]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RmOuS-0000vU-PE for pgsql-performance@postgresql.org; Sun, 15 Jan 2012 12:10:08 +0000 Received: from [192.168.1.6] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mrbap1) with ESMTP (Nemesis) id 0Mc8Nr-1S4l8S2IGl-00J5J6; Sun, 15 Jan 2012 13:09:52 +0100 Message-ID: <4F12C20E.7080400@2ndQuadrant.com> Date: Sun, 15 Jan 2012 07:09:50 -0500 From: Greg Smith User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.24) Gecko/20111120 Icedove/3.1.16 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: auto vacuum, not working? References: <000301ccd1ec$167ab7d0$43702770$@devshock.com> In-Reply-To: <000301ccd1ec$167ab7d0$43702770$@devshock.com> Content-Type: multipart/alternative; boundary="------------010207080508020202050206" X-Provags-ID: V02:K0:LCBzfZtVW2lwBWUdgdZ2lKEBhJLNIV+7SCmW8XCumKM G+tjrLFHrrkW8NPP8VLVojI005gTwxj2H8AD/6Fp4sEMMwwZtw IqJwqk9eSwdMHbQwXxyex0KmZGB6Ejq+P64hWHTUFRDm4fxM12 HCqqcg8DENIKUteE88OIX3tI/a//gBEqY9AAOwkpAy8MLl4QE6 xY1u2x9tMZj+9kbP5py5qyRWAZdKRe3r+H6TG/+O0ZlEZAFGmq Jg9xEe2LeOYrrvLrueQ8NabXLwpPU++NUQDUnhJGxtECtjd2m5 d57/8aLTzolC1aEazqXwoa6HemGPpFXq9+oI8Osqsgv2L0/ULc mko7nqNvf5bRdUGQDR1s= X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201201/47 X-Sequence-Number: 46060 This is a multi-part message in MIME format. --------------010207080508020202050206 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 01/13/2012 07:08 AM, Anibal David Acosta wrote: > > How can I release the space used by deleted rows? Without block the table. > > The database can only reduce the size tables by returning space to the operating system in one situation: there is free space at the very end of the table. In that case, if it's possible to get a brief exclusive lock on the table, it can shrink in size. There are some programs available that reorganize table for goals like this, without having any long-lasting locks on the tables. pg_reorg is the most popular example: http://pgfoundry.org/projects/reorg/ -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com --------------010207080508020202050206 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On 01/13/2012 07:08 AM, Anibal David Acosta wrote:

How can I release the space used by deleted rows? Without block the table.

The database can only reduce the size tables by returning space to the operating system in one situation: there is free space at the very end of the table. In that case, if it's possible to get a brief exclusive lock on the table, it can shrink in size.

There are some programs available that reorganize table for goals like this, without having any long-lasting locks on the tables. pg_reorg is the most popular example: http://pgfoundry.org/projects/reorg/

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

--------------010207080508020202050206--