From aa@devshock.com Thu Aug  16 20:34:03 2012
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1T26lX-0004vm-OX
	for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 20:34:03 +0000
Received: from mail-gg0-f174.google.com ([209.85.161.174])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1T26lU-0006DX-Ve
	for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 20:34:03 +0000
Received: by ggdk6 with SMTP id k6so3387403ggd.19
	for <pgsql-performance@postgresql.org>;
	Thu, 16 Aug 2012 13:33:59 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=from:to:subject:date:message-id:mime-version:content-type:x-mailer
	:thread-index:content-language:x-gm-message-state;
	bh=dL/0vm+0OCyJCtMCIomoqYiJjfnHpANu8E35Wg3nMkA=;
	b=ig4ny/TgJ9aBNoq+Lz3QyXo7c3vClenztLk+RlFfAjEPTCTV9nfSNMG4/FddsOoLx1
	J6OpqK8XE6QCanD+tdi30S+1ob7IzH1+g4e8aZZaGbHiJp5SJJ7qVa106vrxlifOKwH4
	gS4EgvT5U8bpO5tm9RukUebenzcnkdovIoKKCQoxpqrZXk2xKEjrRo5YOt9MOVa3QYtB
	LBbKr0W1QX1O6fYJWvT5mNOSqCQWDmTMXo+ZRgZcQk6u/N9+O/ExRXaFJq1XDyxrrvWQ
	BK6kLj3TIR9agVaMK+tMaHsXFpbLBLqevDxhjAr84hQ9ORefpoMgVjnccDQPIeZENfvW
	bfNA==
Received: by 10.236.182.228 with SMTP id o64mr3432265yhm.85.1345149239657;
	Thu, 16 Aug 2012 13:33:59 -0700 (PDT)
Received: from AnibalVAIO ([190.128.222.90])
	by mx.google.com with ESMTPS id p21sm10220298yhj.11.2012.08.16.13.33.55
	(version=TLSv1/SSLv3 cipher=OTHER);
	Thu, 16 Aug 2012 13:33:59 -0700 (PDT)
From: "Anibal David Acosta" <aa@devshock.com>
To: <pgsql-performance@postgresql.org>
Subject: best practice to avoid table bloat?
Date: Thu, 16 Aug 2012 16:33:56 -0400
Message-ID: <009d01cd7bee$785b1380$69113a80$@devshock.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_009E_01CD7BCC.F14A84F0"
X-Mailer: Microsoft Outlook 14.0
Thread-Index: Ac177hP5geRgxHOnRWuIfGH67fZMeg==
Content-Language: es-py
X-Gm-Message-State: 
 ALoCoQldXsgvHl71DjknEnM8DGZ2bG720oAABVNrxL8k3HEuyM2IHQXTnMPdzjn2H560CDAkCyd+
X-Pg-Spam-Score: -1.2 (-)
X-Archive-Number: 201208/112
X-Sequence-Number: 47650

This is a multipart message in MIME format.

------=_NextPart_000_009E_01CD7BCC.F14A84F0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi, 

if I have a table that daily at night is deleted about 8 millions of rows
(table maybe has 9 millions) is recommended to do a vacuum analyze after
delete completes or can I leave this job to autovacuum?

 

This table is very active during the day but less active during night

 

I think that the only only thing where Postgres is weak, is in this area
(table and index bloat).

 

For some reason for the same amount of data every day postgres consume a
little more.

 

Thanks!


------=_NextPart_000_009E_01CD7BCC.F14A84F0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta =
http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii"><meta name=3DGenerator content=3D"Microsoft Word 14 =
(filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EstiloCorreo17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri","sans-serif";}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DEN-US link=3Dblue =
vlink=3Dpurple><div class=3DWordSection1><p class=3DMsoNormal>Hi, =
<o:p></o:p></p><p class=3DMsoNormal>if I have a table that daily at =
night is deleted about 8 millions of rows (table maybe has 9 millions) =
is recommended to do a vacuum analyze after delete completes or can I =
leave this job to autovacuum?<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>This table =
is very active during the day but less active during =
night<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>I think that the only only thing where Postgres is =
weak, is in this area (table and index bloat).<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>For some =
reason for the same amount of data every day postgres consume a little =
more.<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>Thanks!<o:p></o:p></p></div></body></html>
------=_NextPart_000_009E_01CD7BCC.F14A84F0--





From andrew@dunslane.net Thu Aug  16 20:48:46 2012
Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andrew@dunslane.net>) id 1T26zm-0004qr-Fc
	for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 20:48:46 +0000
