GitLab Database Office Hours, 7 Mar 2019

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Database Office Hours - 2019-03-07

Description

Mostly around database reviews and process

First part revolves around database review process and documentation, particularly these issues:
https://gitlab.com/gitlab-org/gitlab-ce/issues/52210
https://gitlab.com/gitlab-com/www-gitlab-com/merge_requests/19980
https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/25867

Performing 3 MR reviews on the call:
* Two MRs with regular migrations from 29:10
* Query optimization with partial index from 38:57

A

Cool, so I I realized that this time is quite only for at least for Casey I. Don't know you it's also quite quite over for you in the morning. Isn't it.

B

A

Thirty right so I thought about moving in two to four pm and unless I mean, if that accommodates, better and I think front end Tony you're in European times known as walls who fits for you as well. Okay,.

C

Make a bit later perfect.

A

I'll move that later did you want to start discussing with the delivers expert becoming a database expert tomb.

C

Well, it's probably related to the next item, but I think well. If I will tell from my own experience or what my goal is so for the Geo team, we want to have like one or maybe two people in T to have more database expertise. You will have like two, the initial reviews of database changes and yeah like filter out the biggest issues before they head on to the real database. Maintainer we've used that would make our process faster and also offload.

C

The database maintained errs to do more thorough reviews.

D

C

Sure what friend take is on this stuff, no.

E

I am sorry I, I, totally agree, I mean I, I I think we should first fill the gaps before that caddies this reviewing process so.

A

Yeah I agree to I, mean and I realize. We've been talking about it for quite a while. A knife I think it would be good if you figure out how to how to start or how to head in the right direction and.

C

Also, it's not my goal to do like we've used for the whole company. I want to focus on new stuff, because I don't see the bandwidth for myself to do, reviews all over the project for and.

A

C

For knowledge for what's happening, where are the queries happening? Why are they doing this? It's hard to know all all the features of gate lab, so I want to focus on our team. Only I know that might sound selfish, but it would be efficient if I would do it like that. You.

A

Can also look at it from the other's perspective that the reviewing other stuff also is a great opportunity to learn more about how we use the database, but I understand. There is a lot of things to kara baby.

C

We can do it more or voluntary schedule. One because I saw your merge request about the reviews, and you have liked the database issue for writing things down. That need to be reviewed, and then people pick issues from that list whenever they want to, so that that might be something that that would make it easy to even the load between several reviewers.

A

And I mean it's totally voluntary. To do that, I mean nobody is going to force you to do reviews, but it's it's helping a lot like you said it's distributing the workload better and it's also a great opportunity to dig into stuff that you may not be comfortable comfortable with like a different program, but it's absolutely what I'm Tara to do that. Did you find the language to suggest that it's not voluntary or no.

C

No I think I think it was pretty good. I was about to submit a review on the request, but I haven't done yet just a few few comments. Okay I would like but I like most part of it actually cool.

A

I was I was just starting to write that down, because I wanted to do the for a long time, because it's somehow not clear or not was not a man, but what is expected from a database review how the process works, how to become or a reviewer or also maybe what resources we have available to learn more about that stuff. So I'm writing the Dom, but it's only my first take so I'm I'm happy to receive comments and improve on the on the document. Yeah.

C

I wasn't sure what what the write like title for it would be. Reviewer might be a great title because I've been looking in the handbook. What's an expert or specialist I think your database, specialist and people can be an expert when you have more than average knowledge about something. So maybe something in between like expert or review might be might be. Okay, mm-hmm.

A

I mean reviewer stems from the the roles we have for for the project like a reviewer and then a maintainer and I. Don't know it to be called still call it train tenor, the training, maintainer and then the other other thing is see. What do you have on your like job title voice? Oh well, in this case and I just picked reviewer, because it's the common common term for it.

A

And so I'm, starting to think that, like I mean we've, I know we've touched on the on the database reviews for quite a long time, and I would like to get going and document better and also outline like a little bit of a process how to become a database reviewer, and one part of that is. I remember that we also use onboarding issues for other roles or when you join a company or whatever, and I'm starting to create or I've actually put up in a more it's, hopefully linked somewhere.

A

Let me find out.

A

A

So it just contains a checklist of suggestive things that might be useful to do when, when you consider the review role like getting familiar with I mean just code reviews in general and then the database specific sites I linked all the database guidelines, the best practices and actually, when I, when I, do a database review.

A

