Skip to content

v1.9.1

Choose a tag to compare

View all tags
@cwt cwt released this 13 Mar 09:36
· 524 commits to main since this release
v1.9.1
a21d057

NeoSQLite v1.9.1 Release Notes

TL;DR

🔧 Maintenance Release — Configurable Journal Mode & Performance Optimizations

  • Upgrade if you need: Configurable SQLite journal modes (WAL, DELETE, MEMORY, etc.) for specialized environments like NFS or single-file distribution, or improved cursor performance.
  • No need to upgrade if: Everything works fine with default WAL mode — this release is fully backward compatible.
  • Breaking changes: None — 100% backward compatible with existing code.

Overview

NeoSQLite v1.9.1 is a maintenance and optimization release that introduces configurable SQLite journal modes and significant cursor performance improvements. This version focuses on providing flexibility for specialized deployment environments while maintaining full PyMongo API compatibility.

Key Achievement: With this release, NeoSQLite provides production-grade flexibility for SQLite deployment scenarios, allowing developers to choose the optimal journal mode for their specific use case while maintaining ACID compliance and PyMongo API parity.

Major Feature: Configurable Journal Mode

Full support for SQLite journal mode configuration via Connection constructor:

Journal Mode Options

  • ✅ WAL (Default) - Write-Ahead Logging for best concurrency
  • ✅ DELETE - Traditional rollback journal, single-file when closed
  • ✅ TRUNCATE - Similar to DELETE, but truncates journal
  • ✅ PERSIST - Overwrites journal with zeros for reuse
  • ✅ MEMORY - Journal in RAM, fastest but no crash recovery
  • ✅ OFF - No journaling, fastest but no transaction safety

API Usage

from neosqlite import Connection, JournalMode

# Using enum (recommended)
db = Connection("app.db", journal_mode=JournalMode.WAL)

# Using string
db = Connection("app.db", journal_mode="DELETE")

Type Safety

  • JournalMode class - Enum-like class with validated constants
  • Case-insensitive - Accepts "wal", "WAL", "Wal", etc.
  • Validation - Raises ValueError for invalid modes
  • Clone propagation - Journal mode correctly propagated to cloned connections

Performance: SQL-Tier Cursor Optimization

Significant cursor performance improvements by moving operations to SQL level:

Cursor Optimizations

sort() Optimization:

  • Moved to SQL ORDER BY clause
  • Eliminates Python-level sorting overhead
  • Supports collation for case-insensitive sorting

limit() and skip() Optimization:

  • Moved to SQL LIMIT and OFFSET clauses
  • Prevents $O(N)$ memory overhead for large result sets
  • Up to 2.4x performance improvement in cursor benchmarks

Automatic Tier Selection:

  • Smart detection of when SQL optimization is possible
  • Falls back to Python when complex filters require it
  • Transparent to users — no API changes needed

Performance Impact

Operation Before After Improvement
Cursor with sort Python sort SQL ORDER BY ~2.4x
Cursor with limit Load all, slice SQL LIMIT Memory efficient
Cursor with skip Load all, skip SQL OFFSET Memory efficient
Combined operations Multiple passes Single SQL query Significant

Benchmark Mode: Automated Performance Testing

Integrated benchmark capabilities into the existing API comparison suite:

Benchmark Features

Multiple Iterations:

  • Configurable iteration count (default 10)
  • Statistical analysis (min, max, avg, stddev)
  • Disk I/O benchmarking support

Comprehensive Coverage:

  • 55+ test categories benchmarked
  • Automatic report generation (Markdown and CSV)
  • Side-by-side NeoSQLite vs MongoDB comparison

Report Generation:

  • Markdown reports - Human-readable with summary tables
  • CSV exports - Machine-readable for further analysis
  • Detailed timing - Per-iteration breakdown

Usage

# Run compatibility tests
python3 examples/api_comparison_main.py

# Run benchmark with default 10 iterations
python3 examples/api_comparison_main.py --benchmark

# Run benchmark with custom iterations
python3 examples/api_comparison_main.py --benchmark 100

# Silent mode for automated runs
python3 examples/api_comparison_main.py --benchmark 100 --silent

Benchmark Scripts

Shell Script:

# Run with default settings
./scripts/run-api-benchmark.sh

# Run with 50 iterations
./scripts/run-api-benchmark.sh --benchmark 50

# Silent mode
./scripts/run-api-benchmark.sh --benchmark 100 --silent

Implementation Details

Core Improvements

Query Helper Enhancements:

  • _build_sort_clause() - SQL ORDER BY generation
  • _build_pagination_clause() - SQL LIMIT/OFFSET generation
  • Collation support for case-insensitive sorting
  • Automatic clause composition in query building

Cursor State Tracking:

  • _sql_handled_sort - Track if SQL handled sorting
  • _sql_handled_pagination - Track if SQL handled pagination
  • Smart fallback when Python filtering is required

Connection Cloning:

  • Journal mode propagation to cloned connections
  • Proper state preservation in with_options()

Test Suite Reorganization:

  • Split new_operators.py into focused modules
  • New modules: aggregation_bucket.py, type_operators.py, expression_operators.py, object_operators_extended.py, array_operators_extended.py, binary_operators.py
  • Better test organization and maintainability

