5 ms·

I want to know why there hasn’t been much innovation in the less-sexy parts of ISO SQL. My go-to for this is to point-out how SQL-92 defined the same small set

I want to know why there hasn’t been much innovation in the less-sexy parts of ISO SQL. My go-to for this is to point-out how SQL-92 defined the same small set of constraints that we have today (PK, FK, UNIQUE, CHECK, and that’s pretty-much it). It’s been 30+ years and the expressiveness of SQL DDL constraints for data-modelling is completely unchanged. So far, only Postgres has extended it with EXCLUDE constraints; what I’d love to have is a “Non-unique foreign key” constraint and its inverse: a “NOT EXISTS” constraint - these alone would go far. Another huge need is for safe-and-sound data-structure invariants - it’s almost impossible to correctly store a linked-list on a table without running until concurrency issues.

SpicyLemonZest 2y ago
Active development in data systems has focused heavily on larger datasets where complex global state isn't practical to enforce on write. Some systems don't even enforce their PK or FK constraints.
- codeulike 2y ago
  Yeah thats it, I've never seen contraints used that much in the real world. Unique keys but thats about it. Clever database-level contraints usually come back and bite you in the arse when you're trying to do some large scale change or upgrade or fix. And if you're building an app you usually want the validation of input to be done as close to the user as possible (like in the UI), not relying on a round trip to the database and handling an exception. And some might say 'well have validation in both places' but then you've got a load of complex stuff to keep in sync which will be a pain in the arse if it gets out of sync (if your db rules differ slightly from your ui rules)
tanelpoder 2y ago
FWIW, Oracle now has data domains that allow more complex constraints, including JSON schema based ones: https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/create-domain.html https://docs.oracle.com/en/database/oracle/oracle-database/2...
- DaiPlusPlus 2y ago
  Domain-constraints are just reusable CHECK constraints on steroids :3
  - vips7L 2y ago
    JavaScript is just reusable assembly on steroids.
  - tanelpoder 2y ago
    There's indeed some comparison there as (as far as I understand) the current implementation of domain constraints all still works on a single table (one column or multicolumn). But the flexible domains (enforce different domains based on a field's value or expression's result) are pretty cool and the JSON-schema based domains could make some (JSON-related) validation things easier out of the box too (I searched the web, looks like Postgres has support for domains too). For more complex cases, one could write (or autogenerate) triggers, but that will likely cause performance and maintenance issues... so the other option is to standardize any important data validation & logic into application code - and that part of application code can be stored procedures if it needs to be close to the database. Edit: Sometimes it's easier to sell the idea of using stored procedures for (data) logic to non-database developers when you call them database APIs instead of stored procedures :-)
- yndoendo 2y ago
  Side note. Had to setup an Oracle development environment this week. I find the sql shell to be quite bad compared to sqlite shell. Latter can has dynamic scaling with the terminal width while former requires a manually applied fixed width assignment. Even the HMTL5 tools where bad. Creating a table via the UI caused a right pop-out modal that did not properly utilize the web browsers view. Typing in the column name would cause the data type field to be partial visible and selecting the data type caused the column name to be partial visible. My take, expensive solution with inferior tooling based on competition.
  - bux93 2y ago
jandrewrogers 2y ago
Many constraints are extremely expensive to enforce at scale to the point of being prohibitive. You are essentially turning your relational database into a graph database under the hood, with the infamously poor scalability and performance implied. A legitimate argument for the obsolescence of SQL DDL (independent of the DML) is that it defines some features that inherently scale too poorly to be used in many modern databases and it assumes certain implementation details that aren't actually true in some modern database architectures.
- sethammons 2y ago
  are you saying that graph databases suffer scalability issues or are you saying relational databases used as graph databases suffer scalability issues? I've done some high scale stuff off mysql with 10s of billions of operations a day across sharded clusters with some nodes holding terabytes of data -- fks, triggers, cascading deletes and such tended to be too expensive. I've not had the opportunity to use something like neo4j, which supposedly has a great horizontal scaling story. edit: not that my downvoters will see, but, seriously? I'm asking a question to understand if I'm following. What are you downvoting? And yes, I've heard you are not supposed to question downvotes; I'm honestly perplexed.
  - PeterCorless 2y ago
    The best scalability I knew about was JanusGraph running on ScyllaDB. You can also run JanusGraph on Cassandra, which is horizontally scalable. ScyllaDB will be more performant and vertically scalable. • Docs: https://docs.janusgraph.org/storage-backend/scylladb/ https://docs.janusgraph.org/storage-backend/scylladb/ • Detailed use case: https://www.scylladb.com/2019/05/14/powering-a-graph-data-system-with-scylla-janusgraph/ https://www.scylladb.com/2019/05/14/powering-a-graph-data-sy... • Video: https://www.youtube.com/watch?v=7WZyVUTwYJ4 https://www.youtube.com/watch?v=7WZyVUTwYJ4 Note this was a few years ago. I haven't seen a TigerGraph vs. JanusGraph head-to-head. The TigerGraph people are pretty sharp. If anyone has deeper knowledge would love to see comparative benchmarks. Both JanusGraph and TigerGraph are rated about the same in terms of popularity on DB-engines.com (between 100-150 rankings). Neo4j is still far more popular/well-known, at rank #21 this past month. But there are other options you can explore.