It's mostly about checking those those things off like making sure that we follow the best practices for migrations, and it's a lot of that stuff is covered in documentation already so I thought I linked it Andy in that issue, template and the year the rest of it is basically how to to get involved in the process. We have the it's a bit of a mix.

A

At the moment we have the shield database group where people are being pinged for reviews or for for advice, or things like that, and those are a bit hard to manage, because what I do is I see those pings and I put them on an issue in the infrastructure tracker to keep a list of pending things so to say, and and then we distribute the load from there. But it's sort of a manual thing and it doesn't scale so well, I think the alternative to that is using the review. Roulette.

A

I, don't know if you've seen that already there's it already works from the from the team yamo data file, where you indicate that you, your reviewer or maintainer role and I, think it may not even may not catch all of the database reviews or database things, but it may it probably catches a lot. So I thought we start using that.

A

So when you, when you want to become a reviewer, you basically indicate it and then the team llamó, and what happens is that there is when there is a new, mr, that is supposed to be reviewed by a database. There is a danger comment on the mr and you're going to be mentioned as a reviewer, and then there is going to be mentioned. The maintainer as well and people would still individually reach out to you to say: oh, can you reboot you review that and then pass it to a maintainer I.

A

Think there's there's still improvements. We can make to to catching more more changes automatically to detect that there is a database change in the EMR.

C

Yeah I think we also can do like abort generate the selection of all merge requests which have a database label instead of the the work you're doing now by hand like copying the issue from when your paint to another issue in it in an infrastructure. Tracker that should be should be possible to automate that yeah.

A

I was I, was actually working on, I, have a script handy that responds to those pings and creates an issue for it and and assigned someone somewhere randomly. But it's I realize it's quite similar to the original, --it and I think it would prefer that to work work for database as well.

C

We have kid lab triage already that that does most of that work already because for I know for our team. It also generates like a weekly issue. Whenever there are issues which don't have, what is it again? Priority is a site, that's one, that's one thing that happens like every week and it should be very similar to what what the database label issue collector. Something can do.

A

That's good to know if.

C

You, like I'll, look into that yeah.

A

That would be great I'm a bit like lost because there's quite a mix at the moment. We also don't have the code approvers feature at the moment, unable to think we're still figuring out what to do.

A

Alright, so I plan to expand a bit on the on the documentation for the reviews. If you want to take a look and put comments in there, I would appreciate, but it's the mango is basically to explain: what's what to do, how to do it and what to look for, and do you think, it's useful to put more and more details into those individual points.

C

You mean on the merge on the initial merge request about the again at support. No, oh.

A

Yeah, let me share my screen. The.

C

Infrastructure database reviews that's.

C

It's in 1, 9, 9, 8, oh I, guess.

A

Yeah I'm sharing my screen for the full page. Maybe we can walk through it quickly.

A

So I was I was trying to you know, explain a bit how what to generally expect, for example, I regularly ask for the MRO office to translate to sequel instead of active, active record, because it makes my life much easier, and obviously you can do that on your own, but it takes also takes time to get set up and it really helps to speed things up so bit of the general process and then what is actually expected from a reviewer, and this is only my first pass on it.

A

So it's not very language is not very straight, but I would generally say that it is only expected that you perform a first positive review on the EMR and then work with the author to suggest improvements or any changes you can see and then once you're satisfied, you pass this on to a database maintainer and the database.

A

Maintainer is also going to perform a second pass review and if there are any further improvements, go get back to the to the reviewer and the MRO theory and and explain stuff in the group and I think this is actually where, where there is the most potential of learning things when you, when you review something and you and there's still something you overlooked and then you get together and then explain what what is so missing.

A

I was for me was the best best way to learn, run that stuff and yeah the the maintainer is going to approve for merge and in the end, I.

E

Think there was a point in this in this metric was when it said that we should only see whatever is inside the DVD directory or if the developer or something else, but sometimes.

E

The database perspective can catch things even in the code through a code.

E

I don't know if it is worth it to just review them as requests and maybe focus more on what is inside the DV directory, but I think we should at least review the whole poem, which means that you have to separate very well your role from the database reviewer and the regular reviewer, because it's really easy to make both roles here. I.

A

Agree with that, I was what I wanted to point out. Is that sometimes it's hard for you to understand if there was any change to her like a complex query that that it's needs review and sometimes I I, actually reach out to the author and say like hey? Is there anything that you want me to specifically look at because the author usually knows the best?

A

What is really complex areas are or if there are any any queries that might might warrant a review or more more care, but personally, I generally also I tend to look at the at the whole code base because or the whole change because gives you the best impression of what was going on. So you.

