Query Processing, Indexing (B-tree, Hashing), and Advanced Database Systems

Query Processing

Definition: Activities to transform high-level query into efficient execution plan

Steps:

1. Parsing and Translation

   • Check syntax
   • Verify relations and attributes exist
   • Translate SQL to internal representation (relational algebra)

2. Optimization

   • Generate alternative execution plans
   • Estimate cost of each plan
   • Choose plan with lowest cost

3. Evaluation

   • Execute chosen plan
   • Return results to user

Why Optimization Matters:

• Same query, different plans
• Execution time can vary by orders of magnitude
• Example:
  • Bad plan: 10 minutes
  • Good plan: 1 second
  • 600x speedup!

Indexing

Purpose: Fast data retrieval without scanning entire table

Analogy: Book index vs reading cover to cover

Trade-offs:

• Benefit: Fast SELECT queries
• Cost: Slower INSERT/UPDATE/DELETE, storage overhead

Types:

1. B-tree Index: Balanced tree, range queries
2. Hash Index: Hash table, equality queries
3. Bitmap Index: Bit vectors, low-cardinality columns
4. Full-text Index: Text search

Advanced Database Systems

Beyond traditional single-server relational databases:

1. Distributed Databases: Data across multiple sites
2. Data Warehouses: Historical data for analytics
3. NoSQL: Non-relational, scalable
4. Mobile Databases: Lightweight, synchronization
5. Web Databases: Internet-scale, cloud-native

COMPREHENSIVE EXAMPLES

EXAMPLE 1: Query Optimization

Original Query:

SELECT E.Name, D.DeptName
FROM Employee E, Department D, Salary S
WHERE E.DeptID = D.DeptID
  AND E.EmpID = S.EmpID
  AND D.DeptName = 'IT'
  AND S.Salary > 50000;

Inefficient Plan:

1. Employee × Department × Salary  -- Huge cartesian product!
2. σ(DeptName='IT' ∧ Salary>50000)
3. π(Name, DeptName)

Cost: |Employee| × |Department| × |Salary| scans

Optimized Plan:

1. σ(DeptName='IT')(Department)  -- Filter early: 1 dept
2. σ(Salary>50000)(Salary)       -- Filter early: ~10% of rows
3. Result1 ⋈(DeptID) Employee    -- Join filtered dept with employees
4. Result2 ⋈(EmpID) Result2      -- Join with filtered salaries
5. π(Name, DeptName)(Result3)

Improvements:

• Push selections down (reduce intermediate results)
• Replace cartesian products with joins
• Use indexes on DeptName and Salary

Cost Reduction: 1000x or more!

EXAMPLE 2: Join Algorithm Selection

Query:

SELECT *
FROM Orders O, Customer C
WHERE O.CustomerID = C.CustomerID;

Scenario A: No Index

• Orders: 1M rows, 10,000 blocks
• Customer: 100K rows, 1,000 blocks

Block Nested-Loop:

Cost = 10,000 + 10,000 × 1,000 = 10,010,000 block reads

Sort-Merge (M=100 buffers):

Sort Orders: 2 × 10,000 × ⌈log₉₉(100)⌉ = 40,000
Sort Customer: 2 × 1,000 × ⌈log₉₉(10)⌉ = 2,000
Merge: 10,000 + 1,000 = 11,000
Total = 53,000 block reads

Hash Join:

Partition: 2 × (10,000 + 1,000) = 22,000
Join: 10,000 + 1,000 = 11,000
Total = 33,000 block reads

Winner: Hash Join (300x faster than Nested-Loop!)

Scenario B: Index on Customer.CustomerID

Indexed Nested-Loop:

Cost = 10,000 + 1,000,000 × 2 = 2,010,000 block reads
(For each Order, 2 reads: index + data)

Winner (B): Still Hash Join! (16x faster)

EXAMPLE 3: B+-tree Operations

B+-tree (order 3, max 3 keys per node):

Initial Tree:

             [20]
            /    \
       [10,15]  [25,30]
       /  |  \    /  |  \
    [5,7][12,13][18,19][22,23][27,28][32,35]
     ↓     ↓       ↓      ↓      ↓      ↓
  (leaves linked)

Insert 14:

1. Search finds leaf [12,13]
2. Leaf full (3 keys max), must split
3. Split [12,13,14] → [12] and [13,14]
4. Promote 13 to parent [10,15] → [10,13,15]

