Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why SQL is beating NoSQL, and what this means for the future of data

blog.timescale.com

Post reply on HN
Print view View original

281–290 of 310 posts

Re: Why SQL is beating NoSQL, and what this means for the future of data

#281

Post by stephenr »

jjirsa wrote: post #280
stephenr wrote: post #247

Earlier quoted context omitted.

Galera + HAProxy or ProxySQL should give you a multi-master MySQL with no app rewrites required, no?

Primary key conflicts are still a thing. Especially problematic where you're either not using auto_incrementing PKs, and/or you try to switch writes from one master to the other and app has to be aware of the switch.

Not using auto incrementing keys is surely a rarity with MySQL projects.

Even before galera was a thing regular MySQL replication could use auto_increment_(increment|offset) to make each server generate unique id's.

Re: Why SQL is beating NoSQL, and what this means for the future of data

#282

Post by oldandtired »

tynpeddler wrote: post #249
marktangotango wrote: post #231

Earlier quoted context omitted.

It's most certainly not hair splitting. If the data isn't natural to one table, as the GP says, then it's strong indicator of two things: 1. NoSQL of any kind is not the solution 2. Joining at the location of the data is always preferable to joining in application code due to the Principle of Locality[1]. The amount of data sent over a wire should always be minimized. [1] https://en.wikipedia.org/wiki/Locality_of_ref…

I'm not sure that point 2 is always clearcut. Because of the cartesian product issue, searching for large amounts of data with multiple joins can produce a huge number of returned rows containing a lot of redundant data. I've had a few small optimization issues that I fixed by breaking up one large query with several joins with 2 or 3 queries.

Something we discovered years ago in relation to joins in all of the SQL DBMS systems we used (from the lowly MS-Access to ORACLE and SQL-Server). The order in which you create the joins in the SQL determines how bad the cartesian product cardinality is. The DBMS's seemed incapable of rearranging the joins internally to get the smallest number of records.

We had quite a few instances where by a judicious change in the join structure of the SQL meant a many order of magnitude change is speed due to the decrease of the cardinality.

Our process was to test different orders of joins in problematic SQL and see what effects would be produced by the DBMS. It usually required we knew how many tuples were in each of the tables and how they would be restricted. The upshot was that it should have been possible for the DBMS to do its own analysis to produce the best result. We found none of them capable of this task in any meaningful way.

Re: Why SQL is beating NoSQL, and what this means for the future of data

#283

Post by oldandtired »

elvinyung wrote: post #36
pjungwir wrote: post #25

I've always found it a little funny that SQL was originally designed for non-programmers, sort of like AppleScript. I used to think neither of those panned out, but in fact there really are a lot of smart not-programmers who can use it. At a company I work with many of the support staff have been learning SQL to help customers pull reports from our data warehousey reporting database. So maybe the article is onto some…

(Disclaimer: I work at Databricks.) I really believe that Spark's "more-than-SQL" query interface is how things should be. SQL is of course the gold standard and probably represents north of 90% of analytic workloads, but there a lot of queries that (especially for us programmers) are much easier to express procedurally/functionally, rather than purely declaratively.

Many years ago, I worked on a project that required changes made to a reporting system that the original author had used procedural means to do data selection and updates. It was a highly convoluted "mess". The section I had to update was going to take some time to analyse the code to ensure that the specific updates would work.

I looked at what the code was doing and restructured into a set of queries that would do the same task in a simple sequence. It turned out that this simplification also sped up the entire update process by at least 1 if not 2 orders of magnitude.

There are few cases where processing the data procedurally is better than using SQL queries. Most time that it has been done procedurally, I have found that there are better ways to do what you require by judicious thinking and using SQL (the bronze standard).

I should say that my definition of procedural processing involves opening queries and processing tuples one by one. If you have a different definition then please let me know.

Re: Why SQL is beating NoSQL, and what this means for the future of data

#284

Post by oldandtired »

ak39 wrote: post #174
dragonwriter wrote: post #79

Earlier quoted context omitted.

Tutorial D obviously isn't SQL, but its not noSQL, either; despite the name, noSQL refers to non-relational stores, not relational stores with alternative query languages. But as much as I think it's better than SQL, I don't think Tutorial D (or D as a concept more broadly) offers enough to really displace SQL.

> noSQL refers to non-relational stores, not > relational stores with alternative query languages. Very well said. The concept of relational databases (based on relational/algebraic theory) is thoroughly independent of the dialects to access said databases. Currently it's SQL.

SQl is not relational for all the hype that ORACLE/IBM/Microsoft have put out over the decades.

SQL fails to be relational because it doesn't deal with relations. At its base, it deals with bags of records and not relations of tuples.

Tutorial D is relational but has a lot of warts. D4 was relational but changed to allow access to SQL databases.

If my understanding is correct Rel (by Dave Voorhis) is a Java implemented Relational DBMS (a true RDBMS). There are a variety of projects also are designed to implement a true RDBMS. Another might be Andl (by David Bennett) and there is also SIRA_PRISE (by Erwin Smout). There are more and are documented at www.thethirdmanifesto.com, if you are interested.

[EDIT - correct spelling erro]

Re: Why SQL is beating NoSQL, and what this means for the future of data

#285

Post by oldandtired »

shub wrote: post #130
sk5t wrote: post #98

Earlier quoted context omitted.

Absolutely. Even a bigint / long sequence column so often feels a bit ridiculous.

I have 5 tables which have overrun the range of int in the id column. Happily they were defined bigint. A while ago one ran out of tinyints, but doing the schema change...was pretty terrible actually due to the foreign keys. The tinyint decision wasn't me, I want to make clear. New tables all get bigint. Either you don't have many rows, so you can spare the space, or you have a lot of rows and the thought of changing…

