このTABLE定義であやういところはどこでしょう？　サービスにどんなインシデントが発生したか想像できるでしょうか？

CREATE TABLE reports
(
    id bigserial NOT NULL,
    user_id bigint NOT NULL,
    time_of_report timestamp without time zone NOT NULL,
    previous_report_id bigint REFERENCES reports (id),
    elapsed_time integer NOT NULL DEFAULT 0,
    CONSTRAINT reports_id_pkey PRIMARY KEY(id),
    CONSTRAINT reports_user_id_fkey FOREIGN KEY(user_id)
        REFERENCES users(id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE INDEX reports_user_id_time_of_report_idx ON reports USING btree(user_id, time_of_report);

前回の time_of_report の date_trunc()関数の話とは別件です。

前提知識

利用者の最大1日1回の操作で1行追加されます。
インシデント発生日には 500万行ほど蓄積していました。

参照制約下のDELETE

インシデント発生時の postgres log です。28秒の長期トランザクションになりました。
LOG: 期間: 28575.514 ミリ秒実行 <unnamed>: DELETE FROM reports WHERE user_id = $1

全体で500万行とはいえ、user_id １つにつき、高々数百行です。しかも user_id を第一キーにしたインデックスがついています。
これ以上シンプルにはできない SQL で、SQLプランナが何かを間違えようもないのに。

アンチパターンは、下記カラム定義でした。

previous_report_id bigint REFERENCES reports (id)

さらりと参照制約がついています。user_id の方は、最後にまとめて CONSTRAINT定義しているところに記述しているのに、不統一ですね。これで、SQL予約語を大文字にするというコーディング規約も破っていたら、見落とすかもしれません。このへんは余談で本論ではないですが。

記法を揃えると下記のようになります。これで、危うさが少し見えやすくなってきました。外部キー制約ですが、previous_report_idの場合は同じTABLEを参照しています。

CONSTRAINT reports_previous_report_id_fkey FOREIGN KEY (previous_report_id)
 REFERENCES reports(id)
 MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION

仕様としては省略できるところでも、プロダクト用の設定やDDLでは、あえて明示したほうがいいです。そのほうがレビューで問題を検出しやすい。書いてあるところの間違いを指摘するのは容易ですが、記述してないものを見通すのは技量がいります。「あるべき記述が無い」と指摘できる力や、「記述がないときのデフォールトを諳んじている」知識力というのは、そう簡単に手に入りません。

外部キー参照には参照先DELETE時の動作定義もついてきます。

NO ACTION
RESTRICT:　参照先のDELETEをさせない
CASCADE:　連鎖して参照元もDELETEする
SET NULL:　値としてNULLを入れる
SET DEFAULT:　カラム定義のDEAFULT値を入れる

インシデント発生時は、デフォールトのNO ACTION でした。
NO ACTION の字面は、とくにDBMS側でやることはないような気がしますよね？　実は、NO ACTION と RESTRICT はそれほど違いは無くて、参照先のDELETE時点でトランザクションの失敗が確定する（RESTRICT）か、トランザクション中で、参照元もDELETEしないとトランザクション失敗する（NO ACTION）か。NO ACTIONは判定を先送りするだけです。DBMSとしては参照整合性を守るのは大前提で、どうやって実現するかを指定しているので。

インシデント経緯

このTABLEへ500万ほどのINSERTの後、サービスインして相当期間が経ってから初めてのDELETE文で発生しました。

トランザクション終了直前には、DELETEされたid を、previous_report_id に持つ行が存在しないことを、確認する
previos_report_id のインデックスがないので 500万行の Seq. Scan が発生する
user_id １つにつき、数百回の DELETE があるので、数百回×500万行のSeq. Scan が発生する
28秒もかかる

運用のインデックスは怖くて消せない話を前記事でしましたが、こんな具合に普段まったく使ってないindexが必要とされるケースがあるのです。

対策

previous_report_id のインデックスを追加する

これが一般解です。

実際のサービスでは、DROP CONSTRAINT reports_previous_report _id_fkey でした。実は previous_report_id は定義されたものの、使ってなくて、500万行全てで NULL だったのです。
btree(user_id, time_of_report) があるんだから、 previous_report_id がなくても一つ前は簡単に取り出せます。

もちろん、DROP COLUMN で大正解ですが、それやるにはプログラム側を先に変更対応する必要があるので、取り急ぎの性能不具合回避に、DROP CONSTRAINT を先行させました。

まとめ

外部キー制約は参照先と同様に参照元にもインデックスがないと更新がつらい
外部キー制約の NO ACTION はなにもしないわけじゃない
SQL DDL にもコーディング規約は欲しい
プロダクト用の設定ファイルやDDLではデフォールトのところも明示しよう
不要カラム定義は、運用投入前に取り除いておきましょう

PostgreSQLアンチパターン：外部キー制約の更新コストを見くびる

参照制約下のDELETE

インシデント経緯

対策

まとめ