OSS Insight

GitHub's event data warehouse presents unique challenges for data analysis, particularly as the volume continues to grow exponentially. This article examines OSS Insight's architecture, built on TiDB, which processes 300,000+ new rows per hour from GitHub's GH Archive and event API. We explore how this system handles the increasing dataset, from query execution and optimization to natural language query processing through Chat2Query. The technical overview also examines trends in GitHub Actions adoption, programming language usage, and developer activity patterns across different regions and event types.

System Overview

OSS Insight combines data from GitHub's hourly-updating GH Archive (collecting data since 2011) with their own event API. Together, these sources generate 300,000+ new rows per hour, with the system designed to handle the continuous growth of this dataset.

The backend architecture uses TiDB for both raw storage and real-time analytics, chosen for its support of distributed SQL, secondary indexing capabilities, and online serving features. This choice enables the platform to scan 29,639 rows of 4.6 billion event rows in just 4.9 milliseconds using secondary indexes.

As GitHub's event data volume continues to grow, with predictions reaching 5.36 billion by 2022 and potentially exceeding 10 billion by early 2025, the system's scalability becomes increasingly important. While MySQL has served as GitHub's primary storage solution for non-git warehouse data, recent disruptions highlight the limitations of this approach.

Database and Optimization

The system architecture leverages TiDB's distributed SQL capabilities with specific optimizations for handling complex queries and large datasets. TiDB Cloud serves as the fully-managed backend database, supporting an application that requires both complex and flexible queries while scaling to process billions of rows of data.

When TiDB processes a query, it divides the SQL statement into multiple subtasks, with some operations occurring on the root node and others on TiKV nodes. The query performs index range scans using secondary indexes, such as index_github_events_on_repo_id, which allows for efficient data retrieval. The system demonstrates significant performance improvements through careful indexing strategy, including composite indexes that optimize query execution plans and reduce data scan requirements.

The database handles data storage and indexing using TiKV nodes, where table data and indexes are stored as key-value pairs. The index lookup process involves multiple I/O operations to read data from different physical locations, requiring careful consideration of query optimization techniques to minimize overhead.

The system implements several optimization techniques, including the creation of covering indexes that allow TiDB to perform aggregation calculations directly on TiKV nodes rather than requiring additional network communication. Specific optimization efforts focused on reducing the impact of cache effects during data retrieval operations, particularly for queries involving DISTINCT and aggregate functions.

The tool successfully processes large volumes of GitHub event data through a combination of database technologies. For data-intensive queries, TiDB employs its coprocessor implementation to push down computation to TiKV nodes, optimizing query execution for both performance and scalability. The overall architecture demonstrates effective use of distributed SQL capabilities to handle the complex analytical workloads required for GitHub data processing.

Query Execution and Performance

The SQL execution process in OSS Insight involves several key steps facilitated by TiDB's architecture. When a query is submitted, TiDB divides the SQL statement into multiple subtasks, with some operations processed on the root node and others distributed to TiKV nodes. This distributed approach enables efficient data retrieval through secondary indexes, specifically the index_github_events_on_repo_id.

The system demonstrates remarkable performance in handling complex queries, as evidenced by its ability to scan 29,639 out of 4.6 billion rows in just 4.9 milliseconds using secondary indexes. This capability is crucial for processing the dataset's continuous growth, with predictions suggesting 5.36 billion rows by 2022 and potentially exceeding 10 billion rows by early 2025.

Query performance optimization has been a significant focus for the team, particularly in addressing the limitations of TiDB's single-threaded execution model for aggregate functions with DISTINCT. One effective approach has been to create composite indexes that improve query coverage while minimizing cache effects. For example, the creation of an index with the column order repo_id, type, action, number significantly reduced the query execution time for counting distinct issue numbers from 123.6 milliseconds to 417.7 milliseconds.

The system's architecture effectively leverages TiKV nodes for storing both table data and indexes as key-value pairs. In the case of an index lookup, TiKV reads data blocks that may be scattered across different physical locations, requiring multiple I/O operations to retrieve information from various storage blocks. However, the database has implemented strategies to mitigate performance bottlenecks, such as optimizing query execution plans to reduce cache effects and using coprocessor implementation to push down aggregation calculations to TiKV nodes when possible.

GitHub Data Exploration

OSS Insight enables users to explore GitHub data through natural language queries without requiring direct SQL knowledge. The tool leverages Chat2Query, a GPT-powered SQL generator integrated with TiDB Cloud, to translate user questions into executable queries. This AI-driven approach allows users to discover insights across 5+ billion rows of GitHub data by simply asking questions in natural language.

The platform's query generation process begins with users posing questions in plain language. These queries are then processed through Chat2Query, which generates the corresponding SQL statements. The system retrieves data from the combined GitHub dataset, including the hourly-updating GH Archive (since 2011) and the GitHub event API. Results are visualized using Apache Echarts, providing users with clear, graphical representations of the underlying data.

To optimize this process, the tool provides several key features and best practices for users. Each query request is limited to 15 per hour to maintain service stability. Users are encouraged to rephrase their questions using clear, specific GitHub-related terminology, such as full repository names instead of nicknames. The database itself introduces some limitations, particularly for complex queries that push the boundaries of AI understanding of specific database structures.

Performance considerations are also important for users. While the system has proven effective in many scenarios, particularly for aggregate functions and distinct value counts, some complex queries can still present challenges. The database architecture, combining TiKV for row storage and TiFlash for columnar storage, requires users to be mindful of query optimization techniques to achieve the best performance.

Technical Trends and Insights

Technical Areas: GitHub Actions and Databases

GitHub Actions adoption continues to grow, with 63.85% active repositories in this technical category for 2022, though growth has slowed from previous years. The database ecosystem shows stable growth, with projects maintaining consistent annual increases. The Low Code and Web3 categories exhibit significant activity, with 76.3% and 63.85% active repositories respectively, though Web3's growth rate has decelerated since 2021.

Programming Language Trends

Python maintained its top three position, while TypeScript reached sixth place and SCSS advanced to nineteenth. Ruby and R languages experienced declining rankings over the years. For back-end development, Python and Java retained their positions as the most used languages, with Go overtaking Ruby for third place in 2021. The Rust language showed steady growth, ranking ninth in 2022.

Geographic Developer Behavior

The United States exhibited the highest activity in Pull Request Review Events (6.15%), while Korean developers demonstrated the greatest engagement in Push Events. Japanese developers led in Pull Request Events with 10% market share, followed closely by German developers active in Issue Events (4.18%) and Comment Events (12.66%). Chinese developers contributed significantly to Watch Events (17.23%) and Fork Events (2.7%), demonstrating substantial regional participation in repository activities.

Developer Activity Patterns

Developer engagement peaks during weekday hours, with 77.73% of events occurring between Monday and Friday. The analytics reveal specific behavior patterns: Pull Request Events, Pull Request Review Events, and Issues Events show the highest activity on Tuesdays, while the lowest event volumes occur on weekends. Push Events display consistent activity levels across weekdays, as do Watch and Fork Events. These trends highlight the evolving work habits of GitHub users and the importance of understanding temporal behavior in technical community analysis.