treflop 2y ago
Can’t you just use insert triggers and reject inconsistent changes? I haven’t used Postgres much but SQL Server triggers let you do pretty much any constraint you want. Of course you have to be careful because constraints can really slow things down.
- DaiPlusPlus 2y ago
  The overriding reason is that a trigger (compared to a constraint) does not represent a static invariant which one can then reason about, and potentially manipulate further - or serve as the basis for query optimisations (for example). When your RDBMS says that a particular CHECK constraint is enabled, then that’s a hard-and-fast guarantee that every row in that table satisfies that constraint. As a concrete example: if I have a simple CHECK constraint that ensures the Users.EmailAddress column matches a regex like “^\S+?@[\w\.\_\-]$”, then my ETL code that reads those records out from the DB doesn’t need to handle the case when “@“ is missing, thus greatly simplifying software design. Put another way: consider how refinement-types (or dependent-types) in OOP exist to declare and enforce hard guarantees about the state or shape of an object or data in-memory, and they also provide effectively zero-cost compile-time precondition checks; then you can appreciate how constraints in a databases do the same, except for persisted data on-disk. But a trigger, on the other-hand, needs some qualification as there are many kinds - we can ignore triggers which simply audit (I.e. log or alert) when some action is taken, but there are DML triggers which perform some test on data affected by the DML and either block the DML from continuing or committing, or allow it to happen. This kind of trigger is functionally identical to a CHECK constraint - but unlike a CHECK it doesn’t provide any guarantees. While he third kind of trigger won’t block DML, but it instead applies some transformation to the data affected by the DML before the change is committed; this has the advantage of allowing otherwise-invalid data to be corrected and accepted instead of blocking the operation; all of this is good stuff, honestly. …the problem is that the presence and enabled/disabled statue of the trigger tells you absolutely nothing about the state of the data at-rest - but a CHECK, FK, or UNIQUE CONSTRAINT does. Consider that if I removed that e-mail address regex CHECK and moved it into a block/allow TRUGGER then anyone who reads from my Users table now no-longer has any guarantees about the contents of the Email column; Now also let’s suppose that some other DBA, or perhaps an automated process, might need to legitimately disable the TRIGGER temporarily while they perform data cleanup work and then re-enable it; in the event that the cleanup job was botched then the invalid email addresses will lie there undiscovered until the ETL loader crashes the next time it runs because it was never written to handle malformed email addresses. So in truth, TRIGGERs and CONSTRAINTS are complementary to each other: a DML trigger that transforms data to satisfy a CONSTRAINT is a good example. ———— Now consider an abort/allow trigger that performs something “non-trivial” (as far as the DBMS is concerned) DML validation check, such as a cross-table, or even cross-database query; obviously that won’t scale and it also introduces more points of failure (ext. DB connections) - but hang on: for limited kinds of cross-table validation checks we use FK CONSTRAINTS as that’s what they’re designed for - and they work great because we know how to optimise for them (usually by adding FK indexes). So my argument is that there exist many other classes of validation rules which today can only be implemented as TRIGGERs but could instead be better implemented as a declared CONSTRAINT because of the hard guarantees that only declarative constraints can give (when enabled, of course). For example, a “non-unique FK” constraint would not be any more expensive to enforce than an FK because it reduces down to the same (indexed!) set-membership test as a normal FK constraint. The exact same applies to an “anti-foreign-key” constraint (except the set-membership test is inverted). It’s so trivial to implement - which means its so maddening that it doesn’t exist in SQL. My position is thus: if you can express a desired constraint as a deterministic function of a single row and the set of all indexes which runs within at-most O( n log n ) time proportional to the initiating DML’s time complexity then we should be allowed to do it ourselves.
