Database scaling & optimization
Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.
How to Implement Keyset Pagination in Python (Seek Method)
Implement keyset (seek) pagination in Python with a mock paginator that efficiently fetches pages based on the last row rather than OFFSET.
from dataclasses import dataclass
from typing import List, Optional
@dataclass
class Row:
id: int
name: str
def __lt__(self, other: "Row") -> bool:
return (self.id, self.name) < (other.id, other.name)
class MockKeysetPaginator:
"""Pagination using keyset (seek) method instead of OFFSET."""…How to Implement Read-After-Write Consistency Mock in Python
Simulate strong versus eventual read-after-write consistency with a primary and replica store, demonstrating the difference in data visibility over time.
import time
class MockStorage:
def __init__(self, write_delay=0.1):
self.store = {}
self.replica = {}
self.write_delay = write_delay
def write(self, key, value):
# Write to primary storage immediately
self.store[key] = value
# Simulate async replication delay
…How to Insert a Mock Route Record Using SQLite in Python
This code creates an in-memory SQLite table for routes and inserts a mock route record, returning the inserted row for verification.
import sqlite3
from datetime import datetime
def insert_mock_record(db_path=":memory:"):
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS routes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
origin TEXT NOT NULL,
des…How to Limit a Result Set to Top N Rows in Python
Sort a list of dictionaries by a numeric key and return only the top N results, formatted as a readable ranked list.
import random
def top_n_mock(limit: int = 5):
"""Return a formatted top-N result set as a mock example."""
# Simulated data source
scores = [
{"name": "Alice", "score": 87},
{"name": "Bob", "score": 92},
{"name": "Charlie", "score": 78},
{"name": "Diana", "score": 95},
…How to Mock Date Sharding by Range in Python
Split a date interval into fixed-size contiguous shards, returning each window as an ISO date string pair.
from datetime import date, timedelta
def shard_ranges(start_date, end_date, shard_days=7):
if start_date > end_date:
raise ValueError("start_date cannot be after end_date")
shards = []
current = start_date
while current <= end_date:
shard_end = min(current + timedelta(days=shard_days …How to Mock Replica Lag Monitoring in Python
Simulates database replica lag with a mock monitor class that generates realistic lag metrics and health statuses.
import time
import random
from datetime import datetime, timedelta
class MockReplicaLagMonitor:
def __init__(self, replicas=3, base_lag=0.5, jitter=0.2):
self.replicas = [f"replica-{i}" for i in range(replicas)]
self.base_lag = base_lag
self.jitter = jitter
self.last_write = dateti…How to Mock SQLite executemany When Batch Inserting in Python
Batch insert many rows into SQLite with executemany and mock the cursor for isolated tests.
import sqlite3
from unittest.mock import Mock, patch
def insert_users(conn, users):
"""Insert multiple user records using executemany."""
cursor = conn.cursor()
cursor.executemany(
"INSERT INTO users (name, age) VALUES (?, ?)",
users
)
conn.commit()
return cursor.rowcount
if _…How to Mock Sticky Session Read-Your-Writes in Python
Simulates a sticky session store that routes reads for a session to the node where the last write occurred, demonstrating read-your-writes consistency.
class StickySessionStore:
def __init__(self):
self.data = {}
self.session_nodes = {}
def write(self, session_id, key, value):
self.data[key] = value
self.session_nodes[session_id] = key
return f"Wrote {key}={value} for session {session_id}"
def read(self, session_i…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 Function Call in Python with unittest.mock
Use unittest.mock.Mock to wrap a function and spy on its call count and arguments in Python.
import random
from unittest.mock import Mock, patch
def select_n_plus_one(numbers: list[int]) -> int:
"""Return the first number that appears more than once, if any."""
seen = set()
for num in numbers:
if num in seen:
return num
seen.add(num)
return -1
def detect_mock(se…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…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.