Do you remember when we were talking about cardinality and selectivity? If you skipped that video, it's a very good one. We were talking about cardinality and selectivity, and I said that if your data distribution was highly skewed in one direction or the other, it might still be a good idea to put an index on that column, depending on what the query is. I stand by that, that is true.

There's another option though, we can use a partial index and this is a very cool feature that Postgres has, SQLite has, MySQL doesn't have. This allows you to put an index over a portion of the table, including a unique constraint over a portion of the table, which is very cool. What we can do is we can say, "Hey, let's add an index to email, but only for our pro-members." 'Cause that's the, maybe that's the query that we do most often.

Let's take a look. Creating this index is quite easy. We can say, create index email on users email. That part should be familiar to you. We're gonna add a predicate here, and we're gonna say, where is_pro is true. If we create that, what is happening here is the B-tree structure is being created, but it's not being created as a composite index. It is still an index on a single column, but instead of including all of the emails in the table, it only includes the emails that match this certain predicate, which is the is_pro = true.

Now, if we were to run select * from users where email = [email protected], you know I'm a Pro User. We run that, we see I am a Pro User, but if we explain it, unlucky, we are not using that index whatsoever. That is because Postgres has no idea that I am a Pro User, we haven't matched our query to our index predicate here. Let's make a little bit of space. We're just gonna drop you down there, and yep, do that. Here we go. We're gonna say, is_pro is true. Now this part of the query matches this predicate of this partial index. With any luck, Postgres is going to say, "I gotcha." It says, "I have seen this before and I've seen it in this index. In fact that index, that index includes the email column for which you are searching right now. I am going to use that partial, I'm gonna use that partial index to satisfy this."

Importantly, it doesn't work the other way around. We are doing a sequential scan on users, because again, this predicate does not match. This goes back to our cardinality selectivity discussion, where you might have a massive table and just a little bit, just a few of the rows are interesting to you. That is a great use case for a partial index, because you can filter out all of that uninteresting noise. You can prevent it from ever entering the B-tree in the first place. That's gonna make your selects a lot more efficient. It's gonna make that B-tree very small and not bloated and it's going to make your inserts, updates, and deletes better, because you're not maintaining a massive B-tree with values that you do not care about.

If every time a row is touched or an email is updated in this table, you have to go update 980,000 records that you don't care about, that's bad news. If you can limit down your B-tree to just the values that you actually care about, that's gonna be better for all queries, whether that's reads or writes. I want to show you how you can enforce a partial unique index as well. We're gonna start out by adding another Aaron Francis. So select * from users limit 2. I'm gonna change, who do I want to be? We'll do cleo.simonis. Now, cleo.simonis is now [email protected] as well. If we were to read that back, select * from users where email = my email, now there are two of me in there, one is aaron.francis, one is cleo.simonis, which is much cooler.

Now here's the situation, I cannot create a unique index on users email at this point because there are two of me. What I want to do is I actually want to create a unique constraint, but only for active users. If you deleted your account, that email address should be able to sign up for a new account. What I want to do is I want to mark cleo.simonis as deleted. I want to say that this second Aaron is actually deleted. If we run that back, you'll see there are in fact, two of me, aaron.francis and aaron.francis, however, there is only one that is active. I feel like that should be allowed, I should be able to have a bunch of deleted accounts and keep resigning up. Why you don't restore my old account? I don't really know. But that's a you decision.

If you want to have soft deletes or tombstones or archives or whatever you want to call 'em, this is a way that you can enforce partial uniqueness across the table. We can create a unique index on email users, where you guessed it, deleted_at is null. Across all active users, i.e., the ones that are not deleted, they must have a unique email address. You can have 50 deleted accounts, I don't care about that, but for active users, you can only have one. You see that that index was created just fine, no constraint problems. I am still in there twice. In fact, if I were to try to delete this and null it out, it would say, "Sorry, there's already one active aaron.francis, there cannot be two active aaron.franci. Franci is absolutely the plural of my last name. That is a formal plural of my last name.

When it comes to partial indexes, you have to be careful that you include that predicate in your query. Remember when we created the index on email, where is_pro is true, you have to include that is_pro is true in your query, otherwise the planner stands no chance of knowing that it should go use that partial index.

When you're creating a partial unique index, you really have to know your domain. You really have to know your business logic, because soft deletes are kind of easy, it makes a lot of sense for soft deletes. What about order statuses? If an order is fulfilled, is there a certain unique constraint over fulfilled orders or orders that have been confirmed or orders that are not in a draft state? These can be incredibly powerful for enforcing business logic, but you have to have a good grasp on what the business logic is, before you go to create a partial unique index.