Partial Indexes are a way to create indexing on a partial data set of a table which satisfies the given logic.
Let's get into an exmaple to understand partial indexes in Postgres.
Let us take an an exmaple of Bus tickets booking platform where we have table called bookings which stores the bookings, to keep things simple let us assume it only contains bus_id, seat_number, date, time, user_id, status, created_at.
Most of the time users would like to check their upcoming bookings and also as part of Booking platform features, the success bookings are highly retreived.
So it is always a good idea to index the success booking records, we can have a partial index for success booking as shown below.
1 | CREATE INDEX bookings_status_idx ON bookings (status) where status = 'SUCCESS'; |
Now comes the important use case where we can avoid duplicate bookings for the same seat in a bus, either from a same user of different user, In this case we cannnot use multi-column unique constraint becasue there might be multiple records in bookings table for a same seat_number, bus_id, date and time in CANCELLED or FAILED status.
So, Here we need to have conditional unique constraint on status field where status = 'SUCCESS'. we can add conditional unique constraints using unique partial indexes
Eg: Query to create unique partial indexes for the above mentioned use case.
1 | CREATE UNIQUE INDEX bookings_status_idx ON bookings (bus_id, seat_number, date, time) where status = 'SUCCESS'; |
These partial indexes become handy for developers to avoid duplication of records for high concurrency tables.