Re: Proposal: Add a UNIQUE NOT ENFORCED constraint

On Sun, 4 Jan 2026, 18:34 Jacob Jackson, <jej.jackson.08@gmail.com> wrote:
>
> I am working through a case that requires very high throughput inserts
> that would be slowed down by a unique index, but that has externally
> guaranteed unique values and could benefit from the query planning and
> documentation provided by a unique constraint.
>
> To fill this need, I propose adding a UNIQUE NOT ENFORCED constraint,
> which would tell Postgres to assume uniqueness without creating a
> unique index to enforce it, leaving ensuring uniqueness to the
> developer, similar to unenforced CHECK and foreign key constraints
> (https://commitfest.postgresql.org/patch/5285/).
>
> -- Reasoning --
>
> In many cases, the idiomatic/generally best way to write a query
> requires a uniqueness check (a SELECT WHERE ... = ANY()/IN or really
> any semi-join when optimized to an inner join, UNION, etc), meaning a
> Unique/HashAggregate node will be added, increasing overhead unless
> there is an explicit unique constraint. An unenforced unique
> constraint would allow developers to use their knowledge of the
> data/previous validation procedures to eliminate the extra node.

Unenforced constraints should be considered invalid (how would we
prove they're valid?), and because the planner is not allowed to rely
on invalid constraints, why would the planner be allowed to remove
these nodes for unenforced-and-therefore-invalid unique constraints?

It's quite likely that the query output would be incorrect when we
expected the data to be unique for unenforced constraints, while the
data in the table isn't actually unique for that unenforced unique
constraint; and knowingly giving incorrect results is not something I
can agree to.

> This
> would also help with documentation/observability tools by providing
> more context on the data without adding overhead.

Adding such constraints as metadata (without backing index) seems
fine, but we can't use the constraint in the planner, nor in the
executor, for the reason mentioned above.

> A number of SQL databases already implement this, not only including
> most data warehouses but also many more traditional databases (Db2
> (https://www.ibm.com/docs/en/ias?topic=constraints-informational),
> SingleStore (https://docs.singlestore.com/cloud/create-a-database/specifying-unenforced-unique-constraints/),
> etc).

I'm concerned they don't actually comply with the SQL standard when
they process data from those tables, if they assume unenforced unique
constraints to always be valid.

> 2. Create a new "dummy index" index type. This would not include any
> update triggers and would have an infinite cost to prevent usage in
> query planning, but it would still serve the purpose of proving the
> existence of a unique index.

It would be faster than a btree, but it'd still have the issue of
adding the overhead of column projection costs, and it'd be a (false?)
target for LR's replication identity, which I'm concerned about.

> Unenforced unique constraints also differ from other unenforced
> constraints in that their use in query planning can yield poor
> results, rather than just worsening planning estimates.

Does a table with unenforced constraints produce different plans than
one without those unenforced constraints? That seems wrong to me;
there is no material difference to the information we have on such a
table that we can consider true.

> This problem
> should be clearly documented, and error handling will likely need some
> changes, but ultimately, it is not fundamentally different from the
> many other ways that developers can write faulty queries.
>
> Let me know what you think.

I think this feature would be a loaded footgun, especially if the
planner starts to consider unenforced constraints as valid.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)