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

How NoSQL forced the evolution of a scalable relational database

blog.memsql.com

Post reply on HN
Print view View original

91–100 of 211 posts

Re: How NoSQL forced the evolution of a scalable relational database

#91

Post by lkschubert8 »

matwood wrote: post #80
189723954 wrote: post #38

Earlier quoted context omitted.

>I think NoSql, especially things like Mongo, got popular because it is super easy to program with javascript. I chose Mongo for a project recently. I have many years experience with SQL databases, and I think SQL will become more of a niche in the future. Here are the reasons: Everybody uses an ORM with a SQL database. You can pretend they don't and everyone is writing raw sql, but they aren't. This is basically a b…

> Everybody uses an ORM with a SQL database. I'm ripping ORMs out of any backend code and using things like jOOQ. > I was just thinking to myself "I can write this in code in a few minutes instead of an hour or more".... > Joins are slow and complicated You know what's really slow? Creating joins in code. > This is not true at all. Programming languages are vastly superior at querying a database. Just go write a comp…

To the last point linq/ef actually generates one SQL query for whatever you are doing if you use it properly.

Re: How NoSQL forced the evolution of a scalable relational database

#92

Post by logophobia »

CuriousSkeptic wrote: post #75
threeseed wrote: post #27

Earlier quoted context omitted.

Not sure what you are talking about here. There have been scalable and strongly consistent databases since the invention of the concept of NoSQL i.e. HBase and Cassandra (CL=ALL). And the idea that "learning and optimize SQL" would instantly change people's rationale for using these databases shows you don't understand them much at all. There are many factors that come into play. For example you can't use SQL databas…

To me “large scale feature engineering” is precisely the kind of problem SQL excel at. Is it something that makesme grab for SQL it is the need to do adhoc querying exploring data. Care to elaborate?

He's probably refering to a combination of features that "nosql" databases have.

* Schema-on-read: Makes it easier to ingest large amounts of data, and then do ad-hoc exploration. The schema is only determined when reading the data, which is a bit easier for one-off data exploration, you determine how to interpret the data when actually using it. Not appropriate for production systems though. For example, a customer gives you a few TBs of data, you dump it on hadoop, and query it with spark. It would slow you down if you first have to convert it to a relational schema. Again, only good for one-off stuff.

* Most SQL databases have column limits, so if you have a very large amount of features, I'd imagine you'd run into these limits.

* Scalability. Feature engineering is very parallelizable, most normal SQL databases (excluding stuff like cassandra) aren't trivial to scale.

Re: How NoSQL forced the evolution of a scalable relational database

#93

Post by Amezarak »

189523954 wrote: post #90
matwood wrote: post #80

Earlier quoted context omitted.

> Everybody uses an ORM with a SQL database. I'm ripping ORMs out of any backend code and using things like jOOQ. > I was just thinking to myself "I can write this in code in a few minutes instead of an hour or more".... > Joins are slow and complicated You know what's really slow? Creating joins in code. > This is not true at all. Programming languages are vastly superior at querying a database. Just go write a comp…

>You know what's really slow? Creating joins in code. Ahh, you have combined two separate points into a new point that I didn't make and took it out of context. As I mentioned, I was writing queries for analytics software. This software needed fresh data once per day. There was no requirement for speed. I was referring to speed of development, not speed of the query. As for the other point, yes joins are slow, and ye…

> Nobody is using joins at scale and every large company that started their scaling journey in a SQL database started by performing multiple queries on a distributed database.

I've used joins "at scale" in multiple jobs in (conventional relational) databases up to a few TB with tens of thousands of transactions per second and never experienced any performance problem that was the result of a join, unless you count cross joins with no filters. I have never seen an instance where doing a join was faster in code and I don't see how it could be except perhaps in some unusual edge cases.

I'm sure things change at Google or Facebook scale, but almost nobody is Google or Facebook scale.

I've never understood the "joins are slow" meme or where it came from.

I'm also having difficulty understanding how writing analytic SQL queries is slower than writing normal code. Can you go into more detail? In my experience, the "slow" part of writing any analytic query is deciding exactly what you want to know and making sure you understand that the data means what you think it means. Once you have that, the only thing slowing you down is your typing speed, and I don't think a more compact syntax would really make a difference.

Re: How NoSQL forced the evolution of a scalable relational database

