GitLab Database Office Hours, 30 Apr 2020

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Database Office Hours 2020-04-29/APAC

Description

No description was provided for this meeting.
If this is YOUR meeting, an easy way to fix this is to add a description to your video, wherever mtngs.io found it (probably YouTube).

A

B

B

C

B

C

I shall stop.

C

Well, give Tiger some time to join.

C

C

Let's start it so the first question is from Thiago and write very high. Well, unfortunately, we don't have a maintain present right now is Tiger Jones. You can speak about it, but I can't tell you about being a reviewer and training maintainer and now yeah.

A

I'm interested in that as well, because it's the steps to becoming a maintainer so definitely.

C

A

C

Like usually the answer is, it depends.

A

Yeah well, it's.

C

The most honest one, so initially we had just one maintainer like half a year ago, maybe- and it was he was of course super over wallet and that's where we, a bunch of people, joined his trainees and there was quite a few of them, so the load was okay, but over time mania not me, some of them become a meteor. So now we have five maintainer and I. Think four of six trainee maintainer, which the difference between reviewer and trained, is that the trainee has like 30 times more likely. The chest together. Review, sign and.

C

Especially when there were just for the trainee maintainer see it was, it was quite busy scientist and the other thing it depends on is like what type of more sequence you get assigned cause. Some of them are like pretty easy and quick to review, and some can take like literally like a week or something to figure it out.

C

So it depends a lot, but the more people we have as reviewers or trainers or maintained as the the easier it will be and I I, don't know like I can for sure, but I think it could be a little bit easier for maintain us right now, because it's there more of them and they they get much requests data like already regulated one so shortly. Some some of the problems was work already, but touch would whisper to maintain us to to to answer. But if you are asking in wondering about joining just just join yeah.

A

I'm asking some someone in the team, my team expressed an interest and yeah. Your answer helps, but it'll be a long journey before they become maintainer. The peer reviews and train tenants before that I'm.

D

A

Wondering how much of a hit I should be expecting on them daily or weekly, but take.

C

It as it comes it, but, but eventually it's up to you cause. You can always use the red circle. Icon is a status emoji, and this way danger will not assign you any reviews.

A

C

The moment it works for all type of reviews like Becky and front-end database. So there was discussion a few weeks ago about we may introduce another icon. That will mean just don't like if you want to keep doing fake interviews, but you don't have the time for more database, but at the moment, is just one for also so eventually, that's what I'm, using a lot when I, when I'm like over know that I just sit direct icon, and that gives you some time to like clear your back to visit reviews of just team work.

C

One can always give it a try.

A

C

Very much yeah yeah. Thank you so.

A

D

C

Hip one, my point, this point is mine, and this is something that.

C

Popped up during the possibility of training we're doing this week and I was looking at that extension before and was I. Don't know somehow was left with the expression that's used only when developing color is indexes and it's kind of mode for production use. So it's basic. It's like station called b3 gist, which lets you create an index on an integer column in text column using the the gist text.

C

Operators, which is something I've, seen a lot in reviews when people want to let a given out complete for branch, name or environment, name of something else that usually scoped or project, and they you, like you, try to do the index on project ID, whatever the text field is, but you can't at the moment because it has to be either victory or disdain. Just doesn't for index co-op. So and curiosity can be quite know something more about it's like yeah.

C

Just to finish. The other thing is that for some reason, I guess someone was testing. We already hit that extension in production, but it's not in structure SQL. So we can't like assume that everyone else- okay, it's off, we have it, but we don't use it so I was thinking. Should we just like off its time using right.

B

Right extension is there because it's part of core distribution of postage right, so it's in contributes a very old country, a standard country module and as for like I will I will answer from the like to last question. You are thinking about combining it with existing just indexes, I believe those are for three grand yeah.

C

B

Like a couple of months ago, I said on the rest that we see that sometimes we want to have a like tree gram, lookup and plus.

B

