1 00:00:00,060 --> 00:00:05,790 ‫Creating an index on a production database, especially on the large table, can take a long time. 2 00:00:05,790 --> 00:00:09,000 ‫And not only that, but it also blocks it. 3 00:00:09,000 --> 00:00:09,480 ‫It's. 4 00:00:10,680 --> 00:00:19,050 ‫On your tables, you cannot really write rights are blocked when you want to perform these kind of operations, 5 00:00:19,050 --> 00:00:20,490 ‫so which kind of sucked, right? 6 00:00:20,650 --> 00:00:22,530 ‫You can do reads just fine. 7 00:00:23,070 --> 00:00:30,050 ‫For example, select from grades where it is less than then that works. 8 00:00:30,060 --> 00:00:35,280 ‫You can read, but you can't really write because the index is using that fee of that particular fee 9 00:00:35,280 --> 00:00:36,720 ‫to build the index. 10 00:00:36,720 --> 00:00:36,990 ‫Right. 11 00:00:37,500 --> 00:00:38,710 ‫So that kind of sucks. 12 00:00:38,730 --> 00:00:44,340 ‫So Oscar came up with a better approach to create an index called Create Index, and this is what I'm 13 00:00:44,340 --> 00:00:45,740 ‫going to show in this video. 14 00:00:45,750 --> 00:00:52,650 ‫Got to drop this index and do that concept of creating the index concurrently so we can allow, write 15 00:00:52,830 --> 00:00:57,420 ‫and read while the index is being created, which is very, very variable. 16 00:00:58,110 --> 00:01:06,060 ‫You cannot afford going down or posing or locking transactions when you when you are essentially creating 17 00:01:06,100 --> 00:01:07,710 ‫index on the production environment. 18 00:01:07,710 --> 00:01:11,400 ‫So we're going to do the same thing, create index on grades. 19 00:01:12,390 --> 00:01:22,320 ‫On the field, but we're going to add a word concurrently, what that will do is it will do multiple 20 00:01:22,320 --> 00:01:29,730 ‫scans to perform then and it will wait for all the transactions, the right transactions to finish before 21 00:01:29,730 --> 00:01:30,300 ‫it, actually. 22 00:01:30,570 --> 00:01:32,270 ‫And it will pull itself right. 23 00:01:32,340 --> 00:01:35,270 ‫While while there are there are transactions. 24 00:01:35,280 --> 00:01:37,890 ‫So now if I want to insert integrase. 25 00:01:38,810 --> 00:01:42,590 ‫And values one, for example, I can do that, can I read? 26 00:01:42,790 --> 00:01:44,290 ‫Definitely you can read. 27 00:01:45,680 --> 00:01:50,330 ‫Where it is like that's the if you can commit and you can read, right. 28 00:01:50,810 --> 00:01:57,530 ‫However, what this will do because of the multicam multiple scares, because of the waiting. 29 00:01:57,790 --> 00:01:58,190 ‫Right. 30 00:01:58,730 --> 00:02:02,300 ‫That this operation is going to take way, way, way longer. 31 00:02:02,330 --> 00:02:08,600 ‫Obviously, COMPAR is going to consume more CPU, obviously, and it's going to consume more memory. 32 00:02:08,600 --> 00:02:14,450 ‫So it might take some of this CPU precious CPU memory from other processors, however. 33 00:02:15,580 --> 00:02:23,770 ‫Not only that, it can also potentially fail, guys, because consider of this index is essentially 34 00:02:23,980 --> 00:02:26,910 ‫a unique index and you're inserting duplicate values. 35 00:02:26,920 --> 00:02:28,900 ‫What the index didn't exist. 36 00:02:29,360 --> 00:02:33,010 ‫Technically, the index will end up in an invalid state. 37 00:02:33,160 --> 00:02:36,100 ‫And then that point you have to drop and recreate the index. 38 00:02:36,280 --> 00:02:40,800 ‫So this was created index concurrently, very, very useful tool. 39 00:02:40,800 --> 00:02:46,420 ‫And I heard that both is going to make this the default because when you create an index, you don't 40 00:02:46,420 --> 00:02:48,970 ‫really care if it takes a long time ride. 41 00:02:49,210 --> 00:02:52,810 ‫You really care about your actual production live database. 42 00:02:52,810 --> 00:02:53,350 ‫Right. 43 00:02:53,350 --> 00:02:55,060 ‫And reads Be fast. 44 00:02:55,310 --> 00:02:55,650 ‫Right. 45 00:02:56,530 --> 00:03:03,490 ‫And unblocked while in this case, you want your index to be created, and so if it takes a little bit 46 00:03:03,490 --> 00:03:08,410 ‫longer, but this was a very quick video talking about this new feature I'm going to see on the next 47 00:03:08,410 --> 00:03:08,720 ‫one. 48 00:03:08,920 --> 00:03:10,060 ‫Stay awesome, Gabbi.