PostgreSQL VACUUM FULLせずに不要領域を削除する

PostgreSQLではDELETEしたレコードは物理的には削除されずそのまま残り続けます。テーブル自体のサイズ（容量）を削減するにはDELETEした後にVACUUM FULLを行う必要があります。

このVACUUM FULLはテーブルへの書き込みロックがかかります。また数GクラスのテーブルへのVACUUM FULLは数時間かかることがあるので、常時書き込みがあるテーブルへは処理を行うタイミングが難しいです。

そこでVACUUM FULLを行わずに不要領域を削除（テーブルサイズを減らす）する方法を考えました。

方法は単純で「テーブルを新たに作って、そちらにデータを移行する」だけです。流れとしては以下のようになります。

1. 不要領域を削除するテーブル（移行元テーブル）と同じレイアウトのテーブルを作成する（移行先テーブル）
2. 移行先テーブルにレコードを移行する
3. 移行先テーブルにインデックスを設定する
4. 移行元テーブル名を変更する
5. 移行先テーブル名を移行元テーブル名に変更する

ここでは不要領域を削除するテーブル（移行元）をitems、移行先をitems_newとします。

1. items_newテーブルを作る

create table as文でitems_newテーブルを作成します。「limit 0」を指定することによりレコードを移行することなくitemsと同じレイアウトのテーブルを生成します。但しこの方法では制約等は受け継がれないので別途作成する必要があります。

create table items_new as select * from items limit 0;

2. レコードを移行する

insert into select文でitemsテーブルからitems_newテーブルにレコードを格納します。ポイントは全レコードを一度に移す必要が無く、select文のwhere句で条件を指定して序々に移行することができる点です。ログのような時系列で並ぶレコードでは一度登録されたレコードを変更されることが無いので、この方法で少しづつ移行することができます。

逆に各レコードがランダムに変更されるようなテーブルだとこのアプローチは使えません。

order by句を指定することにより任意の順序でレコードを格納することができます。

insert into items_new select * from items where created between '2007/1/1 00:00:00' and '2007/1/31 23:59:59' order by created;

3. items_newテーブルにインデックスを設定する

items_newテーブルにインデックスを設定します。インデックス設定はロックがかかるのでこの段階で行います。

create index items_new on ...;

● itemsテーブルへの書き込みを停止する

常時itemsテーブルへの書き込みがある場合は、このタイミングで書き込みを一旦停止します。停止の方法には、サービスを一時停止する、テンポラリテーブルに書き出す、ファイルに書き出す等が考えられます。

4. itemsテーブルのテーブル名を変更する

itemsテーブルのテーブル名を適当な名前に変更します。

alter table items rename to items_old;

5. itemsテーブルのテーブル名を変更する

items_newテーブルのテーブル名をitemsに変更します。

alter table items_new rename to items;

● itemsテーブルへの書き込みを再開する

itemsテーブルへの書き込みを再開して完了です。もし停止期間に書き込むデータがあればitemsに書き戻します。またこの方法では外部制約は設定していないので、もしitemsやそれに関連するテーブルに外部参照制約があるならそちらも設定します。（そもそも外部参照がからむようなテーブルにはこの方法自体が不向きですね。）

VACUUM FULLは意外とやっかい

テーブルサイズが巨大化するとVACUUM FULLは意外とヘビーです。通常はコンカレントVACUUMで用が済むかもしれませんが、テーブルサイズを小さくしたい場合などは、今回のようにちょっとした工夫が必要となります。

ちなみにこの方法を取ると当然ながらレコードのoidが変わります。oidに依存しているシステムでは適用できませんのでご注意を。