Database scaling & optimization
Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.
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.
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…How to Mock a Hot Shard Split in Python
Simulate a database hot shard splitting into two shards by key ranges when it exceeds a threshold, with a mock class for testing.
import random
from collections import defaultdict
class HotShardMock:
"""Mock implementation of a hot shard split in a distributed database."""
def __init__(self, shard_id="shard_1", max_entries=5):
self.shard_id = shard_id
self.max_entries = max_entries
self.entries = {}
def ad…How to Optimize SQLite Database Performance in Python
A Python helper that creates an index, enables WAL mode, and tunes synchronous settings to optimize SQLite database performance.
import sqlite3
DATABASE_PATH = "beginners.db"
UNOPTIMIZED_TABLE_SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL
)
"""
def optimize_database(db_path: str = DATABASE_PATH) -> dict:
with sqlite3.connect(db_path) as connection:
curs…How to Replicate Data Across All Shards in Python
Mocks a global table that replicates a key-value pair to every shard, ensuring reads return the same value from any shard.
from dataclasses import dataclass
from typing import Dict, List
@dataclass
class Shard:
id: str
data: Dict[str, int]
class GlobalTable:
def __init__(self, shards: List[Shard]):
self._shards = {s.id: s for s in shards}
def set_value(self, key: str, value: int) -> None:
"""Replicate …How to Shard Data by User ID Hash in Python
Deterministically map user IDs to shard indexes using an MD5 hash modulo the shard count in Python.
import hashlib
def shard_id(user_id: str, num_shards: int = 4) -> int:
"""Deterministically map a user_id to a shard index using MD5."""
digest = hashlib.md5(user_id.encode("utf-8")).hexdigest()
return int(digest[:8], 16) % num_shards
if __name__ == "__main__":
user_ids = ["alice", "bob", "carol", "d…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.
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…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.
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…How to Simulate a Stable Sort Cursor in Python
Build a MongoDB-style cursor mock that stably sorts records by a key while preserving original order for ties, with next() and rewind() methods.
```python
import random
class CursorStableSortMock:
"""Simulates stable sorting with a cursor-like pointer for MongoDB-style queries."""
def __init__(self, data, sort_key, reverse=False):
self.data = list(data)
self.sort_key = sort_key
self.reverse = reverse
self._index = …How to Speed Up Column Lookups with DataFrame Index in Python
Use pandas set_index to make repeated column value lookups O(1)-style fast instead of scanning the whole DataFrame each time.
import pandas as pd
# Mock dataset with duplicate customer IDs
data = {"customer_id": [101, 102, 103, 101, 104, 102],
"order_amount": [250.0, 85.5, 300.0, 175.25, 420.0, 95.75]}
df = pd.DataFrame(data)
df = df.set_index("customer_id")
# Simulated lookup request
search_id = 102
# Fast index-based lookup (no…How to Validate Data Before Scaling in Python
A reusable Python helper that validates required fields and constraint checks on data rows before entering a database pipeline, improving data quality and throughput.
def validate_data(data, required_fields, constraints=None):
"""
Basic validation helper demonstrating data-quality workflows
before scaling (catches bad rows early, improves throughput).
"""
constraints = constraints or {}
errors = []
for field in required_fields:
if field not in d…How to enforce a unique index constraint in Python
Mock a database unique index in Python that rejects duplicate rows based on one or more columns.
class MockIndex:
def __init__(self, columns):
self.columns = columns
self._values = set()
def insert(self, row):
key = tuple(row[col] for col in self.columns)
if key in self._values:
raise ValueError(f"Duplicate key {key} for columns {self.columns}")
self._v…How to mock batch commit of transactions in Python
Simulate a transaction batch writer with commit, rollback, and summary logic to test database write patterns without a real database.
import json
from datetime import datetime, timezone
class TransactionBatch:
def __init__(self):
self.pending = []
self.committed = []
self._log = []
def add(self, operation):
self.pending.append(operation)
def commit(self):
if not self.pending:
return …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…Monitor Database Index Bloat in Python
Simulates index bloat checks for database tables using random ratio thresholds and reports alerts per index.
import random
import time
class IndexBloatMonitor:
def __init__(self, thresholds=(0.5, 0.8, 0.9)):
self.thresholds = thresholds
self.indices = {
"users_pk": 48.2,
"orders_created_idx": 124.7,
"products_name_idx": 15.3,
"payments_user_idx": 203.9,
…Offset vs Keyset Pagination in Python
Demonstrate offset-based pagination and keyset (cursor) pagination with a simple in-memory dataset, showing how each returns pages of records.
"""Demonstrate pagination using offset vs keyset (cursor) approach."""
ITEMS = [
{"id": 1, "name": "Alice"},
{"id": 2, "name": "Bob"},
{"id": 3, "name": "Carol"},
{"id": 4, "name": "David"},
{"id": 5, "name": "Eve"},
]
def offset_paginate(items, page, page_size):
"""Return a page using offset…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 PostgreSQL Vacuum to Reclaim Space in Python
A Python class that safely rewrites a data file to remove deleted rows and reclaim physical space, mimicking PostgreSQL's VACUUM operation.
import shutil
import os
class VacuumCleaner:
"""Simulates PostgreSQL-style vacuum reclaiming dead space in a file."""
def __init__(self, filepath, fill_ratio=0.7, dead_marker="[DELETED]"):
self.filepath = filepath
self.fill_ratio = fill_ratio
self.dead_marker = dead_marker
…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…UUID vs sequential primary key in Python
Simulate and compare UUID vs sequential primary key generation in Python to understand trade-offs in ordering and uniqueness.
import uuid
import time
def create_record_with_uuid(name):
record_id = uuid.uuid4()
return {"id": record_id, "name": name}
def create_record_with_sequential_id(name, counter):
counter += 1
return {"id": counter, "name": name}
if __name__ == "__main__":
# Simulate users inserting records
sequ…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.