C

Also catch like n +1 carry queries. That's also something I would do.

A

Sure I mean yeah, that's a good point. I have not mentioned I'm on the document, yeah sure, but also I mean I. Think like you can you can be. You can be in those two roles, a back-end reviewer and our database review, but nobody's going to stop you from from making comments about all the other parts like I.

A

Think every comment is useful and you can learn something and you also can learn something so I'm, not I'm, not afraid of making comments about the back-end challenge where I think that oh this is not the style, we usually do it or whatever, but it's not generally expected from a database review. I would say: yeah.

C

No but I think what Fran was trying to point out that sometimes you want to like cut scope at some point, so you don't be actually reviewing all back-end code for all the the merge request. You're just doing the database review so that to make your talk not too big and to make it the word maintainable.

A

Exactly I would say for a database reviewer, it's expected to look at the changes in the DB directory. That is usually migrations.

A

Then there is a part where we touch all the tooling for the migrations, like a migration, hopeless or load balancing anything that touches the database tool tooling, and then the tricky question is how to detect like where we introduce change to complex queries, and this is a bit tricky because it's not obvious, like you change something in the finder and it's it's a rather obvious, but then you you can change it in other parts and it's it's not obvious from just looking at EMR.

A

So in this case I would I would want the author to either specifically point out something like oh yeah. This is a career that I want you to look at for further review or I'm looking at stuff that I find obviously complex like if, but you sometimes it's not. You don't have to well I guess what I'm trying to get at is you don't have to go through all the database, interaction that is going to mate from the EMR change, because that's not feasible!

A

You want to look out for the more complex stuff and usually I I, build on the help from the from the author to know to understand what the more complex stuff is or it's it's obvious from the change. Does that make sense.

E

Yes and no okay, it should, as usually the developer, knows best, which parts may be more complex, but it's true that sometimes, for example, for new developers to test things. For example, you know approvers things in the finders member, see it or things like that performs a lot of where, if you need other ways should you don't really know what it's going on underneath that so there are several Cyril operation, several models that are quite dangerous to touch or update, because they can harm the database performance in this case.

E

So that's why I think we should at least take a look at the whole changes and see anything triggers yeah.

A

That's a good point um this. If you, for example, if you touch the permissions, queries they're all over the place, so this is rather a dangerous spot to to be, and when you know that- and you see the EMR and you scan the whole. Mr then this could your chance to to catch those.

C

Just wondering if we could like make this feasible, if like because, like the performance bar prints out all the queries that happen it would it be possible to like have a diff for the cravings before the change anyway, we answer the change and have a comparison like this is what's what database actually I was.

E

Thinking something very similar using the query record that we have, we already have the code so I know exactly. Do you have seen a world record? Well, you can record all day all day all the queries that having from the database and after after a change or in these aspects, you can confirm if they actual query is performing according would you have probably say or what you already expect.

D

E

I think good at in this case, I think we can figure out something to to detect problems. Yeah.

A

Adam of the more we can automate out the better, what I think we will or what we're what we usually have as a problem is that the test database you have in the bolts is rather small, like it sits small compared to to the production database, but I know we're also working on providing a better test environment where you can actually run your change run the EMR in the context of a production, size database, or at least similar scale, database and yeah.

A

If we can, if you can automate testing that and and comparing queries, detecting changes in queries and then also detect changes in run time, then that that would be really helpful.

A

So there's lots of stuff to improve and appreciate all the all the work we can put in there, because it's it's really helping with the process.

A

Because so far, it's a rather manual thing to do like if I look at what I, at least for reviewing what I generally do is I check the migrations. So this is really for the schema migrations, but also the data migrations, and you do I just ran run through a list of check boxes like you want to take a look of the design choice. If there is like a new table or many tables introduced, it's fine like the the relational modeling is fine.

A

You want to take a look at the usage of the migration helpers that we have and then there is I mean all the best practices we have. We want to make sure the column ordering is fine. We have indexes for foreign keys and quite a bunch of stuff for parts of that we already have robocop helpers, so they should be detected. For example, if there is a missing index for the foreign key, the boat should fail, but it's not always the case.

A

There is a usual problem with the disabling transactions in migration, so you can disable. So by default a migration runs on a transaction. You can disable that we sometimes mix up normal GDL statements with the concurrent helpers or we need to separate them, so something to look out for them.

A

