How To Dev

Introduction Overhead When you store a text string on disk, PostgreSQL cannot simply write those raw letters. If it only did that, upon reading the data back, Postgres would not know: How many bytes long is this string? Where does it end so it can read the next data column? Is this a short string, a long string, or compressed? Therefore, Postgres must spend a few extra bytes right at the beginning of the string to record this management information. This additional consumed capacity is called Overhead, which is the accompanying management cost. Header Structure PostgreSQL uses a common format named varlena (Variable-length array) to store all string types ( TEXT, VARCHAR, BYTEA, JSONB... ). This Overhead portion varies depending on the length of the data string: Very short strings (Under 127 bytes) Overhead: 1 byte . Mechanism: Postgres uses this first 1 byte ( 8 bits ) to store 7 bits for recording the actual length of the string, and 1 bit as a flag signaling that this is a s...

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