GitLab Database Team, 17 Feb 2023

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Postgres.ai Quick Start

Description

A quick guide on how to use the database lab joebot web interface to test the performance of queries against a production clone.

A

A

Alex from the database team here just uh giving you a quick overview of how to use postgres AI jobot uh for testing your query. So uh when you get to the postgres AI console you're going to see this sign in page, you want to select sign in with Google, don't click assignment with gitlab. You need to sign in with Google, because we use that to sync with our organization.

A

So if you sign in with Google, you should be signed in automatically now once you're in, you should see organizations in this case, you'll get Killam now on the side when you first join, I have a bunch of other stuff on here, but you should have under SQL optimization ask Joe.

A

When you go in there, you'll see three different projects, so the main project you'll be using, probably is gitlab production tunnel pg-12, that's our main gitlab database. That said, if you have CI queries that run against the CI database, you'll need to select gitlab production CI for those queries. Instead and if you're using the container registry project, then we have yet another project for you, so I'm going to go into the main project. Here you can see it spit out a list of helpful things that we can use to make plans uh explain plans.

A

Typically, your best bet is to use explain. So not only does that analyze the query, it also executes it with analyze. Buffers so say you wanted to test a query that you were running in in a merge request. You could say, explain, select.

A

um Sorry uh star from users name.

A

So uh what it's going to do is it's going to sit here and think um actually what it's doing right now is it's making a new session, so that means it's uh generating a new clone for you on our database lab server that takes about 20 seconds. So maybe I should pause the recording all right, so that obviously failed because I forgot to put quotes, but if I put quotes around this, it's still mad because I used double quotes instead of single quotes. There we go so that takes 12.5 milliseconds, not bad.

A

So anyway, that's how you do it. uh You can also do things like exec, so.

A

uh You know there's.

A

So I can write an exec and it'll go through and do that so now by.

A

Try to run this command again, you'll see the actual rows that returned was Zero, whereas this time, the actual the first time, the actual rest was one. That's because it updated my name. So now, if I go back and say: oh that's one. Instead uh oops.

A

That was not what I meant to do there. We go so now. If I rerun that select with one you'll see it's got. One uh now say you wanted to reset. You could do that and what this will do is it'll reset the data back to its state. This also takes about 30 seconds, so I'm gonna pause again.

A

The reset is done and I already went ahead and re-ran. This query and you'll see I rerun it with my original name and.

A

It uh it's reset, and so you can see I've got the rows one again. So anyway, reset is pretty powerful. You can do a bunch of stuff reset it and start over, but exec is also really powerful and more than just altering data, you can add new rows. You can add indexes, you can modify indexes, you can try validating foreign Keys, anything anything you could do in your database. You can exec and then you can run queries afterwards to analyze those results.

A

So anyway, uh thank you for listening through my Rambles and uh thank you for doing your part to keep git Labs queries good and healthy and happy, and if you have any questions, please reach out to anyone on our team in the GE database. Slack Channel thanks.