VACUUM FULL into another tablespace?

From: Michael Kussmaul <kussmaul(dot)list(at)nix(dot)ch>
To: pgsql-admin(at)postgresql(dot)org
Subject: VACUUM FULL into another tablespace?
Date: 2013-06-14 17:04:19
Message-ID: 762524C5-AC0B-48C5-897E-6313C1ABC52E@nix.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I have a very large table on my PostgreSQL 9.1 database (openstreetmap data: 64GB data + 100GB indexes in one particular table) stored on a dedicated 220GB SSD-disk (tablespace "SSD"). I would like to run "VACUUM (FULL, FREEZE, ANALYZE)" on that table to reclaim some tablespace. But as you can see, such an operation fails, because rebuilding that table takes an additional 160GB of data - the SSD is just too small for that.

What I did in the past, was changing the tablespace of that huge table to my normal disk (4TB, tablespace "DEFAULT"), run VACUUM there, then changing back the tablespace to "SSD". This just takes a very long time, because all the heavy re-indexing now takes place on my very slow normal disk.

I was wondering if there is a way to directly "VACUUM + alter the table space" in one operation? So basically, the db would read the table on "SSD" and places the resulting vacuumed table on tablespace "DEFAULT".

Looking at the vacuum man-page this seems not possible - but perhaps I'm missing something or there exists some workarounds to achieve something similar (reclaiming space)?

kind regards
Michael

Browse pgsql-admin by date

  From Date Subject
Next Message Andreas 2013-06-14 18:55:00 Can't increase shared_buffers for PostgreSQL on openSUSE 12.3
Previous Message Scott Ribe 2013-06-14 12:48:17 Re: postgres user with automate rsync and private/public key pairs