こんばんは！

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

今回問題になったこと

先日、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実行のためには一時的に対象テーブルと同じサイズの一時ディスク領域が必要になるので、あまりにストレージ容量が少なすぎると実行すらできない模様。

最終的にどうしたか

今後もログに必要なストレージ容量は増えていくことは分かっていたので、素直にRDSのストレージを20GiBから100GiBへ拡張することで対応しました。ストレージの拡張についてはダウンタイムは伴いませんので特に大きな問題もありませんでした。

ついでに気づいたこと

テストで開発環境のRDSのストレージを弄ったりしていて気付いたのですが、ストレージを減らすことは仕様上できないようです。

減らしたい場合の対応策としてAWSが提案しているのは

新たなRDSインスタンスを作成し、旧RDSインスタンスからダンプしたファイルをリストアする
AWS Database Migration Serviceを利用する

の２パターンとのこと。ダウンタイムが許容できないシステムだと後戻りできなくなります。

aws.amazon.com

うっかりストレージを増やし過ぎないように気を付けないといけませんね…。

参考

www.postgresql.jp

stackoverflow.com

chulip.org

エンジニアのはしがき

プログラミングの日々の知見を書き連ねているブログです

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