Received: from relay3-d.mail.gandi.net ([217.70.183.195])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andrew@dunslane.net>) id 1T26zk-0006Rb-3g
	for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 20:48:45 +0000
X-Originating-IP: 217.70.178.129
Received: from mfilter12-d.gandi.net (mfilter12-d.gandi.net [217.70.178.129])
	by relay3-d.mail.gandi.net (Postfix) with ESMTP id DA2D1A8076;
	Thu, 16 Aug 2012 22:48:32 +0200 (CEST)
X-Virus-Scanned: Debian amavisd-new at mfilter12-d.gandi.net
Received: from relay3-d.mail.gandi.net ([217.70.183.195])
	by mfilter12-d.gandi.net (mfilter12-d.gandi.net [10.0.15.180])
	(amavisd-new, port 10024)
	with ESMTP id HL4MP6jW+8RU; Thu, 16 Aug 2012 22:48:31 +0200 (CEST)
X-Originating-IP: 98.27.54.176
Received: from [192.168.10.146] (cpe-098-027-054-176.nc.res.rr.com
	[98.27.54.176]) (Authenticated sender: adsend@dunslane.net)
	by relay3-d.mail.gandi.net (Postfix) with ESMTPSA id 3CBF3A807A;
	Thu, 16 Aug 2012 22:48:31 +0200 (CEST)
Message-ID: <502D5C9D.4020502@dunslane.net>
Date: Thu, 16 Aug 2012 16:48:29 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Anibal David Acosta <aa@devshock.com>
CC: pgsql-performance@postgresql.org
Subject: Re: best practice to avoid table bloat?
References: <009d01cd7bee$785b1380$69113a80$@devshock.com>
In-Reply-To: <009d01cd7bee$785b1380$69113a80$@devshock.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201208/113
X-Sequence-Number: 47651


On 08/16/2012 04:33 PM, Anibal David Acosta wrote:
>
> Hi,
>
> if I have a table that daily at night is deleted about 8 millions of 
> rows (table maybe has 9 millions) is recommended to do a vacuum 
> analyze after delete completes or can I leave this job to autovacuum?
>
> This table is very active during the day but less active during night
>
> I think that the only only thing where Postgres is weak, is in this 
> area (table and index bloat).
>
> For some reason for the same amount of data every day postgres consume 
> a little more.
>
>


Check out pg_reorg.

cheers

andrew




From Kevin.Grittner@wicourts.gov Thu Aug  16 20:52:32 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <Kevin.Grittner@wicourts.gov>) id 1T273Q-0007ps-KG
	for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 20:52:32 +0000
Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <Kevin.Grittner@wicourts.gov>) id 1T273O-0001XQ-N1
	for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 20:52:32 +0000
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Thu, 16 Aug 2012 15:52:29 -0500
Message-Id: <502D173702000025000497D1@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.2 
Date: Thu, 16 Aug 2012 15:52:23 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Anibal David Acosta" <aa@devshock.com>,
 <pgsql-performance@postgresql.org>
Subject: Re: best practice to avoid table bloat?
References: <009d01cd7bee$785b1380$69113a80$@devshock.com>
In-Reply-To: <009d01cd7bee$785b1380$69113a80$@devshock.com>
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: 201208/114
X-Sequence-Number: 47652

"Anibal David Acosta" <aa@devshock.com> wrote:
 
> if I have a table that daily at night is deleted about 8 millions
> of rows (table maybe has 9 millions) is recommended to do a vacuum
> analyze after delete completes or can I leave this job to
> autovacuum?
 
Deleting a high percentage of the rows should cause autovacuum to
deal with the table the next time it wakes up, so an explicit VACUUM
ANALYZE shouldn't be needed.
 
