Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Exploring Postgres VACUUM with the VACUUM Simulator

Keiko Oda
November 23, 2023

Exploring Postgres VACUUM with the VACUUM Simulator

VACUUM Simulatorを使ってVACUUMをもっと理解しよう
Exploring Postgres VACUUM with the VACUUM Simulator

PostgreSQL Conference Japan 2023
November 24, 2023

Keiko Oda

November 23, 2023
Tweet

More Decks by Keiko Oda

Other Decks in Technology

Transcript

  1. VACUUM Simulatorを使って
    VACUUMをもっと理解しよう
    Exploring Postgres VACUUM with the VACUUM Simulator
    PostgreSQL Conference Japan 2023
    November 24, 2023
    Keiko Oda - pganalyze

    View full-size slide

  2. ● 織田敬子 (Keiko Oda)
    ● Product Engineer at pganalyze
    ● Using Postgres ~10 years (with 4
    years gap)
    ● 金沢市在住 Kanazawa, Ishikawa
    Speaker introduction

    View full-size slide

  3. ● スライドは以下からも見れます
    https://speakerdeck.com/keiko713/va
    cuum-simulator
    ● 話したいことがたくさん!なので抽
    象的または概要しか話せないことも
    ○ おまけスライドたくさんついてます
    ● 日本語がメイン、下に英語のサブタ
    イトルが付いています
    Notes
    Learn More Link
    もっと深く学べるコンテンツ
    へのリンク
    Link to learn more

    View full-size slide

  4. Today’s Goal
    01
    What is VACUUM?
    02
    Autovacuums
    03
    Pitfalls
    Autovacuumのつまずきポイ
    ントを学ぶ
    Learn what could go wrong with
    autovacuums
    VACUUMとは何か、なぜ必要
    なのかを理解する
    Understand what VACUUM is and why it’s
    needed
    Autovacuumはどのタイミン
    グで起きるのかを理解する
    Understand what triggers autovacuums
    04
    Tips
    今日から使えるautovacuum
    まわりの小技を学ぶ
    Learn tips related autovacuum that you
    can use from today

    View full-size slide

  5. What is VACUUM?
    VACUUMとは何か、なぜ
    必要なのかを理解する
    Understand what VACUUM is and why it’s
    needed
    01

    View full-size slide

  6. What is VACUUM?
    ● VACUUMは、Postgresのアーキテクチャ (MVCC) によっ
    て生ずるさまざまな「ゴミ」を再利用可能にしてくれ

    VACUUM makes all kinds of “garbage” generated by Postgres due to its architecture
    (MVCC) reusable
    ● MVCC (Multiversion Concurrency Control)
    ○ トランザクションの分離を行うことで、たくさんの読み
    手と書き手がいてもお互いをブロックしない
    By providing transaction isolation, readers never block writers, and writers
    never block readers
    ○ Readers never block writers, and writers never block
    readers
    Learn More: 2-4, 2-5

    View full-size slide

  7. MVCC in 3 minutes
    読み手と書き手は一つのデータをみんなで参照するのではな
    く、それぞれデータのスナップショットを元にクエリを走ら
    せる
    Readers and writers are not referring the single source of data, but running queries with a
    snapshot of the data
    INSERT
    SELECT
    SELECT
    もし一つのデータをみんなで参照していたら…
    If everyone is looking at the same data…
    Learn More: 2-4, 2-5

    View full-size slide

  8. スナップショットはトランザクションごとに発行されるメガ
    ネのようなもの
    Snapshots are like a pair of glasses, issued per transaction
    MVCC in 3 minutes
    INSERT
    SELECT
    メガネによって同一のデータを見ても見え方が変わる
    Different pairs of glasses (snapshots) can show different views of the same scene (database)
    Learn More: 2-4, 2-5
    UPDATE
    snapshot

    View full-size slide

  9. MVCC in 3 minutes
    行が削除されても、他のスナップショットによってまだ参照
    されている可能性があるため、物理的に削除できない
    Even if the row is deleted, it can’t be deleted physically as it could be referred by some
    snapshot
    Learn More: 2-4, 2-5
    SELECT
    🍎🍊
    DELETE 🍎
    🍎🍊 SELECT
    🍎🍊
    🍎🍊
    Can't be deleted
    physically and only
    marked as deleted
    この物理的に削除されないデータが「ゴミ」となる
    These data that cannot be deleted physically becomes “garbage”

    View full-size slide

  10. What is VACUUM?
    ● ゴミの種類 Kinds of garbage
    ○ 削除された行(ゴミタプル) Dead rows/tuples
    ○ 古いトランザクションID Old Transaction IDs
    ● VACUUMのその他の役割 Other roles of VACUUM
    ○ VACUUM ANALYZEで統計情報の更新
    Update data statistics with VACUUM ANALYZE
    ○ Index-only scanで使うVisibility Mapの更新
    Update the visibility map used by index-only scans
    Learn More: Visibility Map

    View full-size slide

  11. What is VACUUM?
    ● ゴミが放置されると…
    When garbage is left uncleared
    ○ 不要なデータが残り続けてクエリの際に余計なI/O
    をしなければならず、パフォーマンスが低下する
    Unremoved dead data remains in the database, requiring additional I/O during
    queries, which results in performance degradation
    ○ トランザクションIDが枯渇して新規に発行できず
    アプリケーション障害に繋がる(トランザクショ
    ンID周回)
    There will be no new transaction ID to issue, resulting in the application down
    (transaction ID wraparound failures)
    ● これらを防ぐためにもVACUUMは大切!
    It is critical to run VACUUM to prevent these issues from happening!

    View full-size slide

  12. What is VACUUM?
    ● ゴミの種類 Kinds of garbage
    ○ 削除された行(ゴミタプル) Dead rows/tuples
    ○ 古いトランザクションID Old Transaction IDs
    ● VACUUMのその他の役割 Other roles of VACUUM
    ○ VACUUM ANALYZEで統計情報の更新
    Update data statistics with VACUUM ANALYZE
    ○ Index-only scanで使うVisibility Mapの更新
    Update the visibility map used by index-only scans

    View full-size slide

  13. Dead rows
    ● 行が削除されるとき、Postgresでは行を物理的に削除
    せず、行が削除されたとマークされる
    When rows are deleted, Postgres doesn’t delete them but marks them as deleted
    UPDATEも
    削除と挿入
    UPDATE is also “DELETE”
    and “INSERT”

    View full-size slide

  14. Dead rows
    ● このように削除された行をdead rows(不要行)、また
    はdead tuples(不要タプル・ゴミタプル)という
    These deleted rows are called “dead rows” or “dead tuples”
    ○ 本スライドでは今後dead rowsまたはゴミタプルと呼ぶ
    In this slide, we will use “dead rows” moving forward
    ○ Postgresのドキュメントや統計ビューなどではdead row versionsや
    dead tuplesと呼ばれている
    In Postgres documentation or system column, it’s often called dead row
    versions or dead tuples
    dead row
    ゴミタプル

    View full-size slide

  15. Dead rows
    ● VACUUMはこれらのゴミタプルを再利用可能にしてくれ

    VACUUM makes these dead rows reusable
    ○ 再利用可能となったスペースはOSには返されずに次回の
    INSERTやUPDATEに使用される
    Reusable spaces won’t be returned to the OS and will be used for the future
    UPDATEs and INSERTs (on the same table)
    dead row
    ゴミタプル
    テーブル
    Table
    ヒープページ
    Heap page

    Row

    View full-size slide

  16. Dead rows
    ● VACUUMが走ったからといってテーブルサイズが減るわ
    けではない
    Running VACUUM doesn’t mean that the table size will decrease
    ○ pg_repackを使用すると実際に減らすことが可能
    Using pg_repack will actually reduce the size of the table
    ○ VACUUM FULLも同様のことをするが、非常に強いロックを要する
    ため実用使用向けではない
    VACUUM FULL also does the same, though it requires a heavyweight lock and
    can’t be used in the most cases

    View full-size slide

  17. What is VACUUM?
    ● ゴミの種類 Kinds of garbage
    ○ 削除された行(ゴミタプル) Dead rows/tuples
    ○ 古いトランザクションID Old Transaction IDs
    ● VACUUMのその他の役割 Other roles of VACUUM
    ○ VACUUM ANALYZEで統計情報の更新
    Update data statistics with VACUUM ANALYZE
    ○ Index-only scanで使うVisibility Mapの更新
    Update the visibility map used by index-only scans

    View full-size slide

  18. Old Transaction IDs
    ● PostgresではトランザクションIDは有限で再利用され
    ている (32-bit ≒ 40億)
    Transaction ID in Postgres is finite and old ones need to be reused (32-bit ≈ 4 billion)
    ● 40億のうち半数の20億は過去のトランザクションID、残
    りの半数は未来のトランザクションIDとしている
    Among this 4 billion, the half of them are assigned as the past transaction IDs,
    and the other half are assigned as the future transaction IDs




    future
    past

    Future
    (invisible)
    XID: 1234
    Age: -1234

    Current
    XID: 1234
    Age: 0

    Old
    XID: 1234
    Age: 5000

    To be
    reused
    XID: 1234
    Age: 2B

    Future
    (invisible)
    XID: 1234
    Age: -1234

    View full-size slide

  19. Learn More: 2-3
    Old Transaction IDs
    ● 行ごとに行に関するメタデータがあり、その行が作ら
    れたトランザクションID (xmin) が書かれている
    Every row contains some associated metadata, including information about the
    transaction ID of the transaction that created it (xmin)
    ● VACUUMは行を走査し、凍結可能な行のメタデータに凍
    結フラグをたてる
    VACUUM will go through rows and update a row metadata to note that the row has been
    frozen

    View full-size slide

  20. Old Transaction IDs
    ● 凍結されることにより、テーブル毎に使用している最
    古のトランザクションIDがより新しいものとなる
    By freezing rows, the oldest transaction ID used by each table will be updated to
    “newer” transaction IDs
    ○ 例では、凍結前は1212が最古のトランザクションIDであっ
    たものが、凍結後は1717になっている
    In the example, the oldest transaction ID before freezing was 1212, but it
    became 1717 after the freezing
    Learn More: 2-3
    凍結されていない最古の
    トランザクションID
    the oldest unfrozen
    transaction ID
    凍結されていない最古の
    トランザクションID
    the oldest unfrozen
    transaction ID

    View full-size slide

  21. ● トランザクションIDのage(齢)は現トランザクションIDとの
    差で表される
    The age of the transaction ID is calculated by the difference from the current
    transaction ID
    ● Example: the current transaction ID is 5,000
    ○ Before freezing: the oldest unfrozen TXID 1212, age 3,788
    ○ After freezing: the oldest unfrozen TXID 1717, age 3,283
    凍結されていない最古の
    トランザクションID
    the oldest unfrozen
    transaction ID
    凍結されていない最古の
    トランザクションID
    the oldest unfrozen
    transaction ID
    Old Transaction IDs - Age

    View full-size slide

  22. Old Transaction IDs
    ● VACUUMはこうして古いトランザクションIDを凍結に
    よって開放することにより再利用可能にしてくれる
    VACUUM will release the old Transaction IDs and make them reusable for the future
    transactions by freezing them
    ● 何もしていないテーブルにも凍結(=VACUUM)は必要
    Tables without any activities still require freezing
    ○ 何もしていなくても、テーブル最古のトランザクション
    IDは現在のトランザクションIDと比べて相対的に古くなっ
    ていき凍結が必要となる
    Even nothing is done with the table, the oldest transaction ID of the table is
    getting older relatively compared to the current transaction ID, and requires
    freezing

    View full-size slide

  23. VACUUM Phases
    ● VACUUMはいくつかのフェーズに分かれている
    VACUUM has several phases
    ○ pg_stat_progress_vacuumビューで今走ってい
    るVACUUMのフェーズと進捗を見ることができる
    You can check the progress of VACUUM using a pg_stat_progress_vacuum
    view

    View full-size slide

  24. VACUUM Phases - terminology 用語
    テーブル・ヒープ
    Table/Heap
    ヒープページ
    Heap page
    行・タプル
    Row/Tuple
    ● Heap: テーブルのこと。特に、インデックスなどではな
    くテーブルの実際のデータ部分を指す
    Heap refers to the table itself, where the actual data rows are stored

    View full-size slide

  25. VACUUM Phases - example
    ● Flowers table: DELETEとUPDATEによってdead rowsが2
    つある状態
    Flowers table: 2 dead rows due to DELETE and UPDATE operations
    ● Primary keyにインデックスflowers_pkeyがあり、イ
    ンデックスにもゴミがたまっている
    Primary key index flowers_pkey also contains some garbage

    View full-size slide

  26. ● Scanning heapフェーズでは
    テーブルを走査してdead rows
    を見つける
    During this phase, VACUUM goes through the
    table to find dead rows
    ● 見つけたdead rowsはVACUUM
    TODOリストに入れておく
    Put found dead rows into the VACUUM TODO list
    ● 凍結する場合はこのフェーズで
    行う
    Freezing is done during this phase
    VACUUM Phases - scanning heap

    View full-size slide

  27. ● Scanning heapフェーズで作っ
    たVACUUM TODOリストを元に
    インデックスを掃除する
    Clean up indexes based on the VACUUM TODO list
    made in the previous scanning heap phase
    ● 一番時間のかかるフェーズ
    The most time consuming phase
    ● インデックスが複数あるときは
    すべてをひとつずつ掃除する
    When there are multiple indexes, VACUUM needs
    to clean up indexes one by one
    ○ [Postgres 13+] 複数のインデッ
    クスを並列に掃除可能
    [Postgres 13+] Vacuuming indexes can be
    run parallel
    VACUUM Phases - vacuuming indexes

    View full-size slide

  28. ● Scanning heapフェーズで作っ
    たVACUUM TODOリストを元に
    ヒープを掃除する
    Clean up dead rows based on the VACUUM TODO
    list made in the previous scanning heap phase
    ● インデックスによって参照され
    ていないことを保証しなければ
    ならないため、必ずvacuuming
    indexesフェーズの後に来る
    This phase must run after the vacuuming indexes
    phase since heap blocks to be cleaned are needed
    to be guaranteed not being referred by any
    indexes
    VACUUM Phases - vacuuming heap

    View full-size slide

  29. VACUUM Phases
    ● VACUUM TODOリストの大きさは決まっている
    The size of VACUUM TODO list is limited
    ○ デフォルトではautovacuum_work_mem
    (maintenance_work_mem)の64MBで、最大は1GB
    The default size is 64MB from autovacuum_work_mem
    (maintenance_work_mem), then it can be the maximum 1GB
    ○ もし最初のscanning heapフェーズでヒープページ
    を全部走査する前に埋まってしまった場合は、全部
    走査ができるまで三つのステップを繰り返す
    If it’s filled during the first scanning heap phase before scanning all
    heap pages, these 3 phases will be repeated until all heap pages are
    scanned

    View full-size slide

  30. VACUUM Phases - 小ネタ
    ● [Postgres 12+] VACUUMには一番時間のかかるフェー
    ズ、vacuuming indexesを飛ばすオプション
    INDEX_CLEANUP offがある
    [Postgres 12+] You can use INDEX_CLEANUP off to skip the most time consuming
    phase, the vacuuming indexes phase
    ● でも…vacuuming indexesを飛ばすとvacuuming heap
    が出来ないのでは?
    Well… if you skip the vacuuming indexes phase, you can’t really run the vacuuming
    heap page because it’s depending on it?
    ○ ゴミタプル自体(Item)の掃除はできるが、ヒープに
    ItemIDのゴミが残る
    You can clean up most of the heap, but some uncleaned bits will remain
    ○ 詳しくはINDEX_CLEANUP offの副作用を参照
    Check out the article “the side effect of INDEX_CLEANUP off” by Masahiko
    Sawada for the detail

    View full-size slide

  31. Autovacuums
    Autovacuumは
    どのタイミングで起きるのか
    を理解する
    Understand what triggers autovacuums
    02

    View full-size slide

  32. ● パラメータに応じてPostgresが自動でVACUUMを走らせ
    てくれる
    Postgres will automatically run VACUUM based on a set of predefined parameters
    Autovacuum
    launcher
    worker1
    worker2
    worker3
    VACUUMが必要か
    チェック
    Needs VACUUM?
    I’m VACUUMing table2!
    I’m VACUUMing table1!
    worker3さん、VACUUM
    お願いします!
    Please run VACUUM with
    table3, worker3!
    は〜い

    View full-size slide

  33. ● パラメータは、サーバーレベルのものをデフォルトと
    して、テーブル毎にも上書きできる
    There are server-level default parameters, which can be overwritten by the table-level
    settings, allowing you to define unique settings for each table
    ● テーブル毎のチューニングも非常に有効
    It is effective to tune parameters per table as each table has different needs
    Autovacuum
    launcher
    Table3のdead tuplesが
    閾値を超えている、
    VACUUMしないと!
    Table 3 is exceeding the
    threshold, need to VACUUM!

    View full-size slide

  34. What triggers autovacuums?
    ● Autovacuumのトリガー要因(閾値)
    What triggers autovacuums? (thresholds)
    ○ Dead rows
    ■ どれだけのdead rowsがテーブルにあるか
    How many dead rows in the table
    ○ Freeze age
    ■ 最古の凍結されていないトランザクションIDのage
    (現トランザクションIDとの差)はどれだけか
    How old is the oldest unfrozen transaction ID of the table
    ○ Inserts [Postgres 13+]
    ■ 最後のVACUUMからどれだけの行が挿入されたか
    How many rows are inserted since the last VACUUM

    View full-size slide

  35. ● Normal
    ○ Dead rowsまたはinsertsによってトリガーされたもの
    Triggered by dead rows or inserts
    ○ 凍結は行われず、dead rowsの掃除またはvisibility map
    の更新のみが行われる
    No freezing, only cleaning up dead rows or updating the visibility map
    ● Aggressive(凍結するぞモード)
    ○ Anti-wraparound autovacuum(Freeze ageによってトリ
    ガーされたもの)、またはNormalのうち、freeze ageが
    vacuum_freeze_table_ageに達したもの
    Anti-wraparound autovacuums (triggered by freeze age), or those with a freeze
    age exceeding the vacuum_freeze_table_age among the normal modes
    ○ 凍結が行われる
    Perform freezing
    Autovacuum Modes

    View full-size slide

  36. デフォルトでは、だいたいテーブルの20%がdead rowsとな
    るとautovacuumがトリガーされる
    ● autovacuum_vacuum_threshold: 50
    ● autovacuum_vacuum_scale_factor: 0.2
    Parameters: triggered by dead rows
    Threshold = autovacuum_vacuum_threshold +
    autovacuum_vacuum_scale_factor * pg_class.reltuples
    Dead rows threshold
    pg_class.reltuples: テーブルの行数 (number of rows of the table)

    View full-size slide

  37. デフォルトでは、テーブルごとに凍結されていない最古のト
    ランザクションIDのageが2億を超えるとautovacuumがトリ
    ガーされる
    ● autovacuum_freeze_max_age: 200,000,000
    ● autovacuum_multixact_freeze_max_age: 400,000,000
    Parameters: triggered by freeze age
    Threshold = autovacuum_freeze_max_age OR
    autovacuum_multixact_freeze_max_age
    Freeze age threshold

    View full-size slide

  38. デフォルトでは最初の1,000行が挿入されるとautovacuumが
    トリガーされる
    ● Autovacuum_vacuum_insert_threshold: 1,000
    ● Autovacuum_vacuum_insert_scale_factor: 0.2
    Parameters: triggered by inserts
    Threshold = autovacuum_vacuum_insert_threshold +
    autovacuum_vacuum_insert_scale_factor * pg_class.reltuples
    Inserts threshold
    pg_class.reltuples: テーブルの行数 (number of rows of the table)

    View full-size slide

  39. ● 過去のテーブルに関する統計情報を元に、autovacuum
    がどのトリガー要因をもとにいつ発生したかをシミュ
    レーションしグラフを描画する
    From the historical statistic of the table, draw simulation graphs of what triggered
    autovacuums and when
    ● Autovacuumのトリガー要因に関係するパラメータを調
    節することによって、autovacuumの発生タイミングが
    どのように変わるのかをシミュレーションする
    Simulate how the timing of autovacuums will change by tweaking the Postgres setting
    related to autovacuums
    ● https://vacuum-simulator.netlify.app
    VACUUM Simulator

    View full-size slide

  40. ● Table: public.issue_references
    ● UPDATEとDELETEが一日に一度、大量に起こっている。
    それ以外は常時UPDATEと少なめのINSERTが起こってい

    Lots of UPDATEs and DELETEs are happening once a day. Other than that, there are
    steady UPDATEs and a little bit low volume of INSERTs
    ● 現状では、dead rows VACUUMが一日に一度かそれ以上
    起こっている
    With the current configuration, dead rows VACUUMs are happening at least once a day
    ● Dead rows VACUUMがなくなったらどれほどのdead
    rowsが発生するのかをみてみよう
    See how much dead rows will be accumulated when VACUUMs weren’t happening
    Simulation 1: dead rows VACUUMs

    View full-size slide

  41. Simulation 2: freeze age VACUUMs
    ● Table: public.postgres_roles
    ● DELETEが一日に一度大量に起こっているが、トータル
    行数に比べると多くない。それ以外は常時少なめの
    UPDATEとINSERTが起こっている
    Lots of DELETEs are happening once a day, though it’s not that much compares to the
    total rows. Other than that, there are a low volume of UPDATEs and DELETEs
    ● 現状では、dead rows VACUUMは起こっていなく、
    freeze age VACUUMのみ起こっている
    With the current configuration, there is no dead rows VACUUMs and only freeze age
    VACUUMs are happening
    ● どうやったらdead rows VACUUMを起こせるか試してみ
    よう
    See how we can trigger VACUUMs by dead rows

    View full-size slide

  42. ● Table: public.schema_table_stats_35d
    ● パーティションテーブルなのでパーティションの日に
    しか編集アクティビティがなく、しかも挿入のみ
    Since this is a partition table, table modifying activities are only happening on the day
    of the partition, also INSERTs only
    ● Inserts VACUUMが無くなったらどうなるのか試してみ
    よう
    See what happens when there is no inserts VACUUMs
    Simulation 3: inserts VACUUMs

    View full-size slide

  43. ● Table: public.servers
    ● トータルの行数に対してUPDATE数が大きい。INSERTと
    DELETEはほぼ行われていない
    Compares to the total row count, UPDATEs per minute is quite high. There is almost no
    INSERTs or DELETEs
    ● autovacuumの数をシミュレーションで減らしてみよう
    Let’s try decreasing the number of autovacuums with VACUUM Simulator
    Simulation 4: too many VACUUMs

    View full-size slide

  44. Pitfalls
    Autovacuumの
    つまずきポイントを学ぶ
     Learn what could go wrong with autovacuums
    03

    View full-size slide

  45. Review: What is VACUUM? - おさらい
    ● ゴミが放置されると…
    When garbage is left uncleared
    ○ 不要なデータが残り続けてクエリの際に余計なI/O
    をしなければならず、パフォーマンスが低下する
    Unremoved dead data remains in the database, requiring additional I/O during
    queries, which results in performance degradation
    ○ トランザクションIDが枯渇して新規に発行できず
    アプリケーション障害に繋がる(トランザクショ
    ンID周回)
    There will be no new transaction ID to issue, resulting the application down
    (transaction ID wraparound failures)
    ● これらを防ぐためにもVACUUMは大切!
    It is critical to run VACUUM to prevent these issues from happening!

    View full-size slide

  46. Meaning…
    Autovacuumが走ってない
    Autovacuum is not running
    ゴミタプルが掃除されない
    No one is cleaning up dead rows
    テーブルがゴミで埋まって
    いく(肥大化・bloat)
    Tables becomes bloated with dead rows
    いつものクエリが遅くなる
    Query performance degrades
    アプリがダウンする
    Application goes down
    トランザクションIDの凍結
    がされない
    No one is freezing older transaction IDs
    発行できるトランザクショ
    ンIDが少なくなってくる
    Only a few TXIDs are left to be issued
    データベースがread-only
    になる
    Database becomes read-only mode

    View full-size slide

  47. Meaning…
    Autovacuumが走ってない
    Autovacuum is not running
    ゴミタプルが掃除されない
    No one is cleaning up dead rows
    テーブルがゴミで埋まって
    いく(肥大化・bloat)
    Tables becomes bloated with dead rows
    いつものクエリが遅くなる
    Query performance degrades
    アプリがダウンする
    Application goes down
    トランザクションIDの凍結
    がされない
    No one is freezing older transaction IDs
    発行できるトランザクショ
    ンIDが少なくなってくる
    Only a few TXIDs are left to be issued
    データベースがread-only
    になる
    Database becomes read-only mode
    一度ゴミが大量に溜まると掃除しにくい・しても
    使うディスク容量自体は変わらず大きいままで元
    のパフォーマンスに戻らない
    Once a table is bloated, VACUUM will take longer time to finish, and
    even if it’s VACUUMed, the total table size won’t shrink and the
    performance kept degraded
    Learn More: 4-1

    View full-size slide

  48. Meaning…
    Autovacuumが走ってない
    Autovacuum is not running
    ゴミタプルが掃除されない
    No one is cleaning up dead rows
    テーブルがゴミで埋まって
    いく(肥大化・bloat)
    Tables becomes bloated with dead rows
    いつものクエリが遅くなる
    Query performance degrades
    アプリがダウンする
    Application goes down
    トランザクションIDの凍結
    がされない
    No one is freezing older transaction IDs
    発行できるトランザクショ
    ンIDが少なくなってくる
    Only a few TXIDs are left to be issued
    データベースがread-only
    になる
    Database becomes read-only mode
    トランザクションID周回問題によるデータ破
    壊を防ぐため、新規トランザクションが必要
    なクエリ(挿入・更新等)を許可しない
    To prevent TXID wraparound from happening, any new queries
    require a new transaction will raise errors
    この時点でPostgresが頑張って色々
    してくれる
    Postgres does its best to prevent the next
    step from happening
    Learn More: 1-3, 1-4

    View full-size slide

  49. Running autovacuums is important
    Autovacuumが走ってない
    Autovacuum is not running
    アプリがダウンする
    Application goes down
    ● Autovacuumがちゃんと走っていないと問題が起きる
    Autovacuums not running properly => problems!
    ● どのようなときにautovacuumがうまく走らないのか?
    When and which situation autovacuums stop running properly?
    ???

    View full-size slide

  50. 3 cases of autovacuums go wrong
    Case 1. Bloat: テーブルが肥大化している
    The table is bloated
    Case 2. Performance: Autovacuumの処理時間が長い
    Autovacuum is taking too much time
    Case 3. Locks: Autovacuumがブロックしたりブロックさ
    れたりする
    Autovacuum is blocking or being blocked

    View full-size slide

  51. ● 閾値の設定がテーブルに合ってい
    なくautovacuumの頻度が低く、
    ゴミが溜まる
    Autovacuum is running infrequently and dead rows
    are accumulated due to improper thresholds
    ● Xmin horizonのせいでゴミタプ
    ルの掃除がうまくできていない
    Unable to clean up dead rows due to the xmin
    horizon
    ● クエリの実行の際により多くの
    データを読まなければならなくな
    りパフォーマンスが低下する
    Autovacuum is running infrequently
    ● ゴミのためより多くのI/Oやディ
    スク容量が必要となる(コスト
    増)
    Increased I/O and/or disk usage due to dead rows
    (might increase operating costs)
    Case 1: Bloated - 肥大化
    Causes
    原因 Consequences
    結果

    View full-size slide

  52. ● 閾値の設定がテーブルに合ってい
    なくautovacuumの頻度が低く、
    ゴミが溜まる
    Autovacuum is running infrequently and dead rows
    are accumulated due to improper thresholds
    ● Xmin horizonのせいでゴミタプ
    ルの掃除がうまくできていない
    Unable to clean up dead rows due to the xmin
    horizon
    ● クエリの実行の際により多くの
    データを読まなければならなくな
    りパフォーマンスが低下する
    Autovacuum is running infrequently
    ● ゴミのためより多くのI/Oやディ
    スク容量が必要となる(コスト
    増)
    Increased I/O and/or disk usage due to dead rows
    (might increase operating costs)
    Case 1: Bloated - 肥大化
    Causes
    原因 Consequences
    結果

    View full-size slide

  53. Case 1: Bloated - 肥大化
    ● 閾値がテーブルのゴミ蓄積パターンに合っていないと
    ゴミを適切なタイミングで掃除できず、不必要に新し
    い容量を使い始めてテーブルが肥大化してしまう
    When thresholds are not matching to the pattern of dead rows accumulating,
    autovacuum is unable to clean up them in appropriate timing, resulting using new
    spaces unnecessary and causing the table bloat
    ● 例えば閾値が20%の場合、100行のテーブルだと20行の
    更新・削除でautovacuumがトリガーされるが、1億行
    のテーブルだと2000万行が更新・削除されないとトリ
    ガーしない
    For example, with 20% threshold, 20 dead rows will trigger autovacuum with a 100 rows
    table. However, 20M dead rows are required to trigger autovacuum for a 100M rows
    table

    View full-size slide

  54. ● 閾値の設定がテーブルに合ってい
    なくautovacuumの頻度が低く、
    ゴミが溜まる
    Autovacuum is running infrequently and dead rows
    are accumulated due to improper thresholds
    ● Xmin horizonのせいでゴミタプ
    ルの掃除がうまくできていない
    Unable to clean up dead rows due to the xmin
    horizon
    ● クエリの実行の際により多くの
    データを読まなければならなくな
    りパフォーマンスが低下する
    Autovacuum is running infrequently
    ● ゴミのためより多くのI/Oやディ
    スク容量が必要となる(コスト
    増)
    Increased I/O and/or disk usage due to dead rows
    (might increase operating costs)
    Case 1: Bloated - 肥大化
    Causes
    原因 Consequences
    結果

    View full-size slide

  55. Case 1: Bloated - 肥大化
    ● ゴミタプルの中でも、この時期以前に作られたものし
    か掃除ができないといった境界“xmin horizon”がある
    Among the dead rows, there is a horizon called “xmin horizon”, which indicates that
    VACUUM can clean up only the dead rows created before this point
    ● Autovacuumがしっかり走っていてもxmin horizonが古
    いとゴミを掃除できずテーブルが肥大化する
    Even though autovacuum is running properly, the “old” xmin horizon might be
    affecting how much can be cleaned and causing the table bloat
    Learn More: 3-1
    1111
    2222 3333
    4444
    5555

    View full-size slide

  56. tuples: 6605628 removed, 140296119 remain, 255707 are dead
    but not yet removable
    removable cutoff: 568440102, which was 2475189 XIDs old
    when operation ended
    Case 1: Bloated - 肥大化
    Learn More: 3-1
    小さいほうがいい
    Smaller is better
    大きいほうがいい
    Bigger is better

    View full-size slide

  57. Case 1: Bloated - 肥大化
    ● Xmin horizonは「一番古いバージョンを見ている人
    (トランザクション)」によって進まない
    The xmin horizon can’t advance because of “the person (transaction) who is watching
    the oldest version”
    Learn More: 3-1
    あ、ぼくまだここ見てるので
    掃除しないでください〜
    Oh, I’m still watching this part, please
    don’t clean up

    View full-size slide

  58. Case 1: Bloated - 肥大化
    ● たいていはxmin horizonのageの分だけ生きているロン
    グトランザクションが見ている
    Usually, the long-running transaction that has been running as the same length/age as
    the xmin horizon is “watching” the oldest dead tuple
    Learn More: 3-1
    3日前に BEGIN した人
    Ran BEGIN 3 days ago
    3日分のゴミ
    3 days worth garbage

    View full-size slide

  59. Solution
    解決
    Xmin horizonが古くなっている原因のロングトランザクションを取り除く
    Remove the long-running transaction that is causing the xmin horizon to be held back
    ● 長く走っているクエリ・トランザクション
    Long-running queries/transactions
    ● ロジカルレプリケーションの遅延または不備
    Lagging or stale logical replication slots
    ● ストリーミングレプリケーションの遅延または不備(hot_standby_feedback on)
    Lagging or stale physical (streaming) replication slots with hot_standby_feedback on
    ● スタンバイサーバでの長く走っているクエリ(hot_standby_feedback on)
    Long-running queries on standbys with hot_standby_feedback on
    ● 放置された二相コミット用のトランザクション
    Abandoned prepared transactions
    Case 1: Bloated - 肥大化

    View full-size slide

  60. ● 適切にI/Oが割り当てられていな
    くI/Oがボトルネックになり時間
    がかかる
    I/O is not allocated well and being a bottleneck
    ● 適切にメモリが割り当てられてい
    なくVACUUMが非効率になってい
    て時間がかかる
    Not assigned enough memory to perform VACUUM
    efficiently
    ● Autovacuumが途中でDDLなどに
    より中断されてしまい終わらない
    Things like DDL could interrupt autovacuums and
    they won’t finish
    ● VACUUM待ちテーブルが発生し、
    肥大化の原因となる
    There will be backlogs of autovacuums, causing
    table bloat
    ● 重要なVACUUMが実行されずアプ
    リダウンの危険がある
    Important VACUUMs (like anti-wraparound) won’t get
    executed and could cause the application down
    Case 2: Performance - パフォーマンス
    Causes
    原因 Consequences
    結果

    View full-size slide

  61. Case 2: Performance - パフォーマンス
    Autovacuumを効率よく走らせるにはパラメータとデータ
    ベースの作業負荷の絶妙なバランスが重要
    Having a good balance between parameters and database workload is important for running
    autovacuums effectively
    launcher
    worker1
    worker2
    worker3

    View full-size slide

  62. launcher
    worker1
    worker2
    worker3
    Case 2: Performance - パフォーマンス
    Autovacuumのパフォーマンス関連のパラメータ
    Autovacuum parameters related to performance
    最大何人ワーカーがいるか
    How many workers concurrently run max
    autovacuum_max_workers
    ワーカーあたり使用できるメモリ
    How many workers concurrently run max
    autovacuum_work_mem
    autovacuum要否チェックの間隔
    Delay between autovacuum runs check
    autovacuum_naptime
    64MB
    1 min 3 workers

    View full-size slide

  63. Case 2: Performance - パフォーマンス
    Autovacuumのパフォーマンス関連のパラメータ
    Autovacuum parameters related to performance
    Cost limitに達したときにどれだけ待つか
    Delay when the cost limit is reached
    autovacuum_vacuum_cost_delay
    一度にVACUUMを進められるコスト量
    The cost limit for how much VACUUM can be progressed
    at one time
    autovacuum_vacuum_cost_limit
    200 200
    2ms

    View full-size slide

  64. ● 適切にI/Oが割り当てられていな
    くI/Oがボトルネックになり時間
    がかかる
    I/O is not allocated well and being a bottleneck
    ● 適切にメモリが割り当てられてい
    なくVACUUMが非効率になってい
    て時間がかかる
    Not assigned enough memory to perform VACUUM
    efficiently
    ● Autovacuumが途中でDDLなどに
    より中断されてしまい終わらない
    Things like DDL could interrupt autovacuums and
    they won’t finish
    ● VACUUM待ちテーブルが発生し、
    肥大化の原因となる
    There will be backlogs of autovacuums, causing
    table bloat
    ● 重要なVACUUMが実行されずアプ
    リダウンの危険がある
    Important VACUUMs (like anti-wraparound) won’t get
    executed and could cause the application down
    Case 2: Performance - パフォーマンス
    Causes
    原因 Consequences
    結果

    View full-size slide

  65. Case 2: Performance - パフォーマンス
    ● cost_limitとcost_delayの設定によって同じテーブ
    ルを同じだけ掃除するにもかかる時間が変わる
    Depending on the cost_limit and cost_delay settings, the time spend for cleaning
    up the exact same table will vary
    ● 言い換えると、cost_limitとcost_delayは
    autovacuumが使用できるI/Oを制限している
    In other words, the cost_limit and cost_delay settings are limiting how much I/O
    autovacuum can spend
    ● Example:cost_delay=250 → total 800 costs / sec
    250ms 250ms 250ms 250ms
    1 second
    200 200 200 200

    View full-size slide

  66. Case 2: Performance - パフォーマンス
    ディスク性能の進化に伴い、デフォルト値も変わってきた
    As disk performance increases over time, the default value of these parameters changed
    cost_limit cost_delay
    page_hit
    page_miss
    page_dirty
    Max I/O read (hit)
    Max I/O read (miss)
    Max I/O write
    Postgres 11
    (2018)
    200 20
    1
    10
    20
    80MB/s
    8MB/s
    4MB/s
    Postgres 12
    (2019)
    200 2
    1
    10
    20
    800MB/s
    80MB/s
    40MB/s
    Postgres 14
    (2021)
    200 2
    1
    2
    20
    800MB/s
    400MB/s
    40MB/s
    costs/s: 10k
    costs/s: 100k
    costs/s: 100k
    1 page: 8KB

    View full-size slide

  67. Case 2: Performance - パフォーマンス
    ● ディスク性能が悪いとVACUUMに時間がかかるだけでな
    く、最悪I/OのボトルネックでVACUUMがクエリパ
    フォーマンスに影響することも
    When the disk performance is not good, it’s not only affecting the time spend for
    VACUUMs, but also VACUUMs could affect the ongoing other queries in the worst case,
    due to the I/O being a bottleneck
    Example: AWS RDS gp2 storage

    View full-size slide

  68. Solution
    解決
    パラメータが現在のディスクに見合ったものかを見直す
    Revisit cost related parameters to see if it matches to the current disk spec
    ● 特にPostgres 11以前は要注意
    Highly recommended to adjust for Postgres 11 and older
    ● 古いバージョンの時にパラメータを既にカスタマイズしていた場合、アップグレード後
    にカスタマイズした値がデフォルト値より小さくなってしまっている場合も
    If cost related parameters were previously customized, it’s possible that these values are actually lower than the default
    values after the upgrade
    ● cost_limitは上げれば上げるほど、cost_delayは下げれば下げるほどautovacuumの使
    用しうるI/Oは大きくなる
    Both bumping cost_limit and lowering cost_delay will result the I/O usage increase by autovacuums
    Case 2: Performance - パフォーマンス

    View full-size slide

  69. ● 適切にI/Oが割り当てられていな
    くI/Oがボトルネックになり時間
    がかかる
    I/O is not allocated well and being a bottleneck
    ● 適切にメモリが割り当てられてい
    なくVACUUMが非効率になってい
    て時間がかかる
    Not assigned enough memory to perform VACUUM
    efficiently
    ● Autovacuumが途中でDDLなどに
    より中断されてしまい終わらない
    Things like DDL could interrupt autovacuums and
    they won’t finish
    ● VACUUM待ちテーブルが発生し、
    肥大化の原因となる
    There will be backlogs of autovacuums, causing
    table bloat
    ● 重要なVACUUMが実行されずアプ
    リダウンの危険がある
    Important VACUUMs (like anti-wraparound) won’t get
    executed and could cause the application down
    Case 2: Performance - パフォーマンス
    Causes
    原因 Consequences
    結果

    View full-size slide

  70. Review: VACUUM Phases - おさらい
    ● VACUUM TODOリストの大きさは決まっている
    The size of VACUUM TODO list is limited
    ○ デフォルトではautovacuum_work_mem
    (maintenance_work_mem)の64MBで、最大は1GB
    The default size is 64MB from autovacuum_work_mem
    (maintenance_work_mem), then it can be the maximum 1GB
    ○ もし最初のscanning heapフェーズでヒープページ
    を全部走査する前に埋まってしまった場合は、全部
    走査ができるまで三つのステップを繰り返す
    If it’s filled during the first scanning heap phase before scanning all
    heap pages, these 3 phases will be repeated until all heap pages are
    scanned

    View full-size slide

  71. ● もしメモリが不十分だと…
    If there is not enough memory…
    ○ scanning heapとvacuuming heapはTODO
    リストに載る・載っている分だけ走査すれ
    ばいいが、vacuuming indexesフェーズは
    繰り返しの度に全部を走査しなければなら
    ない
    The scanning heap and vacuuming heap phases only need
    to go through the parts that can be fit in (or already on) the
    TODO list, however, the vacuuming indexes phase will need
    to go through all pages of all indexes for each iteration
    Case 2: Performance - パフォーマンス

    View full-size slide

  72. Solution
    解決
    autovacuum_work_memもしくはdead rows関連の他の閾値を見直す
    Revisit the autovacuum_work_mem setting or other thresholds related to dead rows
    ● デフォルトでは64MBなのでだいたい1000万のdead rowsまでサポート
    With default value 64MB, it supports holding ~10 million dead rows
    ● 大抵のテーブルはdead rowsが1000万たまる前にautovacuumが走る
    Usually, autovacuums will be triggered before dead rows reaching to 10M
    ● 常に1000万以上のdead rowsがたまっているテーブルなどは、閾値の調整などで
    autovacuumの頻度を上げることも大事
    For tables that always have more than 10M dead rows, it is also important to revisit thresholds to ensure that
    autovacuums are running frequently
    Case 2: Performance - パフォーマンス

    View full-size slide

  73. ● DDLのせいでautovacuumが開始
    できない・中断される
    Autovacuum can’t be started due to DDL, or it will be
    interrupted
    ● Anti-wraparound autovacuumが
    DDLをブロックしてしまう
    Anti-wraparound autovacuums block DDL
    ● Autovacuumが適切なタイミング
    で走らず肥大化の原因となる
    Autovacuum is not running as needed and causes
    bloat
    ● Autovacuum待ちクエリでアプリ
    がダウンする
    Application can go down due to queries indirectly
    waiting for autovacuum
    Case 3: Locks - ロック
    Causes
    原因 Consequences
    結果
    Learn More: 3-2

    View full-size slide

  74. Characteristic of autovacuum - 特性
    ● 必要なロック(SHARE UPDATE EXCLUSIVE)を取得できな
    い場合走るのを諦める
    When unable to acquire the SHARE UPDATE EXCLUSIVE lock, it gives up running
    ● もし他のプロセスがSHARE UPDATE EXCLUSIVEと競合
    するロックを取得しようとしたら走っている
    autovacuumを中断する
    If a process attempts to acquire a lock that conflicts with the SHARE UPDATE
    EXCLUSIVE lock, autovacuum will be intervened
    ● ただしanti-wraparound (triggered by freeze age)の場合
    は中断せず競合するプロセスをブロックする
    However, if autovacuum is triggered to prevent wraparound (aka triggered by freeze
    age), it won’t be interrupted

    View full-size slide

  75. ● SHARE UPDATE EXCLUSIVEロックが競合するロック及
    びクエリ例
    Locks and example queries that conflict with the SHARE UPDATE EXCLUSIVE lock
    ○ SHARE UPDATE EXCLUSIVE
    ■ VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY,
    CREATE STATISTICS, COMMENT ON, REINDEX
    CONCURRENTLY
    ○ SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE
    ■ CREATE INDEX, CREATE TRIGGER, REFRESH
    MATERIALIZED VIEW CONCURRENTLY
    ○ ACCESS EXCLUSIVE
    ■ DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM
    FULL, ALTER TABLE, ALTER INDEX
    Characteristic of autovacuum - 特性

    View full-size slide

  76. Characteristic of autovacuum - 特性
    走るのを諦める
    Give up to run
    中断する
    Cancel
    他をブロックする
    Block others
    VACUUM
    table1!
    Can’t VACUUM…
    ALTER TABLE table1
    Oops, needs
    to cancel
    anti-wraparound!
    I need to wait for
    ALTER TABLE
    ALTER TABLE table1

    View full-size slide

  77. ● DDLのせいでautovacuumが開始
    できない・中断される
    Autovacuum can’t be started due to DDL, or it will be
    interrupted
    ● Anti-wraparound autovacuumが
    DDLをブロックしてしまう
    Anti-wraparound autovacuums block DDL
    ● Autovacuumが適切なタイミング
    で走らず肥大化の原因となる
    Autovacuum is not running as needed and causes
    bloat
    ● Autovacuum待ちクエリでアプリ
    がダウンする
    Application can go down due to queries indirectly
    waiting for autovacuum
    Case 3: Locks - ロック
    Causes
    原因 Consequences
    結果
    Learn More: 3-2

    View full-size slide

  78. Characteristic of autovacuum - 特性
    走るのを諦める
    Give up to run
    中断する
    Cancel
    他をブロックする
    Block others
    VACUUM
    table1!
    Can’t VACUUM…
    ALTER TABLE table1
    Oops, needs
    to cancel
    anti-wraparound!
    I need to wait for
    ALTER TABLE
    ALTER TABLE table1

    View full-size slide

  79. Case 3: Locks - ロック
    ● Autovacuumがしっかり走っているかを確認する
    Check if autovacuum is running
    ● Autovacuumが頻繁に中断していないか確認する
    Check if autovacuums are not cancelled frequently
    ● Autovacuumのログを有効にして状態を確認
    Check status by enabling autovacuum logs
    ○ log_autovacuum_min_duration
    ○ デフォルトは10分だが、ログボリューム的に制限さえな
    ければすべてをログする0を推奨
    Default logs autovacuums taking more than 10 minutes. Unless there is any
    restriction on the log volume, it is recommended to set this to 0 to log all
    autovacuums

    View full-size slide

  80. Solution
    解決
    ● 頻繁にautovacuumが中断されている場合は、中断の原因となっているクエリを見直す
    If autovacuums are cancelled often, find queries that are causing the cancellation and review them
    ● Autovacuumは中断されてもまた走ってくれるので、たまの中断は無視して大丈夫
    Cancelled autovacuums will re-triggered by a launcher, so you can safely ignore non-frequent cancellations
    ● 中断の原因が解消されないと中断され続ける、もしくは中断後に走れない
    Unless the root cause of the cancellation is resolved, it’ll keep being cancelled, or unable to run
    ● Autovacuumがそもそも走っていない場合は、何か特別ブロックするようなクエリがない
    かを確認する
    If autovacuums are not running at all, try to find a query that is specifically blocking them
    ● 長らく走っていない、または頻繁に中断されるテーブルを発見したら一度手動で
    VACUUMするのも手
    For a table that hasn’t been autovacuum-ed for a while or constantly cancelled, it would be good to run VACUUM manually
    Case 3: Locks - ロック

    View full-size slide

  81. ● DDLのせいでautovacuumが開始
    できない・中断される
    Autovacuum can’t be started due to DDL, or it will be
    interrupted
    ● Anti-wraparound autovacuumが
    DDLをブロックしてしまう
    Anti-wraparound autovacuums block DDL
    ● Autovacuumが適切なタイミング
    で走らず肥大化の原因となる
    Autovacuum is not running as needed and causes
    bloat
    ● Autovacuum待ちクエリでアプリ
    がダウンする
    Application can go down due to queries indirectly
    waiting for autovacuum
    Case 3: Locks - ロック
    Causes
    原因 Consequences
    結果
    Learn More: 3-2

    View full-size slide

  82. Characteristic of autovacuum - 特性
    走るのを諦める
    Give up to run
    中断する
    Cancel
    他をブロックする
    Block others
    VACUUM
    table1!
    Can’t VACUUM…
    ALTER TABLE table1
    Oops, needs
    to cancel
    anti-wraparound!
    I need to wait for
    ALTER TABLE
    ALTER TABLE table1

    View full-size slide

  83. Case 3: Locks - ロック
    ● Anti-wraparound autovacuumはロックが競合するクエ
    リが発生しても中断しない
    Anti-wraparound autovacuums won’t be cancelled due to lock conflicts
    ● DDLがブロックされてしまう可能性がある
    DDL will conflict with the lock of autovacuum, therefore DDL can be blocked
    ● さらにDDLが他の簡単なクエリをブロックしてしまいア
    プリダウンの危険性
    That DDL can block other trivial queries, resulting application down
    Learn More: 3-2

    View full-size slide

  84. Case 3: Locks - ロック
    Anti-wraparound VACUUM
    ALTER TABLE
    UPDATE SELECT
    Waiting for
    Anti-wraparound VACUUM
    Waiting for ALTER TABLE

    View full-size slide

  85. Case 3: Locks - ロック
    ACCESS SHARE
    ROW EXCLUSIVE
    SHARE UPDATE EXCLUSIVE
    ACCESS EXCLUSIVE
    Anti-wraparound VACUUM
    ALTER TABLE
    UPDATE UPDATE UPDATE
    SELECT SELECT SELECT
    UPDATE
    SELECT

    View full-size slide

  86. Solution
    解決
    ● Anti-wraparound autovacuumに時間がかからないようにする
    Make sure that anti-wraparound autovacuums won’t take time to run
    ● 閾値が高い = あまり走らない = ブロックする確率が低い
    Higher threshold = not running frequently = lower chance of blocking DDL
    ● でも…閾値が高い = 一つ一つが長い = ブロックしたときブロックする時間も長い
    Higher threshold = each anti-wraparound autovacuums takes more time to run = it’ll block longer time
    ● 50GBのテーブルを走査するにはPostgres11のデフォルト設定だと最低でも10分はかかる
    With the default settings of Postgres 11 (80MB/s), it’ll take at least 10 minutes to scan the 50GB table
    ● 50GBのテーブルを走査するにはPostgres14のデフォルト設定だと最低でも1分はかかる
    With the default settings of Postgres 14 (800MB/s), it’ll take at least 1 minutes to scan the 50GB table
    Case 3: Locks - ロック

    View full-size slide

  87. Solution
    解決
    ● もし起こってしまったら、速やかにDDLまたはanti-wraparound autovacuumを中断する
    If anti-wraparound autovacuums are blocking DDL, cancel either anti-wraparound autovacuums or DDL immediately
    ● 中断後に手動で走らせて凍結を終わらせてしまうのも手
    After cancelled, you could run VACUUM FREEZE manually to freeze this table
    ● その場合は、なるべく早く終わるようにINDEX_CLEANUP offを設定するとよい
    In that case, passing INDEX_CLEANUP off is recommended to complete VACUUM asap
    ○ 余裕のある時にINDEX_CLEANUP onで再度手動で走らせよう
    Make sure to run VACUUM manually with INDEX_CLEANUP on afterwards
    ● DDLが長くanti-wraparound autovacuumを待つ状況にならないようにする
    Make sure that DDL won’t wait for anti-wraparound autovacuums (or any locks) forever
    Case 3: Locks - ロック
    Learn More: 1-2

    View full-size slide

  88. Monitoring is important
    ● autovacuumのデフォルトパラメータは沢山の用途をサ
    ポートできるように設定されているが、パーフェクト
    ではないし、保守的であることも多い
    Autovacuum default parameters are set to support majority of use cases but will never
    be perfect for your database, or can often be conservative
    ● モニタリングを通じた個々のデータベースへのチュー
    ニングが大切
    It is important to adjust them through the monitoring
    ○ マネージドサービスでは独自にパラメータを上書きして
    いるものもある
    Some managed services are overwriting the default parameters
    ■ RDS: autovacuum_vacuum_scale_factor 0.2→0.1

    View full-size slide

  89. Monitoring is important
    ● モニタリングサービスを使うのも一つの手
    Using a monitoring service is a good way to monitor autovacuums

    View full-size slide

  90. Tips
    今日から使えるautovacuum
    まわりの小技を学ぶ
    Learn tips related autovacuum that you can use from
    today
    04

    View full-size slide

  91. Reduce VACUUM by Partitioning
    ● 一番いいのはVACUUMの必要がなくなること
    “No need for VACUUM” is the best
    ● 例:夜間バッチで保持期間を超えた分を削除している
    ようなテーブル
    Example: a table which has a nightly deletion for the data exceeding the retention
    ○ 削除が走った後にVACUUMが走らないとゴミが溜
    まる原因に
    If no VACUUM happens after the deletion, the garbage can accumulate
    ○ 日付ごとのパーティショニングによりテーブルご
    とDROPする
    By partition per day, the table can be simply dropped instead of a mass
    deletion

    View full-size slide

  92. Reduce VACUUM by less UPDATEs
    ● 不必要なUPDATEをなくすことはそれ自体がアプリパ
    フォーマンスによいだけでなく、VACUUMも減らせる
    Reducing unnecessary UPDATEs not only helps to improve application performance,
    but also helps reducing the number of VACUUMS (by dead rows)
    ● 例:issue_referencesテーブルは10秒ごとに「経過時
    間」を設定するためのUPDATEがあったが、これを見直
    して起点時間を設定することにより更新頻度が大幅に
    下がった
    Example: the issue_references table had an UPDATE query that updates the “spend
    time” every 10 seconds, but after changing this to the “start time”, the UPDATE
    frequency dramatically decreased
    ○ VACUUMも週10回から週7回へ
    Weekly VACUUM frequency reduced from 10 to 7

    View full-size slide

  93. Upgrading
    ● VACUUMのエリアはリリースごとに常にパフォーマンス
    向上がなされている
    The improvement of VACUUM is happening in each releases
    ○ Postgres 16: Improve performance of vacuum freezing
    ○ Postgres 14: VACUUM automatically becomes more
    aggressive, and skips inessential cleanup, if the
    database starts to approach a transaction ID
    wraparound condition
    ● パラメータのデフォルト値も見直され続けている
    The default parameters for the VACUUM/autovacuums are continuously revisited too

    View full-size slide

  94. Bloat Check
    ● pgstattupleモジュールを使って調べる
    Find tables that should be triggering autovacuums (but haven’t)
    ○ タプルレベルの統計情報 (tuple-level statistics)
    ○ https://www.postgresql.jp/document/15/html/pg
    stattuple.html
    ● PostgreSQL Wikiに載っているSQLを使って調べる
    Use the SQL on the PostgreSQL Wiki
    ● https://wiki.postgresql.org/wiki/Show_database
    _bloat

    View full-size slide

  95. Xmin Horizon Check
    ● Xmin Horizonが現在どれだけかをチェックして、
    VACUUMをブロックしていないかを見る
    Check the current xmin horizon and determinate if it’s blocking VACUUMs
    ● https://gist.github.com/keiko713/e2cb98a6834c8610
    d49a54bc463ec29f
    backend | replication_slot_xmin | replication_slot_catalog_xmin | prepare_xact | standby
    -----------+-----------------------+-------------------------------+--------------+---------
    178944984 | 0 | 0 | 0 | 0
    (1 row)

    View full-size slide

  96. Xmin Horizon Check

    View full-size slide

  97. Freeze Age Check
    ● 各テーブルをFreeze Ageが大きい順に並べる
    List up freeze age of each table
    ● もしageがautovacuum_freeze_max_ageを超えている
    ものがあれば要注意
    You want to look into more if there is any table that is exceeding
    autovacuum_freeze_max_age
    SELECT relname, age(relfrozenxid) FROM pg_class
    WHERE relkind = 'r' ORDER BY age(relfrozenxid) DESC;
    SELECT relname, age(relminmxid) FROM pg_class
    WHERE relkind = 'r' ORDER BY age(relminmxid) DESC;

    View full-size slide

  98. Freeze Age Check

    View full-size slide

  99. CREDITS: This presentation template was created
    by Slidesgo, including icons by Flaticon and
    infographics & images by Freepik
    Thanks
    Any questions?
    Social: @keiko713
    VACUUM Simulator:
    https://vacuum-simulator.netlify.app/
    pganalyze: https://pganalyze.com/

    View full-size slide

  100. Learn More 1
    1-1. [EN] pganalyze VACUUM Advisor by pganalyze
    What is Bloat?やWhat is Freezing?のセクションはこれらを学び直すのにおすすめ。またどんなところを注意して見る
    べきかのヒントになる
    1-2. [JP] 最速でVacuumを完了させる方法とその副作用 by Masahiko Sawada
    これに限らず澤田さんのVACUUMに関するポストは、VACUUMのコードにまさにコントリビュートしている方だから
    こそのクオリティ
    1-3. [EN] How to simulate the deadly, infamous, misunderstood & complex ‘Transaction Wraparound
    Problem’ in PostgreSQL … by FatDBA
    実際にwraparoundさせかけた人のブログ。9時間かけてTXIDを使い切った模様
    1-4. [EN] Transaction ID wraparound: a walk on the wild side by Laurenz Albe (CYBERTEC)
    実際にwraparoundさせた人のブログ。single-userモードを使ってまでデータ破壊を再現したのは圧巻。これ以外にも
    LaurenzさんのブログやStack Overflowのポストにはよく助けられる

    View full-size slide

  101. Learn More 2
    2-1. [EN] Cloud SQL for PostgreSQL - A deep dive into VACUUM FAQs by Virender Singla (Google
    Cloud)
    VACUUMに関するさまざまなことがQ&A形式で書かれており、テンポよく読みやすい
    2-2. [EN] Debugging Postgres autovacuum problems: 13 tips by Samay Sharma (Microsoft)
    autovacuumに関する問題をカテゴライズして見やすく書かれている
    2-3. [EN] PostgreSQL/Wraparound and Freeze by Wikibooks
    Freezeって結局どういった条件でどうやって走ってるの、という疑問が細かく解消される。このページ以外にも
    WikibooksのPostgreSQLのセクションはよくまとめられている
    2-4. [JA] 同時実行制御 by PostgreSQLドキュメント
    MVCCの概要とPostgresの同時実行制御を完結に表したもの。MVCCについて詳しくない場合はまずここを読んで、
    分からない部分や具体的に知りたい部分を掘り下げるのもよい
    2-5. [JA] MVCCの素顔 by Koichi Suzuki (EDB)
    Bruce Momjian氏のMVCC unmaskedを日本語化したスライド。MVCCについてとことん理解したいときにとても役立
    つ。EDBのウェビナーのページではこのスライドのウェビナーも見ることができる

    View full-size slide

  102. Learn More 3
    3-1. [EN] Postgres VACUUM and Xmin Horizon by Keiko Oda
    手前味噌ですがxmin horizonにフォーカスを当てて掘り下げて書いてあります
    3-2. [EN] Understanding an outage: concurrency control & vacuuming in PostgreSQL by Walter
    Carvalho, João Bernardo (Duffel)
    実際にanti-wraparound autovacuumが原因で起こった障害について書かれてある。ストーリーテリング形式で読みや
    すい

    View full-size slide

  103. 4-1. [EN] Understanding & Managing Postgres Table Bloat | Citus Con: An Event for Postgres 2023 by
    Chelsea Dole (Brex)
    VACUUMでも特に肥大化 (bloat) について学ぶのによい
    4-2. [EN] Bloat in PostgreSQL: a taxonomy by Peter Geoghegan
    VACUUMについてとても深入りしている。また、opportunistic cleanup/pruning(VACUUM以外でのdead tuplesの掃除
    法)についてもカバーされている
    4-3. [JA] Vacuum徹底解説 by Masahiko Sawada
    PostgreSQL Conference Japan 2021でのチュートリアルセッション。VACUUMについて知っておくべきことがよくま
    とめられており、私のトークを聞かなくてもこれを見れば十分
    Learn More 4

    View full-size slide

  104. Appendix
    おまけ
    APPENDIX

    View full-size slide

  105. ● VACUUM and Visibility Map
    ● VACUUM Phases - with logs
    Appendix Table of Contents
    APPENDIX

    View full-size slide

  106. VACUUM and Visibility Map
    ● Visibility Mapの更新:VACUUMの役割うちの一つ
    Updating the visibility map: One of VACUUM’s roles
    ● Visibility Mapが更新されるとうれしいこと
    Benefit of updating the visibility map
    ○ Index-only scanが効率的に使えるようになる
    Improve performance of index-only scans
    ○ 後続VACUUMの処理時間の短縮
    Improve performance for any subsequent VACUUMs
    APPENDIX

    View full-size slide

  107. Visibility Map - 可視性マップ
    ● ヒープページごとに次のどれかの情報を持つ:
    ”all-visible”, ”all-frozen”, ”unknown”
    Each heap page contains one of the followings: “all-visible”, “all-frozen”, or “unknown”
    ● VACUUMによってのみall-visibleまたはall-frozenの情報
    がセットされる
    Only VACUUM can set all-visible or all-frozen information to the visibility map
    ● ヒープページが挿入・削除など編集されると情報が”未
    知”にリセットされる
    Any data-modifying operations on a heap page will reset this to “unknown”
    APPENDIX

    View full-size slide

  108. VACUUM and Visibility Map
    ①テーブルに行を挿入
    Rows are inserted to the table
    テーブル
    Table
    ヒープページ
    Heap page

    Row
    APPENDIX

    View full-size slide

  109. VACUUM and Visibility Map
    ②いくつかの行を削除
    Some rows are deleted
    赤い部分が削除された行
    Deleted rows are marked as red
    APPENDIX

    View full-size slide

  110. ③VACUUMの実行(一回目)
    Run VACUUM (1st time)
    VACUUM and Visibility Map
    Dead rowsが空スペースに
    Dead rows become empty
    spaces
    各heap pageにall-visibleと
    all-frozenがセットされる
    All-visible and all-frozen are set to
    each page
    ヒープによってはall-visibleまたは
    all-frozenがセットされない場合もある
    Some heaps may not be flagged as all-visible
    or all-frozen
    APPENDIX

    View full-size slide

  111. VACUUM and Visibility Map
    ④行を削除したり挿入したり
    Deleting and inserting some rows
    行を削除
    Deleting a row
    編集が行われたヒープページは
    Visibility Mapがリセットされる
    Visibility Maps of the modified heap
    pages are reset
    行を新しく挿入
    Inserting new rows
    APPENDIX

    View full-size slide

  112. VACUUM and Visibility Map
    ⑤VACUUMの実行(二回目)
    Run VACUUM (2nd time)
    VACUUMをスキップ
    できる!
    VACUUM can be skipped with
    these pages!
    APPENDIX

    View full-size slide

  113. VACUUM and Visibility Map
    ● All-visibleまたはall-frozenなヒープページはscanning
    heapフェーズでスキップされる
    All-visible or all-frozen heap pages will be skipped during the scanning heap phase
    ○ Aggressive VACUUM(freezeに重きを置いたVACUUM)では
    all-frozenのみスキップできる
    All-frozen pages can be only skipped with an aggressive VACUUM
    (freeze-focused VACUUM)
    ● このようなヒープページがたくさんあればあるほど後
    続のVACUUMでスキップでき処理時間が短縮できる
    Subsequent VACUUMs can benefit more and more performance improvements when
    there are many skippable heap pages in the table
    ○ Inserts-onlyのテーブルでは効果大
    Especially works really well with inserts-only tables
    APPENDIX

    View full-size slide

  114. Inserts-only table and Visibility Map
    ①VACUUMの実行(一回目)
    Run VACUUM (1st time)
    APPENDIX
    Note: technically with inserts-only table, Postgres
    actually won’t freeze (and mark all-frozen) like in
    the diagram, unless it is an aggressive vacuum

    View full-size slide

  115. Inserts-only table and Visibility Map
    ②VACUUMの実行(二回目)
    Run VACUUM (2nd time)
    APPENDIX
    前にVACUUMしたヒープページ
    は全部スキップできる!
    All heap pages that are VACUUMed
    previously can be skipped!
    挿入のみのテーブルなのでヒープ
    ページが編集されずVisibility Map
    が維持される
    Heap pages won’t be modified because
    the table is inserts-only, therefore the
    visibility map will remain set

    View full-size slide

  116. Benefit of autovacuum triggered by inserts
    ● Postgres 13から、最後のVACUUMからどれだけの行が
    挿入されたかによってautovacuumが走るようになった
    Starting from Postgres 13, autovacuums can be triggered by how many rows inserted
    since the last VACUUM
    ● 従来、挿入のみのテーブルではVACUUMがdead rowsに
    よってはトリガーされず、freeze ageでのみトリガーさ
    れていた
    Before that, autovacuums of inserts-only tables were only triggered by freeze age, as
    there are no dead rows and dead rows weren’t triggering autovacuums
    ● autovacuumの間隔が長くなり、一度のautovacuumで
    処理すべき部分が大きくなっていた
    autovacuums weren’t running frequently with these tables and each autovacuum
    needed to go through a lot of data
    APPENDIX

    View full-size slide

  117. Benefit of autovacuum triggered by inserts
    ● 結果、全体的なパフォーマンスの低下が見られた
    Resulted performance degradation with these tables or with autovacuum processes
    ● Visibility Mapがいつまでも更新されず、効果的な
    index-only scanが使用できない
    The visibility maps are not updated with these tables and they aren’t able to
    take advantage of index-only scans fully
    ● autovacuum一つ一つの処理が重くなりリソース
    がとられる
    Each autovacuum is taking potentially lots of resources
    ● 大量insert後シンプルなSELECT文に時間がかかる
    Simple SELECT statement takes time to run after massive inserts
    ● This takes time because there will be writes to update hint bits after
    inserts. Explained well in “At first, things look pretty normal” part in
    CYBERTEC’s blog post
    ● An aws talk explaining how performance degrade when you don’t
    VACUUM with inserts/read-only tables
    APPENDIX

    View full-size slide

  118. automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1
    pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total)
    tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable
    removable cutoff: 568440102, which was 2475189 XIDs old when operation ended
    new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value
    index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed
    index "index_queries_on_database_id_and_table_names":
    pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable
    index "queries_pkey":
    pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable
    index "index_queries_on_database_id":
    pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable
    index "index_queries_on_last_occurred_at_and_database_id":
    pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable
    I/O timings: read: 224003.288 ms, write: 34770.930 ms
    avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s
    buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied
    WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes
    system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s
    VACUUM Phases - with logs
    Postgres version: 15.3
    APPENDIX

    View full-size slide

  119. automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1
    pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total)
    tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable
    removable cutoff: 568440102, which was 2475189 XIDs old when operation ended
    new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value
    index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed
    index "index_queries_on_database_id_and_table_names":
    pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable
    index "queries_pkey":
    pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable
    index "index_queries_on_database_id":
    pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable
    index "index_queries_on_last_occurred_at_and_database_id":
    pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable
    I/O timings: read: 224003.288 ms, write: 34770.930 ms
    avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s
    buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied
    WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes
    system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s
    VACUUM Phases - with logs
    Aggressive mode
    (凍結するぞモード)
    How many times below is performed
    全体の状況
    Overview
    APPENDIX

    View full-size slide

  120. automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1
    pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total)
    tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable
    removable cutoff: 568440102, which was 2475189 XIDs old when operation ended
    new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value
    index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed
    index "index_queries_on_database_id_and_table_names":
    pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable
    index "queries_pkey":
    pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable
    index "index_queries_on_database_id":
    pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable
    index "index_queries_on_last_occurred_at_and_database_id":
    pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable
    I/O timings: read: 224003.288 ms, write: 34770.930 ms
    avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s
    buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied
    WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes
    system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s
    VACUUM Phases - with logs
    ヒープページの削除数・残数・スキャン数
    How many pages are removed/remain/scanned
    ヒープページ
    Heap page
    行・タプル
    Row/Tuple
    ヒープページ
    Heap pages
    APPENDIX

    View full-size slide

  121. automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1
    pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total)
    tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable
    removable cutoff: 568440102, which was 2475189 XIDs old when operation ended
    new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value
    index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed
    index "index_queries_on_database_id_and_table_names":
    pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable
    index "queries_pkey":
    pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable
    index "index_queries_on_database_id":
    pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable
    index "index_queries_on_last_occurred_at_and_database_id":
    pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable
    I/O timings: read: 224003.288 ms, write: 34770.930 ms
    avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s
    buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied
    WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes
    system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s
    VACUUM Phases - with logs
    タプルの削除数・残数
    How many tuples are removed/remain
    ヒープページ
    Heap page
    行・タプル
    Row/Tuple
    タプル
    Tuples
    APPENDIX

    View full-size slide

  122. automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1
    pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total)
    tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable
    removable cutoff: 568440102, which was 2475189 XIDs old when operation ended
    new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value
    index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed
    index "index_queries_on_database_id_and_table_names":
    pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable
    index "queries_pkey":
    pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable
    index "index_queries_on_database_id":
    pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable
    index "index_queries_on_last_occurred_at_and_database_id":
    pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable
    I/O timings: read: 224003.288 ms, write: 34770.930 ms
    avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s
    buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied
    WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes
    system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s
    VACUUM Phases - with logs
    255,707個のゴミタプルが削除できなかった
    255,707 dead tuples were not removable
    トランザクションIDが568440102より新しく
    作られたゴミタプルは削除できなかった
    Any dead tuples created after XID 568440102 were not
    removable
    VACUUM終了時にトランザクションID 568440102
    の齢は2,475,189であった
    When VACUUM was done, the age of transaction ID
    568440102 was 2,475,189
    ゴミタプル清掃状況
    Dead rows cleaning up status
    Learn More in xmin horizon
    APPENDIX

    View full-size slide

  123. automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1
    pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total)
    tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable
    removable cutoff: 568440102, which was 2475189 XIDs old when operation ended
    new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value
    index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed
    index "index_queries_on_database_id_and_table_names":
    pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable
    index "queries_pkey":
    pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable
    index "index_queries_on_database_id":
    pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable
    index "index_queries_on_last_occurred_at_and_database_id":
    pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable
    I/O timings: read: 224003.288 ms, write: 34770.930 ms
    avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s
    buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied
    WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes
    system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s
    VACUUM Phases - with logs
    凍結の結果、relfrozenxid(凍結されていない最古のトランザク
    ションID)が260,122,451進んで518,440,452となった
    With freezing, relfrozenxid (the oldest unfrozen XID) advanced by 260,122,451, became
    518,440,452
    260,122,451
    258,318,001
    518,440,452
    凍結状況
    Freezing status
    APPENDIX

    View full-size slide

  124. automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1
    pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total)
    tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable
    removable cutoff: 568440102, which was 2475189 XIDs old when operation ended
    new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value
    index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed
    index "index_queries_on_database_id_and_table_names":
    pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable
    index "queries_pkey":
    pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable
    index "index_queries_on_database_id":
    pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable
    index "index_queries_on_last_occurred_at_and_database_id":
    pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable
    I/O timings: read: 224003.288 ms, write: 34770.930 ms
    avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s
    buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied
    WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes
    system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s
    VACUUM Phases - with logs
    5,258,716ページ(全体の46.53%)から合計12,925,696個
    のゴミタプルがVACUUM TODOリストに追加された
    Total 12,925,696 dead rows were added to the VACUUM TODO list, from
    5,258,716 pages (46.53% of total)
    Index scan(vacuuming indexesフェーズ)の要否
    Needs vacuuming indexes phase or not
    0%: not needed(不要), 0-2%: bypassed(スキップ), 2%+: needed(必要)
    Learn More: 4-2
    インデックス全体
    Indexes Overview
    APPENDIX

    View full-size slide

  125. automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1
    pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total)
    tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable
    removable cutoff: 568440102, which was 2475189 XIDs old when operation ended
    new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value
    index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed
    index "index_queries_on_database_id_and_table_names":
    pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable
    index "queries_pkey":
    pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable
    index "index_queries_on_database_id":
    pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable
    index "index_queries_on_last_occurred_at_and_database_id":
    pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable
    I/O timings: read: 224003.288 ms, write: 34770.930 ms
    avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s
    buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied
    WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes
    system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s
    VACUUM Phases - with logs
    4つのインデックスそれぞれについての
    vacuuming indexesフェーズのログ
    Vacuuming indexes phase logs for each 4 indexes
    各インデックス
    Each index
    APPENDIX

    View full-size slide

  126. automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1
    pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total)
    tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable
    removable cutoff: 568440102, which was 2475189 XIDs old when operation ended
    new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value
    index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed
    index "index_queries_on_database_id_and_table_names":
    pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable
    index "queries_pkey":
    pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable
    index "index_queries_on_database_id":
    pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable
    index "index_queries_on_last_occurred_at_and_database_id":
    pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable
    I/O timings: read: 224003.288 ms, write: 34770.930 ms
    avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s
    buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied
    WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes
    system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s
    VACUUM Phases - with logs
    ● Newly deleted
    ○ このVACUUMで削除されたインデックスページ
    Deleted during this VACUUM
    ● Currently deleted
    ○ 現在削除されているインデックスページ(newly deletedも含む)
    Currently deleted index pages (including ones deleted during this VACUUM)
    ● Reusable
    ○ 再利用可能なインデックスページ(currently deletedで再利用可能なものも含む)
    Reusable index pages (including reusable ones within currently deleted index pages)
    各インデックス
    Each index
    APPENDIX

    View full-size slide

  127. automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1
    pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total)
    tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable
    removable cutoff: 568440102, which was 2475189 XIDs old when operation ended
    new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value
    index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed
    index "index_queries_on_database_id_and_table_names":
    pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable
    index "queries_pkey":
    pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable
    index "index_queries_on_database_id":
    pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable
    index "index_queries_on_last_occurred_at_and_database_id":
    pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable
    I/O timings: read: 224003.288 ms, write: 34770.930 ms
    avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s
    buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied
    WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes
    system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s
    VACUUM Phases - with logs
    パフォーマンス・コスト関連のログ
    Logs related to performance/cost
    パフォーマンス
    Performance
    APPENDIX

    View full-size slide