Use Case
We're building a scanner that detects AI policies across open source projects (checking for files like AGENTS.md, CLAUDE.md, AI_POLICY.md, CONTRIBUTING.md, etc.). The scanner checks ~34 target file paths per repo via the GitHub API.
On a 2,000-repo scan using the CollectOSS database, 98% of repos came back as "silent" (no policy files). That means we're making ~40-50 API calls per repo on ~1,960 repos that have nothing — roughly 80k wasted API calls per batch, taking about 3 hours.
Proposed Optimization
Pre-filter using the Augur DB before hitting the GitHub API:
- Query
data.commits.cmt_filename to check which repos have any of our 34 target files in their commit history
- Skip the 98% that don't — zero API calls for those
- Only fetch file contents via GitHub API for repos the DB confirms have relevant files
This would reduce API calls by ~20-30x and bring a 3-hour batch down to ~10-15 minutes.
The Problem
Neither data.commits.cmt_filename nor data.pull_request_files.pr_file_path has an index. Every query does a full table scan.
I tested 12 repos — 11 out of 12 timed out at a 10-second query timeout. The one that completed (apache/airflow) took 78 seconds without a timeout.
Requested Index
CREATE INDEX idx_commits_repo_filename ON data.commits (repo_id, cmt_filename);
And optionally:
CREATE INDEX idx_pr_files_repo_path ON data.pull_request_files (repo_id, pr_file_path);
The query pattern would be:
SELECT DISTINCT cmt_filename
FROM data.commits
WHERE repo_id = (SELECT repo_id FROM data.repo WHERE repo_git = %s)
AND cmt_filename = ANY(%s)
This should drop from 78s to sub-second per repo.
Use Case
We're building a scanner that detects AI policies across open source projects (checking for files like AGENTS.md, CLAUDE.md, AI_POLICY.md, CONTRIBUTING.md, etc.). The scanner checks ~34 target file paths per repo via the GitHub API.
On a 2,000-repo scan using the CollectOSS database, 98% of repos came back as "silent" (no policy files). That means we're making ~40-50 API calls per repo on ~1,960 repos that have nothing — roughly 80k wasted API calls per batch, taking about 3 hours.
Proposed Optimization
Pre-filter using the Augur DB before hitting the GitHub API:
data.commits.cmt_filenameto check which repos have any of our 34 target files in their commit historyThis would reduce API calls by ~20-30x and bring a 3-hour batch down to ~10-15 minutes.
The Problem
Neither
data.commits.cmt_filenamenordata.pull_request_files.pr_file_pathhas an index. Every query does a full table scan.I tested 12 repos — 11 out of 12 timed out at a 10-second query timeout. The one that completed (apache/airflow) took 78 seconds without a timeout.
Requested Index
And optionally:
The query pattern would be:
This should drop from 78s to sub-second per repo.