> For some reason for the same amount of data every day postgres
> consume a little more.
 
How are you measuring the data and how are you measuring the space? 
And what version of PostgreSQL is this?
 
-Kevin




From aa@devshock.com Thu Aug  16 21:10:37 2012
Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1T27Ku-0005X5-W3
	for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 21:10:37 +0000
Received: from mail-gg0-f174.google.com ([209.85.161.174])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1T27Ks-0006rn-1G
	for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 21:10:36 +0000
Received: by ggdk6 with SMTP id k6so3428073ggd.19
	for <pgsql-performance@postgresql.org>;
	Thu, 16 Aug 2012 14:10:33 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=from:to:references:in-reply-to:subject:date:message-id:mime-version
	:content-type:content-transfer-encoding:x-mailer:thread-index
	:content-language:x-gm-message-state;
	bh=pzOlOeq8Wgfmldf/JJG1ZbjX6T63w+xJPQhqeSovNlc=;
	b=SznPSS3IeKDCO4VnfR0O8+QxNZ6Yo2AvE+YvTFRkIx9gFNCBCirDtCOchgn46MRs9y
	3OeIScTMczgalgbo/JiKVklQKGyP+PNAnkeIRlaf+5gZfrrt6z4qTCgZabLmgWqUiRLJ
	6D8g5JwovTsqiYe51OtIRsEDNYLs68w+PPbv5iEkNNx7Yrul/HvXPDByNQ00M7nR5F/F
	CppaQoCaoWJbx8Sg5MUawGr5r7ukjFUgKp2G78AcsndTWL+QTEIt1m2g/c4SvV/p6V4p
	UJq+yYhgwn65DWnnwXArC6dzaePlEIuBF934ukq8oagXBMh/3ylpHCxuhwSR8K7ZZcwq
	81RQ==
Received: by 10.101.60.16 with SMTP id n16mr1128638ank.16.1345151432997;
	Thu, 16 Aug 2012 14:10:32 -0700 (PDT)
Received: from AnibalVAIO ([190.128.222.90])
	by mx.google.com with ESMTPS id c64sm10361215yhj.17.2012.08.16.14.10.31
	(version=TLSv1/SSLv3 cipher=OTHER);
	Thu, 16 Aug 2012 14:10:32 -0700 (PDT)
From: "Anibal David Acosta" <aa@devshock.com>
To: "'Kevin Grittner'" <Kevin.Grittner@wicourts.gov>,
	<pgsql-performance@postgresql.org>
References: <009d01cd7bee$785b1380$69113a80$@devshock.com>
	<502D173702000025000497D1@gw.wicourts.gov>
In-Reply-To: <502D173702000025000497D1@gw.wicourts.gov>
Subject: Re: best practice to avoid table bloat?
Date: Thu, 16 Aug 2012 17:10:31 -0400
Message-ID: <00c201cd7bf3$93bc4130$bb34c390$@devshock.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AQK7QPGWhO6yMB3u2/1Hg2+iMZ7ltwHSQBc1lXLPf8A=
Content-Language: es-py
X-Gm-Message-State: 
 ALoCoQm7QyLavJRsvsni6m0wELRRP79ZX9EQ/73NdrS/3LxpQjo9g3mDs4U+POrNuDx+rVcyUBZR
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201208/115
X-Sequence-Number: 47653

Thanks Kevin.
Postgres version is 9.1.4 (lastest)

Every day the table has about 7 millions of new rows.
The table hold the data for 60 days, so approx. the total rows must be
around 420 millions.
Every night a delete process run, and remove rows older than 60 days.

So, the space used by postgres should not be increase drastically because
every day arrive 7 millions of rows but also same quantity is deleted but my
disk get out of space every 4 months.
I must copy tables outside the server, delete local table and create it
again, after this process I got again space for about 4 months.

Maybe is a wrong autovacuum config, but is really complicate to understand
what values are correct to avoid performance penalty but to keep table in
good fit.

I think that autovacuum configuration should have some like "auto-config"
that recalculate every day which is the best configuration for the server
condition

Thanks!


-----Mensaje original-----
De: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] 
Enviado el: jueves, 16 de agosto de 2012 04:52 p.m.
Para: Anibal David Acosta; pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] best practice to avoid table bloat?

