RDBMS といっても、SQLiteをシングルユーザで使ってたり、ORMやRESTful APIでWrapされた状態で使っていると、SQL問い合わせのできる「ファイル」という感覚になるかもしれません。
その感覚で PostgreSQL を使うとしばしば落とし穴にはまります。
DBMSは大変なのだ
DBMSは昔から同時多発の更新要求を矛盾無くさばくトランザクション処理が要件でした。
この要求の苛烈さ・複雑さに思いを馳せるには、"Transaction Isolation Level", "トランザクション隔離性水準", "トランザクション分離レベル" あたりをキーワードにググってみるのがいいでしょう。
たとえばこんなページ
http://language-and-engineering.hatenablog.jp/entry/20110104/p1
DBの「トランザクション分離レベル」が必要な理由 （PostgreSQLで，ファントム・リードを防止すべきサンプル事例）
http://gyouza-daisuki.hatenablog.com/entry/2013/11/19/150838
トランザクション分離レベルについて極力分かりやすく解説してみた[SQL]
PostgreSQLの追記型アーキテクチャ
要件が複雑なので、RDBMSによって実装方針はいろいろですが、かなり複雑な実装になります。
PostgreSQLが採用した実装は、「追記型アーキテクチャ」です。
- UPDATE でも上書きしない。前の値のレコードがそのまま残っています。
- はい、ご明察。DELETE しても、前の値のレコードがそのまま残っています。
- ようするに古いトランザクションが生き残っていて、古い値を期待している可能性があるかぎり、そのままとっておくのです。
- トランザクションが重なると、前の値だけでなく、前の前の値も、前の前の前の値も、ずっと前まで生き残り続けることがあります。
- 前の値のレコードを参照にくる可能性のあるトランザクションが全部終わったら、ようやく古いレコードはガーベジとなりさがり、vacuum 処理すると始末される。
このアーキテクチャでよくはまるパターン
- Diskの空きが少なくなってきたから古いレコードを大量にDELETEしたら、DiskFullになってしまった。
- smallint のカラムを smallint の値で UPDATE するだけだから、Disk使用量には影響ないだろうと思って大量に繰り返し処理したら、ブクブクDBが膨張してパンクした。
- 悪例：すでにほとんどの対象が status=1 になっているのに、緩い WHERE 条件で
UPDATE .. SET status=1..同じ値を上書きしても結果は同じでしょ？ ってRDBMSにはご無体な話です。トランザクション中で更新対象になったというだけで、やらなきゃいけないことが山ほど出てくるのです。 - パンクしないまでも、ガーベジだらけになって、正味データ量に変化はないのに、だんだん処理効率低下・応答速度劣化に見舞われる
対策
スキーマ設計段階からです。RDBの設計の基本=TABLEの正規化は前提として、
- カラム単位で情報の寿命／更新頻度を検討する
- 利用者の入会から退会まで変わらない情報や年1回くらいで更新される情報？
- 毎月更新される情報？
- バッチ処理などで一時的に必要な情報？
- TABLE設計では、情報寿命の粒を揃えることを考慮する
- どこか1カラムでも短寿命のが混じると、TABLE全体のガーベジ発生率があがります
- 1-2ヶ月で不要になる情報は、時系列パーティションテーブルに入れて、不要になったものを DROP
- TABLE まるごと DROP なら、vacuum を待つこと無く回収されていきます
- バッチ処理などで一時的に必要な情報などは、TEMPORARY TABLE で使い捨てにする
「INSERT したら、SELECT しまくって、UPDATE することなく DROP する」パターンを最大化します。