Checking consistency of schema RBE is something I look first order. The change to the migration entry is also reflected one-to-one and the in the schema file, which is not always the case, and then migrations need to be consistent. There should be reversible and in the end, we also want to maybe have a feeling for how long a migration takes, especially for the data migrations. This is a bit tricky sometimes to to to understand, though, and similarly.

E

For that case, it really hard to estimate that time. You are not running.

A

This is exactly another case for for a better testing environment. Where you, you automatically get the data base, that is of considerable scale, and then you deploy the change. That also includes running the migrations, and then you tear down the the environment and when we're there, this is really going to help, because today we're, if you think about like complex background migrations, we're mostly flying blind, there is stuff you can do.

A

You can look at the queries, statically and and and all that Nick has also been trying to execute macro migrations on one-off instance of the day of the production database. But it is rather manual work and wants me automated that we can be more comfortable of executing those migrations.

A

Alright and when it comes to query performance like this is again for the more complex queries, I generally ask for sequel queries and if possible, I would also want to the author to at least use chat, ops to to execute the query once in production. It.

D

A

Not always be possible because it might rely on rely on the on a migration that is not yet present in production, but sometimes it is, and it's I think it's birth to point out that we can actually use all use chat ups and to do that sometimes.

A

It might even be necessary to look at the data distribution for the parameter. Isn't a query like if you you want to make sure that you have the like the standard, standard, parameter, values or something that is like normal usage and not some specific case, because sometimes it may matter for the performance actually. So this means that you want.

A

You may need to log into the production database figure out what the data distribution is and make sure that the example that you use to evaluate the performance is actually sensible in that distribution and then I, look at the query plans and sometimes I can make suggestions to to the query itself or to the schema or adding indexes, particularly for that query or similar and generally, if it agrees like below 100 milliseconds. That's that's quite okay.

A

So this is something something to aim for and like I said earlier, the the problematic situation is when there is a query that relies on on an index or a column or a table or any migration that hasn't been introduced yet to production, and in this case we have a restore pipeline called restore because it's regularly testing our backups. So it takes a food production backup and it restores into a one-off database instance and the good thing about that is at the end of the process.

A

You basically have a full-size database that is not attached to production anymore, that you can mess with you. Can you can put in read, write mode and I use that to to test queries that rely on on a on a schema change, so you can jump in there. You have the one on one copy of the database. You can add a column, you can add an index just like the migration woods and then you can. You can actually see how the how the query performs you can actually decree. This is really helpful.

C

D

I guess it's not accessible.

C

To anyone, because it can contain information.

A

Right, it's! The access is rather limited at the moment, although I would say at the moment, it's also possible for any engineer to get production access, direct access, so I, don't I, don't think there is something that keeps us from from giving access to those restore boxes in particular for for database reviewers is sometimes really required. There is in a way that you can understand. I mean we're not going to, like just add a column and production, to understand how how the query is performing so using the one of instances.

A

A good idea- I actually put it in in the check list for the.

A

For the issue template.

A

So what I think is really helping when you want to redo stuff, is making sure that you have access to at least a read-only replica in staging and production I.

A

Don't think right! Accessors is required really because, as I said, we're not like messing with production manually anymore and then I put yeah. The other thing is to make sure that you have access to the to the restored project. It's on the ops instance. So this is something you need to access to.

A

This is where we kick off a pipeline that creates this instance after a few hours, and then you want to make sure that you also have access to TCP so that you can actually SSH into the instance.

A

But I don't see why we wouldn't be able to to give you access to to that really at the moment.

A

Cool unless there's other questions about the reviews, I'm going to add the n plus 1 queries to the list and how to detect that initial. For the comments, I appreciate them as well and I thought we can maybe look at three reviews: I I needed to do anyway. So if you guys are interested to look at that, we can do that here.

A

So I picked I picked three randomly from my to-do list and I had a quick look, but I have not prepared more more than that, but the first ones I, rather rather simple migrations.

A

So in this case we add support for ingress host names and what I generally do is when I go to changes.

A

The first thing, I do is look at the changes in the DB directory, because that it's, that is something you have to do to do anyways so in this case, was already pointed out that this is simply about adding two fields into into a table. Two columns, so in this case, I just make sure or I, would make sure that we use properly use any any migration help us in this case we don't use any it's just a standard, add column.

A

So there is not much to look at here. Sometimes if you, for example, if there was a limit specified on the on the string column, this would be something I would point out that we generally don't enforce, or it might not be the best idea to force limits on string fields, because there is no benefit from it. When you want to change that, when you're going to increase the limit, then it's going to be a problem, but other than that there is not much to look for for the migration.

