Comprehensive cursor benchmark with memory and performance metrics

[laughingman7743]

Summary

Create comprehensive benchmarks comparing all cursor types with focus on:

• Result set fetching performance
• Memory usage with different chunk options
• Comparison with AWS Wrangler

Background

Related issues have highlighted the need for clearer performance guidance:

• larger than memory queries? #61: Questions about handling larger-than-memory queries
• SQLAlchemy + Pandas very slow when compared to AWS Wrangler #254: Performance comparison with AWS Wrangler

The existing benchmarks (benchmarks/20180915/, benchmarks/20220201/) are outdated and don't cover the full range of cursor options now available.

Benchmark Scope

Cursors to Test

• Cursor / DictCursor
• PandasCursor (with/without chunksize)
• ArrowCursor (arraysize, unload options)
• PolarsCursor (with/without chunksize)
• S3FSCursor

Metrics to Measure

Category	Metrics
Speed	Query execution time, result set fetch time
Memory	Peak memory usage, memory with chunk options
Comparison	Side-by-side with AWS Wrangler
Scale	Small, medium, large dataset behavior

Data Source

Use the public PyPI download statistics dataset from BigQuery, which can be exported to S3 for Athena queries. This provides realistic, reproducible test data at various scales.

Reference: https://console.cloud.google.com/marketplace/product/gcp-public-data-pypi/pypi

Expected Deliverables

1. Updated benchmark scripts in benchmarks/ directory
2. Documentation with:
   • Performance comparison tables
   • Memory usage guidance
   • Recommendations for cursor selection based on use case
3. README explaining how to reproduce benchmarks

Notes

Data preparation is required before implementation. The benchmark results should help users choose the appropriate cursor for their use case and understand trade-offs.

[laughingman7743]

Addition: Native asyncio cursor benchmarks

With the completion of native asyncio cursors (#662, PRs #666 #667 #668), benchmarks should also include the aio cursor variants:

Additional cursors to benchmark

• AioCursor / AioDictCursor
• AioPandasCursor
• AioArrowCursor
• AioPolarsCursor
• AioS3FSCursor

Aio-specific metrics

Category	Metrics
Latency	Single-query latency: sync vs AsyncCursor (ThreadPoolExecutor) vs AioCursor (native asyncio)
Concurrency	Throughput under concurrent queries (e.g., 10/50/100 concurrent execute() calls)
Event loop impact	Event loop blocking time during query execution and result fetching
Thread usage	Thread pool utilization: AsyncCursor vs AioCursor

Key comparisons

1. Sync Cursor vs AsyncCursor vs AioCursor — same query, single execution
2. Concurrent execution scaling — AsyncCursor (thread pool) vs AioCursor (event loop) at varying concurrency levels
3. Result set creation overhead — asyncio.to_thread(ResultSet, ...) vs direct construction
4. Per-cursor-type comparison — e.g., PandasCursor vs AioPandasCursor for DataFrame workloads