We have some timestamp, some some scalar data like integer or timestamp, and we want to combine it like I thought it should be possible because just should, like gist, is like it's like an extension of the idea of b3 I can explain a little bit in more details, but it's like an extension to any type of data, and but like long story short, it turned out not to be possible to combine grams. Unfortunately, and telegrams are very different types at that type of data.

B

It's not a single dimensional date or two dimensional data like you, would expect for using our tree or something it's a set sets of something and actually those trees like geez. This generalized idea, like first it was first people had only be three and our tree arteries is for multi-dimensional data like on surface or in n dimensions, and then a guy from from Berkeley University he's still there leading that a database department.

B

His name is Keller Stein. He said we can create abstract, abstract structure, not thinking about what kind of data it is and b3 will work the same way and our three work will work in the same way and other stuff and people from Russia implemented it in post views like it was implemented, but war was not supported. They added wall support, so acad properties and this became became gist and they realized that exist in our tree.

B

Implementation and pause B's is much worse than just kind of our tree implementation, but for B tree it was not so performance for be existing bill. Tree native b3 was still better than just way of implementing b3, so so they they created this additional. You can see the teller cigar for egbert no phone III open documentation, so they created this. This just be three as a separate extension, because there are some differences with in infection in functionality compared to a regular battery, but performance will be worse a little bit right. So it's not that like.

B

If you want good performance use standard battery. As for 3 grams, you cannot combine its different kind of data. It's it's like set. A delay just just option for sets is called rational. Three rt3, it's a different story, so why this extension can be useful. It can be useful for exclusion constraints if you want in, like it likes I, think it likes uniqueness. You cannot create unique index using bit resist type, but you can say: I need mutually exclusive, like I need to ensure that some rangers are exclusive or something like this.

B

So exclusion constraints- and this is this- is actually I. The only case I I'm aware of in other cases just loses to standard b-tree, unfortunately, so this is what it is. Sorry.

C

Because I was thinking, let's say you have an out to complete for the environment, name and pretty much. Every project has a production, environment or prod, and currently we should have three gram index on, unlike the name of the environment but like it's like, there are many a dips in the table and if you can draw an index on project ID in the name, wouldn't be that much faster because you don't cause I what I happens now, what I assume happens in house? It gets all the production environments and then filters by project IG.

B

It depends. Maybe there is a bitmap index. Can there so it users to in the combination of two indexes and.

B

So I I'm, like when, like a eternal foreign internal full-text search in Postgres, is also based on gist. It can be based on June and it's faster, but it can be based on gist and there I used like a trick to put, for example, project idea, as it will be a word, but here we have three grams: it it shrinks all chunks or three letters. I I, don't know how we can put project ID there, like I, already thought about it and didn't find a good solution for it.

B

Unfortunately, so so we need to rely on statistics and I assume. We need to consider corner cases like when we have a lot of Records for a particular project and see how we can improve it like, in particular, some query for some particular case. Unfortunately, so no long hanging fruits here and.

C

One last thing: you said that like station is there because it's part of the like default country package, but I got to button productions. Still someone had to do create extension bit ridges records, it's enabled, while it's not immediately on my local database.

B

Right right, possible ships with some extensions. They are not installed in any databases, yet you need to say, for example, PGT are GM 3 grams. Someone did create extension digitarium and enabled it in particular database. It's we have library on server installed, but we need like say we are going to use it with create extension by default. They are not like enabled. There are some other extensions which are very external to pause wisps, like third-party extensions.

C

B

Deliver library first.

C

B

Right right, yes, it's external library, a lot.

C

Of short answer to my questions, right.

B

Before no link hanging fruits a lot low hanging fruits here, unfortunately, ok.

C

A

Don't have anything.

D

C

Oh, if you want to talk about what Adam was asking two weeks ago about the duping, Clark stables, you say to him well,.

B

I I will describe problem shortly. The current implementation of the groud migrations is quite weak when we approach a lot like building records in the table. For example, it shows its inefficiency because first phase of background migrations is to split install like call define batches right, and this is very inefficient. Already I I had like four billion records. I remember I had like 20 minutes, 30 minutes just to finish this planning period, planning phase it's very in the future.

