How To Dev

Introduction Expression Index is an extremely powerful feature of PostgreSQL, which can be used for any data type (such as text, numbers, time, arrays and more) as long as the result of the expression returns a Deterministic value You can use Expression Index for all index types ( B-Tree , Hash , GIN , GiST and more) as long as the expression you write in the CREATE INDEX command returns a data type that the Index type supports for processing The nature of Expression Index is that instead of indexing on the original column value, Postgres precomputes the result of the function or expression as soon as you INSERT/UPDATE and saves the result directly into the Index file Characteristics After creating an Expression Index, when using a query, you must write it exactly like the expression in the Index, otherwise the index will not work Every time a row of data is modified, Postgres must run that function or expression to get the result to create the Index. Therefore, avoid using function...

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...