#94

Post by fnordsensei »

janemanos wrote: post #65
threeseed wrote: post #36

Earlier quoted context omitted.

This is the problem with people that use the term NoSQL. Do you mean Cassandra (BigTable), MongoDB (Document), Riak (Key/Value), Redis (Mix), Kafka (Log Structured) ? There are dozens of fundamentally different systems many of which are closer to an RDBMS than their NoSQL peers. And many of them have rigid schemas so it definitely isn't that either.

Don't forget native multi-model databases like ArangoDB

And Datomic.

Re: How NoSQL forced the evolution of a scalable relational database

#95

Post by amirouche »

nostalgeek wrote: post #5

Well, this is exactly why competition is good, in every domain. That's why PostgreSQL got its column mode and JSON type, because there was a clear use case for performance for the former and schema-less data for the latter. EAV pattern is a plague and I'm glad documents are replacing it.

Why EAV is plague? Isn't JSON type a wrapper of EAV?

Re: How NoSQL forced the evolution of a scalable relational database

#96

Post by 189523954 »

Amezarak wrote: post #93
189523954 wrote: post #90

Earlier quoted context omitted.

>You know what's really slow? Creating joins in code. Ahh, you have combined two separate points into a new point that I didn't make and took it out of context. As I mentioned, I was writing queries for analytics software. This software needed fresh data once per day. There was no requirement for speed. I was referring to speed of development, not speed of the query. As for the other point, yes joins are slow, and ye…

> Nobody is using joins at scale and every large company that started their scaling journey in a SQL database started by performing multiple queries on a distributed database. I've used joins "at scale" in multiple jobs in (conventional relational) databases up to a few TB with tens of thousands of transactions per second and never experienced any performance problem that was the result of a join, unless you count cr…

>I've never understood the "joins are slow" meme or where it came from.

Well SQL databases generally don't support joining across a sharded database, which is usually necessary to scale unless you try to scale vertically with high powered machines and your data fits into memory and so on.

They are also obviously slow compared to denormalizing and querying without a join. Then there is the other fact I mentioned that they contain redundant data so if the query you need to pull into code is large enough, it is a lot of data that has to get sent over the network.

>I'm also having difficulty understanding how writing analytic SQL queries is slower than writing normal code. Can you go into more detail?

Yes. A programming language, combined with a database like mongo or an ORM, allows you to create complex queries much more quickly compared to SQL. You can maybe go into stored procedures and start doing loops and recombining multiple queries in there, but programming languages like javascript etc are typically much nicer than those used in stored procedures.

I am talking about queries that require joins, self-joins, sub-queries, multiple types of joins, group bys layered on top of each other and so on. They are horseshit and terrible compared to nice programming languages and maybe using a couple of queries instead of one.

>In my experience, the "slow" part of writing any analytic query is deciding exactly what you want to know and making sure you understand that the data means what you think it means.

This takes a while, but so does writing the query. My non-programming coworker, while good with SQL, spent entire days trying to write the query to a query that he already knew in concept (as in, he knew what he wanted). So I don't agree with your point that understanding what you need is going to take so much time.

Re: How NoSQL forced the evolution of a scalable relational database

#97

Post by vinceguidry »

threeseed wrote: post #51
richmarr wrote: post #45

Earlier quoted context omitted.

> This is basically a big, complicated, and slow piece of software that tries to make a SQL database into something else. Just to nitpick a little, but I think it's a worthwhile distinction, ORMs don't try to make a SQL database something else... either literally or philosophically. To use them in any more than a trivial way you still to understand RDBMSs. They just make the queries less verbose and the output more c…

> ORMs don't try to make a SQL database something else This is simply not true. Many ORMs are designed to abstract away SQL and RDBMS concepts entirely. They deal in objects and object graphs and output SQL which can be quite disjointed from the object model e.g. many-many relationships.

I'm becoming increasingly convinced that there is only one good ORM and the rest are fit only for CRUD operations. ActiveRecord. The abstraction it provides is amazingly flexible. You can be as close to the SQL as you want to be.

With other ORMs, any time I need something not CRUD, I wind up hand-generating SQL. With ActiveRecord, I never need to. Looking at generated SQL, I do all the time with AR, happily it makes that very easy for me. Including my own SQL snippets in scopes? Easy. Hand-generating joins? Almost never.

