5 ms·

Yeah, no shit, it's hard to get things like referential integrity and transactions working at scale, it takes many genius engineers and many years. A better ti

by abraae 6y ago

Yeah, no shit, it's hard to get things like referential integrity and transactions working at scale, it takes many genius engineers and many years. A better title would be: Feature Casualties of Large Databases (except for old-fashioned databases like Oracle, DB2 etc. that have been doing this stuff at huge scale for decades).

brandur 6y ago
To be fair, I'm more referring to installations where it's been necessary to scale up beyond a single node. Things like referential integrity and transactions get harder for sure, but they're not unsolved — as noted in the article, we have prior art now in the form of products like Citus, Spanner, and CockroachDB. But that said, even though Oracle et all do support these features even for large installs (on a single node), a lot of the RDMS niceties tend to be thrown out anyway. DBAs get afraid that non-trivial `SELECT` operations won't be able to use an index and cause production havoc, adding fields as `NOT NULL` gets hard to do, and even the simple act of raising an index can get expensive/scary. This is a big part of what this piece is about: even where these features are still possible, there's a bad tendency not to use them. Why is that, and how do we fix it?
- loopz 6y ago
  If something works, or sort of, people will not want to touch it. Large datasets become complicated and any optimization can turn bad for several use cases. It's overuse of RDBMS basically.
- abraae 6y ago
  Oracle (and DB2) have had multiple nodes for decades, with support for referential integrity and transactions across nodes. I guess perhaps you are talking about geographically distributed nodes that have slow pipes between them? But in any case, yeah, there's a bizarre tendency for humans to want to latch on to hard and fast rules. I've heard things like "don't join more than 3 tables together", and you encountered at your workplace a rule that sql updates should only affect a single row at a time. Many workplaces have such farcical restrictions, resulting in behaviours like the allegorical monkeys in the room (https://workingoutloud.com/blog/the-five-monkeys-experiment-with-a-new-lesson https://workingoutloud.com/blog/the-five-monkeys-experiment-...). My theory - small children go through a phase where they love to know what the rules are and they love to set down rules for others. Some people never grow out of that, and they hold a worldview where everything would be OK as long as everyone followed the rules. And if for some reason that didn't work, then all you have to do is to add some more rules. Such people may not be the most creative but they cling fiercely to their rule-based approach to life. Since it's always easier to add rules rather than remove them, a poorly managed organisation can find itself trussed up tightly in these rules and an all around rubbish place to work where everything takes forever. While that covers the prevalence of rules such as "no SQL statement may update >1 row", you also ask why people may tend not to use features such as referential integrity in avant garde databases such as Spanner et al. Personally, I would avoid using such features if only because they are so challenging to engineer and are likely to contain bugs for a few months or years as they are bedded in. That's especially true once a system is under heavy usage and the impacts of any issues are enormous. It's easier to just keep on doing whatever you are already doing.
  - zozbot234 6y ago
    > ...multiple nodes for decades, with support for referential integrity and transactions across nodes... By definition, that would mean relying on something like two-phase commit. You're essentially running on a single "node" that just so happens to be physically dispersed across multiple machines, since the system would become partially or totally unavailable if the nodes can't communicate with low latency.
- will4274 6y ago
  > even where these features are still possible, there's a bad tendency not to use them. Why is that, and how do we fix it? Uh... databases ~~suck~~ have terrible usability issues? And so does SQL? Like, really hard core. I took some DB classes at uni and I've learned a lot since too, but no matter how many times some SQL wizard explains to me the "beauty of null," I just don't get it. Why would you want to use three-value logic when you can use two-value logic? Why doesn't SQL have custom types? I avoid almost-programming languages like the plague - if I'm gonna write a Powershell script that's longer than 50 lines, I pull out C#; if I'm gonna write a bash script that's longer than 50 lines, I pull out python; and if I'm gonna write a SQL procedure that longer than 50 lines, I do it in the application. For a job, I had to write a SQL script that, for each row with some conditions true, check the value of a string column, split it into a list of elements by commas, then split each element into pairs by a horizontal line, and return rows that had duplicate keys. Write a query that does this on MSSQL 2012. Then tell me if it works on the top five other SQL vendors. Now do it again, but instead of a string data with char delimiters, it's protobuf data. Other old programming languages like C and C++ have had their usability issues. Conflicting implementations, basic functionality missing (std::string::contains). But SQL feels .. perpetually 20 years behind. PS: Use [Kusto Explorer](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/tools/kusto-explorer https://docs.microsoft.com/en-us/azure/data-explorer/kusto/t...) for an hour and then tell me SQL has lots of nice features again. I have a Pavlovian reaction just to seeing "NOT NULL"
  - roenxi 6y ago
    Yes and no. When you describe '...split it into a list of elements by commas...' style problems you are describing un-normalised data. SQL is explicitly designed to deal with normalised data, so it isn't a fault if it struggles. NULL is unpleasant to deal with, but it is unavoidable - sometimes the value of data isn't known and the database has to represent that somehow. But I'm with you on getting out of SQL quickly as the code gets longer. It is a bad language and it is better to be working in something else.
    - zozbot234 6y ago
      PostgreSQL supports arrays as a first-class feature. There's very little issue with "unnormalized" data when the data will never be used as a lookup key within the database.
pizza234 6y ago
> Yeah, no shit, it's hard to get things like referential integrity and transactions working at scale, it takes many genius engineers and many years. Well, the problem is that such mechanisms _must_ be in place (in well-designed applications); they're not optional. The consequence is that, if they're not implemented at the lower (db) layer, then they're going to be implemented at the higher (app) layer. There is often the discussion of indexing/referential integrity for schemaless data stores. One may think that such data stores solve those problems; they don't - they push it to the application. Therefore, it takes many genius engineers-years... anyway. Solutions provided by the data stores, like FKs, just happen to solve the problem out of the box (where applicable, which of course, it's not always the case). The article actually mentions this, although not so dramatically :-)
zozbot234 6y ago
Yeah well, it all depends what you mean by "scale" or "large". The vast majority of real-world applications are well served by a single-source-of-truth approach, which makes it relatively easy to get these features.

