PostgreSQL Deep Dive Part 11 — Understanding Database Indexing Fundamentals

-- Creating a basic index
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_email on users using btree(email); -- this is an alternate way of creating index
 
-- Behind the scenes, PostgreSQL:
-- 1. Creates a new file on disk (to understand more check Part 13)
-- 2. Organizes email values in B-tree structure
-- 3. Maintains pointers (CTIDs) back to the heap

-- Create a sample table
CREATE TABLE books (
    id BIGINT PRIMARY KEY,
    title TEXT,
    author TEXT,
    published_date DATE
);
 
-- Examining the heap structure
SELECT *, ctid FROM books;
 
-- Result:
/*
| id     | title      | author     | published_date     | ctid      |
|--------|------------|------------|--------------------|-----------|
| 1      | Book A     | Auth A     | 2024-01-01         | (0,1)     |
| 2      | Book B     | Auth B     | 2024-01-02         | (0,2)     |
*/

-- CTIDs can change after VACUUM
-- (will be talking about VACUUM in coming parts)
VACUUM books;
 
SELECT *, ctid FROM books;
-- Notice how CTIDs might be different now

                Root Node
                   |
         |-------------------|
    Internal Node      Internal Node
         |                  |
   |-----------|      |-----------|
Leaf Node  Leaf Node  Leaf Node  Leaf Node

🎄

-- Creating a B-tree index with visualization options
CREATE INDEX CONCURRENTLY idx_books_date ON books USING btree(published_date);
 
-- Examining index statistics
SELECT * FROM pg_stats
WHERE tablename = 'books'
			AND
			attname = 'published_date';
 
-- Regular Index Creation
CREATE INDEX idx_name ON users(email);
-- Takes exclusive lock and blocks writes (INSERT/UPDATE/DELETE)
-- Faster to create but requires planned downtime for production systems
 
-- Concurrent Index Creation
CREATE INDEX CONCURRENTLY idx_name ON users(email);
-- Allows writes to continue during index creation, perfect for production
-- Takes ~3x longer but prevents application downtime during creation

-- PostgreSQL's Unique Heap Architecture: Understanding Primary Keys 🎯
 
-- Traditional Thinking: Data Organization
-- Most developers from MySQL expect:
--   "Primary Key = Data Organization"
-- Because in MySQL, the primary key determines data layout on disk
 
-- PostgreSQL's Different Philosophy:
-- 1. The Heap Approach
--    - All data lives in "heap" storage
--    - Think of heap as a large storage room for data pages
--    - Data placement is independent of indexes
--    - Even primary keys don't affect physical storage
 
-- 2. Index Navigation
--    Step 1: Look up value in index (including primary key)
--    Step 2: Get CTID (physical location pointer)
--    Step 3: Jump to heap to fetch the row
--    This process applies to ALL indexes!
 
-- Primary Keys in PostgreSQL: More Than Meets the Eye
-- When you create a primary key:
CREATE TABLE products (
    id BIGINT PRIMARY KEY,  -- This does multiple things!
    name TEXT
);
 
-- Behind the scenes, PostgreSQL:
-- 1. Creates a UNIQUE constraint
-- 2. Adds a NOT NULL constraint
-- 3. Creates a standard B-tree index
-- 4. Set up system-level primary key tracking
 
-- The Freedom of Secondary Indexes
-- Since primary keys don't determine storage:
CREATE INDEX idx_products_name ON products(name);  -- Just another index
CREATE INDEX idx_products_id ON products(id);      -- Structurally same as PK!
 
-- All indexes (including PK) are "secondary" because:
-- - They point to heap via CTIDs
-- - They don't determine physical data layout
-- - They have equal lookup overhead (index → heap)
 
-- MySQL vs PostgreSQL: A Clear Comparison
-- MySQL's Approach:
-- - Primary key creates a clustered index
-- - Data physically organizes based on this index
-- - Table becomes one large B-tree where:
--   - Primary key orders the data
--   - Complete rows live in leaf nodes
--   - Other indexes must reference the clustered index
 
-- PostgreSQL's Approach:
-- - All data lives in the heap (think: organized storage room)
-- - Every index (including primary keys) is secondary
-- - Each index has CTIDs (like map coordinates) pointing to heap
-- - Primary keys are simply unique, non-null indexes
 
-- The Key Distinction:
-- MySQL: Primary key determines data organization (clustered)
-- PostgreSQL: Data organization stays independent (heap-based)
-- Result: PostgreSQL treats all indexes equally, with direct data pointers

-- Every index lookup requires heap access
EXPLAIN SELECT * FROM products WHERE id = 1;
 
-- Unless it's an index-only scan
EXPLAIN SELECT id FROM products WHERE id = 1;

-- Checking index sizes
SELECT
    pg_relation_size('products') as table_size,
    pg_relation_size('products_pkey') as pk_index_size,
    pg_relation_size('idx_products_name') as name_index_size;
 
-- Analyzing index statistics
SELECT
    schemaname,
    tablename,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'products';