Discoveries in Ruby(and Rails): Partial indexes with Postgres

Rails has support for partial indexes when used with Postgres. According to Postgres a partial index is defined as "A partial index is an index built over a subset of a table".

What it is used for?. Imagine you provide users of your app to delete their account. But, for some reason you want to soft delete the account, maybe for data analysis purposes, or to give the user a grace period where they can undo it. A soft-deleted email is considered to be not taken, because although there is a record with the email in the database, but it is not used(soft deleted)

Then, it does not make sense to include that record's index(which contains a pointer to the original row in the users table) in the index file. This speeds up queries by ignoring soft-deleted users records.

Adding partial indexes with Postgres could look like this in SQL

CREATE UNIQUE INDEX users_email_deleted_at_is_not_null
ON users (email) WHERE deleted_at IS NULL;

Thankfully, you can pass a where: condition as an argument to add_index. So, the above code will look like this in rails's migration

add_index :users, :email, unique: true, where: "(deleted_at IS NULL)"

Thanks for reading, happy coding!.