metaps Advent Calendar 2025

【Aurora PostgreSQL/失敗談】何も考えずにSELECTをリーダー経由にした結果、トラブったお話😢

Last updated at 2025-12-20Posted at 2025-12-19

metaps Advent Calendar 2025 20日目の記事です🏃‍♂️

はじめに

今回は、AWSのAurora PostgreSQLにて、SELECT文をライター経由からリーダー経由に変更したことで、思わぬトラブルに遭遇した話を共有します。
人によっては慣れない用語が出てくると思いますが、簡単な説明を入れつつ進めていきますので、用語に慣れていない方も安心して読み進めてください。
また、誤解がないようにお伝えしますが、Aurora PostgreSQLは可用性・安全性に優れたとても良いサービスです。「便利だけど注意するポイントもあった」というお話になります。

もし、Aurora PostgreSQLを使っていて、「SELECTだけ実行するならリーダーインスタンス経由の方が、ライターへの負荷も下がるし良いことしかないやん！デメリットとか注意点とか特にないっしょ！」と思っている方がいれば、是非読んで欲しい内容となっています。

何をしたのか

トラブルが発生したプロダクトでは、Aurora PostgreSQL（バージョンは14系）のCPU負荷が高くなるような、SELECTの実行が確認されていました。極端な負荷が万が一発生した場合、フェールオーバーするリスクがあったため、当該SELECT処理を、一旦リーダー経由にする改修を行いました。

発生した事象

上述の改修資産をリリースしたところ、以下のようなPHPのエラーが頻発しました。

Query error: ERROR:  canceling statement due to conflict with recovery
DETAIL:  User was holding a relation lock for too long.

Invalid query: SELECT * FROM "HOGEテーブル" WHERE "process_key" = 'xxx' ORDER BY "id" ASC  LIMIT 1000

エラーの内容は、HOGEテーブルへのSELECTが中断されたことを示すものということは分かりましたが、何がSELECTを中断させているのか正確な原因を特定するに至りませんでした。取り急ぎ切り戻しを行い、エラーの再発はなくなったため、原因調査を後日行うことにしました。

原因調査

1. トラブルが発生したテーブルに対する操作について

まず、エラーを検知した時間に、SELECTの中断が発生するようなHOGEテーブルに対する処理があるのか、確認を行いました。すると、あるバッチがライター経由でHOGEテーブルに、毎分以下のようなクエリを実行していることがわかりました。

BEGIN;
SET lock_timeout TO 35000;
LOCK TABLE "HOGEテーブル" IN ACCESS EXCLUSIVE MODE;
RESET lock_timeout;
...
COMMIT;

しかし、ライター経由でLOCK TABLEを行なったとしても、エラーを検知した時間において処理対象となるデータはなかった状況でした。対象がなければすぐにロックを解放するはずのため、リーダー経由のSELECTが中断されることはないだろうと、この時点では予想していました（この予想は外れます）。

2. PostgreSQLの「ACCESS EXCLUSIVE」ロックについて

ライター経由で毎分実行している以下について、詳しく確認をしてみました。

LOCK TABLE "HOGEテーブル" IN ACCESS EXCLUSIVE MODE;

このLOCK TABLE ... IN ACCESS EXCLUSIVE MODE;はLOCK TABLE ...と同等であり、最も強いロックをかける命令であることがこちらにて確認が取れました。
そして、この最も強いロック（AccessExclusiveLock）は、SELECTによるACCESS SHARE（AccessShareLock）と競合するとPostgreSQLの13.3 明示的ロックに記載がありました。以下に抜粋します。

ACCESS SHARE (AccessShareLock)

ACCESS EXCLUSIVEロックモードとのみ競合します。
SELECTコマンドにより、参照されるテーブルに対してこのモードのロックが獲得されます。通常、テーブルの読み取りのみで変更を行わない問い合わせであれば全て、このロックモードを獲得します。

ここまでで、SELECTによるAccessShareLockと、LOCK TABLEによるAccessExclusiveLockは競合することが確認できました。初歩的と思われるかもしれませんが、大事な確認ポイントです。しかし、まだリーダー経由のSELECTが中断される原因の核心には迫れていません。

3. Aurora PostgreSQL のレプリケーションの特徴

次に、Aurora PostgreSQLのレプリケーションについて確認を取りました。

