How Modern Indexing works in PostgreSQL- In depth explanation of how Indexing in PostgreSQL works and what enahncements Postgres has adapted that makes Indexing more faster.

Thinking about indexing, we know a only a big picture that uses B-Tree structures in memory, but in modern DBMs systems there are some enhancements introduced in indexing where systems like PostgreSQL are utilizing some new OS system calls like io_uring to make syncrhonous IO reading, also direct retrieval of record from disk using functions like fseek() and in memory optimization of traversing like applying binary search on leaf page. PostgreSQL always keeps a file for indexing, unlike MySQL where it only keeps an indexing file for non-clustered indexing, and clustered indexing is calculated directly from the table, it is interesting to study the indexing file structure as well Index file structure has line pointers, TID called as tuple ID Tuple is the column value, the same column you registered for indexing, by using which Postgres calculates the actual physical address of the records from the disk to fetch it directly. Postgres has another interesting feature, while creating an index, it always sorts data and maintains ranges of pages in page 0 of the indexing, where Postgres can identify which page to look for and then load that page into memory, and then it becomes a leaf page, then binary search is performed to get the actual TID so that the record can be fetched directly For an in-depth explanation about the index file structure, loading the index file into memory, and then searching for the actual record's address, check out the given link submitted by /u/Ok_Stomach6651 [link] [comments]