Reference library

Database scaling & optimization

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

4 matches

Database Helper in Python with SQLite Scaling Optimization

Build a beginner-friendly SQLite database helper class with WAL, indexed queries, and efficient batch inserts for scaling.

sqlite database scalability

Python

import sqlite3
from contextlib import contextmanager


class DatabaseHelper:
    """Beginner-friendly helper for SQLite database operations with scaling tips."""

    def __init__(self, db_path):
        self.db_path = db_path

    @contextmanager
    def connection(self):
        """Context manager for automatic comm…

14 0 Open

Database scaling & optimization medium

How to Create a Covering Index with INCLUDE Columns in Python

Create a covering index with INCLUDE columns in SQLite from Python and inspect the query plan to confirm the index covers the query.

sqlite indexing covering index

Python

import sqlite3

def create_covering_index_mock():
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()

    cursor.execute("""
        CREATE TABLE employees (
            id INTEGER PRIMARY KEY,
            name TEXT,
            department TEXT,
            salary INTEGER
        )
    """)

    employe…

15 0 Open

Database scaling & optimization medium

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.

jsonb gin-index inverted-index

Python

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…

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.