そもそもレプリケーションとは、複製を作成することを意味します。ライター側の変更内容を適宜リーダーにも適用させて、ライターとリーダーの中身を同期する仕組みがAurora PostgreSQLにも存在します。こちらに記載がある通り、WAL（ログ先行書き込み）を利用して、ライターの変更内容をリーダーに反映していることがわかりました。

そして、今回の原因の核心に迫るものが以下Aurora ユーザーガイドのLock:Relationの記述の中で、見つかりました。
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/apg-waits.lockrelation.html

現在は、ACCESS EXCLUSIVE リレーションロックのみが、リーダーインスタンスにレプリケートされます。ただし、ACCESS EXCLUSIVE リレーションロックは、リーダーが保持する ACCESS SHARE リレーションロックと競合します。

この「ACCESS EXCLUSIVE リレーションロックのみが、リーダーインスタンスにレプリケートされます。」とは、AccessExclusiveLockをライター側で獲得した場合、リーダー側でもAccessExclusiveLockが再現されると読み取れます。つまり、ライター側でAccessExclusiveLockを獲得した場合、リーダー側もAccessExclusiveLockを再現して獲得するのではないかと1つの仮説を立てることができました。（後記の再現確認の章で確認していきます）

4. WALをリーダーに反映させる際の待機時間について

もう一つ大事な確認ポイントがありました。

ライター側でAccessExclusiveLockを獲得した場合、リーダー側もAccessExclusiveLockを再現して獲得すると解釈しました。

と、先ほど仮説を立てましたが、以下の流れを考えてみました。

リーダー側で、30秒くらいかかる重いSELECTを実行
ライター側で、AccessExclusiveLockを獲得（LOCK TABLE ... 等）
リーダー側で、WALの内容を反映しようとする（つまりAccessExclusiveLockをライター同様獲得しようとする）
リーダー側では、AccessExclusiveLockと競合するSELECT(AccessShareLock)がロックを獲得しており、WALの内容を反映できない

WALの反映がかなり待たされるのではと思いましたが、Aurora PostgreSQLでは「4」の状態でどのくらい待機するか max_standby_streaming_delay という設定を持っています。 max_standby_streaming_delay ミリ秒待機後に、WALの適用を阻害しているクエリを強制的に中断させます。

max_standby_streaming_delayについては、こちらに記載がある通り、AWSのマネコン（管理画面）から、確認が可能です。デフォルトは14000（ミリ秒）です。つまり、リーダーはWAL適用しようとしてできなくても、即時邪魔なクエリを中断することはなく14秒待機するということになります（これは待機セッションが肥大化しすぎることを防ぐ安全装置のようなものです）。

そのため、以下のような動きになるのではないかと予想を立てることができました。（この予想はあってました）

リーダー側で、30秒くらいかかる重いSELECTを実行
ライター側で、AccessExclusiveLockを獲得（LOCK TABLE ... 等）
リーダー側で、WALの内容を反映しようとする（つまりAccessExclusiveLockをライター同様獲得しようとする）
リーダー側では、AccessExclusiveLockと競合するSELECT(AccessShareLock)がロックを獲得しており、WALの内容を反映できないが、max_standby_streaming_delayミリ秒待機する
リーダー側は、max_standby_streaming_delayミリ秒待機したが、まだSELECTが実行中だったため、SELECTを中断して、WALを適用させる

再現確認

現状、「3. Aurora PostgreSQL のレプリケーションの特徴」にて立てた「ライター側でAccessExclusiveLockを獲得した場合、リーダー側もAccessExclusiveLockを再現して獲得するのではないか」という仮説と、
「4. WALをリーダーに反映させる際の待機時間について」にて、確認をとった「WAL適用を阻害するSELECTがあった場合、待機後に中断される」ということを、検証環境のAurora PostgreSQL（バージョンは15系）を利用して実機で再現してみることにしました。

再現確認1: ライター側でAccessExclusiveLockを獲得した場合、リーダー側でもAccessExclusiveLockが獲得されるかどうか

リーダー側で、AccessExclusiveLockを保持しているかどうかを確認するには、Auroraのユーザーガイド「リーダーロックをチェックする」に記載がある通り、aurora wal replayプロセスがAccessExclusiveLockを獲得しているかを見ることで確認ができます。