C

It is the case, but if you manage to index on this car, like just all the columns that you need are in the index, is it gonna be slow for that large tables? Well,.

B

If you did just, if you have millions of Records, you need just to deal with 600 records. It's not a good idea to plan visiting batches like ordering by IG and planning them, and this planning phases will be slow because, yes, it's okay, it's on index only scan but I believe what I like I think one here past what I saw it's not only index only scan, but some it's like it degrades more. More and more. We go further because they're key think here is there is that we are.

B

We are trying to organize it into same size, batches which I think is false target. Actually, because why we like it's not a big deal. If batches are like there, I know not a problem actually, but we're trying to have like 10,000 items per batch and that's why it's not possible to use index only scan, because you need to have offset and offset the grace.

B

While you go further and further and like it's, it's it's definitely slow and then, if you want just to process, 600 rolls it's better, it's better to find proper index from existing ones or to invent some in index and then and then use select for update. You know this thing like right, non-blocking, like honestly I think like we are also throwing this.

B

We throwing these batches to sidekick and then to run a lot of parallel jobs, but my experience shows that we have more headache from parallelization here and we can process in one thread very fast. If we do proper query actually and have much less I have already implemented many times large table processing with select for update with parallelization support like ten ten processes in parallel and then disabled it and deploy it in single thread, and it was fast like billion records per day. It's really fast.

B

So, like a consuming one CPU, we also need to take care about how much wall we produce during the period of course, like more DPA related stuff, so I like I'm, trying to build an understanding that we need to somehow to rethink current interpretation of background migrations. Actually, because I have some draft I had some rough draft for some time ago, based on the idea that there is some indication we just use like for update and.

C

How is it not blocking so what.

B

Cardamon current permutation, we take like 10 min rows and we have planning phase we order by G and take first 10,000. Then next 10,000 we've offset right and then we remember boundaries put it to sidekick, and then we can paralyze the work, because we know that we patches do not intersect right because IDs are defined, and this is very slow to plan and very inefficient. If we just need to reach our hundred like six hundred rows, that's it like Adam discussed last week, just six six hundred rows, we better we could create.

B

Just we just create a temporary index which would ask allow us quickly, locate our target rows and then select for update using index. Only scan next say hundred or thousand rows select from debt would lock them. While we are issuing update, like with city we've select for update, then update released and they they, since our index, would be maybe like partial, like eighteen, only hundred or six hundred in the rows.

B

Once we update them, they are ready, not under our field of view right. They are outside of scope of index and select Rob. That allows us to run multiple batches because skip locked, skip, lock, track I've.

C

B

About important part, skip lock means if other process already up processing some of our target rows skip them very quickly, and this approach is much much more efficient if we need it's both efficient for full table, processing and partial processing, as Adam.

C

By the way we we to create indexes temporary indexes to support background migrations, quite often already for big tables, and that's basically like we try to get an index that has everything we need so that when the scheduling has done its, you can take just the roles that you are interested and its index only scan and then pass it to sidekick and I I. Don't think we do passing to to sidekick that much to paralyze it cause inside each job. It's usually sequential processing, it not paralyzed, and what one.

B

C

Yeah I think we well. We have one consumer and running at the time and that's actually why we want the budget so that we can run a batch then put a delay of like the minimum, is two minutes, give some time of database to recover and then process another bar. So usually there is no parallel processing.

C

Sometimes some some migrations can like split that batch in further batches and process them, but I think it's still sequential so yeah we we don't like select for updates, keep a lot will help, but I at least I haven't seen like many cases where we needed so and the other thing about is you said about trying to get equal size, batches I, think we do this mostly, so that we can predict how long it will take and like estimated migration.

D

A

C

Like my door, but wait for like two minutes or more than two another about so so that we don't create too much load to the thing right.

B

But okay I agree, but the question was I just why it was ticketed. My head, the question was how we can skip full table processing. This is how we we just need to get rid of planning phase these batches approach. We can do just like Rob. Let's keep locked based on our index. You.

