During an in-depth analysis of the Postgres schema of GitLab, which serves as a viable alternative to Github and offers the flexibility of self-hosting due to its open-source nature, I delved into understanding its intricate structure. The primary objective behind this exploration was to draw comparisons with the schemas I am currently designing and glean some invaluable best practices employed by GitLab.
It is crucial to acknowledge that best practices in schema design are often dependent on the specific context, and it is essential not to blindly implement them without considering the unique requirements of each project.
GitLab’s schema file “structure.sql” spans over 34000 lines of code, reflecting its monolithic Ruby on Rails architecture. While the conventional approach to managing schema migration in Rails applications involves using the “schema.rb” file, GitLab opted for “structure.sql” to allow for the utilization of advanced PostgreSQL features. These features, such as triggers, PostgreSQL partitioning, materialized views, and others, are not supported by the limitations of the Rails DSL-centric “schema.rb.”
To harness the full potential of these advanced PostgreSQL features, it is recommended to transition from using the standard “schema.rb” to a plain SQL schema file (“structure.sql”). This transition entails changing the configuration settings to utilize SQL format and regenerating the schema accordingly, possibly necessitating adjustments to build processes as well.
Through my exploration of GitLab’s Postgres schema, several key learnings emerged, shedding light on crucial aspects of database schema design that are often overlooked.
### Insights from the GitLab Postgres Schema:
1. **Optimizing Primary Key Types for Tables**:
When standardizing on primary key types, such as “bigint” or “uuid,” it is imperative to consider the table’s structure, access patterns, and growth rate. Selecting the appropriate primary key type can significantly impact storage space, write speed, and read speed as the database scales. For instance, opting for Postgres native UUID v4 over bigserial can lead to a 25% increase in table size and a corresponding drop in insert rate. This emphasizes the importance of thoughtful consideration when choosing primary key types for tables.
Persisting in the perpetual pursuit of enhancing database design proficiency, it is invaluable to glean insights from exemplary projects like GitLab’s schema. By adopting a discerning approach to schema design, fueled by a nuanced understanding of the nuances and optimizations, one can elevate the efficiency and effectiveness of database operations.
Should these reflections resonate with you or spark curiosity, I invite you to engage with the detailed analysis of GitLab’s Postgres schema and join the discourse on Twitter.
—
**Tweet:**
“If you’re eager to uncover the intricacies of database schema design, explore the analysis of GitLab’s schema. [Link] @shekhargulati”
—
*Original content source: [Read More](https://shekhargulati.com/2022/07/08/my-notes-on-gitlabs-postgres-schema-design/)*