Database scaling & optimization
Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.
Monitor Database Index Bloat in Python
Simulates index bloat checks for database tables using random ratio thresholds and reports alerts per index.
import random
import time
class IndexBloatMonitor:
def __init__(self, thresholds=(0.5, 0.8, 0.9)):
self.thresholds = thresholds
self.indices = {
"users_pk": 48.2,
"orders_created_idx": 124.7,
"products_name_idx": 15.3,
"payments_user_idx": 203.9,
…Offset vs Keyset Pagination in Python
Demonstrate offset-based pagination and keyset (cursor) pagination with a simple in-memory dataset, showing how each returns pages of records.
"""Demonstrate pagination using offset vs keyset (cursor) approach."""
ITEMS = [
{"id": 1, "name": "Alice"},
{"id": 2, "name": "Bob"},
{"id": 3, "name": "Carol"},
{"id": 4, "name": "David"},
{"id": 5, "name": "Eve"},
]
def offset_paginate(items, page, page_size):
"""Return a page using offset…Rebalance Shard Ranges Across Nodes in Python
A mock rebalancing function that shuffles shard ranges and distributes them evenly across nodes using round-robin assignment.
import random
from dataclasses import dataclass
@dataclass
class Shard:
id: int
start: int
end: int
def rebalance_shards(shards: list[Shard], node_count: int) -> dict[int, list[Shard]]:
"""Mock rebalancing of shard ranges across nodes."""
all_ranges = [(s.start, s.end) for s in shards]
random…Route SELECT Queries to Read Replicas in Python
A mock round-robin router that forwards SELECT queries to read replicas and sends writes to the primary.
import random
class ReadReplicaRouter:
"""Round-robin router that sends SELECT queries to read replicas."""
def __init__(self, replicas):
self.replicas = replicas
self.counter = 0
def route(self, sql):
if sql.strip().upper().startswith("SELECT"):
replica = sel…Simulate PostgreSQL Vacuum to Reclaim Space in Python
A Python class that safely rewrites a data file to remove deleted rows and reclaim physical space, mimicking PostgreSQL's VACUUM operation.
import shutil
import os
class VacuumCleaner:
"""Simulates PostgreSQL-style vacuum reclaiming dead space in a file."""
def __init__(self, filepath, fill_ratio=0.7, dead_marker="[DELETED]"):
self.filepath = filepath
self.fill_ratio = fill_ratio
self.dead_marker = dead_marker
…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)
…UUID vs sequential primary key in Python
Simulate and compare UUID vs sequential primary key generation in Python to understand trade-offs in ordering and uniqueness.
import uuid
import time
def create_record_with_uuid(name):
record_id = uuid.uuid4()
return {"id": record_id, "name": name}
def create_record_with_sequential_id(name, counter):
counter += 1
return {"id": counter, "name": name}
if __name__ == "__main__":
# Simulate users inserting records
sequ…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.