How To Dev

Introduction BRIN (Block Range Index) works by grouping a cluster of pages in the HEAP together into a single range rather than creating an index for every single data row like a B-Tree. The structure of a data row inside a BRIN Index (called an Index Tuple) consists of the following three core components: blknum (Block Number): The block number starting the range (for example: 0, 128, 256, 512...). min_value: The minimum value of that column within a block range. max_value: The maximum value of that column within a block range. How It Works When executing a query with a BRIN Index, Postgres scans the index and checks whether the value being searched for falls within any [Min, Max] range If not, it skips that entire block range, eliminating a huge amount of redundant data. If yes, Postgres relies on the blknum to load the corresponding Pages in the HEAP up and checks them sequentially to find the exact data row. Advantages Super compact: Because it only stores the Min and Max for an en...

Introduction In this article, we will explore another type of index, which is the Hash index. True to its name, when using this index type, Postgres passes the value to be indexed through a hash function to generate a number ranging from 0 to 4,294,697,295 with a uint32 (4 bytes) type. Index Creation Process As soon as you create a Hash index, Postgres pre-allocates Pages to store the indexes. If the table does not have data yet, Postgres will still create: 1 Metapage : Always located at the first position (Block 0). This page stores the configuration of the index. 1 Bitmap page : Used to manage which pages in the file are currently empty to avoid Overflow pages. 10 default Primary Bucket Pages . Thus, right from the start, your newly created Hash Index file will have a size of 12 Pages x 8KB/Page = 96 KB. If the table already contains data, based on the configuration information about the Ram limit that Postgres can use, it will create the maximum number of Pages (that it can create)...