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

91–100 of 247 posts

Re: Things to know about databases

#91

Post by brightball »

randomdata wrote: post #60
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.

> 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…

I've honestly never understood why people have such a distaste for SQL. SQL and Linux/Unix have been the biggest constants of my entire programming career to this point (20ish years). I always know I can count on them.

Re: Things to know about databases

#92

Post by marcosdumay »

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.

> Is 'Not performance bound, and dot knowing the future shape of your data' a valid reason?

That's a very good reason for going with a RDBMS even if looks like it's not the clearest winner for your use case.

If you invert any of those conditions, it may become interesting to study alternatives.

Re: Things to know about databases

#93

Post by SulphurSmell »

Yhippa wrote: post #76
emerongi wrote: post #68

Earlier quoted context omitted.

> 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 poin…

>business logic is put into stored procedures

This is a double-edged sword. I have seen massive business logic baked into stored procedures...so much so, that the applications themselves are rather slim. If this stored procedure code is properly versioned and otherwise managed, this is not entirely bad. If the data model is sound, I don't worry that much...stored procs vs 100KLOC of Java code? I can tell you what is easier to migrate. The other side of it is that stored procedures can also serve as an API into the actual database. I built a system once (90's) where the developers never accessed the actual database tables directly. They always called stored procedures. The advantage here was that we could tune the snot out of the database without a code change/build/deploy. It also allowed the DBA some control over poorly written, runaway queries. YMMV. I think today I probably would try to keep the database as vanilla as possible.

Re: Things to know about databases

#94

Post by imachine1980_ »

jpdb wrote: post #89
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.

Hard disagree. The operational overhead of RDBMS and specifically their inherent reliance on a single primary node makes them, in my opinion, a bad place to start your architecture. I want to be able to treat the servers in my database tier as cattle instead of pets and RDBMSs don't fit this paradigm well. Either NoSQL or NewSQL databases are, in my opinion, a much better place to start. I feel like RDBMSs being the…

You can have multi tb postgree database, that are fast and usable whit limited number of cache layers for speed, but you probably don need it. mediums migrate from single postress in 2020.

Re: Things to know about databases

#95

Post by anonymousDan »

jpdb wrote: post #89
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.

Hard disagree. The operational overhead of RDBMS and specifically their inherent reliance on a single primary node makes them, in my opinion, a bad place to start your architecture. I want to be able to treat the servers in my database tier as cattle instead of pets and RDBMSs don't fit this paradigm well. Either NoSQL or NewSQL databases are, in my opinion, a much better place to start. I feel like RDBMSs being the…

What is your go to NewSQL database these days (and why) out of interest?

Re: Things to know about databases

#96

Post by marcosdumay »

spmurrayzzz wrote: post #50
dspillett wrote: post #40

Earlier quoted context omitted.

There are circumstances where you really don't know the shape of the data, especially when prototyping for proof of concept purposes, but usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. More often than not it is worth sometime thinking and planning to work out at least the core requirements in…

> usually not understanding the shape of your data is something that you should fix up-front as it indicates you don't actually understand the problem you are trying to solve. This is a good point and probably correct often enough, but I also think not understanding the entire problem you are solving is not only common, but in fact necessary to most early-stage velocity. There is need to iterate and adapt frequently,…

> This presumes that systems built on top of non-RDBMS are incapable of enforcing similar constraints.

Are you kidding? They never can.

The entire point of ditching the relational model is discarding data constraints and normalization.

Re: Things to know about databases

#97

Post by evilduck »

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 :).

About a decade ago I worked for an insurance company. It was an offshoot that was spun out of of another insurance company from another state, which itself was decades old. As best as I could infer from my vantage point, my expertise at the time, and the spare time I was willing to investigate the matter, the database schema and a good chunk of the core data tables were first created in the late-80s on a mainframe and had outlived 4 or 5 application rewrites and (at least) two SQL variant migrations. I'm hand-waving exact details because nobody from the original company or that time period was still around even prior to the corporate split and so there was nobody who could answer history questions in detail, but that's also a testament to how persistent data can be. There was one developer from the parent company they slapped with golden handcuffs who knew where most of the bodies were hid in that software stack that enabled decent productivity but even she was lacking a solid 15 years of first-hand experience of its inception. To the best of my knowledge that database is still in use today.

Databases in heavy use will not just outlast your application, they have a strong chance of outlasting your career and they very well may outlast you as a person.

Re: Things to know about databases

#98

Post by petergeoghegan »

jandrewrogers wrote: post #82
petergeoghegan wrote: post #43

Earlier quoted context omitted.

> Modern hardware has evolved to the point where B+trees will often offer disappointing results, so their use in indexing has dwindled with time. This is pure nonsense. B+Trees are used extensively and by default by 5 out of 5 of the top database systems, according to db-engines.com.

You don't actually address the point. If your database engine is an old design or your data is small by modern standards, then a B+tree will be one of the few indexing algorithms available and if the data is small it will probably work. Modern database kernels targeting modern hardware and storage densities typically aren't using B+trees and the reasons why are well-understood. No one with any sense is using a B+tree…

> You don't actually address the point.

You said that B-Trees "use in indexing has dwindled with time". This is demonstrably false.

> Back then I used them ubiquitously but I honestly don't remember the last time I've seen one in a new design.

Even if that was true (which it definitely isn't), why would anybody judge the commercial or scientific relevance of B-Trees by looking at what new systems do? There are very few new systems that are intended to be competitive as general purpose systems, which is where most of the market is.

You still haven't actually named a single example of a "modern database kernel" that exemplifies what you're talking about.

Re: Things to know about databases

#99

Post by hhjinks »

jpdb wrote: post #89
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.

Hard disagree. The operational overhead of RDBMS and specifically their inherent reliance on a single primary node makes them, in my opinion, a bad place to start your architecture. I want to be able to treat the servers in my database tier as cattle instead of pets and RDBMSs don't fit this paradigm well. Either NoSQL or NewSQL databases are, in my opinion, a much better place to start. I feel like RDBMSs being the…

Only once have I worked on a project where a document database did not completely gimp our ability to deliver the data that was required of us, and that was only because that data was regularly cloned to a relational database we could use for asynchronous tasks. As a project grows, I have, without fail, come to find that you need relations to efficiently deliver the data that new requirements demand.

Re: Things to know about databases

#100

Post by giovannibonetti »

nlnn wrote: post #71
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 a…

> 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.

Kimball's dimensional modelling helps a lot in cases like this, since probably there is a lot of repeated data in these columns.

Post reply on HN