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

Relational databases aren’t dinosaurs, they’re sharks

simplethread.com

Post reply on HN
Print view View original

61–70 of 135 posts

Re: Relational databases aren’t dinosaurs, they’re sharks

#61

Post by jonathanlydall »

laurent92 wrote: post #7

For some reason, Java developers didn’t like writing SQL, so we introduced Hibernate which “does SQL for you”. Hibernate creates appallingly bad SQL, so “databases are slow”. Particularly when using a getter on a lazy-loaded relationship. A query might end up taking 1ms per record instead of 10ms for 10k records. You can rewrite all you want in Hibernate and greatly improve performance, but you often need to introduc…

C# person here and big fan of Entity Framework (but I have used NHibernate in the past).

I find the the main benefit of ORMs is type safety. Provided you're using the ORMs to also manage your schema (which you really should if you're using an ORM), then the compiler can guard you from a whole class of mistakes. You're also able to change table and column names with trivial effort and without fear of missing updating some SQL query string somewhere and only finding out during runtime that something is wrong.

While it's possible to write LINQ which results in bad SQL, if you didn't have an ORM it would still be possible for developers to write bad SQL anyway. Either way your developers should understand SQL at least a bit and be able to use something like SQL Profiler to ensure that their queries are performant. There is no substitute for competent developers.

There is a performance overhead to Entity Framework in the way it tracks changes to objects in contexts. If you're needing to work with a lot of objects from/to a database, then for these particular use cases you can opt out of some of these convenience features to avoid the performance costs.

ORMs are an incredibly useful tool if the trade-offs make sense for your project and you know how to use them correctly. But as with any tool, if you use it inappropriately, you're going to have a bad time.

Re: Relational databases aren’t dinosaurs, they’re sharks

#62

Post by int0x2e »

j16sdiz wrote: post #51

I don’t understand why we still need these kind of article.. After all these years, I thought the advantage and trade-off of different database should be well-understood. But the fact is, there are still lots of mis-infomation floating around. It looks like the lesson we have learnt are not communicated to border groups of engineers.

I'll provide my own anecdote - I was a part of a team that ended up choosing a document-store for a small service that services ~500 users (total, concurrent figure is far lower), where writes are uncommon, and where we ended up building ourselves all the tooling that common SQL tools offer for free. Why? Because people think RDBMS are dead. No matter how much I tried explaining it was the wrong choice - we still went for it.

Re: Relational databases aren’t dinosaurs, they’re sharks

#63

Post by vladmihalcea »

laurent92 wrote: post #7

For some reason, Java developers didn’t like writing SQL, so we introduced Hibernate which “does SQL for you”. Hibernate creates appallingly bad SQL, so “databases are slow”. Particularly when using a getter on a lazy-loaded relationship. A query might end up taking 1ms per record instead of 10ms for 10k records. You can rewrite all you want in Hibernate and greatly improve performance, but you often need to introduc…

I've been using Hibernate for 17 years and never had any performance issue with it.

Want to know my secret? It's very simple. I just read the manual in 2004.

After using it successfully for so long, I wrote a best-seller about how to get the most out of it:

https://www.amazon.com/High-Performance-Java-Persistence-Vla...

And, just because afterward I even worked on Hibernate to add all sorts of performance improvements to it, it doesn't mean I don't love SQL. In fact, I happen to run a High-Performance SQL training too:

https://vladmihalcea.com/trainings/high-performance-sql/

Your comment is based on a very common misconception that Hibernate aims to replace SQL. If that were true, then why do you think Hibernate has been offering the createSQLQuery (a.k.a. createNativeQuery) since 2002?

Re: Relational databases aren’t dinosaurs, they’re sharks

#64

Post by cageface »

aenis wrote: post #26

Another consideration is that, at scale, no sql is way cheaper. I run a service with approx. 900k daily users, each generating about 210 object writes and reads that need to execute within 50ms, and I am running this on firestore for about $350 a month, incl. Elb, waf, regionally replicated compute, managed NLP and translate. I sync the no sql stuff to bigquery for analytical usage. Cheap, and scales without any prob…

I've seen some NoSQL object/schemaless database turn into intractable balls of mud over time. Things that would be trivial in a relational DB with joins can turn into major engineering efforts and data corruption can be very hard to clean up.

Re: Relational databases aren’t dinosaurs, they’re sharks

#65

Post by vharuck »

squeaky-clean wrote: post #58
jhgb wrote: post #31

Earlier quoted context omitted.

> Our solution was to move the typing of this information to the application layer, and just store the arbitrary data in a NoSQL solution. This worked perfectly, and to my knowledge it's still working without a glitch. > This was before RDBMS solutions supported JSON, and if I were to do this again, I'd probably just continue to use MySQL, PostgreSQL or whatever, and store it as JSON in the database. Both solutions s…

I work with locations and addresses on an international system. They are not anywhere near as standardized as to allow this. And also at scale, you'll get things that seem like they shouldn't be addresses but are. One of our addresses that caused trouble is literally: "The yellow sign across the street from the Seven-Eleven at ". We have one address that's legally in two countries at the same time. One address is jus…

I've come around to this view, too. Also like names, the best solution is to avoid using them for analysis. Run them through an address geocoding service and store the coordinates next to the address. Use the original address for sending mail or filling out forms, and coordinates for analysis.