C

Do this you do this until there is nothing selected right right.

B

Right use returning ID and have count or something like if we see that nothing was returned. Okay, we are done right. We can drop index. I can.

C

B

And put it to document, maybe will someone look at it? Maybe you may.

C

Have seen it but right, but it that's interesting and I will keep it in mind. Well, I am doing migration speaks, and yet sometimes it's really annoying. When you know you hate, like just a few rolls that you want to process, but you have to like iterate through the whole large table, just to find a mean right.

B

To predict when we like, where we are in terms of processing, we can have two approaches.

B

First, we can just have explained, select where it will be based on our index, and it will show us how many rows index have has still like Arbit, like not estimated right, because it's based on statistics- and we can also add, like vacuum, analyze to or just analyze to recalculate statistics and another approach is, we still can order by IG, especially if our index is friendly with this approach, and then we just know maximum ID in our table and starting from zero and again we can. We can have like how many percentage has left.

B

I also usually have like progress bar in such I also wanted to understand, like 50 percent 60 percent I, sometimes I, just even embedded to the same queries, multistage City, select, Rob, let's skip locked update, then estimate where we are then count if it's zero stop, usually so yeah I'm going to attach to put to the document that draft. So if you see we can benefit, we can return to the draft.

C

The other thing that was mentioned during the training this week is that cause like I said we do, but in a background population, and then we wait for some time to give the data based on some failure.

B

D

C

D

C

There is not there is nothing that guarantees that vacuum kicked in in those two minutes and finished, and the the guy who is doing the training suggested that in such cases it's good to explicitly dubaku on the table, wins you're done. Do you think that good idea, I think.

B

It's a reasonable idea and I. Remember: Yorick implemented this in the current background, regressions implementation. Actually it should be there already I.

C

So we are doing we're kickin back home with the background it.

B

Should be immigration helpers? This is what I remember. It was love the gold okay.

C

Yeah I'll check out shake the coat I. Do.

B

Great I found this sure I will put it to the document.

B

Actually my opinion that we will have, we will see the problem with the current application. More and more, with the growth, so 1 billion rows will be already very noticeable. It's hard to process whole table, for example, and actually we already have some tables we're into the four primary key reached, forty percent, so we will need to return to this topic again. I remember last time we escaped from the problem just dropping some column, yeah.

C

B

But right now it's approaching again and we are waiting for migration to pose beasts 11 to be able to to have non-blocking not now on default. We should need not now, but we will use some trick to install not now not now without blocking whole table for long. Oh hi,.

C

Dark idling face is that when people write on migration, they they try the curated execution plan on database lot, but that's usually the for the first bunch of the first few boxes and when it's run in production, it comes out that sometimes it may timeout for reasons, because one is like a country's explained. The density of the corn that we are looking at or just like you said, with with bigger offsets, it starts degrading.

C

So we there is no way for us to know how it's going to work until we actually execute it, which reminds me for your other suggestion about having a way to do test run of migration against production size database. This part of CI, which will be very helpful.

C

Anything else Tiger do want you to share your experiences maintainer to on the first question,.

D

Sure its maintainer, it's a lot better than reviewer. To be honest, that's.

C

What I was that's.

D

What I was assuming when I was a reviewer right near the end? It got pretty bad and it was, you know at least half a day, sometimes as a maintainer, maybe two hours a day, and sometimes you get one, and sometimes you get quite a few of four or five. So it depends, but probably on average yeah a quarter of the day very.

B

Helpful, thank you. Are there any like waves of load when some release approaches like before I know that, like release was changed to like continuously delivering features, and so on before I remember like every month, it was a huge wave of requests and it was like tough. Is it still order there.

C

Is still some of it, but not, it is used to be because we deploy to calm at least once a week, but still the the self ninja releases have cut once a month, and sometimes people want to get there they're working to the release because it's like it was asked form self-managed, customers or something so probably it's kind of the same, but not that bad.

C

All right so do we have anything else. Oh, we should.