Result:

             [20]
            /    \
      [10,13,15]  [25,30]
      /  |  |  \    /  |  \
   [5,7][12][13,14][18,19][22,23][27,28][32,35]

Insert 16, 17 (causes cascade):

Leaf [18,19] becomes [16,17,18,19] (overflow!)
Split → [16,17] and [18,19], promote 18
Parent [10,13,15] becomes [10,13,15,18] (overflow!)
Split parent → [10,13] and [15,18], promote 15 to root
Root [20] becomes [15,20]

Result:

           [15,20]
          /   |   \
    [10,13] [15,18] [25,30]
      ...     ...     ...

Delete 12:

1. Remove 12 from leaf [12]
2. Leaf empty (underflow!)
3. Try to borrow from sibling [13,14]
4. Success: Redistribute → [13] and [14]
5. Update parent key 13 → 14

EXAMPLE 4: Hash Index

Static Hash (M=5 buckets):

h(K) = K mod 5

Insert: 5, 15, 25, 3, 8, 13, 18, 23, 28

Bucket 0: 5 → 15 → 25 (overflow chain!)
Bucket 1: empty
Bucket 2: empty
Bucket 3: 3 → 8 → 13 → 18 → 23 → 28 (long chain!)
Bucket 4: empty

Problem: Clustering, poor distribution

Extendible Hash (d=2):

h(K) = last 2 bits of K

Directory (2² = 4 entries):
00 → Bucket A: [4, 8, 12]
01 → Bucket B: [5, 9, 13]
10 → Bucket C: [6, 10, 14]
11 → Bucket D: [7, 11, 15]

Insert 16 (binary: ...10000, last 2 bits: 00):
- Goes to Bucket A
- Bucket A full (assume capacity=3)

Split:
- Create Bucket A' for keys with last 3 bits 000
- Keep Bucket A for keys with last 3 bits 100
- Increase directory depth d=3

New Directory (2³ = 8 entries):
000 → Bucket A': [8]
001 → Bucket B
010 → Bucket C
011 → Bucket D
100 → Bucket A: [4, 12, 16]
101 → Bucket B
110 → Bucket C
111 → Bucket D

EXAMPLE 5: Distributed Query

Query:

SELECT *
FROM Orders O, Customer C
WHERE O.CustomerID = C.CustomerID
  AND C.City = 'Mumbai';

Data Distribution:

• Orders: Horizontally partitioned by region (Site 1: North, Site 2: South)
• Customer: Replicated at all sites

Strategy 1: Centralized

1. Ship Orders from Site 1 to coordinator
2. Ship Orders from Site 2 to coordinator
3. Join at coordinator

Cost: Transfer all Orders (expensive!)

Strategy 2: Distributed Join

1. Apply σ(City='Mumbai')(Customer) at each site
2. Join locally at each site
3. Union results

Cost: Transfer only Mumbai customers (small!)

Example Numbers:

• Orders: 1M rows × 1KB = 1GB
• Customers: 100K rows × 500B = 50MB
• Mumbai Customers: 5K rows = 2.5MB

Cost Comparison:

• Centralized: 1GB network transfer
• Distributed: 2.5MB network transfer (400x less!)

EXAMPLE 6: Star Schema

Fact Table: FactSales

CREATE TABLE FactSales (
    SaleID INT PRIMARY KEY,
    DateKey INT,           -- FK to DimDate
    ProductKey INT,        -- FK to DimProduct
    CustomerKey INT,       -- FK to DimCustomer
    StoreKey INT,          -- FK to DimStore
    Quantity INT,
    TotalAmount DECIMAL(10,2)
);

Dimension Tables:

CREATE TABLE DimDate (
    DateKey INT PRIMARY KEY,
    Date DATE,
    Day INT,
    Month INT,
    Quarter INT,
    Year INT,
    DayOfWeek VARCHAR(10)
);

CREATE TABLE DimProduct (
    ProductKey INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    Brand VARCHAR(50),
    UnitPrice DECIMAL(10,2)
);

CREATE TABLE DimCustomer (
    CustomerKey INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    Country VARCHAR(50)
);

CREATE TABLE DimStore (
    StoreKey INT PRIMARY KEY,
    StoreName VARCHAR(100),
    City VARCHAR(50),
    Manager VARCHAR(100)
);

OLAP Query:

-- Total sales by quarter and product category
SELECT 
    D.Year,
    D.Quarter,
    P.Category,
    SUM(F.TotalAmount) AS TotalSales