New Test Suites

  • tests/test_journal_mode.py - Journal mode configuration and validation tests
  • examples/api_comparison/timing.py - Timing utilities for benchmarking
  • examples/api_comparison/reporter.py - BenchmarkReporter class for results
  • examples/api_comparison/runner.py - run_benchmark() function

Benchmark Infrastructure

Timing Utilities:

  • start_neo_timing() / end_neo_timing() - NeoSQLite timing
  • start_mongo_timing() / end_mongo_timing() - MongoDB timing
  • get_last_neo_timing() / get_last_mongo_timing() - Retrieve last timing

Benchmark Reporter:

  • BenchmarkResult class - Per-category timing storage
  • BenchmarkReporter class - Aggregate results and reporting
  • Statistical analysis (min, max, avg, stddev)
  • Markdown and CSV export

Benchmark Scripts:

  • scripts/run-api-benchmark.sh - Automated benchmark runner
  • Container management (podman/docker)
  • MongoDB lifecycle management

Test Results

Unit Tests

All existing tests pass without modification, confirming 100% backward compatibility:

Metric Result
Total Tests 2,177+
Passed 2,170+
Failed 0
Code Coverage 83%+

Journal Mode Tests

New test coverage for journal mode functionality:

  • ✅ Default WAL mode verification
  • ✅ Custom mode configuration (DELETE, MEMORY, etc.)
  • ✅ Case-insensitive mode acceptance
  • ✅ Clone propagation
  • ✅ Invalid mode rejection

API Comparison Tests

Metric v1.9.0 v1.9.1
Total Tests 373 369*
Passed 362 358
Skipped 11 11
Failed 0 0
Compatibility 100% 100%

*Test count decreased due to deduplication during module reorganization.

Benefits for Users

For Application Developers

Journal Mode Flexibility: Choose the optimal journal mode for your deployment scenario.

Better Cursor Performance: Faster queries with sort/limit/skip operations.

No Breaking Changes: All existing code continues to work without modification.

Production-Ready: WAL mode as default provides best concurrency for most applications.

For DevOps

NFS Support: Use DELETE mode for NFS deployments where WAL is problematic.

Single-File Distribution: DELETE mode ensures database is single file when closed.

Memory-Constrained Environments: MEMORY or OFF modes for specific use cases.

For Contributors

Benchmark Infrastructure: Easy to measure performance impact of changes.

Modular Test Organization: Easier to maintain and extend test suite.

Clear Architecture: SQL-tier optimization patterns well-documented.

Migration Guide

Upgrading from v1.9.0

Steps:

  1. Update NeoSQLite: pip install neosqlite==1.9.1
  2. Run tests to confirm compatibility (all should pass)
  3. Consider journal mode configuration for specialized environments
  4. Benefit from automatic cursor performance improvements

Effort: Minimal — typically 5-10 minutes for most users.

Adopting Journal Mode Configuration

Default Behavior (no code changes):

# Still uses WAL mode by default
db = Connection("app.db")

NFS Deployment:

# Use DELETE mode for NFS compatibility
db = Connection("app.db", journal_mode=JournalMode.DELETE)

Single-File Distribution:

# DELETE mode ensures single file when closed
db = Connection("app.db", journal_mode=JournalMode.DELETE)

Maximum Performance (no crash recovery needed):

# OFF mode for maximum write performance
db = Connection("app.db", journal_mode=JournalMode.OFF)

Using Benchmark Mode

Quick Benchmark:

python3 examples/api_comparison_main.py --benchmark 50

Automated Benchmarking:

./scripts/run-api-benchmark.sh --benchmark 100 --silent

Analyze Results:

  • Markdown report: documents/benchmarks/Benchmark-NeoSQLite-1.9.1-YYYYMMDDHHMM.md
  • CSV report: documents/benchmarks/Benchmark-NeoSQLite-1.9.1-YYYYMMDDHHMM.csv

Known Limitations

Journal Mode Considerations

WAL Mode:

  • Creates -wal and -shm files alongside database
  • Not suitable for NFS or network filesystems
  • Best concurrency for local deployments

DELETE Mode:

  • Writers block readers (lower concurrency than WAL)
  • Single file when database is closed
  • Required for NFS deployments

MEMORY/OFF Modes:

  • No crash recovery guarantees
  • Use only for specific use cases (caching, temporary data)

Cursor Optimization Limitations

Python Fallback Required When:

  • Complex $expr filters that can't be translated to SQL
  • $where predicates using Python functions
  • Certain edge cases with nested array queries

Performance Impact:

  • SQL-tier optimization provides 10-100x improvement for supported operations
  • Python fallback still functional but slower

Acknowledgments

This release represents a focused investment in production flexibility and performance optimization. The addition of configurable journal modes makes NeoSQLite suitable for a wider range of deployment scenarios, while cursor optimizations provide tangible performance benefits for common query patterns.

The benchmark infrastructure establishes a foundation for data-driven performance improvements, enabling systematic optimization based on empirical measurements.

NeoSQLite v1.9.1 — Flexible Deployment, Optimized Performance.

Assets 2
Loading
1 Join discussion