DanielHB 2y ago
What we really need is a standard bytecode representation for relational queries so tooling can become more agnostic and more powerful. It is kinda crazy the contortions you have to do to make some dynamic queries with the existing tooling.

SpicyLemonZest 2y ago

Active development in data systems has focused heavily on larger datasets where complex global state isn't practical to enforce on write. Some systems don't even enforce their PK or FK constraints.

codeulike 2y ago
Yeah thats it, I've never seen contraints used that much in the real world. Unique keys but thats about it. Clever database-level contraints usually come back and bite you in the arse when you're trying to do some large scale change or upgrade or fix. And if you're building an app you usually want the validation of input to be done as close to the user as possible (like in the UI), not relying on a round trip to the database and handling an exception. And some might say 'well have validation in both places' but then you've got a load of complex stuff to keep in sync which will be a pain in the arse if it gets out of sync (if your db rules differ slightly from your ui rules)

tanelpoder 2y ago

FWIW, Oracle now has data domains that allow more complex constraints, including JSON schema based ones: https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/create-domain.html https://docs.oracle.com/en/database/oracle/oracle-database/2...

DaiPlusPlus 2y ago
Domain-constraints are just reusable CHECK constraints on steroids :3
- vips7L 2y ago
  JavaScript is just reusable assembly on steroids.
- tanelpoder 2y ago
  There's indeed some comparison there as (as far as I understand) the current implementation of domain constraints all still works on a single table (one column or multicolumn). But the flexible domains (enforce different domains based on a field's value or expression's result) are pretty cool and the JSON-schema based domains could make some (JSON-related) validation things easier out of the box too (I searched the web, looks like Postgres has support for domains too). For more complex cases, one could write (or autogenerate) triggers, but that will likely cause performance and maintenance issues... so the other option is to standardize any important data validation & logic into application code - and that part of application code can be stored procedures if it needs to be close to the database. Edit: Sometimes it's easier to sell the idea of using stored procedures for (data) logic to non-database developers when you call them database APIs instead of stored procedures :-)
yndoendo 2y ago
Side note. Had to setup an Oracle development environment this week. I find the sql shell to be quite bad compared to sqlite shell. Latter can has dynamic scaling with the terminal width while former requires a manually applied fixed width assignment. Even the HMTL5 tools where bad. Creating a table via the UI caused a right pop-out modal that did not properly utilize the web browsers view. Typing in the column name would cause the data type field to be partial visible and selecting the data type caused the column name to be partial visible. My take, expensive solution with inferior tooling based on competition.
- bux93 2y ago

jandrewrogers 2y ago

Many constraints are extremely expensive to enforce at scale to the point of being prohibitive. You are essentially turning your relational database into a graph database under the hood, with the infamously poor scalability and performance implied. A legitimate argument for the obsolescence of SQL DDL (independent of the DML) is that it defines some features that inherently scale too poorly to be used in many modern databases and it assumes certain implementation details that aren't actually true in some modern database architectures.

sethammons 2y ago
are you saying that graph databases suffer scalability issues or are you saying relational databases used as graph databases suffer scalability issues? I've done some high scale stuff off mysql with 10s of billions of operations a day across sharded clusters with some nodes holding terabytes of data -- fks, triggers, cascading deletes and such tended to be too expensive. I've not had the opportunity to use something like neo4j, which supposedly has a great horizontal scaling story. edit: not that my downvoters will see, but, seriously? I'm asking a question to understand if I'm following. What are you downvoting? And yes, I've heard you are not supposed to question downvotes; I'm honestly perplexed.
- PeterCorless 2y ago
  The best scalability I knew about was JanusGraph running on ScyllaDB. You can also run JanusGraph on Cassandra, which is horizontally scalable. ScyllaDB will be more performant and vertically scalable. • Docs: https://docs.janusgraph.org/storage-backend/scylladb/ https://docs.janusgraph.org/storage-backend/scylladb/ • Detailed use case: https://www.scylladb.com/2019/05/14/powering-a-graph-data-system-with-scylla-janusgraph/ https://www.scylladb.com/2019/05/14/powering-a-graph-data-sy... • Video: https://www.youtube.com/watch?v=7WZyVUTwYJ4 https://www.youtube.com/watch?v=7WZyVUTwYJ4 Note this was a few years ago. I haven't seen a TigerGraph vs. JanusGraph head-to-head. The TigerGraph people are pretty sharp. If anyone has deeper knowledge would love to see comparative benchmarks. Both JanusGraph and TigerGraph are rated about the same in terms of popularity on DB-engines.com (between 100-150 rankings). Neo4j is still far more popular/well-known, at rank #21 this past month. But there are other options you can explore.

