BRIN 意思是 Block Range Index。BRIN 設計用於處理非常大的資料表，其中某些欄位與其在資料表中的物理位置具有某些自然的相關性。區域範圍是在資料表中是物理上相鄰的一組頁面。對於每個區域範圍，索引都會儲存一些摘要資訊。例如，儲存商店銷售訂單的資料表中可能有一個日期欄位，每個訂單都放置在該欄位上，大多數時候，較早訂單的項目也會在資料表中更早出現。儲存郵遞區號欄的資料表可能會將某個城市的所有編碼自然地組合在一起。
BRIN 索引可以透過一般的 bitmap 索引掃描來滿足查詢，如果索引儲存的摘要資訊與查詢條件一致，則 BRIN 索引將回傳每個範圍內所有頁面中的所有資料列。查詢執行程序負責重新檢查這些資料列，並丟棄不符合查詢條件的資料列 - 換句話說，這些索引是失真的。由於 BRIN 索引非常小，因此與順序掃描相比，掃描索引幾乎不會增加成本，但是可以避免掃描資料表多數不符合條件的部分。
BRIN 索引將儲存的特定資料，使該索引能夠滿足的特定查詢，其取決於為索引的每一欄位選擇的運算子類。例如，具有線性排序順序的資料型別可以具有儲存每個區域範圍內的最小值和最大值的運算子類。幾何型別可以儲存區域範圍內所有物件的邊界。
區域範圍的大小在索引建立時由 pages_per_range 儲存參數決定。索引項目的數量等於頁面中關係的大小除以 pages_per_range 的值。因此，數字越小，索引就越大（由於需要儲存更多的索引項目），但是同時儲存的摘要資料可以更精確，並且在索引掃描期間可以跳過更多的資料區塊。
在建立的時候，將會掃描所有現有的 heap 頁面，並為每個資料範圍（包括最後可能不完整的範圍）建立一個摘要索引資料。當新的頁面充滿資料時，已經彙總的頁面範圍將會讓彙總資訊被來自新資料的資訊更新。當建立的新頁面不在上一個彙總範圍內時，該範圍就不會自動獲取彙總資訊；這些資料保持未摘要狀態，直到稍後呼叫摘要重整以建立初始摘要。可以使用 brin_summarize_range(regclass, bigint) 或 brin_summarize_new_values(regclass) 函數手動呼叫此程序。 VACUUM 時會自動處理資料表；或透過 autovacuum 執行自動彙總（INSERT 指令時）。（最後一個觸發器預設情況下處於停用狀態，可以使用 autosummarize 參數啟用。）相反地，可以使用 brin_desummarize_range(regclass, bigint) 函數對範圍進行反彙總，當索引資料不再是一個很效的索引時，此函數會很有用，因為現有值已經變更。
啟用 autosummarization 後，每次頁面範圍被填滿時，都會發送一個請求到 autovacuum，以對其執行針對該範圍的目標彙總，該請求將在下一個在同一資料庫上執行的工作程序結束時完成。如果請求佇列已滿，則不會記錄該請求，並且會將訊息發送到伺服器日誌：
發生這種情況時，該範圍將在資料表的下一次日常 vacuum 期間修正彙總資訊。
The core PostgreSQL distribution includes the BRIN operator classes shown in Table 67.1.
The minmax operator classes store the minimum and the maximum values appearing in the indexed column within the range. The inclusion operator classes store a value which includes the values in the indexed column within the range.
Name
Indexed Data Type
Indexable Operators
int8_minmax_ops
bigint
< <= = >= >
bit_minmax_ops
bit
< <= = >= >
varbit_minmax_ops
bit varying
< <= = >= >
box_inclusion_ops
box
<< &< && &> >> ~= @> <@ &<| <<| |>> |&>
bytea_minmax_ops
bytea
< <= = >= >
bpchar_minmax_ops
character
< <= = >= >
char_minmax_ops
"char"
< <= = >= >
date_minmax_ops
date
< <= = >= >
float8_minmax_ops
double precision
< <= = >= >
inet_minmax_ops
inet
< <= = >= >
network_inclusion_ops
inet
&& >>= <<= = >> <<
int4_minmax_ops
integer
< <= = >= >
interval_minmax_ops
interval
< <= = >= >
macaddr_minmax_ops
macaddr
< <= = >= >
macaddr8_minmax_ops
macaddr8
< <= = >= >
name_minmax_ops
name
< <= = >= >
numeric_minmax_ops
numeric
< <= = >= >
pg_lsn_minmax_ops
pg_lsn
< <= = >= >
oid_minmax_ops
oid
< <= = >= >
range_inclusion_ops
any range type
<< &< && &> >> @> <@ -|- = < <= = > >=
float4_minmax_ops
real
< <= = >= >
int2_minmax_ops
smallint
< <= = >= >
text_minmax_ops
text
< <= = >= >
tid_minmax_ops
tid
< <= = >= >
timestamp_minmax_ops
timestamp without time zone
< <= = >= >
timestamptz_minmax_ops
timestamp with time zone
< <= = >= >
time_minmax_ops
time without time zone
< <= = >= >
timetz_minmax_ops
time with time zone
< <= = >= >
uuid_minmax_ops
uuid
< <= = >= >
The BRIN interface has a high level of abstraction, requiring the access method implementer only to implement the semantics of the data type being accessed. The BRIN layer itself takes care of concurrency, logging and searching the index structure.
All it takes to get a BRIN access method working is to implement a few user-defined methods, which define the behavior of summary values stored in the index and the way they interact with scan keys. In short, BRIN combines extensibility with generality, code reuse, and a clean interface.
There are four methods that an operator class for BRIN must provide:
BrinOpcInfo *opcInfo(Oid type_oid)
Returns internal information about the indexed columns' summary data. The return value must point to a palloc'd BrinOpcInfo, which has this definition:
BrinOpcInfo.oi_opaque can be used by the operator class routines to pass information between support functions during an index scan.
bool consistent(BrinDesc *bdesc, BrinValues *column, ScanKey key)
Returns whether the ScanKey is consistent with the given indexed values for a range. The attribute number to use is passed as part of the scan key.
bool addValue(BrinDesc *bdesc, BrinValues *column, Datum newval, bool isnull)
Given an index tuple and an indexed value, modifies the indicated attribute of the tuple so that it additionally represents the new value. If any modification was done to the tuple, true is returned.
bool unionTuples(BrinDesc *bdesc, BrinValues *a, BrinValues *b)
Consolidates two index tuples. Given two index tuples, modifies the indicated attribute of the first of them so that it represents both tuples. The second tuple is not modified.
The core distribution includes support for two types of operator classes: minmax and inclusion. Operator class definitions using them are shipped for in-core data types as appropriate. Additional operator classes can be defined by the user for other data types using equivalent definitions, without having to write any source code; appropriate catalog entries being declared is enough. Note that assumptions about the semantics of operator strategies are embedded in the support functions' source code.
Operator classes that implement completely different semantics are also possible, provided implementations of the four main support functions described above are written. Note that backwards compatibility across major releases is not guaranteed: for example, additional support functions might be required in later releases.
To write an operator class for a data type that implements a totally ordered set, it is possible to use the minmax support functions alongside the corresponding operators, as shown in Table 67.2. All operator class members (functions and operators) are mandatory.
To write an operator class for a complex data type which has values included within another type, it's possible to use the inclusion support functions alongside the corresponding operators, as shown in Table 67.3. It requires only a single additional function, which can be written in any language. More functions can be defined for additional functionality. All operators are optional. Some operators require other operators, as shown as dependencies on the table.
Support function numbers 1-10 are reserved for the BRIN internal functions, so the SQL level functions start with number 11. Support function number 11 is the main function required to build the index. It should accept two arguments with the same data type as the operator class, and return the union of them. The inclusion operator class can store union values with different data types if it is defined with the STORAGE parameter. The return value of the union function should match the STORAGE data type.
Support function numbers 12 and 14 are provided to support irregularities of built-in data types. Function number 12 is used to support network addresses from different families which are not mergeable. Function number 14 is used to support empty ranges. Function number 13 is an optional but recommended one, which allows the new value to be checked before it is passed to the union function. As the BRIN framework can shortcut some operations when the union is not changed, using this function can improve index performance.
Both minmax and inclusion operator classes support cross-data-type operators, though with these the dependencies become more complicated. The minmax operator class requires a full set of operators to be defined with both arguments having the same data type. It allows additional data types to be supported by defining extra sets of operators. Inclusion operator class operator strategies are dependent on another operator strategy as shown in Table 67.3, or the same operator strategy as themselves. They require the dependency operator to be defined with the STORAGE data type as the left-hand-side argument and the other supported data type to be the right-hand-side argument of the supported operator. See float4_minmax_ops as an example of minmax, and box_inclusion_ops as an example of inclusion.
Operator class member
Object
Support Function 1
internal function brin_minmax_opcinfo()
Support Function 2
internal function brin_minmax_add_value()
Support Function 3
internal function brin_minmax_consistent()
Support Function 4
internal function brin_minmax_union()
Operator Strategy 1
operator less-than
Operator Strategy 2
operator less-than-or-equal-to
Operator Strategy 3
operator equal-to
Operator Strategy 4
operator greater-than-or-equal-to
Operator Strategy 5
operator greater-than
Operator class member
Object
Dependency
Support Function 1
internal function brin_inclusion_opcinfo()
Support Function 2
internal function brin_inclusion_add_value()
Support Function 3
internal function brin_inclusion_consistent()
Support Function 4
internal function brin_inclusion_union()
Support Function 11
function to merge two elements
Support Function 12
optional function to check whether two elements are mergeable
Support Function 13
optional function to check if an element is contained within another
Support Function 14
optional function to check whether an element is empty
Operator Strategy 1
operator left-of
Operator Strategy 4
Operator Strategy 2
operator does-not-extend-to-the-right-of
Operator Strategy 5
Operator Strategy 3
operator overlaps
Operator Strategy 4
operator does-not-extend-to-the-left-of
Operator Strategy 1
Operator Strategy 5
operator right-of
Operator Strategy 2
Operator Strategy 6, 18
operator same-as-or-equal-to
Operator Strategy 7
Operator Strategy 7, 13, 16, 24, 25
operator contains-or-equal-to
Operator Strategy 8, 14, 26, 27
operator is-contained-by-or-equal-to
Operator Strategy 3
Operator Strategy 9
operator does-not-extend-above
Operator Strategy 11
Operator Strategy 10
operator is-below
Operator Strategy 12
Operator Strategy 11
operator is-above
Operator Strategy 9
Operator Strategy 12
operator does-not-extend-below
Operator Strategy 10
Operator Strategy 20
operator less-than
Operator Strategy 5
Operator Strategy 21
operator less-than-or-equal-to
Operator Strategy 5
Operator Strategy 22
operator greater-than
Operator Strategy 1
Operator Strategy 23
operator greater-than-or-equal-to
Operator Strategy 1