• Access Database Lab Engine
  • Query testing
    • Generate query plans
    • Making schema changes
    • Reset the clone
  • Migration testing
  • Access the console with psql

Database Lab and Postgres.ai

Internal users at GitLab have access to the Database Lab Engine (DLE) and postgres.ai for testing performance of database queries on replicated production data. Unlike a typical read-only production replica, in the DLE you can also create, update, and delete rows. You can also test the performance of schema changes, like additional indexes or columns, in an isolated copy of production data.

Access Database Lab Engine

Access to the DLE is helpful for:

• Database reviewers and maintainers.
• Engineers who work on merge requests that have large effects on databases.

To access the DLE’s services, you can:

• Perform query testing in the #database_lab Slack channel, or in the Postgres.ai web console. Employees access both services with their GitLab Google account. Query testing provides EXPLAIN (analyze, buffers) plans for queries executed there.
• Migration testing by triggering a job as a part of a merge request.
• Direct psql access to DLE instead of a production replica. Available to authorized users only. To request psql access, file an access request.

For more assistance, use the #database Slack channel.

Query testing

You can access Database Lab’s query analysis features either:

• In the #database_lab Slack channel. Shows everyone’s commands and results, but your own commands are still isolated in their own clone.
• In the Postgres.ai web console. Shows only the commands you run.

Generate query plans

Query plans are an essential part of the database review process. These plans enable us to decide quickly if a given query can be performant on GitLab.com. Running the explain command generates an explain plan and a link to the Postgres.ai console with more query analysis. For example, running EXPLAIN SELECT * FROM application_settings does the following:

1. Runs explain (analyze, buffers) select * from application_settings; against a database clone.
2. Responds with timing and buffer details from the run.
3. Provides a detailed, shareable report on the results.

Making schema changes

Sometimes when testing queries, a contributor may realize that the query needs an index or other schema change to make added queries more performant. To test the query, run the exec command. For example, running this command:

exec CREATE INDEX on application_settings USING btree (instance_administration_project_id)

creates the specified index on the table. You can test queries leveraging the new index. exec does not return any results, only the time required to execute the query.

Reset the clone

After many changes, such as after a destructive query or an ineffective index, you must start over. To reset your designated clone, run reset.

Migration testing

For information on testing migrations, review our database migration testing documentation.

Access the console with psql

Team members with psql access, can gain direct access to a clone via psql. Access to psql enables you to see data, not just metadata.

To connect to a clone using psql:

1. Create a clone from the desired instance.
   1. Provide a Clone ID: Something that uniquely identifies your clone, such as yourname-testing-gitlabissue.
   2. Provide a Database username and Database password: Connects psql to your clone.
   3. Select Enable deletion protection if you want to preserve your clone. Avoid selecting this option. Clones are removed after 12 hours.
2. In the Clone details page of the Postgres.ai web interface, copy and run the command to start SSH port forwarding for the clone.
3. In the Clone details page of the Postgres.ai web interface, copy and run the psql connection string. Use the password provided at setup.

After you connect, use clone like you would any psql console in production, but with the added benefit and safety of an isolated writeable environment.