By centralizing data validation, it removes many potential failure and inconsistency scenarios (w.r.t different services validating things differently).

Worrying about CPU, without seeing if it's a real problem for your use case, is a premature optimization. Similar to worrying about foreign key constraint checks being too expensive. This is rarely the case, but if it winds up being a problem, you can relax it later and move the check it elsewhere in your stack (or remove it entirely).

Data storage and querying. Validation should be done in the application layer for security and scalability.

"Premature optimization" is the rallying cry of people standing in the corner of a room with a painted floor holding a paint brush.

Storage + querying is vastly underutilizing a tool that is designed to help you safely manage the entire lifecycle of your data – From defining that data (DDL), making it accessible (queries, indexes), ensuring consistency (checks, constraints, types, etc...), managing cascading data events (triggers), and more!

It's a fool's errand to try to (poorly) replicate these things elsewhere when there's already a battle hardened system with decades of investment that allows you to do these things declaratively and consistently for free.

And it's much safer! The DB will enforce that validation even when services are doing massive migrations or new clients are mutating the data in ways you didn't anticipate. It's far less error prone than ensuring that all of your services are validating data the same way.

> Validation should be done in the application layer for security and scalability.

Input validation, for sure. Sanitize as close to the perimeter as you can. But for consistency and integrity of data, let the database do what it's great at.

Your application server will probably not do it as efficiently as your highly-tuned database. There's no reason to assume that pushing that work into the database isn't a better scalability strategy. And it's certainly a more robust strategy from an organizational perspective.

Fixing slow things is frequently trivial compared to fixing bad data or tracking down subtle data bugs. For example, borrowing from a real world scenario in an Elixir service that uses Ecto to handle data mapping and validation, our code assumed a key in a JSON blob was a string, but in a handful of old rows it's a number. This inconsistency only existed in ~0.0005% of rows, and only in old data, but it surfaced in all sorts of weird ways. And was difficult to track down.

Using the RDBMS to handle validation would have never allowed this to happen.

> "Premature optimization" is the rallying cry of people standing in the corner of a room with a painted floor holding a paint brush.

That strikes me as an uninformed opinion. Optimizations usually work by making lots of assumptions on the nature of your problem and then implementing something that depends on those assumptions always holding. This leads to more convoluted and fragile code, but in return you get performance.

Writing optimized code frequently involves removing optionality, which is the equivalent of painting yourself into a corner.

Could you please elaborate on the "security" part of your comment? To me it seems database level checks would be more secure, as they would work with every possible way to update data, rather than just the blessed paths going through the app layer.

And as far as scalability goes... it depends. If your ambition is to take on twitter, you probably need to take scaling seriously on every step along the way. However, I've been building mainly B2B systems for 15 years now, and just about all of them have been purring away happily on the low end of AWS RDS offerings. There are orders of magnitude more performance available at the click of a button. Database scalability is not something that keeps me up at night.

If CHECKs are causing perf issues, then okay maybe put the problematic ones somewhere else.

TIL about https://github.com/mulesoft-labs/json-ld-schema: "JSON Schema/SHACL based validation of JSON-LD documents". Perhaps we could see if that's something that should be done in pgx or in the application versions deployed alongside the database versions?

Toon Koppelaars shows that in a few experiments done with Oracle ( https://www.youtube.com/watch?v=8jiJDflpw4Y&ab_channel=Oracl... )

Also very often doing the work in the DB means a simpler solution, fewer race-condition issues, and less work overall.

When talking about this particular case, the CHECK constraint, it is probably matter of measuring the impact and understanding at which point this might be relevant, and considering the cost of the alternative implementation at the application layer

It's good to be aware of additional strain you put on your DB, but I've worked with lots of applications where scaling simply wasn't a problem, and there I'd prefer validations closer to the data.