PostgreSQL: Generated Columns and Expressions in Unique Indexes

During work, I came across an interesting bug report, where it was stated that saved website domains kept getting duplicated. By website domains, we mean literally just that: strings like https://whatever.tld.

On the surface, it seemed like a peculiar application side bug, something that’s better for a developer to handle, but it turned out to be a similar problem I’ve once already faced as an SRE.

The bug

The table in question, let’s call it domains, already had an index enforcing one per row (some_external_id, url), but that’s exactly where the problem lied. That only works for an exact string match on url, but in reality, some sources may add a domain with a trailing slash /, so, it was possible for both https://whatever.tld and https://whatever.tld/ to be input. From the perspective of such an index, these two entries are indeed different, and that meant that some enforcement was needed, to prevent duplicate rows differing only by said trailing slash. Of course, in reality, this meant to dig into the data and solve which row shall be kept, but that is not what this article is about.

Now, a generated stored column, similarly to my previous article on the subject is a possible idea:

ALTER TABLE domains ADD COLUMN url_no_slashes TEXT GENERATED ALWAYS AS (regexp_replace(url, '/+$', '')) STORED;

Then dropping and adding the indexes:

DROP INDEX idx_url;

CREATE UNIQUE INDEX idx_url ON domains (some_external_id, url_no_slashes);

But the problem is that this would’ve meant either finding and rewriting every SELECT query that references the original column to use the generated column, or keeping the original index to avoid potential performance regressions. Not only that, but anyone writing a new SELECT query in the future would have to know about the generated column to benefit from its index, which is difficult to guarantee.

So this is not quite right for an approach here.

So what can we do?

Postgres actually has a very fascinating feature to solve this specific type of problem, because it lets one build an index on the result of an expression, not just on a raw column. For example, we can literally say “for every row, store the result of regexp_replace(url, '/+$', '') as an index” We could then enforce uniqueness on this computed value, rather than the value of the old url column.

This means that SELECT queries do not have to be rewritten, and future query writers, like BI, would not need to adjust anything that they’re currently doing.

The SQL for adding such an index:

CREATE UNIQUE INDEX idx_url_slashes ON domains(some_external_id, (regexp_replace(url, '/+$', '')));

The price to pay of course, is that all the UPSERTs have to respect this new index, rather than the old, so anywhere with

ON CONFLICT (some_external_id, url)

has to become:

ON CONFLICT (some_external_id, (regexp_replace(url, '/+$', '')))

The UPSERT condition has to literally match the index, otherwise the insert would fail stating that there’s nothing to match the condition of the UPSERT with.

With a DO NOTHING as a pairing action, then any number of future INSERTs will simply quietly respect the constraint, no new row added.

What is the point?

The main benefit really is that now, all the application code can continue to work as is, it doesn’t matter if the input data has or has no slashes, it can now be saved all the same, without creating new duplicates, without having to create an entire now column’s worth of data as a workaround, and the database acts as clear, universal enforcer of rules, which keeps things simpler on the applicattion side.

The business rule now works the same way as a human would interpret it: “these two things mean the same thing, handle them as such”.

Moreover, WHERE clauses that match this exact expression, i.e. * -> WHERE regexp_replace(url, '/+$', '') = 'https://whatever.tld' will actually be able to use the index

Making it a rare case of breaking the no functions in WHERE clauses RoT.

In general, when a WHERE clause is a function, an index won’t be used, but this specific case makes it possible. Whether an index will actually be used, of course may still depend on other factors, so always check your EXPLAIN.

Conclusion

The flexibility Postgres provides with its indexes is a nice, but rarely discussed tool to add to the SRE toolbox, and felt it was worth a discussion.