3

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.

Improve this question
3
  • 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
    Commented Sep 28, 2017 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
    Commented Sep 28, 2017 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
    Commented Sep 28, 2017 at 15:48

1 Answer 1

Reset to default
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.

Improve this answer
2
  • In other words, by taking pg_dump and transferring it, I would already have reclaimed the space in my new set up?
    – Hassan Baig
    Commented Sep 28, 2017 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
    Commented Sep 28, 2017 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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