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…B-Tree Insert and In-Order Traversal in Python
Simulates a B-tree (order 2) with insert and split logic, then prints keys in sorted order via in-order traversal.
class BTreeNode:
def __init__(self, leaf=False):
self.leaf = leaf
self.keys = []
self.children = []
def is_full(self, t):
return len(self.keys) == 2 * t - 1
class BTree:
def __init__(self, t=2):
self.t = t
self.root = BTreeNode(leaf=True)
def insert(s…Broadcast a Small Reference Table in Python
Simulates SQL-style broadcasting of a small lookup table against a larger fact table in memory for mockups or load tests.
import random
def broadcast_mock(target, source, columns):
result = {}
for col in columns:
if col in target and col in source:
result[col] = target[col] + [source[col][i % len(source[col])] for i in range(len(target[col]))]
elif col in target:
result[col] = target[col]
…Build a Partial Index Mock in Python for Database Filtering
Simulate a partial database index by filtering keys with a predicate, then return a limited mock lookup dictionary.
data = [
"alpha", "beta", "gamma", "delta", "epsilon",
"zeta", "eta", "theta", "iota", "kappa"
]
filtered_keys = [item for item in data if len(item) >= 5]
def mock_partial_index(keys, filter_func, limit=3):
result = {}
for key in keys:
if not filter_func(key):
continue
res…Composite index leftmost prefix in Python
Simulate a composite index in SQLite and check whether query columns match the leftmost prefix rule for index usage.
import sqlite3
def get_indexed_columns(table_name):
"""Simulate a composite index by reading column names that start with 'idx_'."""
conn = sqlite3.connect(":memory:")
conn.execute(f"CREATE TABLE {table_name} (id INTEGER, idx_col1 TEXT, idx_col2 INTEGER, other TEXT)")
conn.execute(f"CREATE INDEX idx_…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."""
…Database Helper in Python with SQLite Scaling Optimization
Build a beginner-friendly SQLite database helper class with WAL, indexed queries, and efficient batch inserts for scaling.
import sqlite3
from contextlib import contextmanager
class DatabaseHelper:
"""Beginner-friendly helper for SQLite database operations with scaling tips."""
def __init__(self, db_path):
self.db_path = db_path
@contextmanager
def connection(self):
"""Context manager for automatic comm…Database indexing and query timing optimization in Python
Create SQLite indexes and time query performance to measure speedup for large table lookups in Python.
import sqlite3
import time
def time_query(db_path, query, params=()):
conn = sqlite3.connect(db_path)
conn.execute("PRAGMA journal_mode = WAL")
start = time.perf_counter()
result = conn.execute(query, params).fetchall()
elapsed = time.perf_counter() - start
conn.close()
return result, ela…Geo shard by region in Python
Maps users to database shards based on geographic region with a deterministic hash fallback.
import json
from collections import defaultdict
REGION_SHARD_MAP = {
"na": ["shard-01", "shard-02"],
"eu": ["shard-03", "shard-04", "shard-05"],
"ap": ["shard-06"],
"sa": ["shard-07", "shard-08"],
}
# user_id -> region (mock lookup)
USER_REGIONS = {
"u_1001": "na",
"u_1002": "eu",
"u_1003…Hash index equality mock concept in Python
A simple hash index class in Python that stores key-value pairs in buckets and demonstrates basic equality-based lookup.
class HashIndex:
def __init__(self):
self._buckets = {}
def insert(self, key, value):
"""Insert a key-value pair into the hash index."""
index = hash(key) % 10
if index not in self._buckets:
self._buckets[index] = []
self._buckets[index].append((key, value))…How to Avoid SELECT * and Mock SQL Column Queries in Python
Mock a SQLite cursor to verify that queries specify explicit columns instead of using SELECT *.
import sqlite3
from unittest.mock import Mock, patch
def get_user_emails(connection):
"""Fetch only the required columns instead of SELECT *."""
cursor = connection.cursor()
cursor.execute("SELECT email FROM users")
return [row[0] for row in cursor.fetchall()]
def test_get_user_emails_specific_colu…How to Batch Load JSON Data in Python for Database Optimization
This code parses JSON data into records and loads them in batches to simulate efficient database insertion, reducing load and improving performance.
import json
import time
def parse_and_load(data, batch_size=100):
"""
Parse JSON data and batch-load into a list of dicts.
Demonstrates batching for database efficiency.
"""
records = json.loads(data)
batches = []
for i in range(0, len(records), batch_size):
batch = records[i:i + …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 Convert Data with Scaling for Database Optimization in Python
A beginner-friendly helper that normalizes and scales numeric fields in a list of dicts, reducing storage footprint for database efficiency.
import json
from datetime import datetime
def convert_data(data: list[dict], scale_factor: int = 1) -> list[dict]:
"""Convert a list of dicts to a scaled, normalized format for database efficiency."""
converted = []
for row in data:
normalized = {}
for key, value in row.items():
…How to Create a Data Helper Class in Python for JSON Files
Build a beginner-friendly Python helper class to read, write, filter, and summarize JSON data files with clean, reusable methods.
import json
from pathlib import Path
class DataHelper:
"""Simple beginner-friendly helper for reading and writing JSON data files."""
@staticmethod
def read_json(filename):
file_path = Path(filename)
if file_path.exists():
with file_path.open("r", encoding="utf-8") as f:
…How to Create a Database Helper Class for Beginners in Python
Build a beginner-friendly SQLite helper class with indexing and batch inserts to optimize database queries in Python.
import sqlite3
class DatabaseHelper:
def __init__(self, db_path):
self.connection = sqlite3.connect(db_path)
self.cursor = self.connection.cursor()
def create_table_with_index(self, table_name, columns, indexed_column):
columns_sql = ", ".join(f"{name} {dtype}" for name, dtype in col…How to Eager Load with JOIN to Reduce N+1 Queries in Python
Demonstrates eager loading with a SQL JOIN to reduce N+1 query patterns down to a single database call when fetching related data.
import sqlite3
def eager_load_join_reduce(mock_db_path=":memory:"):
"""Demonstrate eager loading where joins reduce query count from N+1 to 1."""
conn = sqlite3.connect(mock_db_path)
cursor = conn.cursor()
cursor.executescript(
"""
CREATE TABLE authors (id INTEGER PRIMARY KEY, name TE…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 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 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…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.