Loading Academy Hub...
Master database physical storage structures: B-Tree vs Bitmap vs GIN indexes, composite index column order, table partitioning, and partition pruning verification.
[!NOTE] In transactional (OLTP) systems, indexes provide point lookups. In analytical data warehouses (Snowflake, BigQuery, Redshift), micro-partition pruning and clustered keys eliminate scanning billions of irrelevant rows.
| Index Type | Complexity | Best Suited For | Caveats |
|---|---|---|---|
| B-Tree | High cardinality (UUID, User ID, Timestamp) | Degrades write performance | |
| Bitmap | Bitwise AND/OR | Low cardinality (Status, Country, Gender) | High lock contention during updates |
| Hash Index | Exact equality (=), no range scans | Does not support <, >, BETWEEN | |
| BRIN (Block Range) | Metadata min/max | Huge append-only sorted logs | Tiny footprint, ineffective on unsorted data |
(tenant_id, created_at) dictates index selectivity). Cloud analytical engines (Snowflake, BigQuery, Databricks Delta) store immutable columnar Micro-Partitions / Parquet Row Groups without row-level B-Trees—using Zone Maps (Min/Max stats) + Partition Pruning + Clustering/Z-ORDER instead!(country, created_at, status) speeds up WHERE country = 'IN' AND created_at > ... cleanly, but cannot seek efficiently on WHERE created_at > ... alone unless country is constrained or index skip-scan applies.Review the production code, company interview Q&As, incident traces, and comprehension quiz inside this lesson.
-- 1. Create partitioned transactional table
CREATE TABLE sales_orders (
order_id BIGINT,
customer_id INT,
order_date DATE,
amount NUMERIC(12, 2)
) PARTITION BY RANGE (order_date);
-- 2. Create monthly child partitions
CREATE TABLE sales_2026_01 PARTITION OF sales_orders
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
-- 3. Composite Index: Most selective column first (customer_id, order_date)
CREATE INDEX idx_sales_cust_date ON sales_orders (customer_id, order_date);
-- 4. Query that triggers BOTH partition pruning AND index seek
SELECT * FROM sales_orders
WHERE order_date >= '2026-01-15'
AND customer_id = 45201;EXPLAIN: Seq Scan on users (cost=0.00..45021.00 rows=150000 width=64)