Architecture

This document explains the internal design of FastMSSQL, how the Rust/Python bridge works, and the key optimization strategies used throughout the codebase.

High-Level Overview
Technology Stack
Project Structure
Core Components
Execution Flow
Performance Optimizations
Concurrency Model
Type System & Conversion
Memory Management
Connection Pooling
Summary: Why It's Fast
Further Reading

High-Level Overview

FastMSSQL is a Python extension written in Rust that provides a high-performance, async SQL Server client. The architecture consists of:

Python User Code
       ↓
   [PyO3 Bindings] ← Python/Rust Bridge
       ↓
   [Rust Runtime] ← Core async execution
       ↓
  [Tiberius] ← SQL Server protocol client
       ↓
  [Tokio] ← Async I/O runtime
       ↓
  [TCP/TLS] → SQL Server (port 1433)

Key Design Principle: Minimize latency and maximize throughput by keeping Rust in the hot path while providing a clean Python API.

Technology Stack

Python-Rust Bridge

PyO3 0.27.2 — Python bindings for Rust
- Handles Python ↔ Rust data marshalling
- Manages Python reference counting and GIL interactions
- ABI3 stable across Python 3.11-3.14

Async Runtime

Tokio 1.48 — Async I/O runtime
- Multi-threaded executor with tuned worker count
- Handles thousands of concurrent connections
- Built-in channel types for async communication
pyo3-async-runtimes — PyO3 ↔ Tokio integration
- Bridges Python's async/await with Tokio
- Handles GIL release during async operations

Database Client

Tiberius 0.12 — Native SQL Server TDS protocol client
- Pure Rust implementation (no ODBC/ODBC drivers needed)
- Features: parameterized queries, SSL/TLS, connection pooling integration
- Uses tokio-tls for encrypted connections
- Supports TCP and named pipes on Windows

Connection Pool

BB8 0.9 — Async connection pool manager
- Type-agnostic pooling (works with any async connection type)
- Configurable min/max sizes and timeouts
- Health checking and automatic reconnection

Optimization Libraries

MiMalloc — Microsoft's high-performance memory allocator
- Reduces allocation latency by ~30-50% vs default allocator
- Better multi-threaded performance
- Enabled globally via #[global_allocator]
SmallVec 1.15 — Stack-allocated vectors for small collections
- Parameters: up to 16 stored on stack, no heap allocation
- Avoids allocation overhead for typical query parameter counts
ahash 0.8 — Fast hashing algorithm
- ~3x faster than SipHash for short strings
- Used internally by Tokio and collections

Project Structure

src/
├── lib.rs                      # Entry point, module declarations, Tokio setup
├── connection.rs               # PyConnection class, query/execute methods
├── pool_manager.rs             # Connection pool initialization and management
├── batch.rs                    # Batch query operations
├── parameter_conversion.rs      # Python → Rust parameter conversion
├── parameter_utils.rs          # Helper functions for parameter parsing
├── py_parameters.rs            # Python Parameter/Parameters classes
├── type_mapping.rs             # SQL Server ↔ Python type conversions
├── ssl_config.rs               # SSL/TLS configuration
├── pool_config.rs              # Connection pool configuration
└── types.rs                    # PyFastRow, PyQueryStream result types

python/
└── fastmssql/
    └── __init__.py             # Python package root

fastmssql.pyi                   # Type hints for IDE support

Core Components

1. Module Initialization (`lib.rs`)

The entry point configures the entire runtime environment:

#[pymodule]
fn fastmssql(m: &Bound<'_, PyModule>) -> PyResult<()> {
    let mut builder = tokio::runtime::Builder::new_multi_thread();

    builder
        .worker_threads(cpu_count.max(4).min(16))
        .max_blocking_threads((cpu_count * 2).min(32))
        .thread_keep_alive(Duration::from_secs(60))
        .thread_stack_size(2 * 1024 * 1024)
        .global_queue_interval(61)
        .event_interval(61);

    pyo3_async_runtimes::tokio::init(builder);
    // ... register classes ...
}

Design Decisions:

Worker threads: 1× CPU count, bounded 4–16. DB queries are async I/O-bound; additional workers only add work-stealing overhead without improving throughput.
Blocking threads: CPU × 2, max 32. No spawn_blocking is used anywhere in the codebase — all DB I/O is async via Tiberius. The small ceiling provides a safety margin if future sync work is added, without ballooning virtual memory.
Keep-alive: 60 seconds. Amortises burst thread creation while reclaiming idle thread stacks promptly (the previous 900 s kept surge threads alive 15 minutes).
Stack size: 2 MB — Tokio's recommended default for async runtimes.
Queue intervals: Tokio defaults (61). Previous values of 7/13 caused excessive global-queue polling; 31 was mislabelled as "less frequent" but was actually more frequent than the default.

2. Connection Class (`connection.rs`)

The main user-facing API:

# User code
async with Connection(conn_str, ssl_config=ssl_config) as conn:
    result = await conn.query("SELECT * FROM users WHERE id = @P1", [user_id])
    rows = result.rows()

Implementation Details:

Stores connection pool state in Arc<Mutex<Option<ConnectionPool>>>
- Arc: Shared ownership across async tasks
- Mutex: Synchronize lazy initialization
- Option: Pool created on first use (lazy initialization)

GIL Handling: Async operations release Python's Global Interpreter Lock

pub fn query<'p>(&self, sql: &str, parameters: Option<Bound<'p, PyAny>>)
  -> PyResult<Bound<'p, PyAny>> {
  // GIL is released during future_into_py
  future_into_py(py, self.query_async(...))
}

Error Handling: Rich error messages for debugging

.map_err(|e| {
  PyRuntimeError::new_err(
    format!("Query execution failed: {}", e)
  )
})

3. Connection Pool (`pool_manager.rs`)

Manages the BB8 connection pool with thread-safe initialization:

pub async fn ensure_pool_initialized(
    pool: Arc<Mutex<Option<ConnectionPool>>>,
    config: Arc<Config>,
    pool_config: &PyPoolConfig,
) -> PyResult<ConnectionPool> {
    // Fast path: check if already initialized (lock released before async)
    {
        let pool_guard = pool.lock();
        if let Some(ref p) = *pool_guard {
            return Ok(p.clone());
        }
    } // Lock released here

    // Slow path: initialize if needed
    let new_pool = establish_pool(&config, pool_config).await?;

    // Double-check pattern: another thread might have initialized first
    let mut pool_guard = pool.lock();
    if let Some(ref p) = *pool_guard {
        Ok(p.clone())
    } else {
        *pool_guard = Some(new_pool.clone());
        Ok(new_pool)
    }
}

Key Pattern: Double-checked locking

Fast path: No lock contention (pool already initialized)
Slow path: Lock released before await (no blocking I/O under lock)
Thread-safe initialization without deadlock risk

BB8 Configuration:

Pool::builder()
    .retry_connection(true)      // Auto-reconnect on failure
    .max_size(pool_config.max_size)           // Default: 10
    .min_idle(pool_config.min_idle)           // Default: 2
    .max_lifetime(pool_config.max_lifetime)   // Conn lifetime limit
    .idle_timeout(pool_config.idle_timeout)   // Idle timeout
    .build(manager)

4. Batch Operations (`batch.rs`)

High-performance batch execution for multiple queries:

# User code
result = await conn.execute_batch([
    ("INSERT INTO users (name) VALUES (@P1)", ["Alice"]),
    ("INSERT INTO users (name) VALUES (@P1)", ["Bob"]),
])

Implementation:

Parses list of (SQL, parameters) tuples
Converts parameters once at the start (not per query)
Executes sequentially in a single transaction-like context
Returns aggregate results (total affected rows, any errors)

Optimization: Uses SmallVec for parameter storage, avoiding allocations for typical batch sizes.

5. Parameter Conversion (`parameter_conversion.rs`)

Bridges Python and Rust type systems:

pub enum FastParameter {
    Null,
    Bool(bool),
    I64(i64),
    F64(f64),
    String(String),
    Bytes(Vec<u8>),
}

impl tiberius::ToSql for FastParameter {
    fn to_sql(&self) -> tiberius::ColumnData<'_> {
        match self {
            FastParameter::Null => tiberius::ColumnData::U8(None),
            FastParameter::Bool(b) => b.to_sql(),
            FastParameter::I64(i) => i.to_sql(),
            // ... other types
        }
    }
}

Conversion Process:

Python object → FastParameter enum
FastParameter implements tiberius::ToSql
Tiberius converts to TDS protocol bytes

SmallVec Optimization:

let mut result: SmallVec<[FastParameter; 16]> = SmallVec::with_capacity(len);
// 0-16 parameters: zero heap allocations
// 17+ parameters: single heap allocation (rare)

6. Type Mapping (`type_mapping.rs`)

Converts SQL Server column values to Python objects:

#[inline(always)]
fn handle_int4(row: &Row, index: usize, py: Python) -> PyResult<Py<PyAny>> {
    match row.try_get::<i32, usize>(index) {
        Ok(Some(val)) => Ok((val as i64).into_pyobject(py)?.into_any().unbind()),
        _ => Ok(py.None())
    }
}

Supported SQL Server Types:

SQL Server Type	Python Type	Notes
INT, BIGINT, SMALLINT	int	8/16/32/64-bit signed integers (INT1-INT8)
TINYINT	int	Unsigned 8-bit integer
FLOAT, REAL	float	IEEE 754 floating point (FLOAT4, FLOAT8)
NUMERIC, DECIMAL	Decimal	High-precision numeric (via decimal module)
VARCHAR, NVARCHAR	str	UTF-8 strings (variable-length)
CHAR, NCHAR	str	Fixed-width strings
TEXT, NTEXT	str	Legacy large text types
BIT	int	0 or 1 boolean values
BINARY, VARBINARY	bytes	Raw bytes (BIGVARBINARY, BIGBINARY)
IMAGE	bytes	Legacy binary type
MONEY, SMALLMONEY	Decimal	Financial data (via decimal module)
DATETIME, DATETIME2	datetime	Date and time values
DATETIME4	datetime	32-bit datetime
DATE	date	Date only values
TIME	time	Time only values
DATETIMEOFFSET	datetime	DateTime with timezone offset
UNIQUEIDENTIFIER	str	UUID as string
XML	str	XML data as string
NULL	None	Python None

Optimization Strategy:

#[inline(always)] on type handlers allows compiler to specialize per column type
Minimal branching in hot paths
Early return for NULL values
Uses try_get to handle missing columns gracefully

7. SSL/TLS Configuration (`ssl_config.rs`)

Manages encrypted database connections:

from fastmssql import SslConfig, EncryptionLevel

ssl_config = SslConfig(
    encryption_level=EncryptionLevel.Required,
    ca_certificate_path="/path/to/ca.pem"
)

Encryption Levels:

Required: All traffic encrypted (recommended)
LoginOnly: Only credentials encrypted
Off: No encryption (development only)

Certificate Validation:

Mutually exclusive: either trust server OR provide CA certificate
File existence and readability checked at construction time
Supported formats: .pem, .crt, .der

Execution Flow

1. Single Query Execution

Python:
    result = await conn.query("SELECT @@VERSION", [])
                ↓
Rust (PyConnection.query):
    1. Convert parameters: Python → FastParameter
    2. Release GIL
    3. future_into_py() creates Python coroutine
                ↓
Rust (Async):
    4. ensure_pool_initialized() - lazy create pool
    5. Get connection from BB8 pool
    6. Build Tiberius parameters
    7. Execute query via tiberius::Client::query()
    8. Collect rows into Vec<Row>
                ↓
Rust (Type Conversion):
    9. For each row:
       - For each column:
         - Use type_mapping to convert to PyObject
       - Store in PyFastRow
    10. Wrap in PyQueryStream
                ↓
Python:
    11. Await future, get PyQueryStream
    12. Iterate using async for or call .rows() to get list of PyFastRow dicts

2. Batch Execution

Python:
    result = await conn.execute_batch([
        ("INSERT ... VALUES (@P1)", ["Alice"]),
        ("INSERT ... VALUES (@P1)", ["Bob"]),
    ])
                ↓
Rust (batch.rs):
    1. Parse batch items - extract SQL and parameters
    2. Convert all parameters in one pass
    3. Release GIL
    4. future_into_py() creates Python coroutine
                ↓
Rust (Async):
    5. ensure_pool_initialized()
    6. For each batch item:
       - Get connection from pool
       - Build Tiberius parameters
       - Execute query
       - Accumulate affected row count
    7. Return aggregate result
                ↓
Python:
    8. Await future, get batch execution result

Performance Optimizations

1. Zero-Copy Parameter Passing

Problem: Copying parameters between Python and Rust adds latency.

Solution: Direct conversion without intermediate allocations