by abraae 6y ago

brandur 6y ago

To be fair, I'm more referring to installations where it's been necessary to scale up beyond a single node. Things like referential integrity and transactions get harder for sure, but they're not unsolved — as noted in the article, we have prior art now in the form of products like Citus, Spanner, and CockroachDB. But that said, even though Oracle et all do support these features even for large installs (on a single node), a lot of the RDMS niceties tend to be thrown out anyway. DBAs get afraid that non-trivial `SELECT` operations won't be able to use an index and cause production havoc, adding fields as `NOT NULL` gets hard to do, and even the simple act of raising an index can get expensive/scary. This is a big part of what this piece is about: even where these features are still possible, there's a bad tendency not to use them. Why is that, and how do we fix it?

loopz 6y ago
If something works, or sort of, people will not want to touch it. Large datasets become complicated and any optimization can turn bad for several use cases. It's overuse of RDBMS basically.
abraae 6y ago
Oracle (and DB2) have had multiple nodes for decades, with support for referential integrity and transactions across nodes. I guess perhaps you are talking about geographically distributed nodes that have slow pipes between them? But in any case, yeah, there's a bizarre tendency for humans to want to latch on to hard and fast rules. I've heard things like "don't join more than 3 tables together", and you encountered at your workplace a rule that sql updates should only affect a single row at a time. Many workplaces have such farcical restrictions, resulting in behaviours like the allegorical monkeys in the room (https://workingoutloud.com/blog/the-five-monkeys-experiment-with-a-new-lesson https://workingoutloud.com/blog/the-five-monkeys-experiment-...). My theory - small children go through a phase where they love to know what the rules are and they love to set down rules for others. Some people never grow out of that, and they hold a worldview where everything would be OK as long as everyone followed the rules. And if for some reason that didn't work, then all you have to do is to add some more rules. Such people may not be the most creative but they cling fiercely to their rule-based approach to life. Since it's always easier to add rules rather than remove them, a poorly managed organisation can find itself trussed up tightly in these rules and an all around rubbish place to work where everything takes forever. While that covers the prevalence of rules such as "no SQL statement may update >1 row", you also ask why people may tend not to use features such as referential integrity in avant garde databases such as Spanner et al. Personally, I would avoid using such features if only because they are so challenging to engineer and are likely to contain bugs for a few months or years as they are bedded in. That's especially true once a system is under heavy usage and the impacts of any issues are enormous. It's easier to just keep on doing whatever you are already doing.
- zozbot234 6y ago
  > ...multiple nodes for decades, with support for referential integrity and transactions across nodes... By definition, that would mean relying on something like two-phase commit. You're essentially running on a single "node" that just so happens to be physically dispersed across multiple machines, since the system would become partially or totally unavailable if the nodes can't communicate with low latency.
will4274 6y ago
> even where these features are still possible, there's a bad tendency not to use them. Why is that, and how do we fix it? Uh... databases ~~suck~~ have terrible usability issues? And so does SQL? Like, really hard core. I took some DB classes at uni and I've learned a lot since too, but no matter how many times some SQL wizard explains to me the "beauty of null," I just don't get it. Why would you want to use three-value logic when you can use two-value logic? Why doesn't SQL have custom types? I avoid almost-programming languages like the plague - if I'm gonna write a Powershell script that's longer than 50 lines, I pull out C#; if I'm gonna write a bash script that's longer than 50 lines, I pull out python; and if I'm gonna write a SQL procedure that longer than 50 lines, I do it in the application. For a job, I had to write a SQL script that, for each row with some conditions true, check the value of a string column, split it into a list of elements by commas, then split each element into pairs by a horizontal line, and return rows that had duplicate keys. Write a query that does this on MSSQL 2012. Then tell me if it works on the top five other SQL vendors. Now do it again, but instead of a string data with char delimiters, it's protobuf data. Other old programming languages like C and C++ have had their usability issues. Conflicting implementations, basic functionality missing (std::string::contains). But SQL feels .. perpetually 20 years behind. PS: Use [Kusto Explorer](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/tools/kusto-explorer https://docs.microsoft.com/en-us/azure/data-explorer/kusto/t...) for an hour and then tell me SQL has lots of nice features again. I have a Pavlovian reaction just to seeing "NOT NULL"
- roenxi 6y ago
  Yes and no. When you describe '...split it into a list of elements by commas...' style problems you are describing un-normalised data. SQL is explicitly designed to deal with normalised data, so it isn't a fault if it struggles. NULL is unpleasant to deal with, but it is unavoidable - sometimes the value of data isn't known and the database has to represent that somehow. But I'm with you on getting out of SQL quickly as the code gets longer. It is a bad language and it is better to be working in something else.
  - zozbot234 6y ago
    PostgreSQL supports arrays as a first-class feature. There's very little issue with "unnormalized" data when the data will never be used as a lookup key within the database.

pizza234 6y ago

> Yeah, no shit, it's hard to get things like referential integrity and transactions working at scale, it takes many genius engineers and many years. Well, the problem is that such mechanisms _must_ be in place (in well-designed applications); they're not optional. The consequence is that, if they're not implemented at the lower (db) layer, then they're going to be implemented at the higher (app) layer. There is often the discussion of indexing/referential integrity for schemaless data stores. One may think that such data stores solve those problems; they don't - they push it to the application. Therefore, it takes many genius engineers-years... anyway. Solutions provided by the data stores, like FKs, just happen to solve the problem out of the box (where applicable, which of course, it's not always the case). The article actually mentions this, although not so dramatically :-)

zozbot234 6y ago

Yeah well, it all depends what you mean by "scale" or "large". The vast majority of real-world applications are well served by a single-source-of-truth approach, which makes it relatively easy to get these features.