B

Though, sometimes we want to sound some migration I see they add cognate with default value, some dozens. So how do we choose whether we want to add a column with default value or not?.

A

Think this depends a bit on on what the application expects and adding a column without the default is generally a quick operation even on the on the large table. So if we add a default column, this is going to be more expensive, but it's generally also possible. So I think it's it's more up to what the application expects if it should get a default value.

A

The the other drawback from adding your default value is that you possibly rewrite all the table or all the all the index references so because it has to go to through the table once and to add the default column. So this is going to introduce a lot of bloat, so it might, it is really unexpensive operation.

A

So what you can do, when you see at column with the default here, is to reach out and ask if the default is actually necessary or people are aware of the fact that this is expensive, and sometimes it turns out that oh we can, we can actually live with a little now, while you just fine- um and that makes that that is an actual improvement to the migration. You can make.

A

And with the major Postgres upgrade I forget, which version this was, but we can actually supposed to suppress supports, adding columns with the default without being an expensive operation. That is what we are actually quite useful to have.

A

Right the other thing I look at is this adds two columns and I want to see that reflected in the schema file as well. So there should be a bump in the version, usually unless, unless the version is already higher and then I want to see those two columns be added I think there is a test for that as well, so the the the problem would fail. If this is inconsistent.

A

But sometimes we managed to introduce leftovers that this is not reflected in the in the migration and then they stick with the the schema. So it's good to have a look at the at a schema file as well.

A

Other than that I would probably look through the like. We said earlier, like look what is happening on the other and on the backend side and the models, if I can spot anything that I would say. Oh this is a database query and it looks rather expensive. I would retail them ask about it in this case, I haven't found any so.

A

And the rest is like documentation specs. So unless you have any further comments about that, I would say we're done with it. So.

B

For this kind of simple schema change change. Yes, is it possible to just keep the database review process if the author is confident that changes simple enough and no one caused any performance issues are emitting.

A

It's a good question.

D

A

I feel that when the author is comfortable and knows enough about the database, then that's totally fine.

A

What I my experience has been so far is that we still make a lot of, or we still have quite a few oversights in those simple migrations as well. So it's a bit hard to to put a general rule of thumb when you, when you say oh, when you're comfortable, you can just you can just put it. You can just skip the review.

A

So I tend to think that we still would want want. The database review in this case.

A

Cool I'll pass it on later. We can liquid another one and the last one is more interesting. One I think.

A

So same thing here, I'll look at the migration first.

A

It may be worth pointing out that this is an enterprise edition change and we also have database migrations in the EE directory. So something to look for.

A

And at this case, we're adding a table with first thing: I do is I, remember that we have a guide for the column ordering, which is important. You want to put the bigger columns, first so say the and the fixed fixed length columns first.

A

So in this case, if we would, for example, start with a string which is a variable length and then only then put the time stems, this would be something to point out that we want to and just point to the best practice. We have a document for that. There's an outline what how to how to order those columns.

A

But in this case we have two x terms: 8 bytes each and then we have a reference which is an integer, and then we have the variable length text field. So ordering is fine.

A

We also want what we actually enforce a foreign key to have an index, and this is a bit tricky in this case because it says references group, so this is going to have going to add a group ID a column without an index. So this is something that catches my eye and then I. Look for all the we add any other indexes, and it actually turns out.

A

We had a combined index for group ID and token, and that's fine as well, because you can actually use the prefix of when the next the same as you would if you had an index, particularly for that column. So in this case, it's really good that we disable index and only create the combined index, because otherwise, if we created two indexes, they would be redundant.

A

Right and then there is not much to look for other than okay. This is also reflected in the schema file.

A

Can take a quick look at the group, so it also introduces the relationship and obviously a new model for the table and other than that. It I believe it doesn't do much like it exposes the resource on the API, if I'm not mistaken or it's just a road just wrote so yeah really again is straightforward for with schema migration, which doesn't need a lot of review or comments. In this case.

A

And then the third one I have on the list is more interesting, starting in the same way.

A

Or maybe, actually process-wise, so at some point, Bob thanked me directly. It's also something that happens, preferably in the future. I think we would I would want to have the review roulette in that case, so people don't have to worry about who they ping and then it's pretty clear who would be? Who would be doing the review or who would be pinging in the first place in this case, Bob already pointed out that there is a query and apparently we're adding an index to speed up that Cree.

A