// BEFORE: Multiple allocations
let params = Vec::new();
for param in py_params {
    params.push(python_to_fast_parameter(param));
}

// AFTER: Stack allocation with SmallVec
let mut params: SmallVec<[FastParameter; 16]> = SmallVec::with_capacity(len);
// Typical queries have ≤16 parameters → zero heap allocation

2. Global Memory Allocator

Problem: Default Rust allocator has higher latency.

Solution: MiMalloc allocator

#[global_allocator]
static GLOBAL: mimalloc::MiMalloc = mimalloc::MiMalloc;

Benefits:

30-50% lower allocation latency
Better multi-threaded performance
Uses per-thread freelists

3. GIL Release Strategy

Problem: Python GIL blocks other threads during long Rust operations.

Solution: Release GIL before async work

pub fn query<'p>(&self, sql: &str, parameters: Option<Bound<'p, PyAny>>)
    -> PyResult<Bound<'p, PyAny>> {
    let py = query.py();
    // GIL is released inside future_into_py
    future_into_py(py, self.query_async(sql, parameters, py))
}

This allows Python to execute other threads while database I/O happens.

4. Lazy Initialization

Problem: Creating pools/runtimes on import adds startup latency.

Solution: Create on first use

let pool_guard = pool.lock();
if let Some(ref p) = *pool_guard {
    return Ok(p.clone());  // Already initialized
}
// Create only on first query

5. Type Handler Inlining

Problem: Type dispatch has branch misprediction overhead.

Solution: #[inline(always)] on handlers lets compiler specialize

#[inline(always)]
fn handle_int4(row: &Row, index: usize, py: Python) -> PyResult<Py<PyAny>> {
    // Compiler generates specialized code for each call site
    // Zero runtime dispatch
}

6. Batch Parameter Optimization

Problem: Converting parameters per query is redundant.

Solution: Single conversion pass

// Convert all parameters once
let fast_params = convert_parameters_to_fast(Some(&params), py)?;
// Reuse or apply to queries

7. Connection Pool Defaults

Problem: Poor pool settings hurt throughput.

Solution: Tuned defaults based on deployment patterns

PoolConfig {
    max_size: 10,        // Few open connections
    min_idle: 2,         // Pre-warmed
    max_lifetime: 1800s, // 30-minute rotation
}

Concurrency Model

Tokio Runtime Configuration

FastMSSQL uses a tuned Tokio multi-threaded runtime:

let builder = tokio::runtime::Builder::new_multi_thread();

builder
    .worker_threads(cpu_count.max(4).min(16))          // ← 1× CPU, capped
    .max_blocking_threads((cpu_count * 2).min(32))     // ← Minimal; no spawn_blocking used
    .thread_keep_alive(Duration::from_secs(60))        // ← 1 min, not 15 min
    .thread_stack_size(2 * 1024 * 1024)                // ← 2 MB (Tokio default)
    .global_queue_interval(61)                          // ← Tokio default
    .event_interval(61);                                // ← Tokio default

Rationale:

1× CPU workers (max 16): All DB I/O is async; workers only schedule tasks. More workers would increase work-stealing overhead without improving throughput.
Minimal blocking threads (max 32): No spawn_blocking calls exist anywhere in this codebase. The small pool is a safety margin for future additions.
60 s keep-alive: Smooths burst patterns while releasing idle thread stacks promptly. 900 s kept surge threads alive for 15 minutes consuming virtual memory.
2 MB stacks: Tokio's recommended default; sufficient for async call depth.
Tokio-default intervals (61): No evidence custom values improve performance; the previous values (7 / 13 / 31) caused excessive polling overhead.

Task Model

Each query execution is a single async task:

Connection::query()
    ↓
Tokio spawns task (no blocking)
    ↓
Task waits on pool.get() (async)
    ↓
Task executes tiberius query (async I/O)
    ↓
Task collects results (CPU-bound, fast)
    ↓
Task returns to Python

Key Property: No task blocks, so thousands of queries can be in-flight simultaneously.

Synchronization Primitives

Primitive	Usage	Why
`Arc<Mutex<Option<Pool>>>`	Connection pool storage	Shared ownership, single-threaded access to pool creation
`Arc<Config>`	Shared connection config	Zero-copy reference across tasks

Type System & Conversion

Parameter Conversion Flow

Python Input (user code)
    ↓
