Unique partial indexes with PostgreSQL
How to use unique partial indexes to avoid conditional duplication
When you need to deal with concurrency and unique records databases are your friend. Unique indexes are the best way to avoid data inconsistence and duplication.
No news here, right? OK. Unique indexes are something used by most developers.
What if we need a conditional unique index? One field must to unique based on another field value.
All this stuff started with one real need on my work. We needed to create a toggle feature. Users may enable or disable a given feature but we need to track when enabling and disabling happens.
A simple boolean field didn’t fit our needs, so we created the following table:
CREATE TABLE toggles (
user_id integer NOT NULL,
type varchar(255) NOT NULL,
enabled_at timestamp NOT NULL,
disabled_at timestamp,
);When the user enables some feature a new toggle record will be created. If the user disables that feature the enabled toggle record will be updated with the disabled_at date.
One of our concerns is to design software thinking on high concurrence. It may never happen, but what if a user double click the toggle and we make two async requests enabling the toggle? That’s the kind of crazy question we ask ourselves during the planning.
The first thing that came into our minds is to deal with this kind of problem with locking strategies. But soon I’ve realised PostgreSQL have a better solution to us.
Welcome partial indexes!
Unique indexes are useful to avoid duplicated records, but we needed a conditional unique index.
One user must have only one type of toggle enabled. He can have as many disabled toggles he needed but only one must to be enabled at time.
What we need is an unique index for user_id and type only when disabled_at is null. This is were partial indexes enters.
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate.
Partial indexes are great when you need to filter a specific subset of data. It’s an index, like any other, with a scope, and indexes can be unique… ;)
This is how we create a partial index:
CREATE UNIQUE INDEX unique_enabled_user_toggle
ON toggles (user_id, type)
WHERE disabled_at IS NULL;You can declare the condition where the index must be applied and the database does all the magic for us.
Testing the index
In order to prove our solution lets try to insert some records on the database:
INSERT INTO toggles (user_id, type, enabled_at)
VALUES (1, 'async download', now());The user has enabled the async download feature. He cannot have the same toggle enabled twice. If we run the same insert one more time we receive the following error:
ERROR: duplicate key value violates unique constraint "unique_enabled_user_toggle"
DETAIL: Key (user_id, type)=(1, async download) already exists.
Query failed
PostgreSQL said: duplicate key value violates unique constraint "unique_enabled_user_toggle"
Detail: Key (user_id, type)=(1, async download) already exists.Our index worked pretty well so far!
Lets insert a different toggle type for the same user:
INSERT INTO toggles (user_id, type, enabled_at)
VALUES (1, 'reduce bandwith', now());That’s OK too. In order to validate the full feature we must try to insert a disabled toggle and then enable the same toggle. We expect to insert two records.
INSERT INTO toggles (user_id, type, enabled_at, disabled_at)
VALUES (2, 'reduce bandwith', now(), now());INSERT INTO toggles (user_id, type, enabled_at)
VALUES (2, 'reduce bandwith', now());
It worked!
Conclusion
Partial indexes are pretty useful to achieve performance when you need to deal with conditional queries and it may be used to avoid duplication when you use unique indexes.
Hope you liked and see you next time!
