Reference library

Database scaling & optimization

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

6 matches

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

Database scaling & optimization medium

How to Mock a Cross-Shard Saga in Python

Simulate a distributed saga with compensating transactions across multiple database shards using a lightweight Python class that tracks executed steps and rolls them back in reverse on failure.

saga sharding distributed-systems

Python

import json


class SagaState:
    def __init__(self, saga_id):
        self.saga_id = saga_id
        self.executed_steps = []
        self.compensations = []

    def execute_step(self, shard, step_name, operation):
        self.executed_steps.append((shard, step_name))
        print(f"[Saga {self.saga_id}] Executin…

14 0 Open

Database scaling & optimization medium

How to Simulate Colocated Shard Joins in Python

Groups shards by their node and merges co-located shards into a single logical unit, checking capacity constraints.

sharding database distributed-systems

Python

import random
from collections import defaultdict


def simulate_colocated_shards_join(nodes: list[dict], shards: list[dict]) -> dict:
    """
    Simulates the join of co-located shards (on the same node) into a single
    logical shard. Returns the resulting node-to-shard mapping.

    Each node: {'id': str, 'capaci…

11 0 Open

Database scaling & optimization medium

How to Simulate Distributed Transactions in Python with a Mock

Model distributed transaction behavior with a mock Transaction class that supports commit, rollback, and failure simulation.

transactions mock database

Python

class Transaction:
    def __init__(self, id):
        self.id = id
        self.operations = []
        self.committed = False

    def add_operation(self, op, data):
        self.operations.append((op, data))

    def commit(self):
        if not self.operations:
            raise ValueError("No operations to commit…

13 0 Open

Database scaling & optimization medium

Snowflake ID Generator with Cluster Index Mock in Python

A thread-safe Snowflake ID generator mock that creates unique 64-bit IDs across simulated cluster nodes and maintains a sorted in-memory index for range queries.

snowflake id-generation clustering

Python

import time
import threading

class SnowflakeIDGenerator:
    def __init__(self, machine_id, datacenter_id):
        self.machine_id = machine_id
        self.datacenter_id = datacenter_id
        self.sequence = 0
        self.last_timestamp = -1
        self.machine_bits = 5
        self.datacenter_bits = 5
        …

13 0 Open

Database scaling & optimization medium

Two Phase Commit Cross Shard Mock in Python

Simulates a two-phase commit across shards with failure handling to demonstrate distributed transaction coordination in Python.

two-phase-commit distributed-systems transaction

Python

"""Mock cross-shard two-phase commit with caution handling."""

class Shard:
    def __init__(self, name):
        self.name = name
        self.prepared = False
        self.committed = False
        self.aborted = False

    def prepare(self):
        # Simulate potential failure (1 in 3 chance on third shard)
     …

14 0 Open

Browse by section

Each section groups closely related Python snippets.