From mariobehring@yahoo.com Fri Jan  12 17:45:46 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 50DA19FB3CA
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 13:45:46 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 20603-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 13:45:29 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from web30014.mail.mud.yahoo.com (web30014.mail.mud.yahoo.com
	[209.191.69.31]) by postgresql.org (Postfix) with SMTP id 1E6579FB360
	for <pgsql-sql@postgresql.org>; Fri, 12 Jan 2007 13:45:30 -0400 (AST)
Received: (qmail 28148 invoked by uid 60001); 12 Jan 2007 17:45:29 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=kHUSc6Q0C4iVM2KY9A7gA7BQ5penkjtprpt0+kwEwTb0Iltdkg5cwVbxBpaJg+J3pPrYlDv/eYZS/yO0HCaEwsVIX5lya3bcoqv7CMLLF5j7D/aAD9d3+shtBIiFZjbjojhwYLM6G5H44DjeDzFV0gEj7tSizN7hCtLg9V1mhec=;
Received: from [200.161.5.135] by web30014.mail.mud.yahoo.com via HTTP;
	Fri, 12 Jan 2007 09:45:28 PST
X-Mailer: YahooMailRC/368.3 YahooMailWebService/0.6.132.6
Date: Fri, 12 Jan 2007 09:45:28 -0800 (PST)
From: Mario Behring <mariobehring@yahoo.com>
Subject: deleting records from a table
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-754859633-1168623928=:26875"
Message-ID: <4186.26875.qm@web30014.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/38
X-Sequence-Number: 27190

--0-754859633-1168623928=:26875
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi all,=0A=0ASimple question: once I execute the delete statement, does it =
free disk space immediatelly? Does Postgres uses something like a datafile =
with a pre-defined size like Oracle does??=0A=0APlease advise.=0A=0AThank y=
ou.=0A=0AMario=0A=0A=0A=0A=0A =0A__________________________________________=
__________________________________________=0AGet your own web address.  =0A=
Have a HUGE year through Yahoo! Small Business.=0Ahttp://smallbusiness.yaho=
o.com/domains/?p=3DBESTDEAL
--0-754859633-1168623928=:26875
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D"text/css"><!-- DIV {margin:0px;} --></style></he=
ad><body><div style=3D"font-family:arial,helvetica,sans-serif;font-size:10p=
t"><div>Hi all,<br><br>Simple question: once I execute the delete statement=
, does it free disk space immediatelly? Does Postgres uses something like a=
 datafile with a pre-defined size like Oracle does??<br><br>Please advise.<=
br><br>Thank you.<br><br>Mario<br></div></div><br>=0A=0A<hr size=3D1>=0ADon=
't get soaked.  Take a<a href=3D"=0Ahttp://tools.search.yahoo.com/shortcuts=
/?fr=3Doni_on_mail&#news"> quick peak at the forecast </a><br> with the<a h=
ref=3D"=0Ahttp://tools.search.yahoo.com/shortcuts/?fr=3Doni_on_mail&#news">=
Yahoo! Search weather shortcut.</a></body></html>
--0-754859633-1168623928=:26875--



From rabroersma@yahoo.com Fri Jan  12 17:53:40 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 760989FB367
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 13:53:40 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
	with ESMTP id 61006-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 13:53:35 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from web31803.mail.mud.yahoo.com (web31803.mail.mud.yahoo.com
	[68.142.207.66]) by postgresql.org (Postfix) with SMTP id F32149FB313
	for <pgsql-sql@postgresql.org>; Fri, 12 Jan 2007 13:53:34 -0400 (AST)
Received: (qmail 1826 invoked by uid 60001); 12 Jan 2007 17:53:33 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=1/3sh/gkHE2m1T0sR6/CJntwTfqgl7HkjSxkDFJhz3eSqgogkbVQHL/AgSgvCrk9O7UlOp/oIJZpTbqK+PlMfSwn0xSU/TNPD8jSnWoNtosUFopGu1zkyTrDage00Vy/g+p8dlYgHzVjavdVxsm10k7RMiTrLcVGQHr5xhdwkEM=;
X-YMail-OSG: 
 AFU49fQVM1ni9ifjBQZr5pcPOsoVZx9TOtPfr4Yid7jjWs74JXan9qjsloW.cDthBbWWu.PdaA6tjBIHwmRexeELrExSwwr8x_URAihdD23C5yE.c9.kDEDPwJmD68NeSWQ3RJblsbdT4rmr6s.MnPartryHv4ax
Received: from [63.203.180.122] by web31803.mail.mud.yahoo.com via HTTP;
	Fri, 12 Jan 2007 09:53:33 PST
Date: Fri, 12 Jan 2007 09:53:33 -0800 (PST)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: deleting records from a table
To: Mario Behring <mariobehring@yahoo.com>, pgsql-sql@postgresql.org
In-Reply-To: <4186.26875.qm@web30014.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <877653.1327.qm@web31803.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.659 tagged_above=0 required=5 tests=AWL, BAYES_20,
	DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS
X-Spam-Level: 
X-Archive-Number: 200701/39
X-Sequence-Number: 27191


---> Simple question: once I execute the delete statement, does it free disk space immediatelly?
No.  deleted rows (in a sense) are simply marked as deleted and are made avaliable for being
written over by a new or updated tuple.  If you want to recover the space you can you TRUNCATE or
VACUUM FULL & possible REINDEX.

Regards,
Richard Broersma Jr.



