Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id A59B51337B34 for ; Tue, 5 Oct 2010 11:56:23 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 01303-05 for ; Tue, 5 Oct 2010 14:56:17 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from bay0-omc1-s19.bay0.hotmail.com (bay0-omc1-s19.bay0.hotmail.com [65.54.190.30]) by mail.postgresql.org (Postfix) with ESMTP id 481AD1335B36 for ; Tue, 5 Oct 2010 11:56:16 -0300 (ADT) Received: from BAY149-W24 ([65.54.190.61]) by bay0-omc1-s19.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Tue, 5 Oct 2010 07:56:16 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_de6b8934-56ee-4743-91dd-a42e72346bb2_" X-Originating-IP: [91.206.0.43] From: Kieren Scott To: Subject: vacuum full table - internals in 8.3 Date: Tue, 5 Oct 2010 14:56:15 +0000 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 05 Oct 2010 14:56:16.0467 (UTC) FILETIME=[76206A30:01CB649D] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/37 X-Sequence-Number: 35778 --_de6b8934-56ee-4743-91dd-a42e72346bb2_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi=2C I'm trying to understand what is going on internally when doing a VACUUM FU= LL on a table in 8.3. I have a table that is 1GB in size=2C 500M is used=2C and 500M is free spac= e. When I do a vacuum full on this table=2C will it either: - 1) Compact all of the used tuples into free space within the existing disk = file and then shrink the file to 500M. Therefore simply freeing up 500M in the disk file. 2) Rewrite the table data to a new disk file with no free space. Once it ha= s finished=2C then removes the old copy of the table. I took this from http://developer.postgresql.org= /pgdocs/postgres/sql-vacuum.html =2C but I don't know whether this is how it worked prior to version 9. In the case of 2) I would therefore need at least 500M free space in the fi= lesystem whist it writes out the new table=2C and once complete=2C 1GB would then be freed up when the o= ld copy of the table is deleted. Regards Kieren = --_de6b8934-56ee-4743-91dd-a42e72346bb2_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi=2C

I'm trying to understand what is going on internally when doin= g a VACUUM FULL on a table in 8.3.

I have a table that is 1GB in siz= e=2C 500M is used=2C and 500M is free space. When I do a vacuum full
on = this table=2C will it either: -

1) Compact all of the used tuples in= to free space within the existing disk file and then shrink the file
&nb= sp=3Bto 500M. =3B Therefore simply freeing up 500M in the disk file.
2) Rewrite the table data to a new disk file with no free space. Once = it has finished=2C then removes
the old copy of the table. I took this f= rom http://developer.postgresql.org/pgdocs/postgres/sql-vacuum.html
=2C = but I don't know whether this is how it worked prior to version 9.

I= n the case of 2) I would therefore need at least 500M free space in the fil= esystem whist it writes out
the new table=2C and once complete=2C 1GB wo= uld then be freed up when the old copy of the table is
deleted.

R= egards

Kieren
= --_de6b8934-56ee-4743-91dd-a42e72346bb2_--