Repository navigation
Reduce the AWS cost of integration tests without reducing real-service coverage #834
Description
Use case
PyAthena's integration tests run against real AWS services, which is how the project guarantees behavior. The project has no funding, so the test cost should be as low as possible without reducing that real-service coverage.
This month (2026-09-01 to 09-25, Cost Explorer, us-west-2, before tax) the test account cost about $143:
| Service | Cost | Largest usage |
|---|---|---|
| S3 | $74.4 | PutObject 10.8 million calls ($54.0), ListBucket $8.4, GetObject $7.6, HeadObject $2.3; storage about $1.5 |
| Athena | $52.5 | SQL scanned 6.4 TB ($32.2), Spark 58 DPU-hours ($20.3) |
| Glue | $9.7 | 10.7 million Data Catalog requests |
Daily cost follows CI activity (for example $37 on 09-19 with 60 Test runs, $0.3 on 09-22 with none), about $0.6 per Test run. The CI bucket expires all objects after one day, so storage is negligible; the cost is requests and per-query charges.
In September the Test workflow ran 244 times: 239 from pull_request events, 3 scheduled, 2 manual. 134 of them were cancelled by concurrency: cancel-in-progress when a newer push arrived, 78 succeeded, and 28 failed. A cancelled run still pays for everything it executed before cancellation. Each run starts 15 jobs (pyathena, sqla, sqla-async × Python 3.10–3.14), including runs for Draft PRs.
Where the requests come from (static analysis of the test code, to be confirmed by measurement):
- The SQLAlchemy compliance suites (10 of the 15 jobs) create every table as Iceberg (
tests/sqlalchemy/conftest.pyappends'table_type'='ICEBERG'). SQLAlchemy'sTablesTestinserts its fixture rows before each test and deletes them afterwards (run_inserts="each",run_deletes="each"), including tests that are skipped by method-level requirements. The dialect sends these rows throughexecutemany, which runs oneexecute()per row (pyathena/result_set.py), so every fixture row is a separate Iceberg commit that writes several objects. Classes such asExpandingBoundInTest,FetchLimitOffsetTest,WindowFunctionTest(49 rows per test), andRowCountTestdominate. - Every Athena query writes a result object and a
.metadataobject, and each query is billed at least 10 MB scanned, so the number of queries drives both S3 PutObject and Athena scan cost. The session setup of the pyathena suite also re-creates the test tables in each of the 9 pytest processes per job. - Spark tests account for the Spark DPU cost.
Proposed change
Keep full real-service coverage where it matters (before merge and on master), and stop paying for runs whose results are discarded:
- Do not run AWS integration jobs for Draft PRs. Run them when a PR is marked Ready (
ready_for_review), on pushes to a Ready PR, onmaster, weekly, and on manual dispatch. Draft PRs keep the offline checks (lint, unit-level jobs, docs, license headers). Contributors can still dispatch a run manually or test locally with their own AWS account. - Keep the full 15-job matrix before merge, but consider a smaller matrix for intermediate pushes to a Ready PR (for example, all three suites on the oldest and newest Python) and the full matrix on the final pre-merge run and on
master. This needs a way to request the full matrix explicitly (label or dispatch input). - Run Spark tests only when Spark-related code or tests change, and weekly.
- Measure the effect with Cost Explorer (S3 PutObject and Athena per day, divided by Test runs) for two weeks before and after.
Code-level reductions that also improve behavior for users are tracked separately (batched multi-row INSERT for SQLAlchemy executemany), and so is making the compliance suite's read-only fixture data cheaper (for example, run_inserts="once" or Hive tables for classes that do not need DML); those change what the tests exercise and need their own discussion.
Validation plan (if implementing)
- Workflow changes can be validated with a Draft PR (integration jobs skipped), marking it Ready (jobs start), and a manual dispatch, then comparing the jobs that ran against the intended matrix.
- Compare Cost Explorer PutObject counts and Athena cost per run before and after.
- Refs Comprehensive cursor benchmark with memory and performance metrics #644, where the cost was investigated.
Activity
laughingman7743 commentedon Sep 26, 2026
Measurements (read-only) that refine the analysis above:
Objects written to the CI bucket. The bucket keeps one day of objects (1-day expiration), so a listing on 2026-09-26 covered about 1.5 days of CI: 2,311,263 objects.
github/)<uuid>.csv,.csv.metadata,.metadata,.txt,.txt.metadata,-manifest.csv)workgroup=pyathena-spark/)So S3 PutObject cost is driven by the number of Athena queries (2–3 result objects each), not by table data. The per-query Athena minimum charge and the Glue catalog requests scale with the same number.
Which queries. A sample of the latest 10,000 queries in the
primaryworkgroup used by CI:INSERT INTO ... VALUESSELECTDELETE FROMCREATE TABLE/DROP TABLECREATE EXTERNAL TABLE,UPDATE,CREATE DATABASE/VIEW,UNLOAD, ...)Single-row inserts and per-test deletes, which come from the SQLAlchemy compliance fixtures (
run_inserts="each",run_deletes="each", andexecutemanyrunning one statement per row), are over half of all queries.Additional option: a weekly-only test tier. Keep a core set of real-service tests on Ready PRs and
master, and move expensive, slow-changing groups to a separate workflow that runs weekly (and before a release, and on manual dispatch). Candidates to evaluate by cost and by how often the covered code changes:ExpandingBoundInTest,FetchLimitOffsetTest,WindowFunctionTest,RowCountTest), or the async variant of the compliance suite on all but one Python version.The trade-off is that a regression in a weekly-only area is found up to a week later; running the weekly tier before every release and on demand limits that risk. The weekly workflow should report failures clearly (for example, by opening or updating an issue).
laughingman7743 commentedon Sep 26, 2026
The code-level change to batch SQLAlchemy
executemanyinserts into multi-rowINSERTstatements is tracked in #835.laughingman7743 commentedon Sep 26, 2026
Proposed direction for the SQLAlchemy compliance suites (
just test sqlaandjust test sqla-async, 10 of the 15 jobs per Test run):In the same sample of the latest 10,000 CI queries, 65% came from the compliance suites (databases
test_*) and 35% from the PyAthena suite (pyathena_test_*). The PyAthena suite already exercises the dialect against AWS intests/pyathena/sqlalchemy/andtests/pyathena/aio/sqlalchemy/, while the compliance suites run SQLAlchemy's generic conformance tests.Run the compliance suites:
pyathena/sqlalchemy/**,pyathena/aio/sqlalchemy/**,tests/sqlalchemy/**, and the SQLAlchemy dependency inpyproject.toml/uv.lock),Other pull requests would run the PyAthena suite only, which on this sample removes about two thirds of the queries (and therefore of the S3 PutObject, per-query Athena charges, and Glue requests) and 10 of the 15 jobs per run. Compliance regressions caused by non-dialect changes would be found by the weekly run at the latest; the release run keeps them from shipping.
laughingman7743 commentedon Oct 10, 2026
All sub-issues are resolved and the cost measurement in #846 shows about an 87% reduction in the daily test account cost (from $17.6 to $2.3 per day). Closing.