So this is something we can check for and if you look at the the whole change, there's in fact the a migration for EE only and it's adding a index over here for this Cree. So it's very good that Bob already pointed out what the query is that this is supposed to improve other than that there is right. There is a bit of addition to the scope of the project model where we actually use this I guess. So this is the yeah. This is actually used in the Korean and.

A

Here is a full query where we actually count projects given some filter criteria, so this is this relates directly to to that query.

A

So what would you do next, with that cream.

E

Oh, maybe maybe try to run it in introduction and see they execute on time. Oh I'm see the benefit.

A

Yeah, that's a good idea.

A

I'll do that in in the database channel on slack so I'm just pasting the Korean there.

A

But I have to admit that I generally default to just hopping on a replica and trying the the Korean myself but chat ups. This is quite helpful if you don't want to do that and we have a result on the database channel um I'll open the plan and the I'm still sharing my screen arc.

A

So this is the Kree plan and it actually performs a sequel sequential scan on the project. So there is no index that was being used and it takes quite a long time like nearly four seconds, which is too long, and we can also see that this is the filter it is being applied to directly translates to the rare condition to those fields and while the sequential scan scans all the records on the table, it removes some 8.1 million and only returns three rows.

A

Right so new, it returns quite a lot of like it removes most of the table from the sequential scan and removes only three rows, and this is like a prime candidate for an index because in the next wants to be quite specific or in the next lookup. This needs to be quite specific.

A

So this is actually a good idea to to add for the to add an index for that I'm a bit lost. Now, where is it there we go.

A

So what can we do to test the index? Because we don't have that in production.

E

D

A

Business, the only choice you have because creating an index needs a needs, a write, writable database production is not a choice, so we have to do that. I, don't know. Have you all seen how that works? I.

E

A

So we have on the ops instance, we have a project.

C

Also I saw you do something else was doing adding an index and intrinsic transaction. That's not an option here or how does that work? I.

A

Would generally not advised to do that because.

C

A

Can you sure, technically, you can go to the primary and create a transaction, create the index and see how that makes a difference, but there's nothing that we want to continue doing on production. Let me put it that way: okay,.

C

A

All right, so we have this project on the UPS instance. The main purpose is to daily on a daily basis, to test our backup, so it grabs the full backup from the archive. It creates a database instance from it. Does some verification notifies all our monitoring and all that that's the main purpose. The other one is to actually keep that instance, because it's a one-to-one copy of the production that it is, and you can use that for to test those kind of things.

A

So what we can do is we could kick off if we don't have an instance. Yet I already do so. We don't have to wait too long, but if you don't- and you would run a pipeline, it would currently Adam I, don't know how long it takes, but it is going to take a long time like a few hours, something to expect because grabs the full backup restores it verifies in and all that. But in the end, what you get is a GCE instance and.

A

In this case it was for staging and you you see: okay, there's an instance. There is the instance name it hasn't. It still has an external IP we're going to change that at some point, but I can use that information go to the GCP console and I. This is actually the instances that were created from the pipeline, so we have currently for running one for staging one for production and then Nick and I.

A

We have sort of reserved our instances and we keep them running because we regularly use them for for testing and this because I'm just going to use that one and let me reshare my screen for the terminal.

A

If I manage that.

A

There we go I hope you can, you can read the terminal. I can increase the font size a bit.

A

Alright, so I'm basically SSH into the restore instance. This is a standard lab environment, although the diversion is a bit older, I think so, but we install the Enterprise Edition package, it's 11 5, and it only gives you the whole environment, but the only the database is running. So what we can do is just jump in and we get lab. Pc cool gives us the database, and this is really we can check. Database Isis is really the full the full size database.

A

So looking here for a good lab, HQ production gives us two per night terabytes there is this full full-size database now what I would do is I need to manage my my taps better.

A

Alright, so in this case, I would I would grab the the query again and then just as a reference to to run that once.

A

Aha and in this case we're actually in a situation where the the column doesn't exist yet so we we really can't execute that query and.

A

I didn't know because it was not included in this, mr, but there's probably a previous Ammar, that that introduced a column. So in this case I'm just going to add it manually.

B

So why you running the introduction of to chat ops, the column already exists, but the backup that we restored from production doesn't have the column. That's.

A

A good point: oh yeah, it's because I so I I didn't use a so that the instance I'm using is quite old. Maybe I created it last week, I think so. There might have been a deployment in between.

B

A

It's a good point: John.

A

