PostgreSQL の障害対応で使う構文一覧

障害対応（PostgreSQL） コースで覚える構文の一覧です。共通 SQL コースの構文（SELECT・JOIN・GROUP BY・ウィンドウ関数など）はすべて使えます。

lock_timeout と statement_timeout待ちすぎない・長すぎる SQL を止めるLv12 で覚える

SET lock_timeout = '3s';
SET statement_timeout = '30s';
ALTER ROLE ロール SET statement_timeout = '5min';
ALTER SYSTEM SET lock_timeout = '10s';（全体。ふつうはしない）

lock_timeout は、ロックを取れずに待つ時間の上限です。超えると canceling statement due to lock timeout で、その文だけを取り消します。ALTER TABLE などの DDL は強いロック（AccessExclusiveLock）を待つ間、後から来た読み書きもすべて後ろに並ばせるので、lock_timeout を短く決めてから流し、あきらめたら時間をおいて流し直します。statement_timeout は、1 つの文の時間の上限です（canceling statement due to statement timeout。上限は文を始めたときの設定で決まり、いま動いている文には、あとから決めた値は効きません）。ALTER ROLE … SET で決めると、そのロールの次の接続から効きます。

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN memo text;
ALTER ROLE report SET statement_timeout = '10min';

デッドロックと log_lock_waitsにらみ合いを見つける・待ちを記録するLv13 で覚える

SHOW deadlock_timeout;
ALTER SYSTEM SET log_lock_waits = on;
SELECT deadlocks FROM pg_stat_database WHERE datname = 'quest';
\! tail -n 12 log/postgresql.log

2 つのトランザクションが、相手のつかんだ行をお互いに待つと、どちらも進めません（デッドロック）。待ち始めて deadlock_timeout（既定 1 秒）たつと、サーバーが調べ、片方を deadlock detected で取り消します。ログの DETAIL に、だれが・どのトランザクションを・どの SQL で待っていたかが出ます。直し方は、行を書き換える順番を全部の処理でそろえることです。log_lock_waits = on にすると、deadlock_timeout より長く待った文が still waiting for … after … ms としてログに残ります。

ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();
\! grep 'still waiting' log/postgresql.log

SELECT … FOR UPDATE書き換える行を、先に決まった順でつかむLv14 で覚える

SELECT id FROM 表 WHERE id IN (…) ORDER BY id FOR UPDATE;
… FOR NO KEY UPDATE;（キーの列を変えない書き換え。外部キーの確かめとぶつからない）
… FOR UPDATE NOWAIT;（つかまれていたら待たずにエラー）
… FOR UPDATE SKIP LOCKED;（つかまれた行は飛ばす）

SELECT に FOR UPDATE を付けると、出てきた行をトランザクションの終わりまでつかみます（ほかの接続の UPDATE・DELETE・FOR UPDATE は待たされます）。ORDER BY を付けると、並べた順につかみます。2 つの処理が同じ行を逆の順番で書き換えると、お互いを待ってデッドロックになります。どの処理も、最初に「番号の小さい順に FOR UPDATE でつかむ」と決めておけば、後から来た方が先頭の行で待つだけで、輪になりません。表を LOCK TABLE で丸ごとつかんでも起きなくなりますが、関係のない行の処理まで一列に並ばせてしまいます。

BEGIN;
SELECT id FROM ledger WHERE id IN (2, 1) ORDER BY id FOR UPDATE;
UPDATE ledger SET balance = balance - 300 WHERE id = 2;
UPDATE ledger SET balance = balance + 300 WHERE id = 1;
COMMIT;

auto_explain遅かった SQL の実行計画をログに残すLv22 で覚える

LOAD 'auto_explain';（この接続だけ）
SET auto_explain.log_min_duration = '100ms';
SET auto_explain.log_analyze = on;
ALTER SYSTEM SET shared_preload_libraries = 'auto_explain';（すべての接続。再起動が要る）

log_min_duration より長くかかった SQL の実行計画を、サーバーのログに書きます（duration: … ms plan: の次の行から Query Text: と計画）。後から EXPLAIN すると速いのに、本番では遅かった、というときに、そのときの計画が分かります。LOAD はその接続だけで効くので、アプリの接続にも効かせるには shared_preload_libraries に入れて再起動し、auto_explain.log_min_duration を ALTER SYSTEM で決めて読み直します。log_analyze = on にすると actual の行数も出ますが、すべての文で時間を測るので重くなります。

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
\! grep -A 3 'plan:' log/postgresql.log

複合インデックスいくつかの列で 1 つのインデックスLv37 で覚える

CREATE INDEX 名前 ON 表 (列1, 列2);
CREATE INDEX CONCURRENTLY 名前 ON 表 (列1, 列2, 列3);

インデックスは列の順に並べた目次です。(a, b) は a の順に並び、a が同じ中で b の順です。だから a の条件が無いと使いにくく、a の範囲で探すと b の条件は飛ばし読みになります。等号（=）で探す列を先に、範囲（>=・BETWEEN）や ORDER BY の列を後に置くと、読む行が少なくなり、並べ替え（Sort）も要らなくなります。

CREATE INDEX visits_doctor_visited_idx ON visits (doctor_id, visited_at);

式インデックス関数や式の結果で引くLv39 で覚える

CREATE INDEX 名前 ON 表 ((式));
CREATE INDEX 名前 ON 表 (lower(列));

WHERE lower(email) = … のように列に関数をかけると、列のインデックスは使えません。式の結果を目次にした式インデックスなら使えます。問い合わせには、インデックスと同じ形の式を書きます（lower(trim(email)) と lower(email) は別の式）。関数でない式は ((列::date)) のように括弧を 2 重にします。使えるのは IMMUTABLE（同じ入力なら必ず同じ結果）の関数だけです。

CREATE INDEX patients_lower_email_idx ON patients (lower(email));

CREATE STATISTICS（拡張統計）関わり合う列の統計Lv41 で覚える

CREATE STATISTICS 名前 (dependencies) ON 列1, 列2 FROM 表;
CREATE STATISTICS 名前 (ndistinct, mcv) ON 列1, 列2 FROM 表;
ANALYZE 表;
SELECT statistics_name, attnames, dependencies FROM pg_stats_ext WHERE tablename = '表';

プランナーは、ふつう列どうしは関係が無いと考えて、条件の割合をかけ算します。郵便番号が決まれば市も決まるような列では、見積もり（rows）が小さくなりすぎ、Nested Loop を選んでしまいます。CREATE STATISTICS で列の組を教え、ANALYZE すると、関わり（dependencies）・組の種類の数（ndistinct）・よくある組（mcv）を集めて、見積もりが合います。作っただけでは空で、ANALYZE で中身が入ります。

CREATE STATISTICS patients_city_zip (dependencies) ON city, zip FROM patients;
ANALYZE patients;