FROM FactSales F
JOIN DimDate D ON F.DateKey = D.DateKey
JOIN DimProduct P ON F.ProductKey = P.ProductKey
WHERE D.Year = 2024
GROUP BY D.Year, D.Quarter, P.Category
ORDER BY D.Quarter, TotalSales DESC;

Roll-Up (Aggregate to Year):

SELECT 
    D.Year,
    P.Category,
    SUM(F.TotalAmount) AS TotalSales
FROM FactSales F
JOIN DimDate D ON F.DateKey = D.DateKey
JOIN DimProduct P ON F.ProductKey = P.ProductKey
WHERE D.Year = 2024
GROUP BY D.Year, P.Category;

Drill-Down (To Month):

SELECT 
    D.Year,
    D.Month,
    P.Category,
    SUM(F.TotalAmount) AS TotalSales
FROM FactSales F
JOIN DimDate D ON F.DateKey = D.DateKey
JOIN DimProduct P ON F.ProductKey = P.ProductKey
WHERE D.Year = 2024 AND D.Quarter = 1
GROUP BY D.Year, D.Month, P.Category;

EXAMPLE 7: Association Rule Mining

Transaction Database:

T1: {Bread, Milk, Eggs}
T2: {Bread, Butter}
T3: {Milk, Butter, Eggs}
T4: {Bread, Milk, Butter}
T5: {Bread, Milk, Eggs, Butter}

Apriori Algorithm (min_support=40%, min_confidence=60%):

Step 1: Find frequent 1-itemsets

{Bread}: 4/5 = 80% ✓
{Milk}: 4/5 = 80% ✓
{Butter}: 4/5 = 80% ✓
{Eggs}: 3/5 = 60% ✓

Step 2: Generate frequent 2-itemsets

{Bread, Milk}: 3/5 = 60% ✓
{Bread, Butter}: 3/5 = 60% ✓
{Milk, Butter}: 3/5 = 60% ✓
{Milk, Eggs}: 3/5 = 60% ✓
{Bread, Eggs}: 2/5 = 40% ✓
{Butter, Eggs}: 2/5 = 40% ✓

Step 3: Generate frequent 3-itemsets

{Bread, Milk, Butter}: 2/5 = 40% ✓
{Bread, Milk, Eggs}: 2/5 = 40% ✓
{Milk, Butter, Eggs}: 2/5 = 40% ✓

Step 4: Generate association rules

{Bread, Milk} ⇒ {Butter}: 
  Support = 40%, Confidence = 2/3 = 67% ✓

{Bread, Butter} ⇒ {Milk}:
  Support = 40%, Confidence = 2/3 = 67% ✓

{Milk, Butter} ⇒ {Bread}:
  Support = 40%, Confidence = 2/3 = 67% ✓

{Milk, Eggs} ⇒ {Bread}:
  Support = 40%, Confidence = 2/3 = 67% ✓

Actionable Insight:

• Place Bread, Milk, Butter close together
• Bundle discount for combo purchase

EXAMPLE 8: Mobile Database Synchronization

Scenario: Sales app with offline capability

Local SQLite (Mobile):

CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER,
    Amount DECIMAL(10,2),
    Status TEXT,
    CreatedAt DATETIME,
    ModifiedAt DATETIME,
    SyncStatus TEXT  -- 'pending', 'synced', 'conflict'
);

Server Database (PostgreSQL):

CREATE TABLE Orders (
    OrderID SERIAL PRIMARY KEY,
    CustomerID INTEGER,
    Amount DECIMAL(10,2),
    Status TEXT,
    CreatedAt TIMESTAMP,
    ModifiedAt TIMESTAMP,
    Version INTEGER  -- For conflict detection
);

Sync Process:

1. Upload (Mobile → Server):

def sync_upload():
    pending = db.execute("SELECT * FROM Orders WHERE SyncStatus='pending'")
    for order in pending:
        try:
            # Send to server
            response = api.post('/orders/sync', order)
            if response.status == 'success':
                db.execute("UPDATE Orders SET SyncStatus='synced' WHERE OrderID=?", order.id)
            elif response.status == 'conflict':
                db.execute("UPDATE Orders SET SyncStatus='conflict' WHERE OrderID=?", order.id)
        except NetworkError:
            break  # Will retry later

2. Download (Server → Mobile):

