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!

--

--

--

Every day we learn something little, something amazing.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Anderson Dias

Anderson Dias

Ever learning the joys of programming :)

More from Medium

PostgreSQL Database Set-Up on MacOS: PGAdmin and Postegreapp

Postgres: Query That Returns the Top 5 Records in a Group

Let’s have a look at the PostgreSQL CRUD operation

PostgreSQL

Efficient CPU-intensive GRPC service in Python — Part 2