PostgreSQL
vacuumlo
ラージオブジェクト

削除忘れのOIDをさくっと削除したい

OID

postgresqlではラージオブジェクト(BLOB、CLOB)はOIDにより管理することができる。
このOIDはDB上でオブジェクトを一意に識別するためのIDで、postgresqlが自動で発行するもの。(ラージオブジェクトに関わらず全てオブジェクトに割り振られる)

ラージオブジェクトをOIDで管理するとはどういうことか?OIDで管理すると何が嬉しい?

ラージオブジェクトをそのままテーブルに格納することも可能だが、大きなラージオブジェクトを含むデータを大量に問い合わせた際にレスポンスが低下することがある。(ラージオブジェクトのサイズ分メモリに展開するため)

そこで、ラージオブジェクトの実態は別テーブルで管理し、問い合わせ先のテーブルではこのラージオブジェクトのOIDのみを管理することが可能になっている。

こうすることで、問い合わせの際には実際のラージオブジェクトではなくOIDへアクセスするため、応答が早くなる。

実際のラージオブジェクトにアクセスする場合はこのOIDを引数とするユーティリティを使用することで目的のラージオブジェクトのみを問い合わせることができる。

ただ、OIDによる管理手法はデータの登録/削除時に手間が多くかかる。

特に問題となるのはデータ削除時のラージオブジェクトの削除忘れ。

ラージオブジェクトのOIDのカラムを含むテーブル行をDELETE文でただ削除するだけでは、ラージオブジェクトを管理するテーブル(pg_largeobject)のラージオブジェクトは消えてくれない。

対象のデータ行のOIDをラージオブジェクト削除用のユーティリティに渡すことで初めてラージオブジェクトは削除される。

削除忘れをしたラージオブジェクトはどうなる?

もしラージオブジェクトを削除し忘れた場合は、管理テーブル(pg_largeobject)の実データと

OIDの紐づけを管理するテーブル(pg_largeobject_megadata)のOIDとOIDを含むテーブルの既存のOIDと対比し、

既存データとの差分から削除忘れのラージオブジェクトを判断する方法が考えられる。

この方法だと、OIDを含むカラムの数が多い場合、クエリが煩雑になるためメンテナンスが難しくなっていきそう。

そこで、posgresqlのユーティリティに削除忘れのOIDを削除するものがないか調べたところ、以下のユーティリティに辿り着いた。

 vacuumlo

このユーティリティ、postgresqlの拡張モジュールとのことで、デフォルトではインストールされていない、つまり明示的にインストールしなければ使えないとのこと。。。

postgresqlのソースディレクトリ配下にインストール用のツールは全て揃っているものらしいので、それを使ってインストールすることになる。

 CREATE EXTENSION vacuumlo;

※どのようなインストールをしたか不明だが、現場の実行環境のpostgresqlのソースディレクトリ配下に拡張モジュールが1つもなく、インストールを試すことはできなかった。。。

使ったことがないのでなんとも言えないが、このユーティリティをバッチで定期的に呼び出せば、削除忘れのラージオブジェクトが一掃されディスク容量の無駄を省くことができると思う。

ちなみに、アプリケーションとして削除忘れを容認するのはよろしくないので、バッチ作成はテスト環境に限るべきかと。