Re: VACUUM FULL vs backup/restore

On Tue, Apr 27, 2010 at 2:50 PM, David Wall <d(dot)wall(at)computer(dot)org> wrote:
> We are copying a production database and then pairing it down dramatically
> (perhaps removing 99% of records, most of which are large objects).
>
> It seems my options are 1) VACUUM FULL with a reindex_db; or 2) backup, then
> restore.
>
> Is there anything one does better than the other?  Our impression is that
> the backup and restore will run faster (perhaps 2 hours), whereas we have a
> currently running VACUUM FULL that's been running for 4.5 hours already.

Vacuum Full was invented back in the days when drive space was not as
cheap as it is today. It can shrink a table without having to have
enough room free on the drive for a complete copy to be made. In
pgsql 9.0 that behaviour is changing, making vacuum full much faster
than it once was.

So, the advantage of vacuum full is that it can (for now) operate in a
space restricted environment if needed.

Given how cheap drives are nowadays, the preferred method is either to
cluster in place each table (which needs 2x file size drive space) or
backup / restore the db.