Re: VACUUM Performance - Mailing list pgsql-performance

From Tom Lane
Subject Re: VACUUM Performance
Date
Msg-id 21641.1161406926@sss.pgh.pa.us
Whole thread Raw
In response to VACUUM Performance  ("Steve Oualline" <soualline@stbernard.com>)
List pgsql-performance
"Steve Oualline" <soualline@stbernard.com> writes:
> Question:   I have a big table with 120,000,000 records.
> Let's assume that I DELETE 4,000,000 records, VACUUM FULL, and REINDEX.
> Now I have the same table, but with 240,000,000 records.
> I DELETE 8,000,000 records, VACUUM FULL, and REINDEX.
> Should the second operation with twice the data take twice the time as =
> the first?

At least.  If you intend to reindex all the indexes, consider instead
doing
    DROP INDEX(es)
    VACUUM FULL
    re-create indexes
as this avoids the very large amount of effort that VACUUM FULL puts
into index maintenance --- effort that's utterly wasted if you then
reindex.

CLUSTER and some forms of ALTER TABLE can accomplish a table rewrite
with less hassle than the above, although strictly speaking they violate
MVCC by discarding recently-dead tuples.

            regards, tom lane

pgsql-performance by date:

Previous
From: "Joshua D. Drake"
Date:
Subject: Re: Is ODBC that slow?
Next
From: Péter Kovács
Date:
Subject: Re: Index on two columns not used