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 VACUUM
ed gives:
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?)
Best Answer
Point about TRUNCATE/COPY
Unrelated note, depending on the WAL level, wrapping the
TRUNCATE
andCOPY
in the same transaction things may go faster, because WAL will be skipped. Further the CTAS will always skip most WAL.Issue of
VACUUM FULL
There is no need to do a
VACUUM FULL
on a new table. The table is already new in those transactions so there is no need to rewrite it, asFULL
does. There is also no need toVACUUM
it because there are no movable rows that VACUUM will act on. When you runVACUUM FULL VERBOSE
, you can see nothing was removable, and no dead rows were removed.VACUUM FULL
can be useful ifINSERTS
though a temp table, and adding them in batches into a dirty table. Then it can often pay off at the end of the process.UPDATE
on a large batch becauseUPDATE
generates new rows forcingVACUUM
to later mark the old rows as dead.A simple
ANALYZE
will work fine. This will update the statistics on the table.