Re: Vacuum question

"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