Alright and then it's worse to point out that the restore instance, it comes with the bit of different hardware, so I think the memories a bit smaller CPU is a bit worse and on production. So it's worth to execute that once again to provide a reference. So now the query takes almost 10 seconds and you can do that once once again, because the first time you do it, the caches are still cold and then the other runs are should be quite constant at 3.7 seconds or 3. Ok, but is still the same.

A

Now we can take the suggested index.

A

And that is basically creating an index on projects for the archives pending delete and the code owner approval column, so I would go ahead and create the index here.

A

And then archive spending leads.

A

Right, okay, so this took a while and we can check. Maybe this is a it's a full-sized index on the whole table so compared to the table size, it's not really small, like it's ten, nearly ten percent of the table, and when we execute the query again, the that they explained analyzed, it is going to be much faster, so they're, even leveraging an index only scan on this case, because we don't have to really go to the table. We can just use the index for they go for the counting and then the index condition matches.

A

Completely matches the very close I think yeah, and this is really fast. There is a good way of speeding up the career. Can you think of another way to do that? That may be even better I.

E

A

What do you mean by combined.

E

Using using individual index using one only one in between the three columns, okay.

A

In this case, we already have that. So if we look at the index definition we created on projects, this is the table and then we create the index on the combined columns at this gives you one index with three columns in there. So.

C

This index would also speed up the queries when all when one of the flags would be true or false, we would be flipped anyway. Yes,.

A

Exactly so, when you it's not only specific to that query when you have pending, delete false or archived false. Well, it also works for archive true and all the other cases as well.

B

Maybe the partial index, only the common value is not now.

A

Which, which column will you I.

B

Suspect, because, in the column you you just created, it must have something you must have a lot of nerve alleys, so suppose the same situation happen in production when we create a partial index only for values that are not now, the index will be much smaller.

D

A

I remember discussions around that. If I recall that correctly, then the index would be smaller, but it's not too much of an impact, because I think it's only a bitmap that that is kept for for in all values, but I'm, not I'm, not entirely sure.

A

But a partial index is a is a good idea.

A

So I was I was reaching out to Bob in the in the mr and asking if, if we actually specifically want to optimize for this, these parameter values or if we also want to optimize for maybe other combinations of these, and he said like no, this is actually the we only are going to use these exact parameters. I.

E

Think he want to optimize that, because he, you seen a scope scope with those with those with that query. With that complete query in the model exactly.

A

A

So what we could do when we you really want so in this case the index helps right, it's really much faster. There is no point to make about the performance, but when you think about the index itself, it's like I said like nearly 10 10% on size of the original table, and it also needs to be maintained. So when you perform changes to the table updates or inserts or deletes the the index is going to need to be neat to its I'm. Restarting that phrase.

A

So it's we need to maintain the index, so any operation is going to put it's going to be more expense. If we also need to touch the index ends, if we only want to optimize for that specific way, we can actually think about making the index much more specific to that query and in comparison to the existing one, also much smaller.

A

So what we could try is making a partial index and just just put an ID in there, but we make the the actual condition for the partial index match the the decree like we have pending the lead falls archive falls.

A

This is too long to type too.

A

And, of course, we have to give it another name for testing.

C

If you want to drop the old index, then we.

A

Can drop it as well, but let's first see if proscribes is smart enough to figure that out, and it actually is right.

A

So it uses the the other index to the partial index that we just created instead of the other one and I mean you can say it's a bit faster, but it's also like in the milliseconds range. So there is not much point to make there.

A

The main difference is really that if we look at the size of those indexes, the other one is 8 kilobytes right I, in this case it's a bit superficial because it's quite empty the index, because we only created four records that have the owner approval flag set to true, and we just added that column. So there is no record that ever matches this, so take it with a grain of salt, but the index is still much smaller and and since we don't want to optimize for other cases, we have a very specific index for that.

A

Cream is only going to be touched when we change or add or delete an existing record that matches those conditions so for any other updates any other inserts. That don't match the condition. We don't have to touch the index it's much smaller.

A

It would probably also fit better into memory if you can make that point, but those are both quite small but the smaller index, the better. It fits into memory.

C

Now it also only would index the IDS of the projects. If you want auto records of those projects, then the index would not leave it. Yeah.

A

Good point we can see in the plan them. This is an index only scan and we would like you said we would not be able to leverage the index only feature if we were. What can I select from projects? Probably name maybe take a different column.

A

So in this case we select name, and we also have the other partial conditions in there. What we, what then happens is you. You can still use the index because it is going to point you. Given your filter criteria. It is going to point you to the right records on the table.

A