Re: How NoSQL forced the evolution of a scalable relational database

#98

Post by Amezarak »

189523954 wrote: post #96
Amezarak wrote: post #93

Earlier quoted context omitted.

> Nobody is using joins at scale and every large company that started their scaling journey in a SQL database started by performing multiple queries on a distributed database. I've used joins "at scale" in multiple jobs in (conventional relational) databases up to a few TB with tens of thousands of transactions per second and never experienced any performance problem that was the result of a join, unless you count cr…

>I've never understood the "joins are slow" meme or where it came from. Well SQL databases generally don't support joining across a sharded database, which is usually necessary to scale unless you try to scale vertically with high powered machines and your data fits into memory and so on. They are also obviously slow compared to denormalizing and querying without a join. Then there is the other fact I mentioned that…

> Yes. A programming language, combined with a database like mongo or an ORM, allows you to create complex queries much more quickly compared to SQL. You can maybe go into stored procedures and start doing loops and recombining multiple queries in there, but programming languages like javascript etc are typically much nicer than those used in stored procedures.

I guess this is where we differ. I've written many SQL queries many hundreds of lines long taking advantage of all kinds of SQL features. I don't see how I could make them "nicer" by writing them in Javascript: SQL has plenty of warts, but well-formatted and organized SQL is hard to beat for expressing exactly what you want without all the cruft associated with how you;re getting it. Once you know what you want, it comes out pretty quick (IME), only your typing speed is the limit. I find you have to think much more carefully about what you're doing in other languages because you have to think more about how to do it without the db abstracting all of that away.

IME loops in SQL are a huge code smell - everything should almost always be done using set logic to be clean and performant.

Re: How NoSQL forced the evolution of a scalable relational database

#99

Post by zbentley »

sqlcook wrote: post #58
logophobia wrote: post #55

Earlier quoted context omitted.

I've met developers like that. Although honestly, sql is more than 40 years old and can be really nasty. Stored procedures, each database has a different dialect, nullability comparisons, CTEs. It's very different from any other programming language. It's not that hard to imagine people finding it hard to learn, especially front-end web developers who'd rather not touch the backend. If people don't know how indexes w…

It actually IS quite hard to imagine solid engineers finding it hard to learn simple SQL. It's a lot harder to write "nasty" sql than "nasty" js, and if you're a front-end dev struggling with sql, then you probably should not be doing back end work. Yes not all databases are equal, thats why there are ORMs and ANSI standards. From the list of bad practices you've "seen" people do in SQL, I would guess their comfort z…

> It's a lot harder to write "nasty" sql than "nasty" js

That may be true (though, when it comes to dealing with more complex reporting functions and per-database-implementation differences/quirks outside of the realm of ANSI SQL it may not be).

However, it misses the point. Most engineers that struggle with SQL struggle with it because it's hidden from them partially (they're composing queries from snippets/query builders that come from other code, and never get to see the schema directly since it's hidden behind migrators and management interfaces) or completely (ORMs). Because the actual queries being run on actual schemas are less obvious, people do the wrong thing a lot.

That's not to say that abstractions on top of SQL are always bad--perhaps they're over/mis-used, but having worked on massive codebases where every dev's interaction with the database was "write a query in text or with a select().where().from()-type thin wrapper" and codebases that were 100% Django ORM, I can say with confidence that neither approach scales well absent big investments in correctness and RDBMS education.

Re: How NoSQL forced the evolution of a scalable relational database

#100

Post by zzzcpan »

antirez wrote: post #31

Redis is not mentioned here at all so maybe the author is thinking mostly of other NoSQL software here, but well, in the case of Redis the whole point was not just the in-memory performance part, but the data model as well. My claim is that you can't really exploit the advantage of using memory if you perpetuate in using the memory to represent the same data model that you were using with relational databases. For in…

In general understanding performance is way more critical for NoSQL/distributed systems, where one wrong assumption can have a huge performance penalty. SQL as a language and abstract relational model just can't work in this situation well.

> For instance I've issues to see how modern SQL systems can replace CRDTs based stores.

They can't of course, but it's not only CRDTs. SQL is incompatible with a lot of things that came out of distributed systems research. Just like POSIX filesystem APIs or pretty much any legacy stateful tech. And no amount of PR articles can fix it.

Post reply on HN