Database scaling & optimization
Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.
How to mock directory-based sharding in Python
Simulates distributing files into logical shards using a deterministic hash of each filename, mocking how a database might shard rows across nodes.
import os
import hashlib
from collections import defaultdict
from pathlib import Path
def get_shard_for_key(key: str, num_shards: int) -> int:
"""Return a deterministic shard index (0..num_shards-1) for a key."""
digest = hashlib.md5(key.encode('utf-8')).hexdigest()
return int(digest, 16) % num_shards
…Idempotent Writes for Sharded Databases in Python
Implement a mock shard with idempotent write support using request IDs to prevent duplicate writes and track the latest value per key.
import json
class ShardMock:
"""Mock distributed shard with idempotent write support."""
def __init__(self, shard_id):
self.shard_id = shard_id
self._store = {}
def write(self, key, value, request_id):
"""Write value only if request_id not yet processed; idempotent."""
i…Mock CQRS Read/Write Split in Python
Separate order mutations from queries using a read model and write model to mock CQRS-style separation of concerns.
from dataclasses import dataclass, field
from typing import List, Dict
@dataclass
class Order:
id: int
amount: float
status: str = "pending"
class OrderWriteModel:
"""Handles all mutations (writes) to orders."""
def __init__(self):
self._orders: Dict[int, Order] = {}
self._next…Rebalance Shard Ranges Across Nodes in Python
A mock rebalancing function that shuffles shard ranges and distributes them evenly across nodes using round-robin assignment.
import random
from dataclasses import dataclass
@dataclass
class Shard:
id: int
start: int
end: int
def rebalance_shards(shards: list[Shard], node_count: int) -> dict[int, list[Shard]]:
"""Mock rebalancing of shard ranges across nodes."""
all_ranges = [(s.start, s.end) for s in shards]
random…Route SELECT Queries to Read Replicas in Python
A mock round-robin router that forwards SELECT queries to read replicas and sends writes to the primary.
import random
class ReadReplicaRouter:
"""Round-robin router that sends SELECT queries to read replicas."""
def __init__(self, replicas):
self.replicas = replicas
self.counter = 0
def route(self, sql):
if sql.strip().upper().startswith("SELECT"):
replica = sel…Simulate Shard Key Cardinality in Python
Generate mock data with configurable cardinality to evaluate shard key distribution and detect hotspots in database scaling design.
import random
import string
def calculate_cardinality(values):
"""Return the number of distinct values in the given list."""
return len(set(values))
def generate_mock_data(num_records, cardinality):
"""Generate mock records for a shard key with given cardinality."""
possible_keys = [f"key_{i:04d}" fo…Simulate a GIN Index for JSONB in Python
Build a mock Generalized Inverted Index (GIN) that flattens JSON documents into key-value tokens for fast lookup queries, mimicking PostgreSQL JSONB indexing.
import json
import random
from collections import defaultdict
# Mock GIN (Generalized Inverted Index) for JSONB key-value pairs
class GINIndex:
def __init__(self):
self.posting_lists = defaultdict(list) # token -> list of doc_ids
def index(self, doc_id, json_obj):
"""Index a JSON documen…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.
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
…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.
"""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)
…Browse by section
Each section groups closely related Python snippets.
Database scaling & optimization — Python code examples
What you will find here
This page collects database scaling & optimization snippets — short, copy-ready Python you can paste into our free online IDE and run without installing anything. Each sample includes a plain-English explanation and the full source code.
Samples vs tutorials and challenges
Samples are quick reference — one concept per page. For step-by-step teaching, use our Python tutorials. To test yourself, try quizzes or coding challenges. Clean up style with the Python formatter.