treflop 2y ago

Can’t you just use insert triggers and reject inconsistent changes? I haven’t used Postgres much but SQL Server triggers let you do pretty much any constraint you want. Of course you have to be careful because constraints can really slow things down.

DaiPlusPlus 2y ago
The overriding reason is that a trigger (compared to a constraint) does not represent a static invariant which one can then reason about, and potentially manipulate further - or serve as the basis for query optimisations (for example). When your RDBMS says that a particular CHECK constraint is enabled, then that’s a hard-and-fast guarantee that every row in that table satisfies that constraint. As a concrete example: if I have a simple CHECK constraint that ensures the Users.EmailAddress column matches a regex like “^\S+?@[\w\.\_\-]$”, then my ETL code that reads those records out from the DB doesn’t need to handle the case when “@“ is missing, thus greatly simplifying software design. Put another way: consider how refinement-types (or dependent-types) in OOP exist to declare and enforce hard guarantees about the state or shape of an object or data in-memory, and they also provide effectively zero-cost compile-time precondition checks; then you can appreciate how constraints in a databases do the same, except for persisted data on-disk. But a trigger, on the other-hand, needs some qualification as there are many kinds - we can ignore triggers which simply audit (I.e. log or alert) when some action is taken, but there are DML triggers which perform some test on data affected by the DML and either block the DML from continuing or committing, or allow it to happen. This kind of trigger is functionally identical to a CHECK constraint - but unlike a CHECK it doesn’t provide any guarantees. While he third kind of trigger won’t block DML, but it instead applies some transformation to the data affected by the DML before the change is committed; this has the advantage of allowing otherwise-invalid data to be corrected and accepted instead of blocking the operation; all of this is good stuff, honestly. …the problem is that the presence and enabled/disabled statue of the trigger tells you absolutely nothing about the state of the data at-rest - but a CHECK, FK, or UNIQUE CONSTRAINT does. Consider that if I removed that e-mail address regex CHECK and moved it into a block/allow TRUGGER then anyone who reads from my Users table now no-longer has any guarantees about the contents of the Email column; Now also let’s suppose that some other DBA, or perhaps an automated process, might need to legitimately disable the TRIGGER temporarily while they perform data cleanup work and then re-enable it; in the event that the cleanup job was botched then the invalid email addresses will lie there undiscovered until the ETL loader crashes the next time it runs because it was never written to handle malformed email addresses. So in truth, TRIGGERs and CONSTRAINTS are complementary to each other: a DML trigger that transforms data to satisfy a CONSTRAINT is a good example. ———— Now consider an abort/allow trigger that performs something “non-trivial” (as far as the DBMS is concerned) DML validation check, such as a cross-table, or even cross-database query; obviously that won’t scale and it also introduces more points of failure (ext. DB connections) - but hang on: for limited kinds of cross-table validation checks we use FK CONSTRAINTS as that’s what they’re designed for - and they work great because we know how to optimise for them (usually by adding FK indexes). So my argument is that there exist many other classes of validation rules which today can only be implemented as TRIGGERs but could instead be better implemented as a declared CONSTRAINT because of the hard guarantees that only declarative constraints can give (when enabled, of course). For example, a “non-unique FK” constraint would not be any more expensive to enforce than an FK because it reduces down to the same (indexed!) set-membership test as a normal FK constraint. The exact same applies to an “anti-foreign-key” constraint (except the set-membership test is inverted). It’s so trivial to implement - which means its so maddening that it doesn’t exist in SQL. My position is thus: if you can express a desired constraint as a deterministic function of a single row and the set of all indexes which runs within at-most O( n log n ) time proportional to the initiating DML’s time complexity then we should be allowed to do it ourselves.

DanielHB 2y ago

What we really need is a standard bytecode representation for relational queries so tooling can become more agnostic and more powerful. It is kinda crazy the contortions you have to do to make some dynamic queries with the existing tooling.