PostgreSQLアンチパターン：巨大テーブルのCOUNT

COUNTアンチパターン

SELECT COUNT(*) INTO before_count FROM table1;
...
INSERT INTO table1 ...;
...
SELECT COUNT(*) - brefore_count INTO registered_count FROM table1;

なにかTABLEへの追加処理をしてますが、意図通りにできたかどうか、前後で COUNT(*)をとって、その差分を報告させようとしています。
複数のプロジェクトの複数のオーナーのコードで見かけたので、こんな初級アンチパターンも共有しときます。

なにがまずいの?

COUNTは、シーケンシャル・スキャンしないと出てこない値なのです。アンチパターンでは、テーブル全体が対象ですから、主キーインデクスを総なめです。小さいテーブルのうちはいいのですが、運用の1000万行を越えるような巨大テーブルだと、主キーインデックスだけでもGB単位になります。前回記事 で言及したトランザクション分離レベルの話がありますから、このトランザクションで勘定にいれるもの／いれないものの分別も必要です。それがbefore, after の2回も実行されているのです。

対策

INSERTするときにSELECT結果を使っているなら、全体COUNTの差分ではなく、そのSELECT文でINSERT前にCOUNTしてみましょうか。COUNTはシーケンシャル・スキャンなので処理時間は対象行数比例です。INSERT行数 << 全体行数 で、SELECT自体のコストも小さいなら、これで。

主キーがauto increment IDなら MAX(id) で代用できないか検討してください。COUNT(id)と違って、MAX(id)は主キー・インデクスの末端を見るだけです。シーケンシャル・スキャンだった処理がピンポイントになります。ただし、auto increment IDは、ロールバックしたトランザクションが消費したところは欠番になるので、COUNT_after-COUNT_before = MAX_after-MAX_before は成立しません。想定よりもMAX(id)が多ければよしとしていいなら、これで。

そもそも、なぜCOUNT結果を確認する必要があるのか、そこまでさかのぼった方がいいかもしれません。
処理が途中で失敗していたら、そのトランザクションはROLLBACKするしかなくて、2回目の SELECT COUNT(*)は実行できません。
本来一つのトランザクションにするべき処理を、複数トランザクションに分割しているとしたら、COUNT結果で意図通りに処理が完遂できていないことがわかったとしても、どうやってリカバリするのでしょう？もっと大きなアンチパターンにはまっているのでは？

なぜCOUNTアンチパターンが生まれる？

「事前のCOUNTと事後のCOUNTで成功判定する」のは自動IntegrationTestではよくやります。POST のAPI叩いて成功後に、DBのTableにもレコードが増えたかどうか確認するようなTestです。そこではDB初期化から始まるので、大きなtableはまず出てきません。
このパターンに慣れて、O(n)のデータ量比例になることを見過ごして、運用DBにもやってしまうんでしょうか？

SQLは「単純な字面のやつほど怖い」というのを精神注入棒片手に刷り込みに回った方がいいのかな？

SELECT COUNT(*) FROM table1; -- 全件COUNT
SELECT * FROM table1, table2; -- 全件x全件の直積JOIN

「RDBMSっていろいろやってくれるてるから、COUNT()くらいスパッと返せるようにどこかで管理してるんでしょ」という思い違いがあるのかな？　だとしたら、もう少し書いておこう。

COUNT高速化のアンチパターン

RDBMS の COUNT() が遅いので、「INSERT/DELETE に Trigger仕込んで高速COUNTを実装」する。
専用TABLE作って、INSERTで++、DELETEで--して、COUNT()のかわりに、SELECT rows FROM counters WHERE name='table1';

これ、実装に難しいところはないし、小規模テストでは完璧に動くんですが、運用にいれたら死ぬアンチパターンです。
前回記事 のトランザクション分離レベルの話は counters にも適用されます。
運用DBで多人数・同時多発INSERTがくると、counters TABLE の1行目が全体のボトルネックになって、書込ロック競合が頻発してスループットががったり落ちるか、直列等価を保証できなくなってロールバックに陥るトランザクションが多発して、ユーザ体験を悪化させます。

まとめ

RDBMS にとって生きているTABLEの COUNT() は本質的に重いのです。巨大テーブルでは極力回避してください。
使うときは結果/中間結果を小さくしてから適用です。
大きな死んだTABLEと小さな生きたTABLEに分割して、大きなTABLEにマテビューをつけて、集計結果を実体化するのも手です。前日や前週までの記録の入った確定値テーブルと、今日・今週分の速報値テーブルとか。確定値テーブルのCOUNTは1回実行した結果を使いまわします。