はてなブックマーク

1. ストアドプロシージャを使ってトランザクションを消費する 2. pg_resetwalを使う 3. 内部的にXIDをスキップする 4. 内部的にXIDを高速に”進める” 本記事はPostgreSQL Advent Calendar 2023 21日目の記事です。 PostgreSQLのトランザクションID（以下XID）は内部的には「単調増加する32bitの非負整数値」なので、2^32-1に達した後は0に戻ります。PostgreSQLでは、データベースに変更を加えるトランザクションに対して1つXIDが割り当てられます。XIDの大小関係を利用してテーブル内の行の可視性判断[^visibility]をしているので、XIDが上限に達して周回してしまうとそのロジックが壊れてしまいます。 そこでPostgreSQLでは周回が起こる前（具体的にはXIDを2^31(≒約20億)個消費する前）に、agg

Datum insert_executor(PG_FUNCTION_ARGS) { Relation rel; Oid nspid, relid; EState *estate; ResultRelInfo *relinfo; RangeTblEntry *rte; TupleTableSlot *slot; /* Open table "test" */ nspid = get_namespace_oid("public", false); relid = get_relname_relid("test", nspid); if (!OidIsValid(relid)) elog(ERROR, "table \"%s\" does not exist", "test"); rel = table_open(relid, RowExclusiveLock); /* Set up execu

同時実行制御とトランザクションID コミットログ スナップショット ガベージコレクション（VACUUM） ロングトランザクション”扱い”となるもの テーブルのVacuum インデックスのVacuum 最後に このエントリはPostgreSQL Advent Calender 2021の22日目の記事です。 shallow1729さんのMVCCとInnoDBでの実装についてという記事を読んで、これのPostgreSQL版を書きたいなと思い書いてみました。 同時実行制御とトランザクションID PostgreSQLは複数のクライアントがトランザクションを同時に実行することが可能で、各トランザクションは他のトランザクションの影響を受けずに処理します。これはトランザクションのACID特性の隔離性（Isolation）の特性です。 以下に簡単な例を挙げます。 -- トランザクションA 開始 BEGIN

vacuum_cleanup_index_scale_factorの廃止 Btreeのページリサイクルの改善 ページ内に記録するトランザクションIDを64-bit トランザクションIDに変更 Vacuum（とautovacuum）は、テーブルとインデックスのゴミ掃除をした後にindex cleanupと呼ばれる「インデックスVacuumの後処理」のようなものを実行します。実際の処理内容はインデックスの種類によって異なりますが、index cleanupの主な目的はインデックスの統計情報（ページ数、タプル数）を取得することです（インデックスによっては、削除済みページの回収など、他の処理を行う場合もあります）。インデックスのゴミ掃除をした場合（つまりテーブルにゴミがある状態でVacuumが実行された場合）は、インデックスの統計情報はすでに取得済みなので、index cleanupでは何もしませ

パッチレビューの仕方 必要なもの レビューで貢献するともれなく・・・ おすすめパッチ Clients Documentation Miscellaneous Monitoring & Control Performance Replication & Recovery SQL Commands System Administration 注意点 最後に 昨年9月にPostgreSQL 13がリリースされましたが、PostgreSQL開発コミュニティでは現在PostgreSQL 14の開発を行っています。PostgreSQLの開発はCommitfestと呼ばれるWebページにパッチを登録し、みんなで1ヶ月間レビューする、という形で行われます。PostgreSQL 14に向けたCommitfestはすでに3回行われていて、2021年1月1日から第4回が始まりました（3月から始まる第5回が最後）

どこで手に入るの？ 何がはいってるの？ srcディレクトリを見てみる src/backendディレクトリにあるサーバ側のコードを見てみる ソースコードを読む際に知っておくと便利な関数 SQLを受け取り、処理する所 COPYやALTER TABLE等のDDLやSQLコマンドを実行する所 ソースコードを読む時に知っておくと便利な事(2020/12/15 追記) palloc()とpfree()関数 ereport()とelog()関数 先日のPostgreSQLアンカンファレンスでPostgreSQLのソースコードのディレクトリ構成や読み方について簡単に紹介しました。 ソースコードのディレクトリ構成は今後変わる予定があるので、ブログにまとめて今後も適宜アップデートしていこうと思います。 以下の説明はPostgreSQL 13をベースとしています。 どこで手に入るの？ 公式のgitリポジトリ g

7年半勤めたNTTデータを退職し、11月1日から2ndQuadrantで働いています。 データベースを全く触ったことがない状態で配属されてから、NTTデータで4年、NTT OSSセンタで3年半、PostgreSQLを中心に色々な経験をさせていただきとても楽しかったです。本当に感謝しています。 2ndQuadrantという会社を初めて聞く方も多いと思います。2ndQuadrantはイギリスにある企業で、PostgreSQLを中心としてサービスを展開しています。24x365サポートやRemote DBA等のサービスを提供しながら、PostgreSQL関連ツールの作成（pglogical、Postgres-BDR1）や、PostgreSQL本体の開発もとても積極的に行っている企業です。現在PostgreSQLのコミッタは29人いるのですが、その内5人が2ndQuadrantに所属しており、「最も

バグの発見 原因解析 - RECOVERYHISTORYファイルとはなにか？- RECOVERYHITORYファイルのその後は？ まとめ 本バグの影響は？ 私自身PostgreSQL本体の開発やバグ修正を何度か行っているのですが、最近リカバリ機能周りで面白いバグを修正したので、バグの発見から原因の特定、修正まで実際に行ったことを紹介しようと思います。これからPostgreSQLに貢献していきたい、開発を始めたいという方に参考になると嬉しいです。 本バグはすでに修正されているため、再現したい方は9.5.19以前、9.6.15以前、10.10以前、11.5以前のどれかを使うか、開発用ブランチを使う場合は、コミットdf86e52cace2c413よりも古いコードを利用してください 本記事ではPostgreSQLの開発用ブランチ(materブランチ)を使用しています。PostgreSQLのソースコ

ざっくりまとめると、 PostgreSQLのロジカルレプリケーションでレプリケーション衝突が起こった場合、衝突が（手動で）回避されるまでスタンバイでの反映が止まる 衝突を回避するための一つの方法として、pg_replication_origin_advance()関数を使って衝突の原因となるWAL（を送信する事）をスキップできるけど、スキップ先のLSNを指定するので他の必要なデータもスキップしてしまうかもしれないよ ということ。 レプリケーション衝突というのは、データの受信側（Subscriber）でのデータを適用と、テーブル内のデータや受信側で実行中の変更内容が衝突する事を指しています。 試してみる 実施にやってみる。まずは、テーブルを作成して、ロジカルレプリケーションを開始。 -- 上流側（Publisher) =# CREATE TABLE test (c int primary k

TL;DR 聴講メモ Intro into durability PostgreSQLのCHECKPIONT CHECKPOINT中にエラーが発生したら？ fsyncへの2つの間違った期待 なぜ今になって問題が明らかになってきた？ そもそもなぜBufferd I/Oなのか？ どうやって直すかか 参考リンク 質疑 最後に 先日PostgreSQLの新しいマイナーバージョンがリリースされました。このマイナーリリースでメインとなる修正は「fsync周りのバグ修正」で、このバグは間違ったfsyncに対する間違った認識から約20年間存在してたバグということで注目されていました。 このバグについてPostgreSQLのコミッタ(Tomas Vondra氏)が解説しているセッションが、先々週開催されたFOSDEM 2019でありました。私もFOSDEM 2019に参加していたのですがその際は裏セッション

パラレルクエリの目的 EXPLAIN(実行計画)の見方 対応している操作 9.6 10 11 対応していない操作 読み込み専用 参考資料 PostgreSQLでは、バージョン9.6からパラレルクエリが利用可能です。Oracle、DB2でもパラレルクエリは実装されていますが、PostgreSQLのパラレルクエリはどのような特徴があるのでしょうか。簡単にパラレルクエリの概要を紹介します。 パラレルクエリの目的 パラレルクエリは、複数のCPUを使ってクエリを並列(Parallel)に処理する機能です。そのため、クエリの実行時間の短縮が期待できます。並行(Concurrent)とは異なるので注意。 EXPLAIN(実行計画)の見方 パラレルクエリが使われた場合の実行計画の読み方は注意が必要です。 まずは、パラレルクエリOFFの実行計画(ANALYZE, VERBOSE)。 =# explain (

TL;DR PostgreSQLのロックマネージャ 行ロック 行ロック待ち トランザクションIDへのロック トランザクション完了の順番待ち まとめ 参考 PostgreSQLが持つpg_lockシステムビューを使うと、PostgreSQLのロックマネージャが管理している情報を見ることができ、誰がどのようなオブジェクトに対し、どの種類のロックを取得しているのか、または取得できずに待っているのかを確認することができます。 PostgreSQLはロック対象のオブジェクトとして、テーブル、インデックス、行（タプル）やトランザクションIDがあります。実はトランザクションIDへのロックはPostgreSQLでは多用していて、pg_locksビューやサーバログでトランザクションID（やトランザクション）に対してExclusiveLockやShareLockの取得は見たことがある人も多いのではと思います。

Deadlockの必要条件(Coffman conditions) Deadlock対策 Deadlock Prevention Wait-die, Wound-wait、No-wait Wait-Die Wound-Wait No-wait Deadlock Avoidance Deadlock Detection 参考 簡単に調べたのでメモ。「Deadlockとは？」は色んなところで解説されているのでここでは割愛。 Deadlockの必要条件(Coffman conditions) Mutual Exclusion 一度に1つのプロセスのみがリソースを使用できる Hold and Wait なにか一つのリソースを保持したまま他のリソースを待つ No preemption リソースの横取りができない Circular wait リソースの保持待ちが循環している。あるP1がP2によって保持

モードの種類 UBOUNDED PRECEDING, UNBOUNDED FOLLOWING CURRNET ROW ROWSモード RANGE, GROUPSモード offset PRECEDING and offset FOLLOWING ROWSモード GROUPSモード RANGEモード まとめ lang: jp Window関数のパーティションはPARTITION BY句で指定するだけなのですが、フレームについては色々モードやオプションがあり細かく指定できます。 フレーム指定は一見難しそうに見えますが、一回理解すると自由自在にWindow関数が使えるようになると思います。MySQL 8.0でもWindow関数が導入されてますます利用頻度が増えた今、少し時間はかかるかもしれませんが、ちゃんと理解しておくと便利です。 Window関数のシンタックスは以下のようになっています。1 2

Window関数とは？ Window関数の考え方 パーティション分割をする（PARTITION BY句） フレーム境界を指定する まとめ lang: jp 最近久しぶりにWindow関数を詳しく見てみたので、何回かに分けて解説しようと思います。以降、PostgreSQLを例にして解説しますが、PostgreSQLのWindow関数はSQL標準に対応している部分が多いので、他のDBMSでも同じよう使える部分が多いと思います。 Window関数とは？ Wikipediaより、 SQL において、窓関数もしくはウィンドウ関数 (英: window function) は結果セットを部分的に切り出した領域に集約関数を適用できる、拡張された SELECT ステートメントである。 一見GROUP BY句に似ていますが、Window関数はあくまでも関数なので返却される行数には影響しません。 テーブルの状

一つ目: Update the free space map during vacuum (Claudio Freire) 二つ目: Allow vacuum to avoid unnecesary index scans (Masahiko Sawada, Alexander Korotkov) PGConに向う途中です。 搭乗まで時間があるので、先日リリースされたPostgreSQL 11でVacuum機能の改善がいくつかあったのでその中から2つ紹介します。 一つ目: Update the free space map during vacuum (Claudio Freire) Vacuum中にFree Space Map(FSM)が更新されるようになりました。(Claudio Freire) これまでVacuum（FULLオプションなし）では、Vacuumの実行完了後にFSMを更新

1. メタコマンドとSQLを一緒に使う 実行例 2. SELECT結果の値だけを取得する 実行例 3. SQLでSQLを作り実行 (9.6~) 実行例 4. サーバに応じて実行するSQLを変える (10~) 実行例 5. 忘れたDDLのシンタックスを確認する 実行例 6. SQLファイルの内容を一行ずつ確認しながら実行する 実行例 7. 特定のコマンドを定期的に実行したい 実行例 8. psqlを起動した時に実行されるコマンドを設定する 実行例 9. .psqlrcを一時的に使わない 実行例 10. SELECTの結果をCSV形式で出力 実行例 普段よく使っているpsqlで便利だと思う使い方を10個紹介します。運用で使うシェルスクリプトとかでもpsqlは使う事があると思うので、psql派でない人にも多少は役に立つはず。 特に最近のバージョンで追加された機能は、利用できるバージョンを記載して

Replicaiton Originの目的は？ 「1. Logical Replicationの時に、レプリケーションの進捗を追跡する」について 「2. レプリケーションされた情報がローカル発生したのか、リモートから発生したのかを区別する」について PostgreSQLのLogical Replicationはいくつかのコンポーネントから実現されていており、その一つが Replication Origin です。PostgreSQLの日本語マニュアルだとReplication Originは「レプリケーション起点」と訳されていますが、名前だけみてもあまりぱっとイメージが付かなくて気になったので、少し調べた結果をまとめます。 Replicaiton Originの目的は？ Replication OriginはPostgreSQLのLogical Replicationでのみ使用される機能で

UUIDv7はRFC 9562で定義されました。UUIDには8つのバージョンがあり、どれもサイズは128ビットですが、格納するデータがそれぞれバージョンで異なります。私もUUIDv7を知るまでは、UUIDといえばランダムデータのイメージでしたが、それはバージョン4のUUIDで、本記事で紹介するバージョン7のUUID（UUIDv7）はタイムスタンプをデータの先頭に持つためソート可能であるのが大きな特徴です。