Performance Tuning

SQLSpec keeps adapter defaults conservative. Most performance controls are opt-in because the right value depends on query shape, connection lifetime, network latency, and the database service in front of the driver.

Start by measuring the query path you want to improve. The optional benchmark helper documents service-backed scenarios for the cache and fetch controls:

uv run python tools/scripts/bench_tuning.py --list

Cache and fetch controls

Adapter	Knob	Classification	Use when	Avoid when
All drivers	driver_features={"sqlspec_statement_cache_size": N}	SQLSpec statement cache	The same raw SQL text runs repeatedly with simple parameters and no per-call transformers.	SQL text is high-cardinality, DDL changes affect cached result shapes, or you need to isolate query preparation behavior. Set 0 to disable.
asyncpg	connection_config={"statement_cache_size": N}	Native asyncpg prepared-statement cache	A long-lived connection repeats parameterized statements and talks directly to PostgreSQL.	PgBouncer uses transaction or statement pooling, or schema changes happen inside transactions on the same connection. Use 0 to disable.
psycopg	connection_config={"prepare_threshold": N}	Native psycopg server-side prepare threshold	A query repeats enough times on the same pooled connection to amortize server-side planning.	Queries are rarely repeated, connection middleware cannot keep prepared statements session-local, or schema churn is expected. Use None to disable.
oracledb	connection_config={"stmtcachesize": N}	python-oracledb statement cache	The same statement text is executed frequently on pooled Oracle sessions.	Statement text has high cardinality or memory pressure matters more than parse reuse.
oracledb	driver_features={"arraysize": N, "prefetchrows": N}	Cursor fetch buffering	Large result sets spend more time on network round trips than row materialization.	Single-row lookups dominate, rows are very wide, or larger buffers increase memory pressure.
oracledb	driver_features={"fetch_lobs": False, "fetch_decimals": True}	Per-statement fetch representation	You want python-oracledb's native LOB or NUMBER fetch mode for result conversion.	Application code depends on the default LOB locator or numeric representation.
bigquery	driver_features={"query_page_size": N, "query_max_results": N}	Query result paging	SELECT result consumption should bound per-page fetch size or cap rows returned by QueryJob.result().	Running DML or scripts. These controls only apply to SELECT-style result fetching and native table Arrow export completion.
arrow_odbc	driver_features={"chunk_size": N, "max_bytes_per_batch": N}	Native Arrow batch sizing	ODBC Arrow reads should trade memory for fewer batches or smaller batches for steadier memory.	Downstream processing expects a particular batch size, or the driver/database already determines a better batch shape.
arrow_odbc	driver_features={"max_text_size": N, "max_binary_size": N, "fetch_concurrently": bool}	ODBC Arrow fetch behavior	Text or binary columns need explicit bounds, or concurrent fetch improves a high-latency ODBC source.	Column sizes are unknown and truncation is unacceptable, or the ODBC source is unstable under concurrent fetch.

Avoid-when guidance

Prepared-statement and statement-cache controls help only when statement text is reused on the same connection. They can be counterproductive when SQL is generated with many literal variations, when schema changes run on long-lived sessions, or when a proxy changes the server session behind a client connection.

Fetch-size controls help only after the query is already correct and indexed. They do not fix slow plans. Use them to tune memory and network behavior for known result shapes, then verify with representative row widths.

BigQuery result paging controls are SELECT-result controls. They are intentionally not applied to DML or script completion calls, so minimal BigQuery configs keep the client's default result behavior.

arrow_odbc divergence

arrow_odbc does not follow DB-API cursor buffering patterns. Its fetch controls are passed to the driver's Arrow batch reader, where chunk_size and max_bytes_per_batch shape Arrow batches directly. This differs from Oracle arraysize and prefetchrows, which tune cursor buffering before SQLSpec materializes rows.

For arrow_odbc, benchmark both memory and wall time. A larger batch can reduce driver round trips but increase peak memory, while smaller batches can make streaming steadier for downstream Arrow consumers.