RDS(PostgreSQL)のストレージを拡張した話

こんばんは！

データベースは運用し始めてから気づくような問題も多いかと思いますが、今回はそんなトピックです。

今回問題になったこと

先日、AWSコンソールのRDS(PostgreSQL)をモニタリングしていたところ、20GiBのストレージ容量が残り4GiB前後まで減っていることに気づきました。容量の減少ペースを見るにあと数週間以内にはストレージが枯渇しそうな状況で、早めの対処が必要でした。

まずは状況確認した

とりあえずはストレージを逼迫させている要因を明確にしなければなりません。

お恥ずかしながら今の今まで、PostgreSQL自身のストレージ容量の内訳を確認したことが無かった為、確認のためのSQLを調べるところからスタート。

以下のSQLでデータベース別、テーブル別のストレージ使用量を出力しました。

-- データベース別のストレージ使用量
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database;

-- テーブル別のストレージ使用量
SELECT
  oid
  , table_schema
  , table_name
  , row_estimate
  , pg_size_pretty(total_bytes) AS total
  , pg_size_pretty(index_bytes) AS index
  , pg_size_pretty(toast_bytes) AS toast
  , pg_size_pretty(table_bytes) AS table
FROM (
  SELECT
    *
    , total_bytes-index_bytes-coalesce(toast_bytes,0) AS table_bytes
  FROM (
    SELECT
      c.oid
      , n.nspname AS table_schema
      , c.relname AS table_name
      , c.reltuples AS row_estimate
      , pg_total_relation_size(c.oid) AS total_bytes
      , pg_indexes_size(c.oid) AS index_bytes
      , pg_total_relation_size(c.reltoastrelid) AS toast_bytes
    FROM pg_class c
    LEFT JOIN pg_namespace n
      ON n.oid = c.relnamespace
    WHERE c.relkind = 'r'
  ) AS a
) AS a
ORDER BY a.total_bytes DESC;

出力結果は下記のようになります。 ※実際の出力値は業務に関わる重要なデータである為、ここでは掲載用に用意したフェイクのサンプルを載せています。

データベース別のストレージ使用量

datname	pg_size_pretty
postgres	13 GB
template1	7545 kB
template0	7545 kB

pg_size_pretty列の値が大きいデータベースが原因と見て、さらにテーブルを特定していきます。

テーブル別のストレージ使用量

| oid | table_schema | table_name | row_estimate | total | index | toast | table | |—––|––––––––––|———————––|–––––––|———––|———––|———––|———––|————|————|————|————| |16397 | public | user_logs | 2246314 | 9671 MB | 21 MB | 8889 MB | 762 MB| |2608 | public| companies| 7471 | 1168 kB | 688 kB | | 480 kB| |1255 | public| departments| 2961 | 1000 kB | 352 kB | 8192 bytes | 640 kB|

total列の容量が大きいテーブルを探します。このサンプルだとuser_logsテーブルが容量を食っているのが分かります。（あくまで上記の値はフェイクです）

不要な古いレコードはDELETE

さて、ストレージを逼迫させていたテーブルは特定できました。どうやらログ用に吐き出していたレコードが顧客増加に伴い、急激に増えていたようです。

あくまでも開発者用のログであった為、1年以上前のログはDELETEすることにしました。万が一ヒューマンエラーでやらかした時の為にしっかりとRDSのスナップショットを確保したら、DELETE文で物理削除を実行。これにて一件落着と思いきや…

DELETEしてもストレージ容量が減らない

レコードのDELETE後、AWSコンソールからRDSのモニタリングをずっと監視していても、一向にストレージ容量は増えませんでした。

PostgreSQLはVACCUMが実行されたタイミングで、DELETEしたレコードのストレージ領域が解放されるのでは…？とこの時点では思い込んでおり、AUTO VACCUMの実行履歴を下記SQLで確認しました。

-- AUTOVACCUMの実行履歴
SELECT
  schemaname,
  relname,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count,
  autoanalyze_count
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC;

確認した結果、last_autovacuumの日時はDELETE文実行後になっており、確かに削除後にVACCUMはされていました。ここで何か根本的な仕様を見逃している予感を感じ、再調査。

VACUUMではストレージ容量は解放されない

どうやらそもそもの仕様の理解が不足していたようです。

以下はVACCUMについてのPostgreSQL 公式ドキュメントより抜粋です。

23.1.2. ディスク容量の復旧

…

標準形式のVACUUMは、テーブルとインデックス内の不要な行を削除し、その領域を将来の再利用が可能であるものとして記録します。しかし、その領域をオペレーティングシステムに返却することはありません。例外として、テーブルの末尾に完全に空のページが存在し、かつそのテーブルの排他ロックが容易に獲得できるような特殊な場合には、その領域を返却します。対照的にVACUUM FULLは、不要な領域のない全く新しいバージョンのテーブルファイルを書き出すことで、積極的にテーブルを圧縮します。テーブルの容量を最小化しますが、長い時間がかかる可能性があります。また操作が終わるまで、テーブルの新しいコピー用に余計なディスク領域を必要とします。

https://www.postgresql.jp/document/9.2/html/routine-vacuuming.html

VACUUMはあくまでも次回に生成されるレコードの再利用領域として記録をするだけで、ストレージ容量を解放するものではないということでした。

今回のように容量を確保したい場合は、処理に時間がかかるVACUUM FULLを実行する必要があるとのこと。VACUUM FULL実行のためには一時的に対象テーブルと同じサイズの一時ディスク領域が必要になるので、あまりにストレージ容量が少なすぎると実行すらできない模様。