Database development guidelines

Database Reviews

• During the design phase of the feature you're working on, be mindful if you are adding any database-related changes. If you're adding or modifying a query, start looking at the explain plan early to avoid surprises late in the review phase.

• If, at any time, you need help optimizing a query or understanding an explain plan, ask for assistance in #database.

• If you're creating a database MR for review, check out our Database review guidelines.

  It provides an introduction on database-related changes, migrations, and complex SQL queries.

• If you're a database reviewer or want to become one, check out our introduction to reviewing database changes.

Tooling

• Understanding EXPLAIN plans
• explain.depesz.com or explain.dalibo.com for visualizing the output of EXPLAIN
• pgFormatter a PostgreSQL SQL syntax beautifier
• db:check-migrations job
• Database migration pipeline

Migrations

• Adding required stops
• Avoiding downtime in migrations
• Batched background migrations guidelines
• Create a regular migration, including creating new models
• Deleting migrations
• Different types of migrations
• Migrations for multiple databases
• Migrations style guide for creating safe SQL migrations
• Partitioning tables
• Post-deployment migrations guidelines and how to create one
• Running database migrations
• SQL guidelines for working with SQL queries
• Swapping tables
• Testing Rails migrations guide
• When and how to write Rails migrations tests
• Deduplicate database records

Partitioning tables

• Overview
• Date range
• Hash
• Int range
• List

Debugging

• Accessing the database
• Resetting the database
• Troubleshooting and debugging the database
• Tracing the source of an SQL query:
  • In Rails console using Verbose Query Logs
  • Using query comments with Marginalia

Best practices

• Adding database indexes
• Adding a foreign key constraint to an existing column
• Check for background migrations before upgrading
• Client-side connection-pool
• Constraints naming conventions
• Creating enums
• Data layout and access patterns
• Efficient IN operator queries
• Foreign keys & associations
• Hash indexes
• Insert into tables in batches
• Batching guidelines
• Iterating tables in batches
• Load balancing
• NOT NULL constraints
• Ordering table columns
• Pagination guidelines
  • Pagination performance guidelines
  • Offset pagination optimization
• Polymorphic associations
• Query count limits
• Query performance guidelines
• Serializing data
• Single table inheritance
• Storing SHA1 hashes as binary
• Strings and the Text data type
• Updating multiple values
• Verifying database capabilities

Case studies

• Database case study: Filtering by label
• Database case study: Namespaces storage statistics

PostgreSQL information for GitLab administrators

• Configure GitLab using an external PostgreSQL service
• Configuring PostgreSQL for scaling
• Database Load Balancing
• Moving GitLab databases to a different PostgreSQL instance
• Replication and failover with Omnibus GitLab
• Standalone PostgreSQL using Omnibus GitLab
• Troubleshooting PostgreSQL
• Working with the bundled PgBouncer service

User information for scaling

For GitLab administrators, information about configuring PostgreSQL for scaling is available, including the major methods:

• Standalone PostgreSQL
• External PostgreSQL instances
• Replication and failover

ClickHouse

• Introduction
• ClickHouse within GitLab
• Optimizing query execution
• Rebuild GitLab features using ClickHouse 1: Activity data
• Rebuild GitLab features using ClickHouse 2: Merge Request analytics
• Tiered Storage in ClickHouse

Miscellaneous

• Maintenance operations
• Update multiple database objects
• Batch iteration in a tree hierarchy proof of concept
• Scalability Patterns