BRIN(Block Range INdex)

This presentation will provide an overview of the index BRIN provided by PotgreSQL, how it works, and how to tune it.
We hope that you will consider using BRIN indexes as an option when designing your database.

Transcript

1. せっかくなので BRIN を使ってみよう！ PostgreSQL Conferecnce 2023 (2023-11-24)
2. 自己紹介 • ぬこ＠横浜 , @nuko_yokohama • にゃーん • 趣味でポスグレをやってる者だ • PostgreSQL アンカンファレンスゆるふわ枠
3. 内容 • 概要 • 使用例 • アンチパターン • BRIN vs B-Tree • BRIN のしくみ • チューニング • メンテナンス • まとめ PostgreSQL の インデックス設計時に こんな選択肢もあることを 理解してもらえるのがゴール
4. 概要
5. PostgreSQL でサポートされるインデックス • PostgreSQL 標準配布物に含まれるインデックス バージョン 内容 B-Tree PostgreSQL のデフォルトインデックス種別。 Hash 列値を32bitハッシュ化。＝比較のみ対応。 GiST/SP-GiST 特殊な用途のインデックスの基盤 GIN 汎用転置インデックス。配列、JSON 、全文検索等。 BRIN Block Range Index 。本資料で説明。 contrib/bloom ブルームフィルタインデックス。＝比較のみ対応。
6. 概要 • BRIN=Block Range INdex • 値が存在するテーブルのブロック番号の範囲をもつ • BRIN の特徴 – インデックスサイズ：極小 – Colleration に依存 – ほどほどに高速な検索
7. History of BRIN • BRIN の歴史 バージョン 内容 9.5 • BRIN が追加されました。 10 • BRIN の要約をより積極的に行うオプションを追加しました。 • BRIN スキャンが有益かどうかの判断の精度を改善しました。 14 • BRIN が範囲ごとに複数の最小値と最大値を記録できるようにしました。 • BRIN がブルームフィルタを使えるようにしました。 16 • BRIN 列のみが更新される場合に HOT 更新を許可しました。 • pageinspect 関数 brin_page_items() に空範囲出力列を追加しました。
8. 使用例
9. BRIN の構築 • BRIN の構築は、通常のインデックスと同様に CREATE INDEX コマンドを使用 • USING 句に brin を指定する。 postgres@brin=# \d test Unlogged table "public.test" Column | Type | Collation | Nullable | Default --------+--------------------------+-----------+----------+--------- id | integer | | not null | reg_ts | timestamp with time zone | | | data | text | | | Indexes: "test_pkey" PRIMARY KEY, btree (id) postgres@brin=# CREATE INDEX reg_ts_brin ON test USING brin (reg_ts); CREATE INDEX
10. BRIN の構築（対応するデータ型） • 対応しているデータ型 – 文字型、数字型、日付型、バイナリ型 • 数字型っぽい money 型は未対応（！） – その他応用的な型 • inet, macaddr, anyrange, box, uuid, ... – psql の \dAp brin メタコマンドで確認可能
11. BRIN の構築（ユーザ定義型） • ユーザ定義型の場合、 BRIN に対応するインデックスアクセ スメソッドを作成していないと CREATE INDEX でエラー。 postgres@brin=# CREATE TABLE ut_test(id int, fd fraction, dummy text); CREATE TABLE postgres@brin=# \d ut_test Table "public.ut_test" Column | Type | Collation | Nullable | Default --------+----------+-----------+----------+--------- id | integer | | | fd | fraction | | | dummy | text | | | postgres@brin=# EXPLAIN SELECT * FROM ut_test WHERE fd >= '999/2' AND fd <= '1005/2'; QUERY PLAN ---------------------------------------------------------------------- Seq Scan on ut_test (cost=0.00..25.30 rows=5 width=52) Filter: ((fd >= '999/2'::fraction) AND (fd <= '1005/2'::fraction)) (2 rows) postgres@brin=# CREATE INDEX fd_brin ON ut_test USING brin (fd); ERROR: data type fraction has no default operator class for access method "brin" HINT: You must specify an operator class for the index or define a default operator class for the data type. postgres@brin=#
12. BRIN による検索 • BRIN を設定した列を条件に指定すると検索時に BRIN が使用 される。 • BRIN を使った検索の実行計画例 postgres@brin=# EXPLAIN SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01' AND '2025-01-02'; QUERY PLAN ------------------------------------------------------------------------------------------------------------- -------------------------------------------- Bitmap Heap Scan on test (cost=21.59..35190.58 rows=6239 width=45) Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-02 00:00:00+09'::timestamp with time zone)) -> Bitmap Index Scan on reg_ts_brin (cost=0.00..20.03 rows=12407 width=0) Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01- 02 00:00:00+09'::timestamp with time zone)) (4 rows)
13. アンチパターン
14. BRIN と colleration • pg_stats.colleration – 物理的な行の並び順と論理的な列の値の並び順に関する統 計的相関。 • 1 or -1 に近い場合：強い相関がある • 0 に近い場合：相関がない • BRIN は colleration が 1 or -1 に近くないと有効ではない
15. Good:colleration が 1 or -1 に近い • BRIN 対象列の colleration が 1 になっている例 postgres@brin=# \d test Unlogged table "public.test" Column | Type | Collation | Nullable | Default --------+--------------------------+-----------+----------+--------- id | integer | | not null | reg_ts | timestamp with time zone | | | data | text | | | Indexes: "test_pkey" PRIMARY KEY, btree (id) postgres@brin=# CREATE INDEX reg_ts_brin ON test USING brin (reg_ts); CREATE INDEX postgres@brin=# SELECT tablename, attname, n_distinct, correlation FROM pg_stats WHERE tablename = 'test'; tablename | attname | n_distinct | correlation -----------+---------+------------+------------- test | id | -1 | 1 test | reg_ts | -1 | 1 test | data | -1 | 0.002063701 (3 rows) correlation=1 → 論理値と物理的な 配置は完全相関
16. Good:colleration が 1 or -1 に近い • 検索時は最小限のブロックスキャンとなる EXPLAIN ANALYZE SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 01:00:00'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- -------------------------------------- Bitmap Heap Scan on test (cost=20.10..35189.08 rows=258 width=45) (actual time=0.879..2.611 rows=277 loops=1) Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01 01:00:00+09'::timestamp with time zone)) Rows Removed by Index Recheck: 12139 Heap Blocks: lossy=128 -> Bitmap Index Scan on reg_ts_brin (cost=0.00..20.03 rows=12407 width=0) (actual time=0.204..0.205 rows=1280 loops=1) Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01 01:00:00+09'::timestamp with time zone)) Planning Time: 0.254 ms Execution Time: 2.751 ms (8 rows) 最小限のブロックスキャン なので高速
17. Bad:colleration が 0 に近い • BRIN 対象列の colleration が 0 近くになっている例 postgres@brin=# \d test Unlogged table "public.test" Column | Type | Collation | Nullable | Default --------+--------------------------+-----------+----------+--------- id | integer | | not null | reg_ts | timestamp with time zone | | | data | text | | | Indexes: "test_pkey" PRIMARY KEY, btree (id) postgres@brin=# CREATE INDEX reg_ts_brin ON test USING brin (reg_ts); CREATE INDEX postgres@brin=# SELECT tablename, attname, n_distinct, correlation FROM pg_stats WHERE tablename = 'test'; tablename | attname | n_distinct | correlation -----------+---------+------------+-------------- test | id | -1 | 0.0024409215 test | reg_ts | -1 | 0.0024409215 test | data | -1 | 1 (3 rows) correlation が 0 に近い → 論理的な値と物理配置に 相関はほぼない
18. Bad:colleration が 0 に近い • BRIN を使ってもブロックが絞り込めない！ postgres@brin=# EXPLAIN ANALYZE SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 01:00:00'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- ---------- ---------------------------- Bitmap Heap Scan on test (cost=33.18..229725.69 rows=282 width=45) (actual time=13.760..1559.978 rows=277 loops=1) Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01 01:00:00+09'::timestamp with time zone)) Rows Removed by Index Recheck: 9999723 Heap Blocks: lossy=103093 -> Bitmap Index Scan on reg_ts_brin (cost=0.00..33.11 rows=5082895 width=0) (actual time=3.556..3.558 rows=1031280 loops =1) Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01 01:00:00+09'::timestamp with time zone)) Planning Time: 0.097 ms Execution Time: 1560.306 ms (8 rows) 大量のブロックを読込み ほとんど Recheck で除外 とても遅い・・・
19. Bad: 更新が多発するケース • 最初は colleraion が 1/-1 に近い場合でも以下のようなケースでは colleration が 0 に近くなっていく。 – 更新が頻繁に発生し、最初に格納されたデータが物理的に後に移 動する – DELETE や UPDATE によって無効領域→空き領域になった箇所 に新しいデータが格納される • DELETE/UPDATE が多発するテーブルには BRIN は向いていない。
20. BRIN vs B-Tree
21. BRIN vs B-Tree BRIN B-Tree インデックスサイズ / 作成時間 サイズは非常に小さい 作成時間も小さい サイズは大 作成時間は大 検索時間 検索結果件数（範囲）が多 い場合は B-Tree より早い 検索結果件数が少ない場合 は BRIN より高速 制約 使用可能なデータ型は限定 CLUSTER 不可 なし その他 アンチパターンケースあり アンチパターンケースなし
22. BRIN vs B-Tree • インデックスサイズ / インデックス作成時間 btree brin 0 5000 10000 15000 20000 25000 30000 27422 5 インデックスサイズ ブロック数 btree brin 0 2000 4000 6000 8000 10000 12000 9854.367 1962.126 インデックス作成時間 インデックス作成時間 (ms)
23. BRIN vs B-Tree • 取得件数と検索時間 – 取得件数が少ない場合は B-Tree が有利 – ある程度の件数になると BRIN も B-Tree も差がなくなる 0 50000 100000 150000 200000 250000 0 200 400 600 800 1000 1200 インデックス種別と検索時間 btree brin (seq scan) 取得件数 検索時間 (ms) 0 1000 2000 3000 4000 5000 6000 7000 8000 0 1 2 3 4 5 6 7 8 インデックス種別と検索時間（件数小の区間） btree brin (seq scan) 取得件数 検索時間 (ms)
24. BRIN のしくみ
25. contrib/pageinspect • BRIN のファイル内容を解析するときに役に立った拡張機能 – https://www.postgresql.org/docs/16/pageinspect.html • テーブルやインデックスファイル内容を可視化する • BRIN にも対応している – ページ種類の報告 – ページ内容の報告 • 以降のページではこの拡張機能を使って説明しています。
26. BRIN ファイルの構成 • BRIN ファイルは３つのパートに分かれている metainfo ページ revmap ページ regular ページ 1 ページ固定 ページ数可変 ページ数可変 先頭 末尾
27. metainfo ページ • BRIN の先頭ページ • BRIN 全体のメタ情報を格納。項目は以下参照。 項目 機能 magic マジックナンバー version バージョン番号。 BRIN_CURRENT_VERSION(1) pageperrange ストレージパラメータ pages_per_range の値 lastrevmappage 最終 revmap ページ
28. revmap ページ • BRIN 内の各エントリがどのブロックに入っているかを示す。 • ( ブロック番号 , 通番 ) • pageinspect で確認するとこんな感じ。 pages --------- (2,1) (2,2) (2,3) （中略） (2,291) (3,1) (3,2) （中略） (4,223) (4,224) （中略） (0,0) (1360 rows) この BRIN は 2 ページから 4 ページ まで items がある
29. regular ページ • レンジに対応するブロックの情報や値が格納されている 項目 機能 itemoffset BRIN 内の通番 blknum レンジに対応するテーブルのブロック番号 attnum インデックス列番号 allnulls レンジ内のすべての値が NULL か？ hasnulls レンジ内に NULL があるか？ placeholder 仮確保されたレンジか？ empty レンジはタプルを含まない value レンジの値
30. regular ページ • pageinspect で見るとこんな感じ postgres@brin=# SELECT itemoffset, blknum, value FROM brin_page_items(get_raw_page('reg_ts_brin',2), 'reg_ts_brin') ORDER BY value ASC LIMIT 5; itemoffset | blknum | value ------------+--------+---------------------------------------------------- 1 | 0 | {2023-01-01 00:00:00+09 .. 2023-01-02 20:49:55+09} 2 | 128 | {2023-01-02 20:50:08+09 .. 2023-01-04 17:40:03+09} 3 | 256 | {2023-01-04 17:40:16+09 .. 2023-01-06 14:30:11+09} 4 | 384 | {2023-01-06 14:30:24+09 .. 2023-01-08 11:20:19+09} 5 | 512 | {2023-01-08 11:20:32+09 .. 2023-01-10 08:10:27+09} (5 rows) • itemoffset=3 はブロック番号 256 ～ 383 の中に 2023-01-04 17:40:16 ～ 2023-01-06 14:30 の範囲の データが格納されていることを示している。
31. BRIN 検索のイメージ • BRIN インデックス内の参照：①～③ • テーブルファイル内の参照：④～⑤ metainfo ページ revmap ページ items ページ BRIN ファイル items ページ pagepaerrange items は 2 ～ 3 ブロックだ 条件に合うブロッ ク番号は xxx, yyy だ テーブルファイル ページ ページ (xxx) ページ ページ (yyy) ページ ページ ページ ・・・ Recheck 検索条件に合致する タプル ブロック番号 xxx, yyy から pagepaerrange 数分の ブロックを読む ① ② ③ ④ ⑤
32. チューニング
33. BRIN チューニングパラメータ • CREATE INDEX で BRIN を作成する時に、以下のストレージ パラメータを設定可能 項目 機能 デフォルト値 pages_per_range リレーションのページ数を除算する数 リレーション読込数の単位にもなる 128 autosummarize autovacuum 起動時に VACUUM 対象でな くてもサマライズする。 （詳細は BRIN のメンテナンス参照） off
34. pages_per_range パラメータ • BRIN サイズと検索時間とのトレードオフ • この数値を大きくする – BRIN のサイズが小さくなる – 検索時にスキャンされるブロック数の単位が大きくなる • この数値を小さくする – BRIN のサイズが大きくなる – 検索時にスキャンされるブロック数の単位が小さくなる – 検索範囲が比較的小さいケースだと有効
35. pages_per_range パラメータ • pages_per_range を 32, 64, 128, 256, 512 に変更 • インデックスサイズ / 作成時間 0 64 128 192 256 320 384 448 512 0 5 10 15 20 25 30 35 pages_per_range と BRIN サイズ pages_per_range ブロック数 0 64 128 192 256 320 384 448 512 0 500 1000 1500 2000 2500 3000 3500 4000 4500 5000 pages_per_range と BRIN 作成時間 pages_per_range BRIN 作成時間 (ms)
36. pages_per_range パラメータ • pages_per_range を 32, 64, 128, 256, 512 に変更 • 数ブロック内の結果のみヒットする検索 • スキャンブロック数 / 検索時間 0 64 128 192 256 320 384 448 512 0 100 200 300 400 500 600 pages_per_range とスキャンブロック数 heap ブロック数 heap+index ブロック数 pages_per_range 検索時間 (ms) 0 64 128 192 256 320 384 448 512 0 2 4 6 8 10 12 14 16 pages_per_range と検索時間 pages_per_range 検索時間 (ms) heap+index ブロック数 が逆転している
37. メンテナンス
38. BRIN のメンテナンス • BRIN を設定しているテーブルへ更新があった場合、インデックス自 体は更新されるが「サマライズ」はされない。 • サマライズ – 区間内の最小・最大値を更新する • サマライズの実行 – 自動 VACUUM や手動 VACUUM 背景で実行される。 – SQL 関数で即時のサマライズも可能
39. 挿入直後にサマライズされないときの問題 • BRIN 構築後に大量データの挿入があり、かつ自動 VACUUM/ VACUUM が実行されないと挿入データのサマリが生成されな い。 • BRIN 検索時にサマリがないブロックは必ず読んでしまうため 性能劣化する。
40. 挿入直後にサマライズされないときの問題 • 以下のように実行 – 初期データ 1000 万件挿入（ 10000 ブロック程度の挿入） • 最小値 2023-01-01 00:00:00 • 最大値 2027-02-10 23:46:59 – timestamp 型の列に BRIN – 検索（１） – INSERT で 100 万件挿入（ 2028-01-01 00:00:00 ～） – 検索（２） 同じ検索クエリでも INSERT 前後では 処理時間に大きく変わる
41. 検索 (1) • BRIN 構築直後→検索 EXPLAIN ANALYZE SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 01:00:00'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- ---------------------------------------- Bitmap Heap Scan on test (cost=20.11..35189.09 rows=299 width=45) (actual time=0.854..2.545 rows=277 loops=1) Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01 01:00:00+09'::timestamp with time zone)) Rows Removed by Index Recheck: 12139 Heap Blocks: lossy=128 -> Bitmap Index Scan on reg_ts_brin (cost=0.00..20.03 rows=12407 width=0) (actual time=0.188..0.188 rows=1280 loops=1) Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01 01:00:00+09'::timestamp with time zone)) Planning Time: 0.557 ms Execution Time: 2.676 ms (8 rows)
42. 検索 (2) • BRIN 構築直後→ 100 万件ロード→検索 EXPLAIN ANALYZE SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 01:00:00'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- ---------------------------------------- Bitmap Heap Scan on test (cost=20.11..35913.31 rows=329 width=45) (actual time=0.698..124.456 rows=277 loops=1) Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01 01:00:00+09'::timestamp with time zone)) Rows Removed by Index Recheck: 1004843 Heap Blocks: lossy=10363 -> Bitmap Index Scan on reg_ts_brin (cost=0.00..20.03 rows=12415 width=0) (actual time=0.313..0.314 rows=103630 loops=1) Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01 01:00:00+09'::timestamp with time zone)) Planning Time: 0.062 ms Execution Time: 124.605 ms (8 rows)
43. 挿入直後にサマライズされないときの問題 • INSERT 前 / 後の検索時間 – 検索条件 INSERT 前 INSERT 後 検索件数 2025-01-01 の 1 時間 277 277 2028-01-01 の 1 時間 0 277 読込ブロック数 2025-01-01 の 1 時間 128 10363 2028-01-01 の 1 時間 0 10235 検索時間 (ms) 2025-01-01 の 1 時間 2.7 124.6 2028-01-01 の 1 時間 0.18 102.1 読込ブロック数増大 → 検索時間の悪化
44. autosummarize • デフォルトは off • autosummarize=on – autovacuum 発動時に BRIN 設定テーブルが autovacuum 対象でなくても、サマライズを実行する。 • autosummarize=on でも INSERT 性能への影響はない。
45. BRIN メンテナンス関数 • VACUUM 背景で BRIN はメンテナンスされるが、以下の関数を実行してメンテナンス も可能 – サマライズされていない場合、 brin_summarize_new_values で即時反映が可能 関数名 機能 brin_summarize_new_values サマライズされていないページ範囲を元にインデッ クスタプルを生成する brin_summarize_range 指定したブロックに対して brin_summarize_new_values 相当を実行 brin_desummarize_range 指定のブロックを含むページ範囲のインデックスタ プルを削除する
46. brin_summarize_new_values の使用例 • BRIN 構築直後→ 100 万件ロード→サマライズ関数実行→検索 SELECT brin_summarize_new_values('reg_ts_brin'); brin_summarize_new_values --------------------------- 80 (1 row) Time: 176.186 ms EXPLAIN ANALYZE SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 01:00:00'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- ---------------------------------------- Bitmap Heap Scan on test (cost=24.11..35917.31 rows=329 width=45) (actual time=0.577..1.599 rows=277 loops=1) Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01 01:00:00+09'::timestamp with time zone)) Rows Removed by Index Recheck: 12139 Heap Blocks: lossy=128 -> Bitmap Index Scan on reg_ts_brin (cost=0.00..24.03 rows=12415 width=0) (actual time=0.181..0.181 rows=1280 loops=1) Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01 01:00:00+09'::timestamp with time zone)) Planning Time: 0.058 ms Execution Time: 1.730 ms (8 rows)
47. brin_summarize_new_values の使用例 • INSERT 前 / 後 / サマライズ後の検索時間 – 検索条件 INSERT 前 INSERT 後 サマライズ後 検索件数 2025-01-01 の 1 時間 277 277 277 2028-01-01 の 1 時間 0 277 277 読込ブロック数 2025-01-01 の 1 時間 128 10363 128 2028-01-01 の 1 時間 0 10235 128 検索時間 (ms) 2025-01-01 の 1 時間 2.7 124.6 1.7 2028-01-01 の 1 時間 0.18 102.1 1.8 バルク INSERT の後には VACUUM か サマライズかけるべし
48. BRIN とメンテナンスコマンド • BRIN/BRIN を設定したテーブルに対する挙動 メンテナンス機能 / コマンド BRIN に対する動作 VACUUM/ 自動 VACUUM VACUUM 背景で BRIN のサマライズを行う VACUUM FULL テーブル詰め込みの後、その状態から BRIN を再 生成する REINDEX 特に制約なし CLUSTER BRIN を指定した CLUSTER は不可 ANALYZE ANALZYE 実行の影響はなし pg_repack 列指定でテーブル並べ替え→ BRIN を再生成
49. まとめ
50. まとめ • BRIN は B-tree のような汎用性はないが、以下のケースでは有効 – colleration が 1/-1 に近い • 登録日時等、後から挿入するデータが大きくなるケース – INSERT/SELECT のみ行うテーブル – ある程度広い範囲の検索を行うケース • BRIN のメンテナンスの運用設計は必要 – 自動 VACUUM に任せるか、サマライズするか、 REINDEX か
51. おしまい