Skip to content

v0.2.0rc5 - Performance Optimizations & Repository Factory Pattern

Choose a tag to compare

View all tags
@luiscarbonel1991 luiscarbonel1991 released this 20 Jan 18:47
· 53 commits to main since this release
v0.2.0rc5
fe88c7c

Summary

This release introduces significant performance optimizations for schema analysis, including batch embedding generation that reduces API calls by up to 90%, disk-based caching for schema metadata with configurable TTL, and a new Repository Factory pattern for cleaner architecture. These changes address Issue #22 and Issue #24, reducing first-query latency by ~50% and repeated-query latency by ~95%.

Breaking Changes

None in this release.

Key Changes

Performance Optimizations

Batch Element Embeddings (Issue #24)

  • Implements score_relevance_batch() method that batches all element descriptions into a single API call
  • Reduces embedding API calls from N (one per table) to 1 per query
  • For a schema with 10 tables, this reduces API calls by 90%
  • (commit: d333229)

Query Embedding Cache (Issue #22)

  • Caches query embeddings per-request to avoid N+1 API calls during semantic similarity scoring
  • Same query embedding is reused for all tables in a single request
  • Adds clear_query_embedding_cache() method to prevent unbounded memory growth
  • (commit: c39dc9f)

Table Relevance Cache (Issue #22)

  • Enables _table_relevance_cache in SchemaManager for instant repeated queries
  • Subsequent identical queries return cached relevance scores immediately
  • (commit: c39dc9f)

Disk-Based Schema Cache

  • Schema metadata is now cached to disk with configurable TTL
  • Configurable via NLP2SQL_SCHEMA_CACHE_TTL_HOURS environment variable (default: 24 hours)
  • Cache files stored in embeddings/<db_hash>/tables_cache_<schema>.pkl
  • Eliminates redundant database queries for schema metadata on subsequent requests
  • (commit: d333229)

Architecture Improvements

Repository Factory Pattern

  • Introduces RepositoryFactory class for centralized repository creation
  • Auto-detects database type from connection URL (PostgreSQL, Redshift)
  • Provides both sync create() and async create_and_initialize() methods
  • Adds create_repository() convenience function to public API
  • (commit: d333229)

Schema Repository Interface

  • Adds clear_cache() method to SchemaRepositoryPort interface
  • Allows adapters to implement cache invalidation logic
  • (commit: d333229)

MCP Server Refactor

  • Replaces direct repository instantiation with factory methods
  • Uses RepositoryFactory.detect_database_type() for URL parsing
  • Cleaner, more maintainable code structure
  • (commit: d333229)

CLI Improvements

URL Validation Enhancement

  • Now supports both postgresql:// and postgres:// URL prefixes
  • Delegates database type detection to RepositoryFactory for consistency
  • (commit: d333229)

Testing

  • Adds 491 lines of batch scoring tests (test_batch_scoring.py)
  • Adds 380 lines of cache mechanism tests (test_repository_cache.py)
  • Adds 308 lines of factory pattern tests (test_repository_factory.py)
  • Total test count increased from 57 to 133 tests
  • (commit: d333229)

Performance Impact

Scenario Before After Improvement
First query (schema analysis) ~2s ~1s 50% faster
Repeated identical query ~2s <100ms 95% faster
API calls for 10 tables 10 calls 1 call 90% reduction

Note: The overall query time (~25-30s) is dominated by the LLM call for SQL generation (~93% of total time). These optimizations target the schema analysis phase which accounts for the remaining ~7%.

Technical Notes

Architecture Decisions

  1. Factory Pattern: Centralizes repository creation logic, making it easier to add new database adapters and ensuring consistent initialization across CLI, MCP server, and direct API usage.

  2. Disk Cache Strategy: Uses pickle serialization with version tracking to detect format changes. Cache files are stored alongside embedding indexes for consistency.

  3. Batch Scoring Approach: Combines fast text-based scoring (no API calls) with selective semantic scoring only for elements that pass initial thresholds.

Configuration

Environment Variable Default Description
NLP2SQL_SCHEMA_CACHE_TTL_HOURS 24 Hours before schema cache expires
NLP2SQL_EMBEDDINGS_DIR ./embeddings Directory for cache and index files

Migration Notes

If upgrading from 0.2.0rc4:

  1. Existing embedding caches remain compatible - no clearing required
  2. New disk cache will be created automatically on first query
  3. To force schema refresh, set NLP2SQL_SCHEMA_CACHE_TTL_HOURS=0 or call repository.clear_cache()

API Changes

New Public Functions

# Convenience function for repository creation
async def create_repository(
    database_url: str,
    schema_name: str = "public",
    database_type: Optional[DatabaseType] = None,
) -> SchemaRepositoryPort

New Classes

class RepositoryFactory:
    @staticmethod
    def create(database_url, schema_name, database_type) -> SchemaRepositoryPort

    @staticmethod
    async def create_and_initialize(database_url, schema_name, database_type) -> SchemaRepositoryPort

    @staticmethod
    def detect_database_type(database_url) -> DatabaseType

New Interface Methods

# Added to SchemaRepositoryPort
def clear_cache(self) -> None:
    """Clear any cached data (disk cache, memory cache)."""

Dependencies

No dependency changes.

Verification

# Run all tests (133 tests expected)
uv run pytest tests/ -v

# Test disk cache creation
uv run nlp2sql query --database-url "postgresql://..." --question "Show tables"
ls -la embeddings/*/tables_cache_*.pkl

# Test cache invalidation
NLP2SQL_SCHEMA_CACHE_TTL_HOURS=0 uv run nlp2sql query --database-url "postgresql://..." --question "Show tables"

Related Issues

  • Closes #24 - Batch element embeddings to reduce API calls during semantic similarity scoring
  • Closes #22 - Add caching for query embeddings and table relevance scoring (follow-up improvements)

Documentation & Developer Experience

Claude Code Integration

  • Added .claude/ directory with team configuration
  • Custom commands: /test, /quality, /db for common workflows
  • Context-aware rules for Python style, testing, and architecture
  • Pre-approved permissions for uv run, uv sync, and docker compose

Security Policy

  • Added SECURITY.md with vulnerability reporting process
  • Defined security scope specific to nlp2sql (SQL injection, credential exposure)
  • Response timeline commitments (48h initial, 30d resolution)
  • Security best practices for production deployments

Documentation Standards

  • Removed emojis from CONTRIBUTING.md for professional consistency
  • Added LICENSE file (MIT)
  • Updated .gitignore for Claude Code local settings

Contributors

Contributors

luiscarbonel1991
Assets 2
Loading