However, we also have to reach out to the actual table to retrieve the name, because the name is not in the index as compared to the other one where we didn't, we didn't select ID, but we won't just wanted to get a count and the count is obviously also in the index. So we don't have to reach out to the table, and this is then the index only scan, which is quite a bit faster, usually than then an index scan, but the index still helps. If you want want to select other attributes.

C

And now you're only having like three three rows as a result, would you be doing and testing like change some records in a database to have more results to see the impact of larger data sets.

A

Yes, like I, said in this case the index is really small because there are no records on it, so we could well. We can actually try that, maybe in so we have 8 million projects.

A

Yeah I know that we could basically flip the code on approval flag to true.

A

Let's say for I: don't know how many records that actually touches, but you can try.

A

But you can also think of it in the way that which is the DD. We just added the column, and if we know that the major part of the records are not going to have that flag set anyway, so also in production later, because this is a rather rare and you can already say like okay. This is this is a good tip. Partial index is a good improvement.

A

Yeah and since we're running out of time, I'm going to assume that.

A

So, in this case, I would I would I think it's. The DMX is fine, as is what I would suggest to make it a partial index, given that we want to specifically optimized for, like URI I would make that one comment and the other one is. This is something that happens regularly. um This is redundant, something we don't need. The the concurrent index helpers, they already check for index existence, so there is no point and checking that doesn't hurt either, but something I point out, because it's just noise basically yeah other than that.

A

Don't have any more to say about it, which.

C

Or in this case, all will be checking which other indexes to exist, and if we can, you reuse or combine or rewrite them that they can have a common benefit. That's.

A

A very good point: yes, we should. We should actually do that, although it's like, if you, if you look at the initial query plan with the I, don't have it handy yet anymore, but there it is so initially without the indexes we created, it was a sequential scan and if we had indexes on any of these I would expect. Basically they would. It was at least trying to use them, but we should check- and that's let's do that, so we can look at the projects table.

A

And it does have quite a lot of the Nexus.

A

So I can see the pending delete one. So I I stand corrected there. It's not not using the index automatically.

A

And these are the ones we we just created so.

A

Yeah, it's a good point. We need to look out for other indexes, especially we still have a lot of them or quite a few indexes that are redundant and I think this is something to look out for that. We don't end up creating too many indexes.

A

C

Case but I guess there can be cases where two indexes are more beneficial than one index yeah. That's too generic the.

A

Other the tricky part is that for this query, you can only use one index because, like if you have two indexes like, if we had separate, makes on archive pending, delete and and the code owner approval flag, we would have to decide for one. There is no. You can't use three indexes to to satisfy the decree.

A

Although there are, there is a there's ways of doing that, but generally it doesn't doesn't work that way.

A

The alternative is to have a bit a bit scan on the on all the indexes and then and the bit set, but this is rather slow. Compare way.

A

Yeah but there's a good point: I will I'll try to put it on the documentation as well to look for the redundant and mixes.

A

Cool I'm going to write that down on the mr, but unless you have more questions or topics, I would leave it at that. I'll finish up the document I'll once that is merged, I'll, probably reach out for so that we can start get started when you decide to and when you want to become like it. That is reviewer and start with that start with the issue template and then just you know, go through the checkboxes get started.

E

Okay, but I think I have to say that I really like these these the database hours I mean I, would like to do this more next session to review with you, the.

D

E

Almost requests because I think I have to learn a lot great.

A

Thank you, I'm you'll,.

C

Be leading the next one friend and then and race so.

A

It's good to me no.

D

A

We can, we can do that and I mean if there are any questions or any like hey, Can Can, we look at the other quickly. We can jump on a call any time that works as well.

A

Right, especially before code freeze like this week, there are still a lot of pending reviews. So it's a good idea, although I, basically, that's the question. Actually, how do I get more visibility into the office hours I put it on slack in the development and database channel.

A

Where would you notice that the best.

E

I think you are very possibly in the specific channels. I.

D

E

Would at least recommend, for example, these this be session to develop area just to be you at least the last part, and see how to debug this. You know how I know how how things work or can be more applied to to do enhanced or improved a the database performance, because I think that will attract more more people to these sessions.

A

So you know I supposed to do with you.

E

Your friend make people aware, for example, say: hey n will be, we will have been reviewing some magic ways. So, if you have, if you want to see how we do it or something like that, see the video yeah.

A

That's a good idea: I'll do that.

A

Cool, thank you I'm, also enjoying the sessions a lot so appreciate you joining and yeah I'll. Let you know how it goes with the document and see you next time.