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.
Database Lab quick start
- Visit the console.
- Select Sign in with Google. (Not GitLab, as you need Google SSO to connect with our project.)
- After you sign in, select the GitLab organization and then visit “Ask Joe” in the sidebar.
- Select the database you’re testing against:
- Most queries for the GitLab project run against
gitlab-production-main
. - If the query is for a CI table, select
gitlab-production-ci
. - If the query is for the container registry, select
gitlab-production-registry
.
- Most queries for the GitLab project run against
- Type
explain <Query Text>
in the chat box to get a plan.
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 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 requestpsql
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 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:
- Runs
explain (analyze, buffers) select * from application_settings;
against a database clone. - Responds with timing and buffer details from the run.
- 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 (restricted_visibility_levels)
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
.
Checking indexes
Use Database Lab to check the status of an index with the meta-command \d <index_name>
.
Caveats:
- Indexes are created in both the
main
andci
databases, so be sure to use the instance that matches the table’sgitlab_schema
. For example, if the index is added toci_builds
, usegitlab-production-ci
. - Database Lab typically has a small delay of a few hours. If more up-to-date information is required, you can instead request access to a replica via Teleport
For example: \d index_design_management_designs_on_project_id
produces:
Index "public.index_design_management_designs_on_project_id"
Column | Type | Key? | Definition
------------+---------+------+------------
project_id | integer | yes | project_id
btree, for table "public.design_management_designs"
In the case of an invalid index, the output ends with invalid
, like:
Index "public.index_design_management_designs_on_project_id"
Column | Type | Key? | Definition
------------+---------+------+------------
project_id | integer | yes | project_id
btree, for table "public.design_management_designs", invalid
If the index doesn’t exist, JoeBot throws an error like:
ERROR: psql error: psql:/tmp/psql-query-932227396:1: error: Did not find any relation named "no_index".
Migration testing
For information on testing migrations, review our database migration testing documentation.
Access the console with psql
To access the database lab instances, you must:
- File an access request.
- Have a user data bag entry in chef-repo with your SSH key and the
db-lab
role. - Configure
ssh
as follows:
Host lb-bastion.db-lab.gitlab.com
User ${USER}
IdentitiesOnly yes
IdentityFile ~/.ssh/ed25519
Host *.gitlab-db-lab.internal
User ${USER}
PreferredAuthentications publickey
IdentitiesOnly yes
IdentityFile ~/.ssh/ed25519
ProxyCommand ssh lb-bastion.db-lab.gitlab.com -W %h:%p
Manual access through the Postgres.ai instances page
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
:
- Create a clone from the desired instance.
- Provide a Clone ID: Something that uniquely identifies your clone, such as
yourname-testing-gitlabissue
. - Provide a Database username and Database password: Connects
psql
to your clone. - Select Enable deletion protection if you need to preserve your clone. Avoid selecting this option. Clones are removed after 12 hours.
- Provide a Clone ID: Something that uniquely identifies your clone, such as
- In the Clone details page of the Postgres.ai web interface, copy and run the command to start SSH port forwarding for the clone.
- In the Clone details page of the Postgres.ai web interface, copy and run the
psql
connection string. Use the password provided at setup and set thedbname
togitlabhq_dblab
(or check what databases are available by usingpsql -l
with the same query string butdbname=postgres
).
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.
Simplified access through pgai
Ruby gem
pgai
gem has not yet been updated to use the new database lab instances so you will only be able to access the legacy instance using this tool.For instructions on using the pgai
Ruby gem, see: Database Lab access using the pgai Ruby gem.