Master Python from the inside out. Here, we don't just write code; we look under the hood at memory management, data types, and logic, all while applying the mindfulness and philosophy of the Bhagavad Gita to our development journey.
DISTRIBUTED DEEP LEARNING — PART 1/5 Day 1: Demystifying GPU Memory & The ZeRO Revolution — DeepSpeed, FSDP & State Sharding 25 min read Series: The Dharma of Development Distributed DL (Day 1 / 5) Level: Principal / Systems AI Engineer 💥 Context: You attempt to fine-tune or pretrain a 13-billion parameter dense transformer on an 80GB NVIDIA H100 or A100 GPU using standard PyTorch. You set your batch size to 1. You press enter. Within three seconds, your terminal explodes with the most dreaded error in artificial intelligence: torch.cuda.OutOfMemoryError: CUDA out of memory . How is this possible? In 16-bit precision, 13 billion parameters occupy only ~26 GB of disk space. Why can't an 80 GB state-of-the-art GPU train a 26 GB model? Because the naive mental model of machine learning memory is deeply flawed. Today, we demystify the true $16\Phi$ memory footprint of neural networks and master the architecture that made modern large language models possi...
Day 9: B-Trees, Cardinality, and the Query Planner's Betrayal
Reading Time:
Series: Logic & Legacy
Progress: Day 9 of 30
Difficulty: Senior
A startup CTO once hired me because their primary dashboard was taking 14 seconds to load. He told me,
"I don't understand, I added an index to every single column in the table, and it actually got slower!"
This is the danger of textbook development.
Junior developers view indexes as magic "go fast" buttons. Senior developers view indexes as
physically duplicated B-Tree data structures that manipulate the disk. Today, we rip open the
database engine to understand exactly how a query is executed, and why the database optimizer
sometimes decides your index is garbage.
Visual comparison of Table Scans versus B+ Tree Indexing.
1. The Anatomy of a Query
When you send a SQL string to Postgres, it passes through three architectural layers:
The Parser: Checks your SQL for syntax errors.
The Query Planner / Optimizer: The brain of the database. It calculates the cheapest physical path to get your data.
The Executor: Physically reads the disk or RAM.
If your query is slow, it is because the Query Planner decided the best available physical path was still a terrible path.
2. The B+ Tree: The Engine of the Internet
When we say "Database Indexing," 99% of the time, we are talking about a B-Tree Index (specifically a B+ Tree).
3. Table Scans vs. Index Scans
In a Non-Clustered Index, the leaf node contains a pointer to the actual row in the main table data (the Heap).
Traverse the B-Tree to find the pointer.
Jump to the physical SSD to grab the row data.
A Clustered Index physically rearranges the table data to match the index order. The leaf node is the data.
4. Index Cardinality: The Optimizer's Betrayal
Cardinality is the uniqueness of data. High cardinality (UUIDs) is great for indexing; low cardinality (booleans) is often ignored by the optimizer.
Architect's Rule: Never index a column with low cardinality (booleans, genders, statuses) unless you are querying for the extremely rare minority case.
5. Composite Indexes & The Left-Prefix Rule
The order of columns in a composite index dictates its utility. Searching for the second column without the first breaks the index, forcing a Full Table Scan.
6. The Holy Grail: The Covering Index
A covering index includes the selected data directly in the leaf node, triggering an Index-Only Scan and avoiding disk jumps.
-- Example of an Index-Only Scan optimization
CREATE INDEX idx_users_id_covering ON users(id) INCLUDE (email);
SQL syntax for creating a covering index in PostgreSQL.
7. Day 9 Project: EXPLAIN ANALYZE
Stop guessing why your ORM is slow. Prove it. Run EXPLAIN ANALYZE on your slowest queries. Identify Seq Scan as the target for optimization.
PRO UPGRADE: BRIN INDEXES FOR TELEMETRY
For massive time-series telemetry data where B-Trees become too large for RAM, use Block Range Indexes (BRIN). They are 99% smaller and perfect for sequentially appended data.
🔥 DAY 10 TEASER: THE N+1 SILENT KILLER
We know how to index data. Now we look at how your framework pulls it out. Tomorrow, we conclude the Database trilogy by exposing the N+1 Query Problem—the silent ORM design flaw that destroys 90% of architectures in production.
Comments
Post a Comment
?: "90px"' frameborder='0' id='comment-editor' name='comment-editor' src='' width='100%'/>