2

After receiving some insightful guidance in a previous post here, I'm going to run VACUUM FULL on 4 PostgreSQL 9.3.10 tables. Table sizes are:

1) links_publicreply: ~30M rows, 9 columns, 3 indexes (types: int, timestamp, char, bool)

2) links_reply: ~25M rows, 8 columns, 6 indexes (types: int, text, timestamp, char)

3) links_link: ~8M rows, 14 columns, 3 indexes (types: int, text, dbl precision, timestamp, char bool)

4) links_user_sessions: ~2M rows, 7 columns, 4 indexes (types: int, text, timestamp, inet)

This is my first attempt at reclaiming disk space. It's a busy server of a local social networking website. No time is actually "downtime". But the least busy is ~4:00 AM, so that the window I'll use.

Speaking from experience, can you guys form any opinion on how long VACUUM FULL would take for the 4 tables I pointed out? I'd like to put up a "under maintenance till xx:xx:xx" message on the website while it's happening. I know no one can be sure, but is this deterministic enough for you to form a ballpark opinion?

Secondly, just so that we're on the same page, the commands I'd be running on psql are simply VACUUM (FULL, VERBOSE, ANALYZE) link_publicreply; (and so on), correct? Don't want to screw it up.

  • It's more like a question on the other question, but: how do you plan to move your DB from Azure to AWS? By taking a dump here and restore there? In this case, VACUUM FULL won't give any advantage. If via setting up some replication (streaming or similar), then it might make sense. – dezso Sep 28 '17 at 15:32
  • @dezso: yes just via pg_dump. I was hoping this would lower the size of the dump I have to move (I halved these tables). Wouldn't that be an advantage in itself? – Hassan Baig Sep 28 '17 at 15:45
  • It doesn't have an effect on the dump size. On the other hand, taking the dump will be possibly faster - not sure how much faster though. – dezso Sep 28 '17 at 15:48
4

It's a busy server of a local social networking website. No time is actually "downtime".

So VACUUM FULL is going to be a problem since it takes an exclusive lock on each table it processes. Consider the community tool pg_repack instead which achieves the same without exclusive locks.

Related:

None of this affects the size of backups, since those do not include dead rows to begin with.

  • In other words, by taking pg_dump and transferring it, I would already have reclaimed the space in my new set up? – Hassan Baig Sep 28 '17 at 16:20
  • @HassanBaig: That's correct. A dump/restore cycle produces a database in pristine condition without any bloat. It's not exactly "reclaiming" space, since the additional space is never claimed to begin with ... – Erwin Brandstetter Sep 28 '17 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.