‫Creating an index on a production database, especially on the large table, can take a long time.
‫And not only that, but it also blocks it.
‫It's.
‫On your tables, you cannot really write rights are blocked when you want to perform these kind of operations,
‫so which kind of sucked, right?
‫You can do reads just fine.
‫For example, select from grades where it is less than then that works.
‫You can read, but you can't really write because the index is using that fee of that particular fee
‫to build the index.
‫Right.
‫So that kind of sucks.
‫So Oscar came up with a better approach to create an index called Create Index, and this is what I'm
‫going to show in this video.
‫Got to drop this index and do that concept of creating the index concurrently so we can allow, write
‫and read while the index is being created, which is very, very variable.
‫You cannot afford going down or posing or locking transactions when you when you are essentially creating
‫index on the production environment.
‫So we're going to do the same thing, create index on grades.
‫On the field, but we're going to add a word concurrently, what that will do is it will do multiple
‫scans to perform then and it will wait for all the transactions, the right transactions to finish before
‫it, actually.
‫And it will pull itself right.
‫While while there are there are transactions.
‫So now if I want to insert integrase.
‫And values one, for example, I can do that, can I read?
‫Definitely you can read.
‫Where it is like that's the if you can commit and you can read, right.
‫However, what this will do because of the multicam multiple scares, because of the waiting.
‫Right.
‫That this operation is going to take way, way, way longer.
‫Obviously, COMPAR is going to consume more CPU, obviously, and it's going to consume more memory.
‫So it might take some of this CPU precious CPU memory from other processors, however.
‫Not only that, it can also potentially fail, guys, because consider of this index is essentially
‫a unique index and you're inserting duplicate values.
‫What the index didn't exist.
‫Technically, the index will end up in an invalid state.
‫And then that point you have to drop and recreate the index.
‫So this was created index concurrently, very, very useful tool.
‫And I heard that both is going to make this the default because when you create an index, you don't
‫really care if it takes a long time ride.
‫You really care about your actual production live database.
‫Right.
‫And reads Be fast.
‫Right.
‫And unblocked while in this case, you want your index to be created, and so if it takes a little bit
‫longer, but this was a very quick video talking about this new feature I'm going to see on the next
‫one.
‫Stay awesome, Gabbi.