From aa@devshock.com Fri Jan 13 12:08:54 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 5A6312EE4E2 for ; Fri, 13 Jan 2012 08:08:54 -0400 (AST) Received: from mail-gy0-f174.google.com ([209.85.160.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RlfwC-0007mS-Gs for pgsql-performance@postgresql.org; Fri, 13 Jan 2012 12:08:53 +0000 Received: by ghbg16 with SMTP id g16so814938ghb.19 for ; Fri, 13 Jan 2012 04:08:39 -0800 (PST) Received: by 10.236.154.5 with SMTP id g5mr1095476yhk.63.1326456519814; Fri, 13 Jan 2012 04:08:39 -0800 (PST) Received: from AnibalVAIO (host-20-79.b4.cmm.com.py. [200.26.179.79]) by mx.google.com with ESMTPS id j11sm22662027anl.8.2012.01.13.04.08.36 (version=TLSv1/SSLv3 cipher=OTHER); Fri, 13 Jan 2012 04:08:38 -0800 (PST) From: "Anibal David Acosta" To: Subject: auto vacuum, not working? Date: Fri, 13 Jan 2012 09:08:36 -0300 Message-ID: <000301ccd1ec$167ab7d0$43702770$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0004_01CCD1D2.F12F0670" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AczR6oeSnLY+nCiPTIuZIHZpqQLneg== Content-Language: es-py X-Pg-Spam-Score: -1.2 (-) X-Archive-Number: 201201/41 X-Sequence-Number: 46054 This is a multipart message in MIME format. ------=_NextPart_000_0004_01CCD1D2.F12F0670 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi, yesterday I delete about 200 million rows of a table (about 150GB of data), after delete completes the autovacuum process start. The autovacuum is running for about 11 hours but no space is released Autovacuum parameters are with default values in postgresql.conf The postgres version is 9.0.3 The pg activity reports: select (now()-query_start) as duration, waiting, current_query from pg_stat_activity where current_query ilike '%auto%' 10:42:19.829 f "autovacuum: VACUUM ANALYZE public.myTable" How can I release the space used by deleted rows? Without block the table. Thanks! ------=_NextPart_000_0004_01CCD1D2.F12F0670 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi, =

yesterday I delete about 200 million = rows of a table (about 150GB of data), after delete completes the = autovacuum process start.

The = autovacuum is running for about 11 hours but no space is = released

Autovacuum parameters are = with default values in postgresql.conf

The postgres = version is 9.0.3

The pg = activity reports:

select = (now()-query_start) as duration, waiting, current_query from = pg_stat_activity where current_query ilike '%auto%'

10:42:19.829 = f = "autovacuum: VACUUM ANALYZE = public.myTable"

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

Thanks!

------=_NextPart_000_0004_01CCD1D2.F12F0670-- From roadrunner6@gmx.at Fri Jan 13 14:26:25 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id BC49B2EE4E2 for ; Fri, 13 Jan 2012 10:26:25 -0400 (AST) Received: from mailout-de.gmx.net ([213.165.64.23]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Rli5H-0001BF-US for pgsql-performance@postgresql.org; Fri, 13 Jan 2012 14:26:24 +0000 Received: (qmail invoked by alias); 13 Jan 2012 14:26:10 -0000 Received: from office.icomedias.com (EHLO [10.192.1.141]) [109.73.147.5] by mail.gmx.net (mp069) with SMTP; 13 Jan 2012 15:26:10 +0100 X-Authenticated: #5984785 X-Provags-ID: V01U2FsdGVkX1/Ax5oqSSzAP7DoGzZXjBqTnOO2B9Vkk9dxdv73r5 UdZG+3ZJ3jtbcs Message-ID: <4F103F00.2080904@gmx.at> Date: Fri, 13 Jan 2012 15:26:08 +0100 From: Mario Weilguni User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:8.0) Gecko/20111105 Thunderbird/8.0 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="------------010607060806070906080903" X-Y-GMX-Trusted: 0 X-Pg-Spam-Score: -1.6 (-) X-Archive-Number: 201201/42 X-Sequence-Number: 46055 This is a multi-part message in MIME format. --------------010607060806070906080903 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Am 13.01.2012 13:08, schrieb Anibal David Acosta: > > Hi, > > yesterday I delete about 200 million rows of a table (about 150GB of > data), after delete completes the autovacuum process start. > > The autovacuum is running for about 11 hours but no space is released > > Autovacuum parameters are with default values in postgresql.conf > > The postgres version is 9.0.3 > > The pg activity reports: > > select (now()-query_start) as duration, waiting, current_query from > pg_stat_activity where current_query ilike '%auto%' > > 10:42:19.829 f "autovacuum: VACUUM ANALYZE > public.myTable" > > How can I release the space used by deleted rows? Without block the table. > > Thanks! > vacuum does not reclaim space, just marks tuples dead. You need vacuum full. --------------010607060806070906080903 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Am 13.01.2012 13:08, schrieb Anibal David Acosta:

Hi,

yesterday I delete about 200 million rows of a table (about 150GB of data), after delete completes the autovacuum process start.

The autovacuum is running for about 11 hours but no space is released

Autovacuum parameters are with default values in postgresql.conf

The postgres version is 9.0.3

The pg activity reports:

select (now()-query_start) as duration, waiting, current_query from pg_stat_activity where current_query ilike '%auto%'

10:42:19.829 f "autovacuum: VACUUM ANALYZE public.myTable"

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

Thanks!

vacuum does not reclaim space, just marks tuples dead. You need vacuum full.
--------------010607060806070906080903-- From Kevin.Grittner@wicourts.gov Fri Jan 13 14:50:47 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 4AF3B2EE4E3 for ; Fri, 13 Jan 2012 10:50:47 -0400 (AST) Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RliSn-0001cE-Er for pgsql-performance@postgresql.org; Fri, 13 Jan 2012 14:50:46 +0000 Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Fri, 13 Jan 2012 08:50:27 -0600 Message-Id: <4F0FF04D0200002500044778@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Fri, 13 Jan 2012 08:50:21 -0600 From: "Kevin Grittner" To: "Mario Weilguni" , Subject: Re: auto vacuum, not working? References: <000301ccd1ec$167ab7d0$43702770$@devshock.com> <4F103F00.2080904@gmx.at> In-Reply-To: <4F103F00.2080904@gmx.at> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201201/43 X-Sequence-Number: 46056 Mario Weilguni wrote: >> yesterday I delete about 200 million rows of a table >> How can I release the space used by deleted rows? >> Without block the table. > vacuum does not reclaim space, just marks tuples dead. You need > vacuum full. VACUUM FULL will lock the table, blocking all other access, and it can run for quite a while. If you expect to be adding 200 million new rows to the table in the foreseeable future, a regular VACUUM (or autovacuum) will make that space available for reuse by that table. The space won't show in the file system; it will still be allocated to the database but available for new rows. -Kevin From greg@2ndQuadrant.com Sun Jan 15 12:10:09 2012 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--