(1) ライター側でHOGEテーブルに対してAccessExclusiveLockを獲得

BEGIN;
LOCK TABLE "HOGEテーブル" IN ACCESS EXCLUSIVE MODE;

(2) リーダー側で、AccessExclusiveLockが行われることを確認

SELECT
    l.locktype,
    l.relation::regclass AS relation,
    l.mode,
    l.granted,
    a.backend_type,
    a.pid
FROM pg_locks l
JOIN pg_stat_activity a
  ON l.pid = a.pid
WHERE l.relation = 'HOGEテーブル'::regclass::oid
ORDER BY a.backend_type, l.mode
;

実行結果は以下の通りです。

locktype	relation	mode	granted	backend_type	pid
relation	HOGEテーブル	AccessExclusiveLock	true	aurora wal replay process	661

以上により、確かにライター側でAccessExclusiveLockを獲得すると、リーダー側でもWALを適用するために、aurora wal replay processがAccessExclusiveLockを獲得する確認が取れました。
つまり、ライター側でAccessExclusiveLockをかけると、リーダー側でもAccessExclusiveLockが再現されるということが明らかになりました。

再現確認2: WALの適用により、リーダー側でAccessExclusiveLockが獲得される場合、リーダー側で実行中のSELECTは強制中断されるかどうか

次に、WALの適用により、リーダー側でAccessExclusiveLockを獲得された際に、リーダー側で実行中のSELECTが待機後に中断されることを確認していきます。

(1) リーダー側で、約60秒実行するSELECTを実行

hoge.fuga = 'X'で5レコード見つかるような状況であったため、5行それぞれに対してlateralで評価を行い5x12s待機するSELECTを実行してみます。

-- ============================================
-- "HOGEテーブル"に対して、60秒間AccessShareLockを獲得するSQL
-- ============================================
select hoge.*
from "HOGEテーブル" hoge
cross join lateral (select pg_sleep(12), hoge.id) as "dummy"
where hoge.fuga = 'X'
order by hoge.id
;

(2) ライター側で、AccessExclusiveLockを取得

BEGIN;
LOCK TABLE "HOGEテーブル" IN ACCESS EXCLUSIVE MODE;

(3) DataGripから実行していたリーダー側のSELECTが中断された

[2025-11-14 16:42:22] [40001] ERROR: canceling statement due to conflict with recovery
[2025-11-14 16:42:22] 詳細: User was holding a relation lock for too long.

このエラー見覚えがありますよね... そうです、「発生した事象」にて出ていたエラーと一致します。つまり、”リーダー側で実行中のSELECTがWAL適用により待機後中断されることがある” が明らかになりました。

まとめ

今回起きていたこと

Aurora PostgreSQLではライター側でAccessExclusiveLockを獲得すると、WALを適用するためにリーダー側でもAccessExclusiveLockが再現される
リーダー側のWAL適用は、競合するAccessShareLock(SELECT)がある場合、max_standby_streaming_delayミリ秒待機する
待機後、AccessShareLock(SELECT)が解放されていない場合、強制的にAccessShareLockを獲得しているSELECTを強制的に中断させる。その際に、canceling statement due to conflict with recoveryというエラーが発生する

この一連の流れが今回起きていたことでした。

原因がはっきりと分かったから思えることですが、そもそものSELECTの実行時間が短ければ発生しないですし、毎分AccessExclusiveLockをかけないような仕組みにしていれば、このような事象は発生しません。今回は、この2つの要因が重なった結果、リーダー経由のSELECT中断が頻発する事態を引き起こしてしまいました。

得られた教訓

時間のかかるSELECTをリーダー経由にする上で、ライター側でAccessExclusiveLockを獲得するような処理がないかを確認すべしということです。
もし、「ライター側でAccessExclusiveLockを獲得するような処理」があった場合、リーダー側の実行時間が長いSELECTは中断されるリスクがある点を押さえておくと良いでしょう。

最後に

こちらの対応策として、当方では時間のかかるSELECTを早くさせる対応を行う予定です。うまく解消できたら別記事にて紹介させていただきます。
また、もし近くでリーダー経由に切り替える予定のある方がいれば、この記事を紹介していただけると嬉しいです。最後まで読んでいただきありがとうございます。🍀

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up