Re: vacuum vs pg_repack vs pg_reorg

sramay <nic(dot)srama(at)gmail(dot)com> wrote:

> I have a database of size 1.5 TB. The attachments are stored in bytea.
> The attachment table is consuming maximum space. The database version is
> 9.1.x and Streaming Replication is set. Now, I have to removed old records
> to make way for new records without increasing SAN Space.
>
> Which will be a better option
>
> pg_repack - I have primary key on that table
>
> or
> Vacuum
>
> or
> pg_reorg
>
> If you can suggest me a better option out of the above, It will simplify my
> life. At any cost streaming replication should not disturbed.

I strongly recommend just plain VACUUM ANALYZE (or leave it to
autovacuum). The others cause the table to give space back to the
OS filesystem (in your case on a SAN), and that means that before
that space can be used again it will need to be allocated from the
OS again. This is slower and can cause concurrency issues on
extending the table.

You may see a transient increase in space required for WAL archives
during any action that affects a lot of data. If that's a problem
you may want to delete rows in batches big enough to stay ahead of
the insertions, but small enough to limit the archive size --
assuming you clean up the archives.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company