せっかくなので BRIN を使ってみよう！ PostgreSQL Conferecnce 2023 (2023-11-24)

自己紹介 ● ぬこ＠横浜 , @nuko_yokohama ● にゃーん ● 趣味でポスグレをやってる者だ ● PostgreSQL アンカンファレンスゆるふわ枠

内容 ● 概要 ● 使用例 ● アンチパターン ● BRIN vs B-Tree ● BRIN のしくみ ● チューニング ● メンテナンス ● まとめ PostgreSQL の インデックス設計時に こんな選択肢もあることを 理解してもらえるのがゴール

概要

PostgreSQL でサポートされるインデックス ● PostgreSQL 標準配布物に含まれるインデックス バージョン 内容 B-Tree PostgreSQL のデフォルトインデックス種別。 Hash 列値を32bitハッシュ化。＝比較のみ対応。 GiST/SP-GiST 特殊な用途のインデックスの基盤 GIN 汎用転置インデックス。配列、JSON 、全文検索等。 BRIN Block Range Index 。本資料で説明。 contrib/bloom ブルームフィルタインデックス。＝比較のみ対応。

概要 ● BRIN=Block Range INdex ● 値が存在するテーブルのブロック番号の範囲をもつ ● BRIN の特徴 – インデックスサイズ：極小 – Colleration に依存 – ほどほどに高速な検索

History of BRIN ● BRIN の歴史 バージョン 内容 9.5 ● BRIN が追加されました。 10 ● BRIN の要約をより積極的に行うオプションを追加しました。 ● BRIN スキャンが有益かどうかの判断の精度を改善しました。 14 ● BRIN が範囲ごとに複数の最小値と最大値を記録できるようにしました。 ● BRIN がブルームフィルタを使えるようにしました。 16 ● BRIN 列のみが更新される場合に HOT 更新を許可しました。 ● pageinspect 関数 brin_page_items() に空範囲出力列を追加しました。

使用例

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

BRIN の構築（対応するデータ型） ● 対応しているデータ型 – 文字型、数字型、日付型、バイナリ型 ● 数字型っぽい money 型は未対応（！） – その他応用的な型 ● inet, macaddr, anyrange, box, uuid, ... – psql の \dAp brin メタコマンドで確認可能

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=#

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)

アンチパターン

BRIN と colleration ● pg_stats.colleration – 物理的な行の並び順と論理的な列の値の並び順に関する統 計的相関。 ● 1 or -1 に近い場合：強い相関がある ● 0 に近い場合：相関がない ● BRIN は colleration が 1 or -1 に近くないと有効ではない

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 → 論理値と物理的な 配置は完全相関

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) 最小限のブロックスキャン なので高速

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 に近い → 論理的な値と物理配置に 相関はほぼない

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 で除外 とても遅い・・・

Bad: 更新が多発するケース ● 最初は colleraion が 1/-1 に近い場合でも以下のようなケースでは colleration が 0 に近くなっていく。 – 更新が頻繁に発生し、最初に格納されたデータが物理的に後に移 動する – DELETE や UPDATE によって無効領域→空き領域になった箇所 に新しいデータが格納される ● DELETE/UPDATE が多発するテーブルには BRIN は向いていない。

BRIN vs B-Tree

BRIN vs B-Tree BRIN B-Tree インデックスサイズ / 作成時間 サイズは非常に小さい 作成時間も小さい サイズは大 作成時間は大 検索時間 検索結果件数（範囲）が多 い場合は B-Tree より早い 検索結果件数が少ない場合 は BRIN より高速 制約 使用可能なデータ型は限定 CLUSTER 不可 なし その他 アンチパターンケースあり アンチパターンケースなし

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)

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)

BRIN のしくみ

contrib/pageinspect ● BRIN のファイル内容を解析するときに役に立った拡張機能 – https://www.postgresql.org/docs/16/pageinspect.html ● テーブルやインデックスファイル内容を可視化する ● BRIN にも対応している – ページ種類の報告 – ページ内容の報告 ● 以降のページではこの拡張機能を使って説明しています。

BRIN ファイルの構成 ● BRIN ファイルは３つのパートに分かれている metainfo ページ revmap ページ regular ページ 1 ページ固定 ページ数可変 ページ数可変 先頭 末尾

metainfo ページ ● BRIN の先頭ページ ● BRIN 全体のメタ情報を格納。項目は以下参照。 項目 機能 magic マジックナンバー version バージョン番号。 BRIN_CURRENT_VERSION(1) pageperrange ストレージパラメータ pages_per_range の値 lastrevmappage 最終 revmap ページ

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 がある