Unless cleaning, parsing, and geocoding addresses is one of your core business values, let somebody else do it. It's a lot of work that's never really finished. Find a good service, hand them your garbage addresses, and feel confident they'll do better than you could.

Re: Relational databases aren’t dinosaurs, they’re sharks

#66

Post by jhgb »

vharuck wrote: post #65
squeaky-clean wrote: post #58

Earlier quoted context omitted.

I work with locations and addresses on an international system. They are not anywhere near as standardized as to allow this. And also at scale, you'll get things that seem like they shouldn't be addresses but are. One of our addresses that caused trouble is literally: "The yellow sign across the street from the Seven-Eleven at ". We have one address that's legally in two countries at the same time. One address is jus…

I've come around to this view, too. Also like names, the best solution is to avoid using them for analysis. Run them through an address geocoding service and store the coordinates next to the address. Use the original address for sending mail or filling out forms, and coordinates for analysis. Unless cleaning, parsing, and geocoding addresses is one of your core business values, let somebody else do it. It's a lot of…

My idea is not in contradiction with your idea. In fact your idea is a part of my idea.

Re: Relational databases aren’t dinosaurs, they’re sharks

#67

Post by thatwasunusual »

the-dude wrote: post #42
thatwasunusual wrote: post #11

Earlier quoted context omitted.

The main problem was that we were unable to store the different types of addresses because of their different "layouts" in a "relational way."

I understand it might not be possible due to other contraints, but in smaller projects with international addresses I just used a text/memo field for the address and seperate field for the country. Addresses now can be entered the way the user prefers.

This works fine for _small_ systems. But with large data sets, where you actually want to act upon the data, it's impossible to have it stored as text.

One lazy solution would of course be to index that data into Elasticsearch (or similar solutions), but you will end up with database records that have missing or invalid indexed data at some point.

A NoSQL solution is far from perfect, but for us it was _a lot_ better than just storing it as text somewhere.

Re: Relational databases aren’t dinosaurs, they’re sharks

#68

Post by HWR_14 »

aenis wrote: post #26

Another consideration is that, at scale, no sql is way cheaper. I run a service with approx. 900k daily users, each generating about 210 object writes and reads that need to execute within 50ms, and I am running this on firestore for about $350 a month, incl. Elb, waf, regionally replicated compute, managed NLP and translate. I sync the no sql stuff to bigquery for analytical usage. Cheap, and scales without any prob…

And what do you think that would cost with an RDBMS? I see approx. 2000 writes and reads (presumably combined) a second. Depending on how big those objects were, it seems doable in a single computer running SQL.

Also, and this is neither here nor there, I thought Firebase hit a hard user limit of 1,000,000 daily users.

Re: Relational databases aren’t dinosaurs, they’re sharks

#69

Post by HWR_14 »

zepolen wrote: post #50
aenis wrote: post #26

Another consideration is that, at scale, no sql is way cheaper. I run a service with approx. 900k daily users, each generating about 210 object writes and reads that need to execute within 50ms, and I am running this on firestore for about $350 a month, incl. Elb, waf, regionally replicated compute, managed NLP and translate. I sync the no sql stuff to bigquery for analytical usage. Cheap, and scales without any prob…

That's more the benefits of cloud rather than nosql though. 200 writes/second is not even scale, a single instance would handle that fine, with beefy hardware you could do 100x that. You could even run it in cloud, for about $50/month, and as a bonus use the same instance to perform the analytics which will be real time. Not to mention enjoying much better data integrity.

Which cloud lets you rent a beefy SQL server for $50/mo? This is actually really useful info to me, as we're looking at needing to migrate to a new provider soon.

Re: Relational databases aren’t dinosaurs, they’re sharks

#70

Post by avianlyric »

jhgb wrote: post #60
squeaky-clean wrote: post #58

Earlier quoted context omitted.

I work with locations and addresses on an international system. They are not anywhere near as standardized as to allow this. And also at scale, you'll get things that seem like they shouldn't be addresses but are. One of our addresses that caused trouble is literally: "The yellow sign across the street from the Seven-Eleven at ". We have one address that's legally in two countries at the same time. One address is jus…

I understand these issues. That's precisely why a custom type seems appropriate -- it should be able to cover all the alternatives while not burdening you with the problem of storing a discriminated union as a set of disparate relations. Or, at the very least, if this approach still has some issues remaining, it should still have fewer issues than any other approach I can think of, because there's nothing you can't d…

> I understand these issues.

I seriously doubt you do. The closest you can get to “standard address format” is:

Address Line 1

Address Line 2

Address Line n

Postal code (which can be blank)

Postal Area (which can be blank)

Country

There is no way to build “first-class” support for addresses. Because theres no such thing as a valid or invalid address, only whether or not someone can find the correct location by reading the address. Of course, that person should be a local and intimately familiar with local address conventions. Conventions that will change how you describe flat numbers, street name, and address line ordering.

> And considering how often one needs to manipulate addresses in business settings,

Anyone who’s ever had to deal with real addresses would know this is the one thing you avoid doing with addresses. It’s pretty much impossible to correctly “manipulate” an address, because again, there’s no standard, it’s entirely dictated by local conventions, which can change street-to-street, city-to-city.

The best you can hope for when you’re forced to mutilate and address, is make the mutilation simple and obvious enough that the human actually delivering the post can un-mutilate it when they read it.

Post reply on HN