Reference library

Database scaling & optimization

Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.

5 matches

Composite index leftmost prefix in Python

Simulate a composite index in SQLite and check whether query columns match the leftmost prefix rule for index usage.

import sqlite3


def get_indexed_columns(table_name):
    """Simulate a composite index by reading column names that start with 'idx_'."""
    conn = sqlite3.connect(":memory:")
    conn.execute(f"CREATE TABLE {table_name} (id INTEGER, idx_col1 TEXT, idx_col2 INTEGER, other TEXT)")
    conn.execute(f"CREATE INDEX idx_…

14 0 Open

Database scaling & optimization medium

Cross Shard Query Scatter Gather Mock in Python

Simulate a distributed database cross-shard query using a scatter-gather pattern with a mock Python implementation.

scatter-gather sharding distributed-systems

Python

from dataclasses import dataclass
from typing import List, Dict


@dataclass
class NodeResponse:
    node_id: int
    data: Dict[str, float]


def mock_query_shard(shard_id: int, shard_data: Dict[str, float], query: str) -> NodeResponse:
    """Simulate querying a single shard, returning matches whose value > 50."""
 …

13 0 Open

Database scaling & optimization medium

How to Create a Covering Index with INCLUDE Columns in Python

Create a covering index with INCLUDE columns in SQLite from Python and inspect the query plan to confirm the index covers the query.

sqlite indexing covering index

Python

import sqlite3

def create_covering_index_mock():
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()

    cursor.execute("""
        CREATE TABLE employees (
            id INTEGER PRIMARY KEY,
            name TEXT,
            department TEXT,
            salary INTEGER
        )
    """)

    employe…

15 0 Open

Database scaling & optimization medium

How to Eager Load with JOIN to Reduce N+1 Queries in Python

Demonstrates eager loading with a SQL JOIN to reduce N+1 query patterns down to a single database call when fetching related data.

eager-loading n-plus-1 join

Python

import sqlite3


def eager_load_join_reduce(mock_db_path=":memory:"):
    """Demonstrate eager loading where joins reduce query count from N+1 to 1."""
    conn = sqlite3.connect(mock_db_path)
    cursor = conn.cursor()
    cursor.executescript(
        """
        CREATE TABLE authors (id INTEGER PRIMARY KEY, name TE…

16 0 Open

Database scaling & optimization medium

How to Explain SQLite Query Plans in Python

Build a Python function that runs EXPLAIN QUERY PLAN on SQLite in-memory tables and prints the optimizer's execution plan for any SELECT statement.

sqlite query-plan optimization

Python

import sqlite3

def explain_query(sql: str) -> str:
    """Return the SQLite query plan for the given SQL statement."""
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()
    
    # Create sample data for a realistic plan
    cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
    c…

14 0 Open

Browse by section

Each section groups closely related Python snippets.