Postgresql

前提 個人開発のEDINET分析ツール（財務レポートを解析してDiscordに投げる趣味プロジェクト）で、フロントエンドのコードをAIにほぼ丸投げして書かせた部分があった。 後から見直していたら、明らかに厳しい実装が出てきたので調査した記録。今回は原因の特定と応急処置（SELECT修正）までで、DBスキーマ自体の見直しは今後の課題として持ち越す。 出てきたコード API利用状況を表示するダッシュボードコンポーネントで、以下のように4本のAPIをPromise.allで並列取得していた。 const [statsRes, logsRes, tsRes, statusRes] = await Promise.all([ apiClient.get(`/api-logs/stats?days=${days}`), apiClient.get('/api-logs/logs?limit=50'), apiClient.get(`/api-logs/timeseries?days=${days}&interval=${interval}`), apiClient.get(`/api-logs/status?days=${days}`), ]) 取得後、4つのstateをfinallyブロックで一括更新し、loadingも1つのフラグで管理していた。 最初の疑問：Promise.allの並列度は妥当か Promise.all自体は4本のリクエストを同時発火するので、直列でawaitを重ねる実装(MDN: Promise.all)と比べれば理にかなっている。 ただし設計上の課題は残る。 loadingが1つのフラグなので、一番遅いレスポンスに画面全体の表示タイミングが引っ張られる useEffectの依存配列が[days, interval]のみで、intervalしか変えていない操作でも無関係なlogsまで再取得される 対策案としては、各エンドポイントを個別のuseEffect＋個別stateに分割し、届いた順にレンダリングする方式が考えられる。この場合、依存配列も自然に分離でき、interval変更時に不要な再取得も防げる。ただしこれは「体感速度」の改善であり、根本的なネットワーク・サーバー負荷の解消にはならない。 実測結果 Chrome DevToolsのNetworkタブで実測したところ、以下の結果になった。 エンドポイント サイズ レスポンスタイム(1回目) レスポンスタイム(2回目) stats?days=7 0.3kB 5.69秒 6.25秒 logs?limit=50 7.7kB 200ms 351ms timeseries?days=7&interval=hour 14.5kB 11.52秒 6.01秒 status?days=7 0.5kB 7.92秒 11.58秒 同一アプリの別画面（Stats.tsx）にあるsummary・alerts・ranking系のエンドポイントは、同程度の小さいペイロードで30〜111msと桁違いに速い。 結論：問題はフロントの並列化ではなくバックエンド側 数百バイト〜十数キロバイトのレスポンスに対して秒単位〜十数秒のレイテンシは、ネットワークやReact側の描画方式では説明がつかない差だった。並列fetch自体は問題ではなく、疑うべきは以下。 集計クエリのN+1（プロバイダーごとにループでクエリを投げている可能性） 日時カラムへのインデックス未設定（daysによるWHERE句がフルスキャンになっている可能性） interval=hourの集計処理（DB側の集計かアプリ側ループかで原因が分岐） 2回目の計測でむしろ悪化（stats5.69→6.25秒、status7.92→11.58秒）していることから、キャッシュが効いていない、あるいはテーブルの成長に伴って劣化する設計になっている可能性 N+1問題自体についてはN+1 query problem (MDN Glossary)を参照。 今回の対応範囲 今回は該当エンドポイントのSQLクエリを見直し、集計方法をSELECT文レベルで修正するところまでを実施した。 DBスキーマ自体（インデックス追加や正規化の見直しなど）には手を付けていない。テーブルが育つほど劣化する可能性が実測から示唆されているため、これは今後の課題として残す。 教訓 AIに丸投げしたコードは、動いているように見えても「並列化されているか」程度の表面的なチェックだけでは不十分 フロントエンドの実装が妥当でも、DevToolsのNetworkタブで実測しない限りボトルネックの所在は特定できない 「並列処理にしたから速いはず」という思い込みは、実測前の仮説に過ぎない

