Investigate persistent metadata throttling during SQLAlchemy reflection

[laughingman7743]

Problem

SQLAlchemy reflection can still fail with Athena metadata throttling after the request-reuse and concurrency changes in #777.
Restoring reflection coverage in #771 exposed the problem during the compliance suite; the root cause and practical impact outside CI are not yet established.
Track the remaining investigation separately from the weekly database sweep and development-tooling work in #779.
This blocks completion of the prerequisite metadata fix for the ARRAY work in #755.

Current implementation and evidence

Continue from #777, currently Draft at ea4ce1fc04c922592ef9f6b0fad46702c894598c.
Do not discard the existing implementation or start a replacement PR without a concrete reason.

• Positive table metadata is shared within one SQLAlchemy Inspector across listings and column/comment/table-option reflection. Catalog and schema identity, cache invalidation, and preservation of earlier results have regression coverage.
• Fresh CREATE/DROP existence checks remain uncached. A profiled synchronous baseline attributed 378 of 643 GetTableMetadata SDK calls to these checks.
• Wrapped throttling, access denial, and unknown metadata failures must propagate rather than establish or cache table absence.
• CI SDK retries are bounded to standard mode with three attempts. SQLAlchemy matrices run at most two Python versions concurrently, with eight workers per job; this does not limit traffic from other PRs.
• CI run 35448063098 completed on that head: all five regular PyAthena jobs passed; SQLAlchemy 3.10, 3.11, and 3.12 failed, while 3.13 and 3.14 passed. The dependent async suite was skipped.
• Catalog cleanup improved database enumeration, but metadata throttling recurred afterward. The database backlog is not established as its cause.
• Earlier full local synchronous and asynchronous baselines passed, but used a different AWS account from CI. They do not establish that the current head passes in the CI account. Use the current local .env configuration and verify the target before interpreting new results.
• Independent review findings about error-propagation regression coverage and documentation were repaired in the current head. The final independent follow-up remains incomplete.

Questions to resolve

1. Which API calls, callers, retry layers, and concurrency patterns cause the remaining failures in the intended test account? Separate Inspector reflection from fresh existence checks and distinguish client calls from actual SDK attempts.
2. Does comparable reflection traffic fail in normal application use, or only under the compliance suite's load? Measure request counts, elapsed time, and failure rates under controlled concurrency before proposing further retries or a quota increase.
3. Can additional request reuse reduce traffic without changing metadata freshness, cross-catalog behavior, or error visibility?
4. Would a limited information_schema path help bulk column reflection? Current Athena checks found column comments, type parameters, and partition markers, but not all table comments, location, SerDe, or table properties used by the dialect. A full replacement would lose information, and information_schema itself can fail with large catalogs. Any experiment must compare semantics and traffic as well as latency.

Reproduction and validation

Use the existing #777 worktree/branch and repository workflow.
Run just worktree-env in a worktree, then use uv run --env-file .env ... to load the intended AWS configuration without printing credentials.
Do not overlap local live-Athena tests with CI or other benchmark/test workloads.

First recheck the current PR head, CI logs, and any active test runs.
Then reproduce with existing HasTableTest cases and temporary API instrumentation when useful:

just lint
uv run --env-file .env pytest -n 1 tests/sqlalchemy/test_suite.py::HasTableTest -q
uv run --env-file .env pytest -n 1 tests/sqlalchemy/test_suite.py::HasTableTest --dburi async -q

These targeted commands are starting points for investigation, not evidence that the entire current suite passes.
After a repair, run the required synchronous and asynchronous suites sequentially:

just lint
uv run --env-file .env just test sqla
uv run --env-file .env just test sqla-async

Use the existing test classes and fixtures; do not add a TestMetadataReflection class or a parallel mock-heavy framework.
Real S3 Tables absence handling has been checked; Lambda/Hive federated catalogs remain unverified.
Preserve that distinction when reporting compatibility.

Code and history pointers

