Comment by paulmd | Hacker News Reader

Comment by paulmd | Hacker News Reader

Nice work, however, I am structurally dubious of putting too much functionality onto a classical centralized RDBMS since it can't be scaled out if performance becomes a problem. It's CPU load and it's tying up a connection which is a large memory load (as implemented in postgres, connections are "expensive") and since this occurs inside a transaction it's holding locks/etc as well. I know it's all compiled native code so it's about as fast as it can be, but, it's just a question of whether it's the right place to do that as a general concern.

I'd strongly prefer to have the application layer do generic json-schema validation since you can spawn arbitrary containers to spread the load. Obviously some things are unavoidable if you want to maintain foreign-key constraints or db-level check constraints/etc but people frown on check constraints sometimes as well. Semantic validity should be checked before it gets to the DB.

I was exploring a project with JSON generation views inside the database for coupling the DB directly to SOLR for direct data import, and while it worked fine (and performed fine with toy problems) that was just always my concern... even there where it's not holding write locks/etc, how much harder are you hitting the DB for stuff that, ultimately, can. be done slower but more scalably in an application container?

YAGNI, I know, cross the bridge when it comes, butjust as a blanket architectural concern that's not really where it belongs imo.

In my case at least, probably it's something that could be pushed off to followers in a leader-follower cluster as a kind of read replica, but I dunno if that's how it's implemented or not. "Read replicas" are something that are a lot more fleshed out in Citus, Enterprise, and the other commercial offerings built on raw Postgres iirc.

If data validation doesn't belong in the database, then what does? At that point you're treating your RDBMS as a fancy filesystem and drastically underutilizing the tool.

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).

> If data validation doesn't belong in the database, then what does? At that point you're treating your RDBMS as a fancy filesystem and drastically underutilizing the tool.

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.

Saying "Premature optimization" is fine, as long as you leave yourself a way out. And the way out is clear here - when your database no longer can handle the load, drop the DB check constraints and move them over to application layer.

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 you are thinking of SQL injection when you say "security", yes you need to sanitize the input before querying the database. But that is different than validating the sanitized data is correct. The database is generally a better place to do that. If worrying about scalability is a legitimate concern and not a premature optimization, then look at one of the distributed SQL databases (Spanner, TiDB, CockroachDB, Yugabyte) which are getting pretty good.

> Data storage and querying

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.

All this does is make writing a CHECK constraint for a JSON blob relatively straightforward. And yes you should use CHECKs in the database. In practice, it is very easy for application code to either not check or screw it up. It is much harder to find and clean up bad data in the database than prevent it from being inserted. By making it a schema config, it is much easier to audit by other engineers and more likely to be correct.

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

You could cost the stored procedures' deployment costs in cryptographically-signed-redundant-data-storage bytes and penalize unnecessary complexity by Costing Opcodes (in the postgres database ~"virtual machine runtime", which supports many non-halting programming languages; such as rust, which is great to see.) and paying for redundant (smart contract output) consensus where it's worth it.

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?

No processing done at the DB very often implies more queries and consequently a higher workload in the DB than just doing the processing there. That's evident when looking at typical ORM usage (usually very chatty applications).

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

Scaling isn't really a concern for everybody and every application.

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.

Reply on news.ycombinator.com