担当 タイトル案、本文全般：私 環境・サンプルコード系全般：Claude SQL検証解説：Claude ヘッダ生成 TL;DR 暇つぶしに書いたタロットカードアプリで踏んだバグの話。 PostgreSQLの仕様の読み間違えによるバグが思ったより分かりづらかったのでその流れと判明した具体的な原因、そしてそれらにまつわる検証内容を記事にする。 アプリはdocker compose環境下で動くようになっている。 時系列 タロットカードの結果が4回連続逆位置・・・気の所為か タロットカードアプリを書いた。コード自体はほぼCodexで生成した。細部の実装までは見てない。 実際に使ってみて4日、逆方向が妙に続いている。 確率としては 1/2^4 = 1/16 = 0.0625（6.25%） 約6%程度の可能性があるので、まあ・・・気の所為レベルではある。 とはいえ、少し気になったのでCodexに「逆位置が妙に続いているので実装に問題ないか調べてほしい」と指示→結論だけいえば「問題ありません」というニュアンスの解答が戻ってきたので安心（？）して閉じた。 5回目も逆方向 でも実装に問題ないと言われてるし、偶然か 6回目も逆方向は流石におかしい というわけで再度Codexに調査を依頼。 とはいえ、抽象的な指示だと同じような結果になることはわかったので 実際にバックアップを取得した上で、実際に無限にタロットを引けるようにして検証コード作ってもらって検証して調査するように指示した。 結果、以下のSQLに原因があった。 SELECT id, number, name, upright_meaning, reversed_meaning, random() < 0.5 AS is_reversed FROM tarot_cards ORDER BY random() LIMIT 1; このSQLには二箇所random()が存在するが、 PostgreSQLで実行するとなんとrandom()の結果が使い回されてしまうようだ。 今回の実装では大アルカナのみなのでカードは22枚用意した。 ORDER BY random() LIMIT 1は、乱数が最も小さいカードを選ぶため22個の乱数から最小値を選んだ場合、その値が0.5以上になるのは"22個すべてが0.5以上"の場合だけであるため 確率は (1/2)^22 = 1 / 4,194,304 となる。 つまり元の実装では 逆位置：約99.999976% 正位置：約0.000024% となる。 そりゃ全部逆になるわけだ。 修正SQL WITH picked_card AS MATERIALIZED ( SELECT id, number, name, upright_meaning, reversed_meaning FROM tarot_cards ORDER BY random() LIMIT 1 ), picked AS MATERIALIZED ( SELECT picked_card.*, random() < 0.5 AS is_reversed FROM picked_card ) SELECT id, number, name, CASE WHEN is_reversed THEN 'reversed' ELSE 'upright' END, CASE WHEN is_reversed THEN reversed_meaning ELSE upright_meaning END FROM picked; 素直にWITHで区切ったところ、期待通りにタロットカードの位置がバラついた。 ...

記事中で出てくる計測等は以下のリポジトリで実施した。 GitHub - wasuken/pg-offset-bench · GitHub はじめに lobste.rsで “All you need is PostgreSQL” という記事を読んだ。 「Redisやイベントストア、マイクロサービスに安直に逃げる前に、PostgreSQL単体で解決できないか考えろ」という主張を、金融システムをサンプルに証明する内容だった。 詳細については元記事を読んでくれ。 その中でキーセットページネーションに触れていて、私もOFFSETを使っていたので本当にまずいのか気になり、実際に環境を作って計測してみた。 ただし先に結論を言っておく。OFFSETが遅いのは事実だが、特定のページ番号に直接ジャンプする処理が必須な場合はOFFSETを使うしかない。キーセットは「前のページの続き」しか取れないため、任意のページへのランダムアクセスには対応できない。 逆に言えば、次へ,前へや無限スクロールで十分なケースであれば、間違いなくキーセットページネーションを使うべきだ。本記事はその判断材料として読んでほしい。 環境 PostgreSQL 17（Docker） WSL2 / ArchLinux # compose.yml services: postgres: image: postgres:17 container_name: pg-offset-bench environment: POSTGRES_USER: bench POSTGRES_PASSWORD: bench POSTGRES_DB: bench ports: - "5432:5432" volumes: - pgdata:/var/lib/postgresql/data - ./init:/docker-entrypoint-initdb.d command: > postgres -c shared_buffers=256MB -c work_mem=16MB volumes: pgdata: テーブルは100万件のarticlesテーブルを用意した。中身はどうでもいいので適当。 CREATE TABLE articles ( id BIGSERIAL PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); INSERT INTO articles (title, body, created_at) SELECT 'Article ' || i, repeat('body text ', 10), now() - (random() * interval '365 days') FROM generate_series(1, 1000000) AS i; CREATE INDEX idx_articles_created_at ON articles (created_at DESC, id DESC); ANALYZE articles; OFFSETページネーションとは もっとも一般的なページネーション実装。 ...

