Reference library

Database scaling & optimization

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

4 matches

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.

sqlite query-plan optimization

Python

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…

14 0 Open

Database scaling & optimization easy

How to Insert a Mock Route Record Using SQLite in Python

This code creates an in-memory SQLite table for routes and inserts a mock route record, returning the inserted row for verification.

sqlite database insert

Python

import sqlite3
from datetime import datetime

def insert_mock_record(db_path=":memory:"):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS routes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            origin TEXT NOT NULL,
            des…

14 0 Open

Database scaling & optimization medium

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.

pagination keyset offset

Python

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

14 0 Open

Database scaling & optimization medium

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.

snowflake id-generation clustering

Python

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
        …

13 0 Open

Browse by section

Each section groups closely related Python snippets.