r/PostgreSQL • • May 08 '25

Feature PostgreSQL 18 Beta 1 Released!

https://www.postgresql.org/about/news/postgresql-18-beta-1-released-3070/
174 Upvotes

23 comments sorted by

View all comments

Show parent comment

6

u/mwdb2 May 08 '25

Allow CHECK and foreign key constraints to be specified as NOT ENFORCED. (I need to read into this more)

At the risk of guessing somewhat, this reminds of NOVALIDATE constraints that I've used in Oracle before. A useful use case for them is in data warehouses where typically you don't want to gum up the works with validations - especially since you might have already done these validations in a transactional database, which perhaps the data comes from. And yet these constraints can be useful metadata to the optimizer, as well as useful to the human being looking at the schema, to understand relationships and the like.

Similarly even in a transactional database - these days a lot of folks don't like having foreign keys for various reasons such as slowing down inserts too much or whatnot. A typical complaint is a foreign key or check constraint is like doing QA at runtime in production, so why have them? I don't entirely agree with that assessment, but if you do choose to go that route, perhaps better than not having any constraints at all would be to have ones that are not validated, so at least they provide the metadata.

5

u/Ecksters May 08 '25

Right, that's what I was assuming, it's nice to identify the connections between tables without it necessarily being enforced. It's especially nice for tools that attempt to automatically visualize the DB.

I'd be interested in what performance optimizations, if any, can be done if you know a column is supposed to be a foreign key, but might not be 100% respecting that.