BullMQ や外部キューサービスを使わずに、PostgreSQL + while(true) ループでバックグラウンドジョブを管理する仕組みを作った。 「外部依存を増やしたくない」「DB を見るだけでジョブの状態がわかるようにしたい」という理由から。 ⚠️ この実装の前提条件（割り切りポイント） この仕組みは 「予算を抑えたい個人開発」かつ「アプリ単一インスタンス（1プロセス）」 での運用を前提に、あえてシンプルに作っています。 以下のトレードオフを理解した上で使ってください。 1. 厳密な重複排除はしていない（レースコンディション） アプリ側で isAnyRunning をチェック → ジョブ作成という 2 ステップになっているため、ミリ秒単位で同時リクエストが来た場合はすり抜ける可能性があります。 厳密に防ぐなら、後述する DB 側の Partial Unique Index が必要です。 2. マルチインスタンス非対応 起動時にジョブを一括リセットしているため、コンテナを複数台並列で動かす（水平拡張する）場合は、他インスタンスで実行中のジョブを巻き添えにします。 複数台にするなら worker_id カラムを導入するか、一括リセットをやめてください。 「バグ」ではなく「この規模だからこその意図的な割り切り」です。BullMQ を検討する規模になったら移行サインと考えています。 jobs テーブルの設計 CREATE TABLE jobs ( id SERIAL PRIMARY KEY, type VARCHAR(50) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', log TEXT, started_at TIMESTAMPTZ, finished_at TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT NOW() ); status は pending → running → done / error と遷移する。 log カラムにジョブの実行ログを蓄積するので、Web UI から確認できる。 ...

はじめに バッチ処理で大量のデータ変換を行う際、PostgreSQLのCTE（Common Table Expression、WITH句）を多用していた時期がありました。複雑な変換処理を段階的に分割できて、コードの見通しも良くなる便利な機能です。 しかし、実際の現場でCTEを使っているコードは意外と少ない。サブクエリや一時テーブルが使われているケースの方が圧倒的に多い印象です。 この記事では、実務でCTEを使って感じた強み・弱みと、「なぜ現場ではCTEが少ないのか」を考察します。特にPostgreSQL 12で大きく改善された最適化の仕組みについても解説します。 CTEの基本おさらい CTEはWITH句を使って一時的な結果セットを定義し、メインクエリから参照できる機能です。 WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ) SELECT region, total_sales FROM regional_sales WHERE total_sales > 10000; サブクエリと似ていますが、名前をつけて再利用できる点が特徴です。変数のように扱えて、複雑なクエリを段階的に構築できます。 CTEの強みと弱み 強み 1. 可読性・保守性の向上 ネストしたサブクエリ地獄を回避できます。処理を論理的なステップに分割して、各ステップに名前をつけられるため、コードレビューやメンテナンスが格段に楽になります。 2. 再帰クエリが書ける WITH RECURSIVEを使えば、階層構造（組織図、カテゴリツリー）を扱えます。これはCTE独自の強みで、サブクエリでは実現できません。 3. 複数箇所から参照できる 同じCTEを複数回参照できます（ただし最適化の観点で注意が必要、後述）。サブクエリだと同じ処理を重複して書く必要があります。 4. デバッグしやすい 各CTEを個別に実行して中間結果を確認できます。サブクエリだと抜き出して実行するのが面倒です。 5. 変換処理の分離 SELECT句での複雑な計算を先にCTEで処理しておけます。WHERE句で使いたいけど計算が複雑な場合に便利です。 弱み 1. 親クエリのパラメータを参照できない サブクエリなら外側の列を参照できる（相関サブクエリ）のに対し、CTEは独立しているため参照できません。 -- サブクエリなら可能 SELECT * FROM orders o WHERE amount > (SELECT AVG(amount) FROM orders WHERE region = o.region); -- CTEでは不可能（外側のo.regionを参照できない） 2. 大量データ・長時間処理には不向き メモリ上に保持されるため、巨大データだと辛い。一時テーブルならインデックスを作成したり統計情報を活用できます。 実際、バッチ処理で数百万行のデータを扱う際、CTEよりも一時テーブルの方がパフォーマンスが良いケースが多かったです。 3. PostgreSQL 11以前は「最適化バリア」になる これが最大の問題でした。次のセクションで詳しく解説します。 ...

