We are updating a datawarehouse on a weekly basis using a series of
TRUNCATE source_table1 COPY source_table1 FROM [...]
... for data import and:
DROP TABLE IF EXISTS my_table CREATE TABLE my_table AS SELECT [...]
for table updates.
We end our update process with a
VACUUM FULL [VERBOSE] ANALYZE, because, as the documentation suggests,
VACUUM should be done when a significant share of tuples has been updated or removed.
Here, as it is a share of 100% for all tables, we reasonably thought that
VACUUM should be applied.
As we see the ouptput of the verbose option, it seems that Postgresql has not much to do, as every table
INFO: vacuuming "public.table345" INFO: "table345": found 0 removable, 9831703 nonremovable row versions in 62538 pages DETAIL : 0 dead row versions cannot be removed yet.
I guess that on the contrary
ANALYZE is more than helpfull to update the internal statistics.
Most tables are 10-100m rows big.
But we were wondering if
VACUUM FULL or just
VACUUM were really necessary in that case?
(Or may be the whole update process (DROP / CREATE TABLE AS) is not the right way to do?)