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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-10-18 18:18:22 | Re: [SQL] foreign key, create table, and transactions |
Previous Message | Tom Lane | 2002-10-18 13:59:56 | Re: psql/postmaster not freeing up resources? (based on "top" command) |