Database scaling & optimization
Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.
Approximate Count with HyperLogLog in Python
A mock HyperLogLog implementation uses hash-based registers to estimate cardinality of large datasets with sublinear memory.
import hashlib
class HyperLogLog:
def __init__(self, precision=4):
if precision < 4 or precision > 16:
raise ValueError("precision must be between 4 and 16")
self.precision = precision
self.registers = [0] * (1 << precision)
def _hash(self, value):
return int(hashl…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.
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."""
…How to Build a Connection Pool Reuse Mock in Python
Build a mock connection pool with context manager to track connection reuse, acquires, and releases in Python.
import time
from contextlib import contextmanager
class Connection:
def __init__(self, name):
self.name = name
self.in_use = False
self.busy_since = None
def fetch(self):
return f"data from {self.name}"
class ConnectionPool:
def __init__(self, size=3):
self.conn…How to Build a Shard Map Mock Dict in Python
Implement a dictionary-like class that distributes keys across multiple shards using Python's hash() for realistic data partitioning.
class ShardMap:
def __init__(self, shard_count):
self.shards = {i: {} for i in range(shard_count)}
self.shard_count = shard_count
def _shard_for(self, key):
return hash(key) % self.shard_count
def __getitem__(self, key):
return self.shards[self._shard_for(key)][key]
d…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 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 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 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 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 …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…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.