PostgreSQLのデータを削減できた話

2022年7月7日

URLをコピーしました！

はじめまして、PR TIMESの開発本部でインターンをさせて頂いている永井と申します。
現在はパフォーマンス改善のタスクをしています。

はじめに

自分は今回のタスクをやるまでSQLをあまり書いたことがありませんでした（ORマッパーしか使っていませんでした）。しかし、今回のタスクをやることで直接SQLを書くことが多くなりSQLはもちろんPostgreSQLの構造はどうなっていて、何がメモリを消費しているかなどについての理解も深まりました。本当に良い経験をさせて頂きました。

なぜ削減することになったのか

PostgreSQLのストレージがとても逼迫していました。さらにPostgreSQLはオンプレの物理サーバーで動いていてストレージの増設も難しいため、AWSに移行するまでの延命措置としてデータ量を削減することになりました。

まずは状況確認

一番容量を使っているテーブルはどれか

以下のクエリで、PR TIMESにおいて主に使われているDBの中で容量をたくさん使っている上位5つのテーブルを見ていきます。

select relname, pg_relation_size(relid) as capacity from pg_statio_all_tables order by capacity desc limit 5;

1番容量を使っているのはプレスリリースのアクセスログを保存するテーブル（以後access_logテーブルとします）でした。今回データ削減をしていく対象となります。2番目でも約49GBだったのですが、access_logは圧倒的で約72GBも使っています。これは主に使われているDBの中でも相当占めています。

select pg_size_pretty(pg_database_size('dbname')) as capacity;

528GBあったので、約14%を占めていました。
※pg_size_prettyはByte単位の結果を適切な単位に変換してくれます！（容量で並び替える際は単位が異なり、うまくいかないこともあるので注意。）

access_logテーブルのindex確認

select pg_size_pretty(pg_relation_size(indexname::regclass)), indexname from pg_indexes where tablename = 'access_log';

インデックスは7つあり、1つあたり大体20GB~60GBでした（インデックスって結構消費しますね）。
※上記クエリのindexname::regclassについて。indexnameは参照先がpg_class.relnameになっている。relnameの型はname型となってしまう。よってindexnameをregclassにキャストしてインデックスのoidを取得している。

インデックスが実際に使われているかの確認

access_logテーブルには7つのインデックスがあると分かりましたが、そのうちのどれくらい使われているのか確認します。

select * from pg_stat_all_indexes where relname = 'access_log';

上記クエリの結果の見るところはidx_scan。この項目でインデックススキャンされた回数が分かります。ここで、主キーをはじめ3つほどidx_scanが0で使われていないインデックスがあることが分かりました。

実際に行ったこと

このことからaccess_logテーブルについて以下の3つの観点からデータ削減をしました。

不要なカラムの削除
不要なレコードの削除
不要なインデックスの削除

注意すべきところ

インデックスの削除時のロックについて

indexの削除は本番環境では注意が必要でした。もし実行時間が長いと、テーブルにロックがかかった状態で他のトランザクションの更新などができなくなりロック待ちのプロセスが大量に発生します。それらがタイムアウトすることでエラーやDBのダウンなどの原因になり得ます。よって、まずは本番環境と類似した環境でクエリの実行時間を調べます。その上で、テーブルへの書き込みロックをかけたくない場合はconcurrentlyオプションを追加します。するとロック待ちは起きなくなります。

drop index concurrently name;

ただしconcurrentlyオプションをつけると、ロックを獲得せずに競合するトランザクション処理を待ってから実行するようになるのでクエリの実行時間は長くなる可能性があります。

使われていない主キーについて

今回のデータ削減の対象となっているテーブルの主キーはどこからも使われていませんでした。なので削除しようと思ったのですが、主キーを削除するのはあまり良くない（不便）とアドバイスをもらいました。なぜかというと、一意に識別するカラムがなくなるからです。そうすると他に一意に識別できるカラムがあれば良いかもしれませんが、ないと重複しているレコードを更新するのは大変です。とりあえず主キーは削除しない方が良さそうです。（触らぬ神に祟りなし。。。）
PostgreSQLの公式ドキュメントでも以下のように書かれています。

リレーショナルデータベース理論では、全てのテーブルにプライマリキーが1つ必要とされています。この規則はPostgreSQLでは強制されませんが、たいていの場合はこれに従うことが推奨されます。
https://www.postgresql.jp/docs/9.2/ddl-constraints.html

結果

以下の画像でオレンジの線が容量の推移です。約90GB減らすことができました！

最後に

今回初めてやることが多かったので分からないことばかりでしたが、その分身についたこともとても多かったです。特に、PostgreSQLについてはもちろん一部分ではありますが一般的なDBの考え方が身に付きました。
また、PR TIMESの皆さんのサポートが手厚かったので安心して新しいことに挑戦していくことができました。ありがとうございました。