CODEWITHSUNDEEP
postgresqlpostgresql-9.4
user1517922
user1517922

Reputation: 1468

PostgreSQL VACUUM(FULL, ANALYZE, VERBOSE) Duration 1.6T Table

We have a large table (1.6T) and deleted 60% of the records, and want to reclaim that space for the OS and file system. We're running PostgreSQL 9.4 (we're stuck on that pending a major software upgrade).

We need that space, as we're down to 100GB and when materialized views are refreshed we're running out of space on the server.

I tried running VACUUM(FULL, ANALYZE, VERBOSE) schema.tablename and let it run for 24 hours last weekend, but had to cancel it to get the server back online.

I'm running it again this weekend, after deleting the indexes (I'm hoping that will speed it up so it will finish). So far there is no output or indication of progress. I created a tablespace on another SSD array and set it up as temp space using temp_tablespaces = 'name_of_other_tablespaces', but du -chs shows it is still empty.

The query shows active, but since disk usage isn't increasing it just feels like it's just sitting there, making no noise and pretending it's not there.

This is on a server with 512GB of RAM and a RAID 10 array of very fast enterprise SSDs. Is there any way to get progress and know that something is actually happening and that it's working? Any guesses as to duration, or other suggestions?

Upvotes: 5

Views: 10735

Answers (1)

user1517922
user1517922

Reputation: 1468

I found out what was happening, by finally noticing that it was waiting for an autovacuum process to finish, which never happened (autovacuum: VACUUM pg_toast.pg_toast_nnnnn (to prevent wraparound)). Once I killed that the VACUUM ran quite quickly and cleared up over 1TB of space. Time to celebrate!

Upvotes: 8

Related Questions