Relational databases aren’t dinosaurs, they’re sharks • Hacker News Board

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

#31

Post by jhgb » Sat, Oct 09, 2021, 10:14 AM UTC

thatwasunusual wrote: post #8

Great article, and I this particularly brought back memories for me: > [...] in some instances you might work with vast quantities of data, or deal with transactional systems that just don’t easily fit the operational limitations of relational databases. And in those cases, you should consider moving some, or all, of your data into a non-relational database. I've worked with a big application that utilised this appro…

> 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 sound like anti-patterns to me. I was under the impression that the correct solution in an RDBMS would be the introduction of a custom type for the address that would cover all of your use cases, and all the associated operations, indices, etc. (in PostgreSQL, maybe even with PostGIS support for spatial operations and spatial indices). That not only keeps your logic, data integrity checks etc. on the server but also doesn't need text munging for every operation.

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

#32

Post by williamdclt » Sat, Oct 09, 2021, 10:20 AM UTC

ransom1538 wrote: post #22

cpursley wrote: post #20

Earlier quoted context omitted.

This is exactly what I do - store location components as jsonb in Postgres

IMHO Blobs usually turn into a mess though. Some VP will ask "what weekday do users comment the most". With blobs this is drama. You end up parsing all these blobs and creating relational tables anyway. Sure you could do metrics separately -- but good luck predicting what stats you will want in the future. Need an admin portal? With blobs you are coding all these custom admin pages. With mysql, I love when new people…

jsonb isn't a blob, JSON attributes are queryable and indexable like any column (in Postgres at least). And as somebody else says, read models exist for analytics use-cases. I wouldn't (at some scale) use the same DB for analytics as for production use anyway, it would require crazy indexes on the production DB and you'd have to take analytics into account for any schema change. Not to mention that anyway, quite often the prod DB schema requires domain knowledge to query and interpret correctly: read models can be built to abstract this domain knowledge.

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

#33

Post by tomcam » Sat, Oct 09, 2021, 10:21 AM UTC

A far more nuanced article than the clickbait (not a problem to me) title implies. Also the clearest definition of ACID I can recall.

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

#34

Post by Rapzid » Sat, Oct 09, 2021, 10:21 AM UTC

tuatoru wrote: post #12

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…

What ORMs have taught me: Just Learn SQL https://wozniak.ca/blog/2014/08/03/1/index.html

I like how the very first paragraph walks back the title.

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

#35

Post by jhgb » Sat, Oct 09, 2021, 10:21 AM UTC

cpursley wrote: post #20

thatwasunusual wrote: post #8

Great article, and I this particularly brought back memories for me: > [...] in some instances you might work with vast quantities of data, or deal with transactional systems that just don’t easily fit the operational limitations of relational databases. And in those cases, you should consider moving some, or all, of your data into a non-relational database. I've worked with a big application that utilised this appro…

This is exactly what I do - store location components as jsonb in Postgres

Why can't you make a proper, first-class supported custom type in your database instead?

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

#36

Post by willvarfar » Sat, Oct 09, 2021, 10:22 AM UTC

ransom1538 wrote: post #22

cpursley wrote: post #20

Earlier quoted context omitted.

This is exactly what I do - store location components as jsonb in Postgres

IMHO Blobs usually turn into a mess though. Some VP will ask "what weekday do users comment the most". With blobs this is drama. You end up parsing all these blobs and creating relational tables anyway. Sure you could do metrics separately -- but good luck predicting what stats you will want in the future. Need an admin portal? With blobs you are coding all these custom admin pages. With mysql, I love when new people…

I often put json blobs into MySQL tables. Modern MySQL has support for accessing the fields in the json from normal sql. You can also create computed columns that deref the json, and even index it.

Other rdbms often have even better json support.

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

#37

Post by jhgb » Sat, Oct 09, 2021, 10:29 AM UTC

chrisandchris wrote: post #14

pier25 wrote: post #2

The term NoSQL is meaningless. It only means that a database is not SQL (d'oh) but people (such as the author of the article) use it as if it meant anything beyond that. Talking about "NoSQL tradeoffs" implies all non SQL databases share similar features, operational models, use cases, etc, which is simply not true. For example, DynamoDB, Mongo, and Fauna have absolutely nothing in common.

> The term NoSQL is meaningless. It only means that a database is not SQL (d'oh) but people (such as the author of the article) use it as if it meant anything beyond that. […] Except that it does not. It does mean „Not Only SQL“, and not „no SQL“.

That might depend on who you're asking: http://www.strozzi.it/cgi-bin/CSA/tw7/I/en_US/nosql/Home%20P...

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

#38

Post by Rapzid » Sat, Oct 09, 2021, 10:30 AM UTC

jeremyjh wrote: post #25

mastrsushi wrote: post #16

> Relational DBs are Sharks > OOP is the Roman numerals of paradigms > C is a PDP assembler that thinks it’s a compiler Yup, everything popular is evil and bad. Run away to your ivory towers.

You might have missed the fact that sharks are awesome.

Facts. It's also true they kill nearly six times less people than vending machines.

Sharks are healthier than vending machines.

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

#39

Post by hnfong » Sat, Oct 09, 2021, 10:34 AM UTC

thatwasunusual wrote: post #11

jseban wrote: post #10

Earlier quoted context omitted.

I think your solution here was to move the typing to the application layer, which kind of makes sense because that's where you know the locale. But why also move the data to NoSQL, I don't see what that would add. If you had already removed the typing from the relation DB I think that would have worked as well, or am I missing something?

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 don't quite understand "was before RDBMS solutions supported JSON "... did (for example) MySQL not support storing JSONs as binary blobs or text before?

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

#40

Post by cageface » Sat, Oct 09, 2021, 10:37 AM UTC

mrweasel wrote: post #21

cageface wrote: post #17

Earlier quoted context omitted.

Building SQL queries by gluing strings together is tedious and error prone. So you write a lot of helper functions to build queries for you and pretty soon you've invented your own crappy ORM. Why not save a lot of time and bugs and use a battle tested ORM and drill down to SQL for the queries that really matter instead?

Or you rewrite you queries so that you can use prepared statements. You’re right that we should avoid gluing strings together, but in most cases we can use prepared statements. There’s also a middleground where developers learn to use the ORM better. I’ve seem people get terrible performance using the Django ORM, but after a rewrite, redesigning the queries and using the more advanced features performance would impro…

Prepared statements are a good tool to have in your arsenal but for the majority of the queries you need an ORM will be fine. It doesn't absolve you of the need to understand SQL but it does save a huge amount of time and generally results in easier to follow code.