Database scaling & optimization
Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.
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…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 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 Explain SQLite Query Plans in Python
Build a Python function that runs EXPLAIN QUERY PLAN on SQLite in-memory tables and prints the optimizer's execution plan for any SELECT statement.
import sqlite3
def explain_query(sql: str) -> str:
"""Return the SQLite query plan for the given SQL statement."""
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
# Create sample data for a realistic plan
cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
c…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 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 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 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 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 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
…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
…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.