Description

Add a streaming processing mode that allows sql-pipe to handle datasets larger than available memory by processing data in chunks rather than loading everything at once. Users would enable it with --stream and optionally configure memory limits.

This positions sql-pipe for big data use cases — ETL pipelines, log processing, and data warehouse imports — where files can be gigabytes or terabytes in size.

Example usage

# Stream a 50GB log file, processing in 100MB chunks
cat huge-access.log.csv | sql-pipe --stream --chunk-size 100MB \
  "SELECT status_code, COUNT(*) FROM stdin GROUP BY status_code"

# Memory-limited processing
cat warehouse-export.csv | sql-pipe --stream --memory-limit 512MB \
  "SELECT customer_id, SUM(total) FROM stdin GROUP BY customer_id"

# Streaming with window functions
cat sensor-data.csv | sql-pipe --stream \
  "SELECT timestamp, value, AVG(value) OVER (ORDER BY timestamp ROWS 100 PRECEDING) as moving_avg FROM stdin"

Design considerations

Streaming SQL has inherent limitations — not all queries can be processed without seeing the full dataset. The proposed approach:

Acceptance Criteria

• --stream flag enables chunked processing mode
• --chunk-size configures processing chunk size (default: 64MB)
• --memory-limit sets a hard memory ceiling
• Simple queries (SELECT, WHERE, LIMIT) produce identical results to non-streaming mode
• GROUP BY with bounded cardinality works correctly
• Clear error message when a query requires full-table access in streaming mode
• Performance benchmarks: process 1GB+ CSV files with <256MB memory
• Existing non-streaming behavior is unchanged by default

Recommended split plan

This is a size:xl epic. When scheduled for a sprint, split into these sub-issues:

1. Spike: evaluate streaming approaches (size:s, type:spike) — investigate two approaches: (a) SQLite virtual table (sqlite3_vtab) for streaming CSV input, vs (b) disk-backed temp storage (PRAGMA temp_store = FILE). The disk-backed approach may deliver 80% of the value with 20% of the complexity. Produce a recommendation.
2. Disk-backed large dataset support (size:m) — implement the simpler approach first: configure SQLite to use disk-backed temp storage for large datasets. Add --memory-limit flag. No API changes to query semantics — all SQL operations still work, just slower for large data.
3. Chunked CSV reading (size:m) — implement streaming CSV reader that feeds data to SQLite in chunks rather than reading the full file into memory. Add --stream and --chunk-size flags.
4. Streaming query validation (size:s) — detect queries that require full-table access (unbounded ORDER BY, certain JOINs) and emit a clear error in streaming mode instead of producing wrong results.
5. External sort for ORDER BY (size:m) — implement external merge sort for large ORDER BY operations in streaming mode. Only needed if disk-backed approach doesn't solve it.

Dependencies

• Sub-issue ordering: 1 (spike) → 2 or 3 (based on spike outcome) → 4 → 5
• No dependencies on other backlog issues

Notes

• SQLite has a concept of virtual tables that could be used for streaming — investigate sqlite3_vtab for a streaming CSV virtual table
• Alternative approach: use SQLite's temp storage on disk (PRAGMA temp_store = FILE) to handle large datasets without explicit streaming
• The simpler "disk-backed" approach may deliver 80% of the value with 20% of the complexity — start with a spike to evaluate