GitHub Org Event Crawler Using gharchive and Postgres
Use gharchive hourly dumps to avoid API limits.
Set up cron to fetch gharchive and load into Postgres.
Summary
The article describes a crawler that avoids GitHub API rate limits by downloading hourly JSONL dumps from gharchive.org and streaming them into a Postgres database. The pipeline runs a cron job that fetches the archive, filters events for a list of orgs, and copies them into an events_raw table. A simple two‑table schema (events_raw and orgs_watchlist) plus a materialized view provides weekly metrics.
This approach reduces API calls by two orders of magnitude, keeps the connection pool healthy, and allows backfilling the entire year in a single afternoon.
Key components include the cron script, the Postgres schema, and the materialized view refresh strategy.
Teams can replicate this pipeline to monitor engineering velocity and detect fundraising signals.
The article also discusses signal flavors such as hiring bursts, infrastructure buildouts, deploy frequency spikes, and framework migrations.
Adopting this design requires setting up the cron job, creating the schema, and scheduling the materialized view refresh.
Key changes
- Set up hourly cron to fetch gharchive JSONL
- Filter events for orgs using jq
- Load events into events_raw table
- Create orgs_watchlist table
- Refresh materialized view weekly