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--