Reference library

Database scaling & optimization

Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.

5 matches

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…

14 0 Open

Database scaling & optimization easy

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.

json batching database

Python

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 + …

12 0 Open

Database scaling & optimization easy

How to Optimize SQLite Database Performance in Python

A Python helper that creates an index, enables WAL mode, and tunes synchronous settings to optimize SQLite database performance.

sqlite database optimization

Python

import sqlite3

DATABASE_PATH = "beginners.db"
UNOPTIMIZED_TABLE_SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
)
"""


def optimize_database(db_path: str = DATABASE_PATH) -> dict:
    with sqlite3.connect(db_path) as connection:
        curs…

14 0 Open

Database scaling & optimization easy

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.

pandas indexing performance

Python

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…

15 0 Open

Database scaling & optimization easy

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.

uuid primary-key database

Python

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…

14 0 Open

Browse by section

Each section groups closely related Python snippets.