"Anibal David Acosta" <aa@devshock.com> wrote:
 
> if I have a table that daily at night is deleted about 8 millions of 
> rows (table maybe has 9 millions) is recommended to do a vacuum 
> analyze after delete completes or can I leave this job to autovacuum?
 
Deleting a high percentage of the rows should cause autovacuum to deal with
the table the next time it wakes up, so an explicit VACUUM ANALYZE shouldn't
be needed.
 
> For some reason for the same amount of data every day postgres consume 
> a little more.
 
How are you measuring the data and how are you measuring the space? 
And what version of PostgreSQL is this?
 
-Kevin





From Kevin.Grittner@wicourts.gov Thu Aug  16 21:39:32 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <Kevin.Grittner@wicourts.gov>) id 1T27mu-0000nC-3R
	for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 21:39:32 +0000
Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <Kevin.Grittner@wicourts.gov>) id 1T27mr-0002sk-1S
	for pgsql-performance@postgresql.org; Thu, 16 Aug 2012 21:39:31 +0000
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Thu, 16 Aug 2012 16:39:28 -0500
Message-Id: <502D223702000025000497D7@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.2 
Date: Thu, 16 Aug 2012 16:39:19 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Anibal David Acosta" <aa@devshock.com>,
 <pgsql-performance@postgresql.org>
Subject: Re: best practice to avoid table bloat?
References: <009d01cd7bee$785b1380$69113a80$@devshock.com>
	<502D173702000025000497D1@gw.wicourts.gov>
	<00c201cd7bf3$93bc4130$bb34c390$@devshock.com>
In-Reply-To: <00c201cd7bf3$93bc4130$bb34c390$@devshock.com>
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: 201208/116
X-Sequence-Number: 47654

[please don't top-post]

"Anibal David Acosta" <aa@devshock.com> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.go> wrote:
>> "Anibal David Acosta" <aa@devshock.com> wrote:
>>  
>>> if I have a table that daily at night is deleted about 8
>>> millions of rows (table maybe has 9 millions) is recommended to
>>> do a vacuum analyze after delete completes or can I leave this
>>> job to autovacuum?
>>  
>> Deleting a high percentage of the rows should cause autovacuum to
>> deal with the table the next time it wakes up, so an explicit
>> VACUUM ANALYZE shouldn't be needed.
 
> Every day the table has about 7 millions of new rows.
> The table hold the data for 60 days, so approx. the total rows
> must be around 420 millions.
> Every night a delete process run, and remove rows older than 60
> days.
 
Oh, I thought you were saying the table grew to 9 million rows each
day and you deleted 8 million of them each night.  That would
definitely trigger autovacuum.  Deleting 7 million rows from a table
of 420 million rows would not, so an explicit VACUUM ANALYZE after
the delete might be helpful.  Even better, with a pattern like that,
you might want to consider partitioning the table:
 
http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
 
>>> For some reason for the same amount of data every day postgres
>>> consume a little more.
>>  
>> How are you measuring the data and how are you measuring the
>> space?
 
> [no answer]
 
Without knowing what is increasing, it's hard to say why it is
increasing.  For all we know you are logging all statements and
never deleting log files.  The solution for that would be entirely
different from the solution for some other problem.
 
> So, the space used by postgres should not be increase drastically
> because every day arrive 7 millions of rows but also same quantity
> is deleted but my disk get out of space every 4 months.
 
What is getting bigger over time?
 
> I must copy tables outside the server, delete local table and
> create it again, after this process I got again space for about 4
> months.
 
How do you do that?  pg_dump, DROP TABLE, restore the dump?  Have
you captured sizes of heap, toast, indexes, etc. before and after
this aggressive maintenance?  Is the size going up by orders of
magnitude, or are you running really tight and getting killed by a
10% increase.  We don't know unless you tell us.
 
> Maybe is a wrong autovacuum config, but is really complicate to
> understand what values are correct to avoid performance penalty
> but to keep table in good fit.
 
Please show us the entire result from running this query:
 
http://wiki.postgresql.org/wiki/Server_Configuration
 
-Kevin