Skip site navigation (1) Skip section navigation (2)

Re: Vacuum question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Patrick Hatcher" <PHatcher(at)macys(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Vacuum question
Date: 2002-10-18 14:11:01
Message-ID: 17360.1034950261@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"Patrick Hatcher" <PHatcher(at)macys(dot)com> writes:
> Each night I truncate and refresh around 90% of our database.  After each
> table is re-populated, I Vacuum Analyze.  Should I also do a Vacuum Full on
> each of these tables? And if so, should I do the full after I've truncated
> all the data or after I've repopulated and before my vacuum analyze?

If you are actually using TRUNCATE (and not just a DELETE), followed by
only INSERTs and no UPDATEs, then I don't think there's any use for a
VACUUM FULL.  But check for yourself: run the VACUUM FULL with VERBOSE
option some typical night, and eyeball the output to see if it's
managing to shorten any tables significantly (look at the # of pages in
particular).  An example:

regression=# create table foo as select * from tenk1;
SELECT
regression=# delete from foo;
DELETE 10000
regression=# insert into foo select * from tenk1;
INSERT 0 10000
regression=# vacuum full verbose foo;
INFO:  --Relation public.foo--
INFO:  Pages 690: Changed 690, reaped 345, Empty 0, New 0; Tup 10000: Vac 10000, Keep/VTL 0/0, UnUsed 0, MinLen 268, MaxLen 268; Re-using: Free/Avail. Space 2838680/2780888; EndEmpty/Avail. Pages 0/346.
        CPU 0.09s/0.09u sec elapsed 0.19 sec.
INFO:  Rel foo: Pages: 690 --> 345; Tuple(s) moved: 9995.
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        CPU 0.19s/0.45u sec elapsed 0.66 sec.
VACUUM
regression=# drop table foo;
DROP TABLE
regression=# create table foo as select * from tenk1;
SELECT
regression=# truncate foo;
TRUNCATE TABLE
regression=# insert into foo select * from tenk1;
INSERT 0 10000
regression=# vacuum full verbose foo;
INFO:  --Relation public.foo--
INFO:  Pages 345: Changed 345, reaped 0, Empty 0, New 0; Tup 10000: Vac 0, Keep/VTL 0/0, UnUsed 0, MinLen 268, MaxLen 268; Re-using: Free/Avail. Space 59340/1548; EndEmpty/Avail. Pages 0/1.
        CPU 0.04s/0.03u sec elapsed 0.09 sec.
INFO:  Rel foo: Pages: 345 --> 345; Tuple(s) moved: 0.
       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
regression=#

In the second case, a VACUUM would have done as well as the VACUUM FULL,
since VACUUM FULL failed to shorten the table anyway, there being no
reclaimable space.

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Stephan SzaboDate: 2002-10-18 18:18:22
Subject: Re: [SQL] foreign key, create table, and transactions
Previous:From: Tom LaneDate: 2002-10-18 13:59:56
Subject: Re: psql/postmaster not freeing up resources? (based on "top" command)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group