vacuum vs pg_repack vs pg_reorg

sramay <nic.srama@gmail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Mr Kevin,

Thanks for the suggestion, VACUUM analyze is a much simpler solution
compared to rest.
Thanks once more.

Regards

Ramachandran S

--
View this message in context: http://postgresql.nabble.com/vacuum-vs-pg-repack-vs-pg-reorg-tp5832261p5832499.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general