PyO3 binding receives as Bound<'p, PyAny>
    ↓
python_to_fast_parameter() → FastParameter enum
    - Null:       → FastParameter::Null
    - str:        → FastParameter::String
    - int:        → FastParameter::I64
    - float:      → FastParameter::F64
    - bool:       → FastParameter::Bool
    - bytes:      → FastParameter::Bytes
    ↓
FastParameter implements tiberius::ToSql
    ↓
Tiberius converts to TDS protocol bytes
    ↓
SQL Server (port 1433)

Result Conversion Flow

SQL Server (rows over TDS protocol)
    ↓
Tiberius parses bytes → tiberius::Row
    ↓
type_mapping::convert_row_to_pydict()
    - For each column:
      - Use ColumnType to dispatch to handler
      - Handler calls row.try_get::<T, usize>()
      - T::from converted to PyObject
    ↓
PyFastRow: ordered dict of column → value
    ↓
PyQueryStream: async iterator over list of PyFastRow
    ↓
Python user receives result via async iteration or result.rows() → List[Dict[str, Any]]

Memory Management

Stack Allocation Strategy

Goal: Minimize heap allocations for typical workloads.

SmallVec for Parameters:

// Stack storage for 16 parameters
SmallVec<[FastParameter; 16]>
// 0-16 params: no heap allocation
// 17+ params: automatic heap allocation (rare)

Typical query:

await conn.query("SELECT * FROM users WHERE id = @P1", [42])
# 1 parameter → stored entirely on stack
# 0 heap allocations

Reference Counting

Goal: Shared ownership without garbage collection overhead.

Arc Usage:

pub struct PyConnection {
    pool: Arc<Mutex<Option<ConnectionPool>>>,
    config: Arc<Config>,
    // ...
}

Pattern:

Arc is cloned when passing to async tasks (cheap, atomic operation)
Actual data is not copied
When last Arc is dropped, data is deallocated

Connection Lifecycle

Connection created
    ↓
Pool created lazily on first query
    ↓
BB8 creates Tiberius clients (TCP connections)
    ↓
Connections reused across multiple queries
    ↓
BB8 periodically checks connection health
    ↓
Idle connections timeout and close
    ↓
Connection dropped
    ↓
Pool garbage collected when last Arc reference drops

Connection Pooling

BB8 Pool Details

Type: Pool<ConnectionManager> where ConnectionManager = Tiberius

Lifecycle:

Creation (lazy, on first query)

let pool = Pool::builder()
    .max_size(10)
    .min_idle(2)
    .build(manager)
    .await?;

Get Connection

let mut conn = pool.get().await?;
// If available: instant return
// If exhausted: waits for connection to return
// If below min_idle: creates new connection

Query Execution

let result = conn.query(sql, &params).await?;
// Connection stays checked out during query
// GIL is released, other threads can run

Return Connection
- Implicit when conn is dropped
- BB8 returns it to pool
- Health checked on return

Configuration Best Practices

from fastmssql import Connection, PoolConfig

pool_config = PoolConfig(
    max_size=10,              # Typical app: 5-20
    min_idle=2,               # Pre-warm connections
    max_lifetime=1800,        # 30 min (prevents stale connections)
    idle_timeout=600,         # 10 min (close idle connections)
    connection_timeout=30     # 30 sec (timeout waiting for connection)
)

async with Connection(conn_str, pool_config=pool_config) as conn:
    result = await conn.query("SELECT 1")

Tuning:

High throughput (> 1000 RPS): Increase max_size to 20-50
Limited resources: Decrease max_size to 5, increase min_idle to 0
Long-running app: Enable max_lifetime to rotate connections

Summary: Why It's Fast

Rust Core: No garbage collection, memory-safe, optimized
Tokio Runtime: Async I/O without blocking threads
Native TDS Client: No ODBC/drivers, direct protocol implementation
Tuned Memory: MiMalloc + SmallVec eliminate allocation overhead
GIL Release: Python can use other threads during I/O
Type Specialization: Compiler generates optimized code per type
Lazy Initialization: No startup overhead
Connection Pooling: Reuse TCP connections, avoid handshake latency
Batch Operations: Single I/O round-trip for multiple queries
Zero-Copy Conversions: Direct marshalling without intermediate allocations

FilesExpand file tree

ARCHITECTURE.md

Latest commit

History