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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Hassan Baig
  • 1,959
  • 8
  • 29
  • 41
  • 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. – András Váczi 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. – András Váczi Sep 28 '17 at 15:48

1 Answers1

7

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.

Community
  • 1
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • 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
  • 1
    @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