GitHub Repository Crawler

A high-performance crawler that fetches star counts for 100,000 GitHub repositories using the GraphQL API and stores them in PostgreSQL.

Project Overview

This project was built to satisfy the following requirements:

• Crawl 100,000 GitHub repositories and their star counts
• Store data in PostgreSQL with an efficient, flexible schema
• Run via GitHub Actions with a Postgres service container
• Use only the default GitHub token (no private secrets)
• Be designed for future daily crawling

Challenges Faced and Solutions

Challenge 1: GitHub's 1,000 Result Limit

Problem: GitHub's search API returns a maximum of 1,000 results per query. A single search for "all repositories" would only return 1,000 repos, far short of our 100,000 target. Fixed star ranges don't work because dense star counts (e.g., 0-10 stars) have millions of repos while sparse ranges (50,000+ stars) have only hundreds.

Solution: We implemented adaptive range subdivision. The crawler uses a queue-based approach with automatic splitting:

1. Start with initial broad ranges (e.g., stars:0..4, stars:5..9, etc.)
2. Process each range and count results
3. If a range hits the 1,000 cap, automatically split it in half and add sub-ranges back to the queue
4. Continue until target is reached

Example of adaptive subdivision in action:

[CRAWL] stars:0..4 → fetched 1000 (hit cap!)
[SPLIT] stars:0..4 → subdivided into stars:0..2, stars:3..4
[CRAWL] stars:0..2 → fetched 1000 (hit cap!)
[SPLIT] stars:0..2 → subdivided into stars:0..1, stars:2..2
[CRAWL] stars:0..1 → fetched 1000 (hit cap!)
[SPLIT] stars:0..1 → subdivided into stars:0..0, stars:1..1
[CRAWL] stars:0..0 → fetched 1000 (can't split further, move on)

This approach is scalable because:

• Works for any target (100K, 1M, 500M)
• Automatically finds optimal granularity for dense ranges
• Queue-based architecture is ready for distributed workers
• No manual tuning of range boundaries needed

Challenge 2: Duplicate Repositories

Problem: Some repositories appeared in multiple search results, causing our "fetched count" to be higher than actual unique repos in the database.

Solution:

1. We use PostgreSQL's ON CONFLICT DO UPDATE (upsert) to automatically handle duplicates at the database level
2. We deduplicate within each batch before inserting
3. We track the actual database count, not just fetch count, to know when we've truly reached 100,000 unique repos

Challenge 3: Rate Limiting

Problem: GitHub's API has rate limits (5,000 requests/hour for authenticated users). Hitting the limit would cause failures.

Solution:

1. We monitor the rateLimit field in every GraphQL response
2. When remaining requests drop below a threshold, we pause and wait until the reset time
3. We implement exponential backoff on transient failures

Challenge 4: Crawl Duration

Problem: Initial implementation took too long (30+ minutes) due to frequent database checks and small batch sizes.

Solution:

1. Larger batch sizes (1,000 repos per commit instead of 100)
2. Check database count only between star ranges, not during crawling
3. Optimized star ranges to maximize coverage with fewer API calls

Architecture

The project follows clean architecture principles:

Component	Responsibility
config.py	Immutable configuration from environment variables
models.py	SQLAlchemy ORM models and Data Transfer Objects
github_client.py	GraphQL API client with rate limiting and retries
repository.py	Database operations using the repository pattern
crawler.py	Main orchestration logic

Database Schema

The schema uses GitHub's node ID as primary key for efficient upserts:

Column	Type	Purpose
github_id	VARCHAR(255) PK	Stable unique identifier from GitHub
owner	VARCHAR(255)	Repository owner/organization
name	VARCHAR(255)	Repository name
star_count	INTEGER	Current star count
crawled_at	TIMESTAMP	When first crawled
updated_at	TIMESTAMP	When last updated

This design enables:

• O(1) conflict detection for upserts
• Minimal row updates (only changed data is written)
• Efficient incremental daily updates

GitHub Actions Pipeline

The workflow includes:

1. PostgreSQL 15 service container
2. Python 3.11 setup with pip caching
3. setup-postgres step - creates database schema
4. crawl-stars step - fetches 100,000 repositories
5. Database export to CSV artifact

Uses only the default GITHUB_TOKEN provided by GitHub Actions.

Future Schema Evolution

To add issues, PRs, comments, etc., we would create separate tables with foreign keys to the repositories table. Each entity uses its GitHub node ID as primary key, enabling the same efficient upsert pattern. When a PR gets new comments, only the new comment rows are inserted.

Running Locally

1. Start PostgreSQL (e.g., via Docker)
2. Set environment variables: GITHUB_TOKEN, DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD
3. Run: python scripts/setup_db.py
4. Run: python -m src.crawler
5. Export: python scripts/export_db.py