Database scaling & optimization
Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.
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]
…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 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 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 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…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.