4 ms·

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 b

by paulmd 4y ago

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.

billythemaniam 4y ago
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.
- westurner 4y ago
  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 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?
sgk284 4y ago
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).
- mike_d 4y ago
  > 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.
  - silversmith 4y ago
    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.
  - billythemaniam 4y ago
    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.
anonymous23801 4y ago
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=OracleLearning 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
perlgeek 4y ago
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.

by paulmd 4y ago

billythemaniam 4y ago

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.

westurner 4y ago
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 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?

sgk284 4y ago

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

mike_d 4y ago
> 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.
- silversmith 4y ago
  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.
- billythemaniam 4y ago
  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.

anonymous23801 4y ago

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=OracleLearning 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

perlgeek 4y ago

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.