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

Things to know about databases

architecturenotes.co

Post reply on HN
Print view View original

71–80 of 247 posts

Re: Things to know about databases

#71

Post by nlnn »

tiffanyh wrote: post #23

#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you should rethink why. Because unless you're at massive scale (which still doesn't justify it), choosing something else is rarely the right decision.

I've found it's not just scale, but also down to query patterns across the data being stored.

I'm with you on using an RDBMS for almost everything, but worked on quite a few projects where alternatives were needed.

One involved a lot of analytics queries (aggregations, filters, grouping etc.) on ~100-200GB of data. No matter what we tried, we couldn't get enough performance from Postgres (column-based DBs / Parquet alternatives gave us 100x speedups for many queries).

Another was for storing ~100M rows of data in a table with ~70 columns or so of largely text based data. Workload was predominantly random reads of subsets of 1M rows and ~20 columns at a time. Performance was also very poor in Postgres/MySQL. We ended up using a key/value store, heavily compressing everything before storing, and got a 30x speedup compared to using an RDBMS using a far smaller instance host size.

I wouldn't call either of them massive scale, more just data with very specific query needs.

Re: Things to know about databases

#72

Post by beckingz »

SulphurSmell wrote: post #61

This article is informative. I have found that databases in general tend to be less sexy than the front-end apps...especially with the recent cohort of devs. As an old bastard, I would pass on one thing: Realize that any reasonably used database will likely outlast the applications leveraging it. This is especially true the bigger it gets, and the longer it stays in production. That said, if you are influencing the d…

I heard about Flywaydb today, which appears to be an open source database versioning tool. Pretty interesting! https://flywaydb.org/

Re: Things to know about databases

#74

Post by adrianmonk »

w0m wrote: post #37
tiffanyh wrote: post #23

#1 thing you should know, RDBMS can solve pretty much every data storage/retrieval problem you have. If you're choosing something other than an RDBMS - you should rethink why. Because unless you're at massive scale (which still doesn't justify it), choosing something else is rarely the right decision.

Is 'Not performance bound, and dot knowing the future shape of your data' a valid reason? Less overhead on initial rollout to just Toss it up there. > choosing something else is rarely the right decision I think this is a little bit of a 'We always did it this way' statement.

> future shape of your data

Contrary to what people seem to assume, you actually can change the schema of a database and migrate the existing data to the new schema. There's a learning curve, but it's doable.

If you go schema-less, you run into another problem: not knowing the past shape of your data. When you try to load old records (from previous years), you may find that they don't look like the ones you wrote recently. And, if your code was changed, it may fail to handle them.

This makes it hard to safely change code that handles stored data. You can avoid changing that code, you can accept breakage, or you can do a deep-dive research project before making a change.

If you have a schema, you have a contract about what the data looks like, and you can have guarantees that it follows that contract.

Re: Things to know about databases

#75

Post by packetlost »

emerongi wrote: post #52
w0m wrote: post #37

Earlier quoted context omitted.

Is 'Not performance bound, and dot knowing the future shape of your data' a valid reason? Less overhead on initial rollout to just Toss it up there. > choosing something else is rarely the right decision I think this is a little bit of a 'We always did it this way' statement.

With Postgres, you can always just have a JSONB column for data whose shape you're unsure of. Personally, I'd rather start with Postgres and dump data into there and retain the powers of RDBMS for the future, rather than the other way around and end up finding out that I really would like to have features that come out of the box with relational databases. I think a valid reason for not choosing a relational database…

Postgres' JSON implementation perfectly adheres to the JSON spec, which actually sucks if you need to support things like NaNs, Inf, etc. It's a good option, but it doesn't work for all datasets.

Re: Things to know about databases

#76

Post by Yhippa »

emerongi wrote: post #68
SulphurSmell wrote: post #61

This article is informative. I have found that databases in general tend to be less sexy than the front-end apps...especially with the recent cohort of devs. As an old bastard, I would pass on one thing: Realize that any reasonably used database will likely outlast the applications leveraging it. This is especially true the bigger it gets, and the longer it stays in production. That said, if you are influencing the d…

> Realize that any reasonably used database will likely outlast the applications leveraging it. I love this statement. It's true too, having seen a decades-old database that needed to be converted to Postgres. The old application was going to be thrown away, but the data was still relevant :).

I think this is and will continue to be a common use case. I'm very thankful for these applications that the data was still stuck in a crusty old relational database for me to work on top of as I built a new application.

It's going to be interesting when this same problem occurs years from now when people are trying to reverse schemas from NoSQL databases or if they become difficult to extract.

The only sticking point is when business logic is put into stored procedures. On one hand if you're building an app on top of it, there's a temptation to extract and optimize that logic in your new back-end. On the other hand, it is kind of nice to even have it at all should the legacy app go poof.

Re: Things to know about databases

#77

Post by Yhippa »

paulryanrogers wrote: post #70
randomdata wrote: post #60

Earlier quoted context omitted.

> RDBMS can solve pretty much every data storage/retrieval problem you have. Except the most important problem: A pleasant API. Which is, no doubt, why 95% of those considering something other than an RDBMS are making such considerations. RDBMS can have pleasant APIs. It is not a fundamental limitation. We have built layers upon layers upon layers of abstraction over popular RDBMSes to provide nice APIs and they work…

What is a pleasant API? For what kind of data?

I wonder if parent post meant something other than SQL?

Re: Things to know about databases

#78

Post by mslot »

emerongi wrote: post #52
w0m wrote: post #37

Earlier quoted context omitted.

Is 'Not performance bound, and dot knowing the future shape of your data' a valid reason? Less overhead on initial rollout to just Toss it up there. > choosing something else is rarely the right decision I think this is a little bit of a 'We always did it this way' statement.

With Postgres, you can always just have a JSONB column for data whose shape you're unsure of. Personally, I'd rather start with Postgres and dump data into there and retain the powers of RDBMS for the future, rather than the other way around and end up finding out that I really would like to have features that come out of the box with relational databases. I think a valid reason for not choosing a relational database…

Most $100B+ companies (e.g. Google, Meta, Amazon) were built primarily using relational databases for the first 10-15 years.

Re: Things to know about databases

#79

Post by vladsanchez »

beckingz wrote: post #72
SulphurSmell wrote: post #61

This article is informative. I have found that databases in general tend to be less sexy than the front-end apps...especially with the recent cohort of devs. As an old bastard, I would pass on one thing: Realize that any reasonably used database will likely outlast the applications leveraging it. This is especially true the bigger it gets, and the longer it stays in production. That said, if you are influencing the d…

I heard about Flywaydb today, which appears to be an open source database versioning tool. Pretty interesting! https://flywaydb.org/

Pretty open-source, until you need "premium" features like "rollback" :/ (headwall)

Re: Things to know about databases

#80

Post by donatj »

I still think about my first job out of college. Shopping cart application, we would add indexes exclusively when there was a problem rather than proactively based on expected usage patterns. It's genuinely a testament to MySQL that we got as far as we did without knowing anything about what we were doing.

One of my most popular StackOverflow questions to this day is about how to handle one million rows in a single MySQL table (shudder).

The product I work on now collects more rows than that a day in a number of tables.

Post reply on HN