331
技術社區[雲棲]
PostgreSQL 10.0 preview 功能增強 - BRIN 索引更新smooth化
標簽
PostgreSQL , 10.0 , BRIN , de-summarization , summarization , 平滑更新BRIN , 平滑失效BRIN
背景
我們將數據存入PostgreSQL時，如果創建的是堆表，那麼數據是往數據文件的末尾不斷追加存儲的。
為了提高數據的檢索速度，可以對響應的字段創建索引，在PostgreSQL中，已有8種索引類型，分別是B-Tree，hash, gin, gist, sp-gist, brin, bloom, rum。分別對應不同的應用場景。
如果數據值與物理存儲線性相關性比較好，可以使用塊級索引（BRIN），以塊（或連續的若幹塊）為最小單位，索引中存儲的是這些最小單位的統計信息（最大值，最小值，記錄條數，SUM，NULL值條數等）。
為了提高插入速度，HEAP末端的塊，可能並不會實時的更新到BRIN索引中，10.0以前，vacuum或者autovacuum表時，會生成未生成統計信息的HEAP block range，如果插入很快，比如在一個vacuum周期內，插入了100個數據塊的內容，那麼vacuum需要掃描這100個數據塊並生成BRIN索引對應的信息。
10.0 寫時觸發更新brin
為了防止插入過快，導致vacuum一次要統計過多的數據塊，或者導致未進入BRIN索引的堆表末端數據塊過多，10.0的改進如下，在插入時，如果插入到下一個block range的塊時，自動觸發vacuum去統計前一個block range的BRIN統計信息（如果還沒有被統計的話）。
這樣就可以解決前麵提到的問題，因為insert的時候，隻要到達block range邊界，就會自動觸發vacuum去統計它。
開啟"insert的時候，隻要到達block range邊界，就會自動觸發vacuum去統計它"的方法，創建索引時，指定autosummarize參數。
https://www.postgresql.org/docs/devel/static/sql-createindex.html
BRIN indexes accept different parameters:
pages_per_range
Defines the number of table blocks that make up one block range for each entry of a BRIN index (see Section 65.1 for more details). The default is 128.
autosummarize
Defines whether a summarization run is invoked for the previous page range whenever an insertion is detected on the next one.
create index idx on table using brin (id) with (autosummarize=on);
同時，10.0還新增了用戶UDF接口，用戶可以自己調用函數，去統計（更新）BRIN索引，指定表，以及堆表對應的block_id即可。
+-- Test brin_summarize_range
+CREATE TABLE brin_summarize (
+ value int
+) WITH (fillfactor=10, autovacuum_enabled=false);
+CREATE INDEX brin_summarize_idx ON brin_summarize USING brin (value) WITH (pages_per_range=2);
+-- Fill a few pages
+DO $$
+DECLARE curtid tid;
+BEGIN
+ LOOP
+ INSERT INTO brin_summarize VALUES (1) RETURNING ctid INTO curtid;
+ EXIT WHEN curtid > tid '(2, 0)';
+ END LOOP;
+END;
+$$;
+-- summarize one range
+SELECT brin_summarize_range('brin_summarize_idx', 0);
+ brin_summarize_range
+----------------------
+ 1
+(1 row)
+
+-- nothing: already summarized
+SELECT brin_summarize_range('brin_summarize_idx', 1);
+ brin_summarize_range
+----------------------
+ 0
+(1 row)
+
+-- summarize one range
+SELECT brin_summarize_range('brin_summarize_idx', 2);
+ brin_summarize_range
+----------------------
+ 1
+(1 row)
+
+-- nothing: page doesn't exist in table
+SELECT brin_summarize_range('brin_summarize_idx', 4294967295);
+ brin_summarize_range
+----------------------
+ 0
+(1 row)
+
+-- invalid block number values
+SELECT brin_summarize_range('brin_summarize_idx', -1);
+ERROR: block number out of range: -1
+SELECT brin_summarize_range('brin_summarize_idx', 4294967296);
+ERROR: block number out of range: 4294967296
patch信息如下
BRIN auto-summarization
Previously, only VACUUM would cause a page range to get initially
summarized by BRIN indexes, which for some use cases takes too much time
since the inserts occur. To avoid the delay, have brininsert request a
summarization run for the previous range as soon as the first tuple is
inserted into the first page of the next range. Autovacuum is in charge
of processing these requests, after doing all the regular vacuuming/
analyzing work on tables.
This doesn't impose any new tasks on autovacuum, because autovacuum was
already in charge of doing summarizations. The only actual effect is to
change the timing, i.e. that it occurs earlier. For this reason, we
don't go any great lengths to record these requests very robustly; if
they are lost because of a server crash or restart, they will happen at
a later time anyway.
Most of the new code here is in autovacuum, which can now be told about
"work items" to process. This can be used for other things such as GIN
pending list cleaning, perhaps visibility map bit setting, both of which
are currently invoked during vacuum, but do not really depend on vacuum
taking place.
The requests are at the page range level, a granularity for which we did
not have SQL-level access; we only had index-level summarization
requests via brin_summarize_new_values(). It seems reasonable to add
SQL-level access to range-level summarization too, so add a function
brin_summarize_range() to do that.
Authors: Álvaro Herrera, based on sketch from Simon Riggs.
Reviewed-by: Thomas Munro.
Discussion: https://postgr.es/m/20170301045823.vneqdqkmsd4as4ds@alvherre.pgsql
10.0 brin索引條目失效接口
brin記錄的是block range的最大值，最小值，隨著對應block range內數據的更新，刪除，BRIN IDX中對應的block range統計信息可能會越來越寬泛（不準確），因此為了提高BRIN的精度，10.0新增了一個失效接口，可以將對應block range的brin index內的條目失效，然後你可以等VACUUM再次統計它，或者調用前麵提到的UDF接口來統計它。
失效heap block range對應brin idx條目的方法
+SELECT brin_desummarize_range('brinidx', 0);
+ brin_desummarize_range
+------------------------
+
+(1 row)
+
+-- Tests for brin_desummarize_range
+SELECT brin_desummarize_range('brinidx', -1); -- error, invalid range
+ERROR: block number out of range: -1
+SELECT brin_desummarize_range('brinidx', 0);
+ brin_desummarize_range
+------------------------
+
+(1 row)
+
+SELECT brin_desummarize_range('brinidx', 0);
+ brin_desummarize_range
+------------------------
+
+(1 row)
+
+SELECT brin_desummarize_range('brinidx', 100000000);
+ brin_desummarize_range
+------------------------
+
+(1 row)
+
patch信息如下
BRIN de-summarization
When the BRIN summary tuple for a page range becomes too "wide" for the
values actually stored in the table (because the tuples that were
present originally are no longer present due to updates or deletes), it
can be useful to remove the outdated summary tuple, so that a future
summarization can install a tighter summary.
This commit introduces a SQL-callable interface to do so.
Author: Álvaro Herrera
Reviewed-by: Eiji Seki
Discussion: https://postgr.es/m/20170228045643.n2ri74ara4fhhfxf@alvherre.pgsql
這個patch的討論，詳見郵件組，本文末尾URL。
PostgreSQL社區的作風非常嚴謹，一個patch可能在郵件組中討論幾個月甚至幾年，根據大家的意見反複的修正，patch合並到master已經非常成熟，所以PostgreSQL的穩定性也是遠近聞名的。
參考
https://www.postgresql.org/docs/devel/static/sql-createindex.html
最後更新：2017-04-07 21:25:10