Continuation to the previous blog. 28. The “Heavy Data” Reality: TSVectors While working on my topic table, I noticed I’m building a search_vector column using to_tsvector(). That immediately adds a completely new layer to the “8 KB page” math I discussed earlier. Until now, my examples were based on small index keys like UUIDs. But full-text search is a different beast. Why Full-Text Search Becomes Heavy A UUID is small: ...
16. Visualizing the “Freed Up” Space Let’s visualize a row of pointers inside our 8 KB Page. By shrinking each “seat” from 8 bytes to 6 bytes, we allow the next pointer to start sooner. Scenario A: 8-byte Pointers [Ptr 1: 8 bytes] [Ptr 2: 8 bytes] [Ptr 3: 8 bytes] Total used for 3 pointers: 24 bytes. Scenario B: 6-byte Pointers [Ptr 1: 6 bytes] [Ptr 2: 6 bytes] [Ptr 3: 6 bytes] [Ptr 4: 6 bytes] Total used for 4 pointers: 24 bytes. ...
6. The “Handshake”: Why OS and Database Page Sizes Differ When you run getconf PAGESIZE on Ubuntu, you see 4096 (4 KB). Yet, your database often uses 8 KB or 16 KB. If the RAM can process data quickly, why is there this mismatch? The Generalist (Ubuntu) vs. The Specialist (Database) Ubuntu (4 KB): An Operating System is a generalist. It manages everything from tiny config files to massive videos. If the OS used 16 KB pages, a 1 KB text file would still take up 16 KB of RAM—wasting 94% of that space. 4 KB is the “Goldilocks” size for general computing. Database (8 KB+): A database is a specialist. It knows its files are massive. By using a larger Logical Page, it increases Fan-out (the number of pointers per page). This keeps the B-Tree “short and fat” rather than “tall and skinny,” reducing the number of disk reads. How They Sync There is no “clash” because these sizes are multiples. When the Database asks for one 8 KB page, Ubuntu simply fetches two 4 KB hardware blocks. ...
The Fundamental Problem: The Full Table Scan Imagine a table with 10 million rows. To find a specific ID without an index, the database must perform a Full Table Scan—checking every single row from 1 to 10,000,000. This is $O(n)$ complexity. Indexing transforms this into a “search in a book” experience using a data structure called a B-Tree. 1. What is an Index? At its core, an index is a separate, sorted data structure that maps a specific value (like an ID or Name) to a Pointer (the physical address of the row on disk). ...