regular ページ ● レンジに対応するブロックの情報や値が格納されている 項目 機能 itemoffset BRIN 内の通番 blknum レンジに対応するテーブルのブロック番号 attnum インデックス列番号 allnulls レンジ内のすべての値が NULL か？ hasnulls レンジ内に NULL があるか？ placeholder 仮確保されたレンジか？ empty レンジはタプルを含まない value レンジの値

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 の範囲の データが格納されていることを示している。

BRIN 検索のイメージ ● BRIN インデックス内の参照：①～③ ● テーブルファイル内の参照：④～⑤ metainfo ページ revmap ページ items ページ BRIN ファイル items ページ pagepaerrange items は 2 ～ 3 ブロックだ 条件に合うブロッ ク番号は xxx, yyy だ テーブルファイル ページ ページ (xxx) ページ ページ (yyy) ページ ページ ページ ・・・ Recheck 検索条件に合致する タプル ブロック番号 xxx, yyy から pagepaerrange 数分の ブロックを読む ① ② ③ ④ ⑤

チューニング

BRIN チューニングパラメータ ● CREATE INDEX で BRIN を作成する時に、以下のストレージ パラメータを設定可能 項目 機能 デフォルト値 pages_per_range リレーションのページ数を除算する数 リレーション読込数の単位にもなる 128 autosummarize autovacuum 起動時に VACUUM 対象でな くてもサマライズする。 （詳細は BRIN のメンテナンス参照） off

pages_per_range パラメータ ● BRIN サイズと検索時間とのトレードオフ ● この数値を大きくする – BRIN のサイズが小さくなる – 検索時にスキャンされるブロック数の単位が大きくなる ● この数値を小さくする – BRIN のサイズが大きくなる – 検索時にスキャンされるブロック数の単位が小さくなる – 検索範囲が比較的小さいケースだと有効

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)

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 ブロック数 が逆転している

メンテナンス

BRIN のメンテナンス ● BRIN を設定しているテーブルへ更新があった場合、インデックス自 体は更新されるが「サマライズ」はされない。 ● サマライズ – 区間内の最小・最大値を更新する ● サマライズの実行 – 自動 VACUUM や手動 VACUUM 背景で実行される。 – SQL 関数で即時のサマライズも可能

挿入直後にサマライズされないときの問題 ● BRIN 構築後に大量データの挿入があり、かつ自動 VACUUM/ VACUUM が実行されないと挿入データのサマリが生成されな い。 ● BRIN 検索時にサマリがないブロックは必ず読んでしまうため 性能劣化する。

挿入直後にサマライズされないときの問題 ● 以下のように実行 – 初期データ 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 前後では 処理時間に大きく変わる

検索 (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)

検索 (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)

挿入直後にサマライズされないときの問題 ● 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 読込ブロック数増大 → 検索時間の悪化

autosummarize ● デフォルトは off ● autosummarize=on – autovacuum 発動時に BRIN 設定テーブルが autovacuum 対象でなくても、サマライズを実行する。 ● autosummarize=on でも INSERT 性能への影響はない。

BRIN メンテナンス関数 ● VACUUM 背景で BRIN はメンテナンスされるが、以下の関数を実行してメンテナンス も可能 – サマライズされていない場合、 brin_summarize_new_values で即時反映が可能 関数名 機能 brin_summarize_new_values サマライズされていないページ範囲を元にインデッ クスタプルを生成する brin_summarize_range 指定したブロックに対して brin_summarize_new_values 相当を実行 brin_desummarize_range 指定のブロックを含むページ範囲のインデックスタ プルを削除する

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)

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 か サマライズかけるべし

BRIN とメンテナンスコマンド ● BRIN/BRIN を設定したテーブルに対する挙動 メンテナンス機能 / コマンド BRIN に対する動作 VACUUM/ 自動 VACUUM VACUUM 背景で BRIN のサマライズを行う VACUUM FULL テーブル詰め込みの後、その状態から BRIN を再 生成する REINDEX 特に制約なし CLUSTER BRIN を指定した CLUSTER は不可 ANALYZE ANALZYE 実行の影響はなし pg_repack 列指定でテーブル並べ替え→ BRIN を再生成

まとめ

まとめ ● BRIN は B-tree のような汎用性はないが、以下のケースでは有効 – colleration が 1/-1 に近い ● 登録日時等、後から挿入するデータが大きくなるケース – INSERT/SELECT のみ行うテーブル – ある程度広い範囲の検索を行うケース ● BRIN のメンテナンスの運用設計は必要 – 自動 VACUUM に任せるか、サマライズするか、 REINDEX か

おしまい