• pyathena/sqlalchemy/base.py: reflection cache identity, list/individual metadata reuse, and fresh existence checks.
• pyathena/aio/sqlalchemy/base.py: async adapter behavior and effective cursor options.
• pyathena/util.py: retry exception normalization and wrapped service errors.
• tests/sqlalchemy/test_suite.py: existing reflection reuse/invalidation tests and HasTableTest.test_metadata_errors_do_not_establish_absence.
• .github/workflows/test.yaml and test-suite.yaml: SQLAlchemy matrix limits and SDK retry settings.
• Add support for table and column comments. #263 / Add support for table and column comments #264: historical discussion of table metadata, comments, location, and properties.
• Support getting view names in SQLAlchemy (fix #244) #265 / Change metadata retrieval from information schema to API in SQLAlchemy #286: table/view and column reflection migration to metadata APIs.
• No longer a SQLAlchemy column for the Partition #292 / Add missing partition key columns to get_columns in SQLAlchemy (fix #292) #293: partition-column regression after that migration and its repair.
• Change schema names retrieval in SQLAlchemy from information schema to API (fix #359) #363: schema enumeration migration to ListDatabases.

The historical PR descriptions do not prove that earlier Athena versions lacked type parameters: the old client also stripped them.
Preserve this distinction when evaluating a new information_schema experiment.

Completion criteria

• Document a reproducible failure scenario and the evidence supporting the chosen repair, including limits on production-impact claims.
• Preserve cache invalidation, catalog/schema isolation, fresh existence checks, and missing-object versus failed-request semantics.
• Do not mask unexplained failures by increasing retry budgets or weakening tests.
• Pass applicable local validation and current aggregate CI, including the async suite, before marking Fix metadata reflection throttling and reuse listed metadata #777 Ready.
• Record two distinct self-reviews and collect the independent follow-up required by the repository workflow.

Related work

• SQLAlchemy compliance: restore reflection and identifier coverage #757: restored reflection coverage, completed through Restore SQLAlchemy reflection and identifier coverage #771.
• Fix metadata reflection throttling and reuse listed metadata #777: active metadata implementation and review evidence.
• Sweep stale test databases after weekly tests #779: separate weekly sweep and tooling maintenance.
• SQLAlchemy compliance: enable native ARRAY type support #755: original ARRAY objective. The open Draft stack is Support native SQLAlchemy ARRAY types and typed round trips #774 (native types, reflection, typed I/O) → Support SQLAlchemy ARRAY expressions in SELECT and WHERE #775 (SELECT/WHERE expressions) → Support SQLAlchemy ARRAY partial UPDATE and slice resizing #776 (partial UPDATE and slice resizing). Resume that stack separately after the prerequisite metadata fix.

[laughingman7743]

Investigation results at PR #777 head 5ae3125.

Where the remaining failures come from

• All failures in run 35448063098 were ThrottlingException on GetTableMetadata, raised after PyAthena exhausted its five retry attempts. Callers: SQLAlchemy create_all(checkfirst=True) → has_table during class setup, and ComponentReflectionTest reflection. Retry logs captured by pytest show each exhausted call was throttled continuously for 22-25 s; 13 of the 14 calls that reached PyAthena's retry loop in the two failed jobs exhausted it. Single-job runs had zero retries.
• The Athena Service Quotas listing for the CI account contains no entry for GetTableMetadata, ListTableMetadata, or ListDatabases; the metadata limit is per account and cannot be raised through Service Quotas.
• A controlled probe (metadata calls on a non-existent table, no resources created) showed an adaptive limiter, not a fixed bucket: 20 calls/s pass from idle, 40 calls/s is throttled for a few seconds before capacity grows, and after sustained traffic 60 Get + 15 List calls/s pass. One 16-thread run reproduced the CI signature exactly: 32 of 32 calls exhausted the five-attempt policy after 21-25 s each, and the episode cleared within 20 s of silence. PR Sweep stale test databases after weekly tests #779's run with SDK adaptive mode (ten attempts, five versions in parallel) still exhausted one call after 307 s.

Production impact

The limit is account-wide and undocumented, so any deployment that reflects tables or runs checkfirst DDL from several processes at once can hit the same episodes; there is no quota to request. The failure mode is episodes lasting tens of seconds, which the previous retry window (about 15 s of waiting) could not outlast.

What #777 now does

• Jittered exponential backoff and seven default attempts (waits sum to 63 s plus jitter), applied to every PyAthena API retry.
• has_table() falls back to one information_schema.tables query only when the metadata request is still throttled after those retries. The fallback answers existence only; column, comment, and table-option reflection still propagate the throttling error and nothing from the fallback is cached as metadata.
• ListTableMetadata with an exact expression returns metadata identical to GetTableMetadata and works on Glue and S3 Tables catalogs, but the probe showed no separate capacity for it (one mixed run starved the list calls entirely), so has_table() was not moved to the list API.
• information_schema is not a replacement for reflection: it lacks table comments, location, SerDe, and table properties, and a query costs about two seconds against about 0.2 s for the API.
• CI keeps SDK retries at standard/3 and two Python versions per SQLAlchemy matrix so the suites exercise the library policy under real contention.

Limits: the probe comparisons of wait policies were confounded by the limiter's state and are not a controlled measurement; Lambda/Hive federated catalogs remain unverified.

[laughingman7743]

Update: PR #777 head 6d791e2 passed CI run 35476810018 (regular, synchronous and asynchronous SQLAlchemy suites, two versions in parallel, SDK retries standard/3) and is marked ready for review. The inspected job logs show no throttling in that run, so the pass confirms the suites but does not itself exercise the longer retry window or the existence fallback; those are covered by unit tests, the live HasTableTest cases and the probe measurements above. The independent review of 5ae3125 and its follow-up on 9683e36 are recorded inline on the PR; the remaining unverified area is Lambda/Hive federated catalogs.

[laughingman7743]

Update: PR #777 now reads information_schema.columns as soon as a table-metadata request is throttled, for both has_table() and column reflection (table comments and options still use the metadata API with the configured retries; default attempts are eight, waits summing to 127 s plus jitter), and CI runs all five Python versions in parallel again. Head d69e37f passed run 35491352851 (regular, synchronous and asynchronous SQLAlchemy suites, five-way parallel, SDK retries standard/3). An earlier five-way run that overlapped the weekly master run raised no throttling errors; only two request-count tests of the PR failed and were repaired. The weekly master run itself (adaptive/10) failed its async 3.13 job after a GetTableMetadata call stayed throttled for 307 s (run 35488960929), which is the failure this PR addresses. Reviews are recorded inline on the PR; Lambda/Hive federated catalogs remain unverified.

[laughingman7743]

Update: after the Codex independent review (two medium findings: cursor-schema resolution in the information_schema fallback, and MetadataException in the derived lookup policy; one coverage gap) were repaired, PR #777 head c4a4ab9 passed run 35494543202 with all five Python versions in parallel for the regular, synchronous and asynchronous SQLAlchemy suites. The PR remains ready for review.

[laughingman7743]

Final status and measured effect (PR #777 at c4a4ab9)

What changed

• has_table() (including SQLAlchemy checkfirst DDL) and column reflection (get_columns, get_multi_columns) make one table-metadata attempt. When Athena reports ThrottlingException/TooManyRequestsException, directly or wrapped in a MetadataException, they read information_schema.columns at once (about 2 s) instead of waiting out the retry policy. The fallback runs without query result reuse, is cached under its own Inspector key until clear_cache(), and never masquerades as full table metadata.
• get_table_comment/get_table_options and every other API call keep the metadata API with the retry policy, which now uses exponential backoff with uniform jitter and eight attempts by default (waits summing to 127 s plus jitter, previously five attempts and about 15 s).
• Inspector metadata is shared between listings and individual lookups; the cursor catalog and schema resolve consistently for requests, fallback queries and cache keys.
• CI runs all five Python versions concurrently again with SDK retries at standard/3.

Effect

Path	Before	After	Evidence
has_table / checkfirst DDL	failed after ~15 s of retries	switches to information_schema on the first throttled response	run 35488028983 (ten SQLAlchemy jobs at once, overlapping the weekly master run): zero throttling errors; the same load previously produced 20+ setup errors per job
get_columns / get_multi_columns	same	same	five-way runs 35484370698, 35491352851 and 35494543202 passed every job; before this PR, five-way runs failed all five SQLAlchemy jobs (run 35441534584) and the weekly master run failed after a 307 s throttle (run 35488960929)
comments, options, other API calls	15 s window	127 s window plus jitter	outlasts the 20-80 s episodes recorded in CI and the probe

Limits

• Table(autoload_with=...) and MetaData.reflect() also read comments and options, so an episode longer than the retry window (307 s was observed) still fails them; time to failure grows from ~15 s to up to ~130 s.
• Under throttling, reflected column types follow information_schema names (varchar for string) and each table costs one query.
• Verified on Glue and, for listing semantics, S3 Tables; Lambda/Hive federated catalogs and DataFrame/UNLOAD-backed cursors are covered by unit tests only.
• The account-level metadata limiter is adaptive and cannot be raised through Service Quotas; passing CI jobs do not print retry or fallback logs, so the amount of throttling absorbed in green runs is not recorded.

Reviews

Two self-reviews per change set, Claude independent reviews with follow-ups, and a Codex independent review with follow-up are recorded inline on #777; all actionable findings were repaired. The PR is ready for review and not merged.