参考 この記事は、以下の記事を読んで疑問に思ったことを調べた学習記録である。 Zennの検索スピードを5倍に高速化した話 記事では、Zennのサイト内検索をpg_trgm拡張を使って平均6倍、95パーセンタイルで4.25倍高速化した事例が紹介されている。 なぜ中間一致検索は遅いのか 通常、PostgreSQLでLIKE '%keyword%'のような中間一致検索を実行すると、BTreeインデックスが使えずフルスキャンが発生する。BTreeインデックスは文字列の前方一致には有効だが、中間一致では活用できない構造になっているためである。 データ量が増えると、このフルスキャンが深刻なパフォーマンスボトルネックになる。参考記事では、検索に1秒〜数秒かかる状態だったとのことだ。 n-gramインデックスの仕組み n-gramインデックスは、文字列をn文字ずつに分割してインデックス化することで、中間一致検索でもインデックスを効かせる仕組みである。 3-gramの例 「PostgreSQL」という文字列を3-gram（トライグラム）で分割すると以下のようになる。 __P, _Po, Pos, ost, stg, tgr, gre, reS, eSQL, QL_, L__ 先頭と末尾にはパディング文字（_）が付与される。 検索時の動作 「stgre」というキーワードで検索する場合： 検索キーワードを3-gramで分割: stg, tgr, gre インデックスからこれらすべてのトライグラムを含む文書を抽出 抽出された候補に対してRecheck処理を実行 重要なのは「いずれか」ではなく「すべて」のトライグラムが存在する文書が候補になる点である。もし「いずれか」だと、無関係な文書が大量に候補に含まれてしまう。 Recheck処理が必要な理由 n-gramインデックスでは、インデックスレベルでの検索後に必ずRecheck処理が必要になる。 具体例 以下のような状況を考える。 本文: 「小学校校長」 クエリ: 「小学校長」 3-gramで分割すると： 「小学校校長」→ 小学校, 学校校, 校校長 「小学校長」→ 小学校, 学校長 「小学校」が共通しているため、n-gramレベルでは「小学校校長」が候補として抽出される。しかし実際には「小学校長」という文字列は含まれていない。 このようなfalse positive（誤検出）を除外するため、インデックスで絞り込んだ候補に対して、実際に検索キーワードが含まれているかを厳密にチェックする必要がある。これがRecheck処理である。 pg_trgmとpg_bigmの選択 PostgreSQLには2つの主要なn-gram拡張がある。 pg_trgm: 3-gram方式、PostgreSQL本体にcontribとして付属 pg_bigm: 2-gram方式、サードパーティ製（NECが開発） 比較表 機能 pg_trgm pg_bigm エコシステム PostgreSQLコミュニティ サードパーティ ILIKE対応 ○ × 2文字以下の検索 × ○ Recheck無効化 × ○ インデックスサイズ 小 大（約2倍） なぜpg_trgmが選ばれたか 参考記事では、以下の理由でpg_trgmのみを採用している。 ...