def sync_download():
    last_sync = get_last_sync_time()
    changes = api.get(f'/orders/changes?since={last_sync}')
    
    for change in changes:
        local = db.execute("SELECT * FROM Orders WHERE OrderID=?", change.id)
        
        if not local:
            # New record
            db.insert(change)
        elif local.ModifiedAt < change.ModifiedAt:
            # Server newer
            db.update(change)
        else:
            # Local newer (conflict)
            handle_conflict(local, change)

Conflict Resolution (Last Write Wins):

def handle_conflict(local, server):
    if server.ModifiedAt > local.ModifiedAt:
        # Server wins
        db.update(server)
    else:
        # Local wins, push again
        db.execute("UPDATE Orders SET SyncStatus='pending' WHERE OrderID=?", local.id)

EXAMPLE 9: NoSQL Data Modeling

Relational (Normalized):

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50),
    Email VARCHAR(100)
);

CREATE TABLE Posts (
    PostID INT PRIMARY KEY,
    UserID INT,
    Title VARCHAR(200),
    Content TEXT,
    CreatedAt DATETIME,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

CREATE TABLE Comments (
    CommentID INT PRIMARY KEY,
    PostID INT,
    UserID INT,
    Text TEXT,
    CreatedAt DATETIME,
    FOREIGN KEY (PostID) REFERENCES Posts(PostID)
);

Query (3 joins!):

SELECT U.Username, P.Title, C.Text
FROM Users U
JOIN Posts P ON U.UserID = P.UserID
JOIN Comments C ON P.PostID = C.PostID
WHERE U.UserID = 123;

NoSQL (MongoDB - Denormalized):

{
  "_id": ObjectId("..."),
  "username": "john_doe",
  "email": "john@example.com",
  "posts": [
    {
      "postId": 1,
      "title": "My First Post",
      "content": "Hello world!",
      "createdAt": ISODate("2024-01-15"),
      "comments": [
        {
          "commentId": 1,
          "userId": 456,
          "username": "jane_doe",
          "text": "Great post!",
          "createdAt": ISODate("2024-01-15")
        }
      ]
    }
  ]
}

Query (No joins!):

db.users.findOne({ _id: 123 })

Trade-offs:

• Pros: Faster reads (no joins), natural document structure
• Cons: Data duplication, update complexity (update username everywhere)

EXAMPLE 10: Cloud Database Auto-Scaling

AWS Aurora Configuration:

# Application Load
Time    Connections    CPU%    Action
08:00   100           20%     Normal (1 reader)
10:00   500           60%     Add reader (2 readers)
12:00   1500          80%     Add reader (3 readers)
14:00   2000          90%     Add reader (4 readers)
16:00   800           40%     Remove reader (3 readers)
20:00   200           25%     Remove reader (2 readers)
00:00   50            10%     Remove reader (1 reader)

Auto-Scaling Policy:

{
  "minCapacity": 1,
  "maxCapacity": 5,
  "scaleUpThreshold": {
    "cpu": 70,
    "connections": 1000
  },
  "scaleDownThreshold": {
    "cpu": 30,
    "connections": 200,
    "cooldownMinutes": 15
  }
}

Cost Optimization:

• Peak hours (10:00-16:00): 4 instances × $0.50/hr = $2.00/hr × 6hrs = $12
• Off-peak: 1-2 instances × $0.50/hr = ~$0.75/hr × 18hrs = $13.50
• Daily Total: ~$25.50
• vs Fixed 4 instances: $48/day
• Savings: ~47%

PRACTICAL GUIDELINES

1. Query Optimization Tips:

Use EXPLAIN/EXPLAIN ANALYZE:

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM Orders WHERE CustomerID = 123;

-- MySQL
EXPLAIN
SELECT * FROM Orders WHERE CustomerID = 123;

Output Analysis:

• Seq Scan: Full table scan (BAD for large tables)
• Index Scan: Using index (GOOD)
• Index Only Scan: Only index, no table access (BEST)
• Nested Loop: Good for small joins
• Hash Join: Good for large equi-joins
• Merge Join: Good for pre-sorted data

Common Problems and Fixes:

Problem: Full Table Scan

-- BAD
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;
-- Function on column prevents index use

-- GOOD
SELECT * FROM Orders 
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';
-- Index on OrderDate can be used

Problem: Type Mismatch

-- BAD
SELECT * FROM Orders WHERE OrderID = '123';  -- String for INT column

-- GOOD
SELECT * FROM Orders WHERE OrderID = 123;

Problem: OR Conditions

-- BAD
SELECT * FROM Orders 
WHERE Status = 'Pending' OR Status = 'Shipped';

-- GOOD
SELECT * FROM Orders 
WHERE Status IN ('Pending', 'Shipped');
-- Or even better with separate queries and UNION if selective

2. Indexing Strategy:

When to Create Index:

• Columns in WHERE clause
• Columns in JOIN conditions
• Columns in ORDER BY/GROUP BY
• Foreign keys

When NOT to Create Index:

• Small tables (< 1000 rows)
• Columns with low selectivity (e.g., Gender: M/F)
• Frequently updated columns
• Rarely queried columns

Index Types by Use Case:

-- B-tree (default): Range queries, sorted access
CREATE INDEX idx_orders_date ON Orders(OrderDate);

-- Hash: Exact match only (PostgreSQL)
CREATE INDEX idx_customers_email ON Customers USING HASH (Email);

-- Covering index: Includes all queried columns
CREATE INDEX idx_orders_covering ON Orders(CustomerID, OrderDate, TotalAmount);

-- Partial index: Only subset of rows
CREATE INDEX idx_active_orders ON Orders(OrderDate) 
WHERE Status = 'Active';

-- Composite index: Multiple columns
CREATE INDEX idx_orders_customer_date ON Orders(CustomerID, OrderDate);
-- Good for: WHERE CustomerID=? AND OrderDate=?
-- NOT for: WHERE OrderDate=? (wrong order!)

Index Maintenance:

-- PostgreSQL: Rebuild bloated index
REINDEX INDEX idx_orders_date;

-- MySQL: Rebuild table and indexes
OPTIMIZE TABLE Orders;

-- Check index usage
SELECT 
    schemaname, tablename, indexname, 
    idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- Never used!

3. Distributed Database Best Practices:

Data Partitioning:

-- PostgreSQL: Declarative partitioning
CREATE TABLE Orders (
    OrderID BIGINT,
    OrderDate DATE,
    ...
) PARTITION BY RANGE (OrderDate);

CREATE TABLE Orders_2023 PARTITION OF Orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE Orders_2024 PARTITION OF Orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Benefits:

• Query only relevant partitions
• Drop old partitions easily
• Better manageability

Replication Configuration:

-- PostgreSQL: Streaming replication
-- On primary
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 5;

-- On replica
primary_conninfo = 'host=primary_host port=5432 user=replicator'
hot_standby = on

Load Balancing:

# Application-level read/write splitting
def get_db_connection(readonly=False):
    if readonly:
        return connect_to_replica()  # Read from replica
    else:
        return connect_to_primary()  # Write to primary

# Usage
with get_db_connection(readonly=True) as conn:
    results = conn.execute("SELECT ...")  # Read query

with get_db_connection(readonly=False) as conn:
    conn.execute("UPDATE ...")  # Write query
    conn.commit()

4. Data Warehouse Development:

ETL Best Practices:

Incremental Load (Better than Full Load):

-- Track last processed timestamp
CREATE TABLE ETL_Metadata (
    TableName VARCHAR(50) PRIMARY KEY,
    LastLoadTime TIMESTAMP
);

-- Extract only new/modified records
INSERT INTO DW_Orders
SELECT *
FROM OLTP_Orders
WHERE ModifiedAt > (
    SELECT LastLoadTime 
    FROM ETL_Metadata 
    WHERE TableName = 'Orders'
);

-- Update metadata
UPDATE ETL_Metadata
SET LastLoadTime = CURRENT_TIMESTAMP
WHERE TableName = 'Orders';

Slowly Changing Dimensions (SCD Type 2):

CREATE TABLE DimCustomer (
    CustomerKey INT PRIMARY KEY,  -- Surrogate key
    CustomerID INT,                -- Business key
    CustomerName VARCHAR(100),
    City VARCHAR(50),
    ValidFrom DATE,
    ValidTo DATE,
    IsCurrent BOOLEAN
);

-- When customer moves to new city
-- Old record
UPDATE DimCustomer
SET ValidTo = CURRENT_DATE, IsCurrent = FALSE
WHERE CustomerID = 123 AND IsCurrent = TRUE;

-- New record
INSERT INTO DimCustomer (CustomerID, CustomerName, City, ValidFrom, IsCurrent)
VALUES (123, 'John Doe', 'New York', CURRENT_DATE, TRUE);

-- Now fact table maintains history
-- FactSales joins to CustomerKey (not CustomerID)
-- Can report sales by customer's city at time of sale

Aggregation Tables (OLAP Cubes):

-- Pre-aggregate for common queries
CREATE TABLE AggSalesByMonth AS
SELECT 
    DATE_TRUNC('month', OrderDate) AS Month,
    ProductID,
    SUM(Quantity) AS TotalQuantity,
    SUM(TotalAmount) AS TotalRevenue
FROM FactSales
GROUP BY Month, ProductID;

CREATE INDEX idx_agg_month ON AggSalesByMonth(Month, ProductID);

-- Query aggregate (much faster!)
SELECT * FROM AggSalesByMonth
WHERE Month = '2024-01-01';

5. Mobile Database Patterns:

Offline-First Architecture:

// React Native + SQLite
import SQLite from 'react-native-sqlite-storage';

class OfflineDB {
  constructor() {
    this.db = SQLite.openDatabase({ name: 'app.db' });
  }
  
  async createOrder(order) {
    // Always save locally first
    const localId = await this.db.executeSql(
      'INSERT INTO Orders (data, syncStatus) VALUES (?, ?)',
      [JSON.stringify(order), 'pending']
    );
    
    // Try to sync immediately if online
    if (await this.isOnline()) {
      this.syncPendingOrders();
    }
    
    return localId;
  }
  
  async syncPendingOrders() {
    const pending = await this.db.executeSql(
      'SELECT * FROM Orders WHERE syncStatus = ?',
      ['pending']
    );
    
    for (let order of pending.rows) {
      try {
        const response = await fetch('/api/orders', {
          method: 'POST',
          body: order.data
        });
        
        if (response.ok) {
          await this.db.executeSql(
            'UPDATE Orders SET syncStatus = ? WHERE id = ?',
            ['synced', order.id]
          );
        }
      } catch (error) {
        // Network error, will retry later
        console.log('Sync failed, will retry');
      }
    }
  }
  
  async isOnline() {
    return navigator.onLine;
  }
}

// Periodic background sync
setInterval(() => {
  if (db.isOnline()) {
    db.syncPendingOrders();
  }
}, 60000);  // Every minute

6. Performance Monitoring:

Key Metrics:

-- PostgreSQL: Slow queries
SELECT 
    query,
    calls,
    total_time / calls AS avg_time,
    min_time,
    max_time
FROM pg_stat_statements
WHERE total_time / calls > 1000  -- > 1 second avg
ORDER BY total_time DESC
LIMIT 10;

-- Cache hit ratio (should be > 90%)
SELECT 
    sum(blks_hit) * 100.0 / sum(blks_hit + blks_read) AS cache_hit_ratio
FROM pg_stat_database;

-- Index usage
SELECT 
    schemaname, tablename,
    idx_scan,  -- Index scans
    seq_scan,  -- Sequential scans
    idx_scan / (seq_scan + idx_scan + 0.001) AS index_usage_ratio
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- Table bloat
SELECT 
    schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

7. Cloud Database Cost Optimization:

Strategies:

Use Read Replicas for Read-Heavy Workloads:

Primary (Expensive): Writes only
Replica 1, 2, 3 (Cheaper): Distribute reads

Schedule Non-Production Databases:

# Stop dev/test databases outside business hours
aws rds stop-db-instance --db-instance-identifier dev-db

# Schedule with Lambda + CloudWatch Events
# Save ~60% on dev/test costs

Right-Size Instances:

# Monitor actual usage
aws cloudwatch get-metric-statistics \
    --namespace AWS/RDS \
    --metric-name CPUUtilization \
    --dimensions Name=DBInstanceIdentifier,Value=my-db

# If consistently < 40%, downgrade instance
# db.m5.xlarge ($280/mo) → db.m5.large ($140/mo)

Use Reserved Instances:

On-Demand: $0.50/hr = $360/mo
1-Year Reserved: $0.30/hr = $216/mo (40% savings)
3-Year Reserved: $0.20/hr = $144/mo (60% savings)

Archive Old Data:

-- Move old data to cheaper storage (S3)
-- Orders > 2 years old
COPY (
    SELECT * FROM Orders 
    WHERE OrderDate < CURRENT_DATE - INTERVAL '2 years'
) TO '/tmp/old_orders.csv';

-- Upload to S3, then delete from DB
DELETE FROM Orders 
WHERE OrderDate < CURRENT_DATE - INTERVAL '2 years';

-- Query old data from S3 (AWS Athena) when needed