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]
…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…How to Count Star vs Estimate Matches in Python
Count how many times 'star' and 'estimate' annotations match their actual labels in a list of mock comparison results.
def count_star_vs_estimate(mock_scores):
"""
Count the number of times 'star' wins and 'estimate' wins
from a list of mock comparison results.
Args:
mock_scores: list of tuples, each (annotation, actual)
where annotation is 'star' or 'estimate'
Returns:
dict w…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…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.