This is only a problem because of the laziness of those who have built the DBMS. Not necessarily the programmers fault but those who mandate the requirements of the DBMS (usually managers).

If you specify that a field is an integer, you shouldn't have to care how big that integer is. That is a concern for the DBMS and should not be yours. In point of fact, your only concern should be what fields you are using not how they are to be stored. You should never ever have to concern yourself with whether a field is a tiny_int, small_int, integer or big_int.

Any changes to the sizes that the DBMS has chosen for specific relations should be automatically propagated by the DBMS to any other area that refers to the original field. It should never require intervention by a DBA or programmer, ever.

Over the decades, I have read lots of recommendations for DBA's about how to manage the various databases stored in the DBMS. Most of these recommendations should have been put into the DBMS as a part of the attributes by which the DBMS controlled the database. These recommendations were often portrayed as a result of running various sets of queries and other functions in specific orders. All of them could have been automated out of existence, but to keep the DBMS simple, they forced these processes onto the DBA's and database designers.

Since we know what has to be done, surely the DBMS producers (like IBM, ORACLE, Microsoft, etc.) should be able to do so as a part of their product development.

Re: Why SQL is beating NoSQL, and what this means for the future of data

#286

Post by oldandtired »

zimablue wrote: post #200

It feels like everyone mixes relational databases with SQL in discussions. Relational databases are fine but SQL is disgusting. It's a query language that's very hard to parse or generate and has multiple incompatible variants. Ironically the Lambda calculus version looks much better, I'd never seen those before. I wish we'd go back to that as default, unify around a standard and expose block text SQL as an extra for…

Not quite, the confusion is that SQL DBMS's are assumed to be relational when they are not. None of the major players have produced a relational DBMS (there is one proviso, but IBM didn't promote that specific product out of the UK over its DB2 product out of the USA).

Re: Why SQL is beating NoSQL, and what this means for the future of data

#287

Post by jbergens »

bildung wrote: post #223
jjirsa wrote: post #65

Earlier quoted context omitted.

The reason people don’t start with a relational DB has nothing to do with the data model or query language - people choose databases like Cassandra because scaling to 500 Postgres or MySQL instances holding a combined petabyte of data is horrific, but it’s dirt simple in things like Cassandra The query language is a side effect of the underlying storage engine - you don’t choose it because you want a key value store,…

Postgres (as an example) has no upper limit for database size, and the upper limit for a single table was 64TB a while ago, perhaps higher now. And you can buy stock hardware with 6 TB RAM and 76TB SSD e.g. from Dell. If you max out that single server , you could easily hire one database engineer to care about horizontal scaling.

It doesn't help when the nbr of queries and updates increases a lot. For many systems having TB of data also means having thousands or millions of users.

You also probably want backups and possibly slaves that are updated inside transactions to avoid data loss. These things can lower your throughput even more.

Re: Why SQL is beating NoSQL, and what this means for the future of data

#288

Post by jbergens »

craigkerstiens wrote: post #53

I don't think the article could have said it much better. SQL is super powerful and makes much sense in so many ways. Nearly all apps have a relational structure to them and SQL is a reasonable way to interact with them. Some of my favorite conversations from the Postgres community 5-6 years back were when they were talking about a time when Postgres was being disrupted. The gray bearded DBAs (Hi Berkus and others) w…

You do have the impedance mismatch to OO languages, which are probably used in 90% of all applications.

Farter more, I don't really think that all systems have relational data, it's just possible to store it in that format. Sometimes 3 document types are easier to handle from code than 30-60 tables.

Re: Why SQL is beating NoSQL, and what this means for the future of data

#289

Post by jbergens »

tn_ wrote: post #221
randomdrake wrote: post #40

I don't buy it. The makers of the software that this appears to be a carefully written advertisement for, came to the same conclusion as the rest of the IT world (that some of us saw a mile away): NoSQL was, and still is, only good for very specific things in very specific cases; it's generally dreadful for anything that SQL engines could already do well. The subtitle for this contains: "After years of being left for…

There have been a ton of start-ups that I've talked to / been a part of who used MongoDb thinking their company is going to exponentially explode in MAU and they think they'll save themselves the scaling troubles by using NoSQL. What ends up happening is the codebase gets too gnarly when they try to start doing complex analysis. SQL is appropriate for like 95% of companies. A lot of these places I'm referencing end u…

Complex analytics should probably be done in a db separate from the production db anyway. The analytical db could be an RDBMS, a datacube or something else.

Re: Why SQL is beating NoSQL, and what this means for the future of data

#290

Post by elvinyung »

jinqueeny wrote: post #270
elvinyung wrote: post #263

Earlier quoted context omitted.

I said open source and mature :) IMO really the best option for distributed OLTP RDBMS/NewSQL right now is basically either Citus or Vitess, only because they are built on the backs of extremely mature databases. It really feels like all other options are not mature enough (except maybe VoltDB).

Could you please elaborate how you define "mature"?

IMO, in descending order of importance to me:

* Dependability. I can be completely certain that standard usage of the database won't lose my data or leave it in a bad state, at the very least.

* Community. There's an active IRC channel (or the like) where I can ask questions. When an error show up, I can Google it and easily find an applicable solution most of the time.

* Performance. Not just with synthetic (i.e. TPC-C, TPC-H, YCSB, etc.) workloads, but proven to have reasonably satisfactory QPS in many/most real production settings.

* Ecosystem. There's a good collection of first-party (and also ideally third-party) tools, constantly being improved. (Mere wire-compatibility with MySQL or Postgres might be good, but probably isn't enough, since 100% wire compatibility seems rare.)

Post reply on HN