From rabroersma@yahoo.com Fri Jan  12 18:30:52 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 3E7ED9FB442
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 14:30:52 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 70664-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 14:30:34 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from web31801.mail.mud.yahoo.com (web31801.mail.mud.yahoo.com
	[68.142.207.64]) by postgresql.org (Postfix) with SMTP id 8743E9FB3BA
	for <pgsql-sql@postgresql.org>; Fri, 12 Jan 2007 14:30:35 -0400 (AST)
Received: (qmail 2474 invoked by uid 60001); 12 Jan 2007 18:30:34 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=TsvmoEv2d393sGGouNxCLSa96mSwQygXjn3y6m0rgD5M3BX6asan20PxrsMEaDC4ZQHqKhS+jf1e/ASi7d7DgqNsUnAOCWsCPvQFrKscKFKn+WNgNvNyL9v91aHQf+1IwMKwr/hPxoMLhFyAmAx2Eo4joXubD9xRvepNvgGbGtU=;
X-YMail-OSG: 
 ebdN6Q4VM1nYjtKaaVNF3QzbdKVngJ31hWyctA_wRWOh9zmmdLpAr7rq1B920O7WjzId36WNLW9nlGDrxhXCfyLfZ5qA5uZt_DP2vGxm81vZmG2p91XrqGQhaGy9nuWN9NmM6LQV93oOYkI-
Received: from [63.203.180.122] by web31801.mail.mud.yahoo.com via HTTP;
	Fri, 12 Jan 2007 10:30:33 PST
Date: Fri, 12 Jan 2007 10:30:33 -0800 (PST)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: deleting records from a table
To: Mario Behring <mariobehring@yahoo.com>
Cc: SQL Postgresql List <pgsql-sql@postgresql.org>
In-Reply-To: <665687.90156.qm@web30006.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <573892.1143.qm@web31801.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/40
X-Sequence-Number: 27192

> Thank you for your help.
> 
> I am already running the vacuumdb full.............after it finishes (I guess it will take a
> while to finish...) how should I execute this REINDEX ? Is there a single command to reindex the
> entire database?

Here is a link that describes its use:
http://www.postgresql.org/docs/8.2/interactive/app-reindexdb.html

Regards,
Richard Broersma Jr.



From smarlowe@g2switchworks.com Fri Jan  12 18:49:10 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 67D899FB46B
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 14:49:10 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 47071-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 14:48:45 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from exchange.g2switchworks.com (mail.g2switchworks.com
	[63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 11EC99FB43E
	for <pgsql-sql@postgresql.org>; Fri, 12 Jan 2007 14:48:44 -0400 (AST)
Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com
	([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ;
	Fri, 12 Jan 2007 18:48:42 +0000
Received: from state.g2switchworks.com by mail.g2switchworks.com;
	12 Jan 2007 12:48:42 -0600
Subject: Re: deleting records from a table
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Mario Behring <mariobehring@yahoo.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <4186.26875.qm@web30014.mail.mud.yahoo.com>
References: <4186.26875.qm@web30014.mail.mud.yahoo.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1168624891.20602.693.camel@state.g2switchworks.com>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Fri, 12 Jan 2007 12:48:42 -0600
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/41
X-Sequence-Number: 27193

On Fri, 2007-01-12 at 11:45, Mario Behring wrote:
> Hi all,
> 
> Simple question: once I execute the delete statement, does it free
> disk space immediatelly? Does Postgres uses something like a datafile
> with a pre-defined size like Oracle does??
> 
Take a look here:

http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html
http://www.postgresql.org/docs/8.2/static/mvcc-intro.html

That should explain the basics of mvcc and vacuuming as applies to
pgsql.



From ajs@crankycanuck.ca Fri Jan  12 20:52:15 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id E037A9FB30D
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 16:52:15 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 01979-08 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 12 Jan 2007 16:52:02 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from main2.mycybernet.net (main2.mycybernet.net [209.222.63.140])
	by postgresql.org (Postfix) with ESMTP id 0D9569FB28B
	for <pgsql-sql@postgresql.org>; Fri, 12 Jan 2007 16:52:09 -0400 (AST)
Received: from 227-54-222-209.mycybernet.net ([209.222.54.227]
	helo=phlogiston.dydns.org)
	by main2.mycybernet.net with esmtp (Exim 4.62)
	(envelope-from <ajs@phlogiston.dyndns.org>) id 1H5TWw-0006k5-BH
	for pgsql-sql@postgresql.org; Fri, 12 Jan 2007 16:01:42 -0500
Received: by phlogiston.dydns.org (Postfix, from userid 1000)
	id 976B841A2; Fri, 12 Jan 2007 15:51:59 -0500 (EST)
Date: Fri, 12 Jan 2007 15:51:59 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: deleting records from a table
Message-ID: <20070112205159.GA2806@phlogiston.dyndns.org>
References: <4186.26875.qm@web30014.mail.mud.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4186.26875.qm@web30014.mail.mud.yahoo.com>
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/42
X-Sequence-Number: 27194

On Fri, Jan 12, 2007 at 09:45:28AM -0800, Mario Behring wrote:
> Simple question: once I execute the delete statement, does it free
> disk space immediatelly? Does Postgres uses something like a
> datafile with a pre-defined size like Oracle does??

What others said; but note that a vacuumed table that has a number of
now-empty slots can actually be a performance advantage, because new
rows don't need to increase the size of the table's on-disk file (so
you incur slightly less I/O).  There's a "sweet spot" for this that
you can discover by testing.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
		--H.W. Fowler