C

Stop now the half of our.

D

Cross, you had a point about using Petri just well.

C

Yeah we'll cover this right. Nick said Nick said that it's not going here right. So you can. You can watch the recording.

B

Short answer: it is slower than the regular p3 right.

C

But what I still okay, like? Maybe what, if you hate so currently, we had just the gist index on the text call and do you think it's gonna be better than combined b3g stone on the integer n text, column I'll probably should like you.

B

If you, if you just index text column with a regular, be three orbit register, doesn't matter you will have, you will consider this text as a scalar value. Actually, so it's like ordered on in single dimension. Right three grams are do differently it they shrink text to and they they like, they create a race actually in and they then they index this race. There is no like more or less operators there, there's a better like contained, contains and is contained so like operators for sets it's very different story.

B

So so you cannot just use b3 just to to to do the same as you do with program module. Okay,.

C

I just checked there: actually we don't have just index, we have let a gym environment stable. We have victory or name bar, chart pattern, ops or trigger on.

B

All results it's nice, and this is yeah.

C

And it's my question was: can we like? Is there a way to add the project I did to that index because it's usually always called to a project, but I think what happens if you try to add two combined integer with text and bar chart pattern knobs. It's complaining that there is.

B

C

B

Believe that larger pattern- ops, it's up class, it's how like we can compare two values in different ways, for example, can sense, can see, can sensitive included or excluded. So, like very various ways and watch our text pattern absorber char pattern, ops is a way to apply like regular, like ASCII style comparison.

B

It's usually helpful when you want to have like blah blah percent sign lookups, so like prefix search, because you.

C

Know this is what we do for out complete right.

B

This is not. This is not it's different method compared to three grams. It's in this case you are like on like in one in single dimension and you are comparing using less or more or equal operators. So, in this case I believe as I remember.

B

These modifiers are applied not to whole index but to every column inside index. So you have, you can have multi column index and specify this thing to each column. I believe I, better, should check so I suspect we can do this. I mean we can have text with text pattern, ops, plus some number integer.

C

I, just I just checked and I think for char pattern. Ops is kind of like legacy finkles. We have just four of these and we have 27 of gin, three grandmas on a genie index and that's the one we recommend in in the docs and that's why people usually- and it's probably, when you try to add project I- did to Jeanne Jeanne index, which intagram up start where it's complaining, yeah.

D

It will not work.

B

But I understand why it's a legacy because it will work only for prefix search. You can have this percent like blah blah percent, but you cannot have like percent blah blah blah while three grams, they are more powerful, although a slower. Actually so, but they are more powerful because they, you start typing and you find a beginning of what which is not in the first one. In the phrase right.

B

So right, no still no good solution right to have a single index scan and color, both three grams and some integer, the same story with full-text search, unfortunately in pause, because you cannot, for example, have full-text search and order by, for example, ID or timestamp. There is special index, it's called room ram, it's external module, but I don't like it at all, because size of index is so huge. I tried several times cannot adopted it anywhere. It's very huge thing: it's.

D

B

Gene when you insert some integer there same story with just unfortunately, so no good solutions I know guys who could improve it, but it would be costly and and not not fast. No.

C

Yeah I, don't think it's actually that big problem, in least not yet because it's working was just something that I've spotted during previews and sometimes other videos like baking trigger thought. Oh, we have that index. Why don't? We have the project ID, because it.

B

Happened multiple times right it and we can expect degradation when some project have a lot of Records of some type and we do three grams and project ID is competing. What to do first and then like it's, it's it will be slower and slower. Unfortunately, so if we want to be proactive, this kind, this is a kind of problem we need to think about and try to solve somehow else, but I don't see a standard solution right now. Here, unfortunately,.

C

Outsourced plastic.

B

Well, last, six you're shy: you need to synchronize constantly I can do.

C

Yeah, that's but that's the problem, then. Okay, thank you. I.

C

Guess that's all for today, unless anyone else anything else.

C

Oh yeah, exact.

A

C

You cheering thanks.