Postgresql

記事中で出てくる計測等は以下のリポジトリで実施した。 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のみを採用している。 ...