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

51–60 of 247 posts

Re: Things to know about databases

#51

Post by xmprt »

hashmash wrote: post #41
jandrewrogers wrote: post #38

> "Scale of data often works against you, and balanced trees are the first tool in your arsenal against it." An ironic caveat to this is that balanced trees don't scale well, only offering good performance across a relatively narrow range of data size. This is a side-effect of being "balanced", which necessarily limits both compactness and concurrency. That said, concurrent B+trees are an absolute classic and provide…

What kinds of indexing structures are used instead, and how do they differ from B+trees? Do you have examples of which relational databases have replaced B+tree indexes?

I know Clickhouse uses MergeTrees which are different from B+trees. However it can't really be used as an RDBMS. It's especially bad at point reads.

https://en.wikipedia.org/wiki/Log-structured_merge-tree

Re: Things to know about databases

#52

Post by emerongi »

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.

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 is if your business plan requires that you grow to be a $100B+ company with hundreds of millions of users. Otherwise, you will probably be fine with RDBMS, even if it will require some optimizing in the future.

Re: Things to know about databases

#53

Post by [deleted] »

spmurrayzzz wrote: post #42
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.

The premise here (I think, correct me if I'm mistaken) is that there are net-negative tradeoffs to using nosql/non-rdbms. If that assumption is true, then it follows that the same argument used in the last statement also applies— that if you're not at massive scale, then its likely the aforementioned tradeoff of not using RDBMS is likely de minimis. (This assumes that the tradeoffs are of the magnitude that they only…

[deleted]

Re: Things to know about databases

#54

Post by dspillett »

larrik wrote: post #28
AtNightWeCode wrote: post #21

Not sure how to use these recommendations in practice though even if the info is somewhat correct. SQL is a beast of tech and it is used because of battle history and since there is simply no other viable tech replacing it when it comes to transactions and aggregated queries. Indexes are a nightmare to get right. Often performance optimizations of SQL databases include removing indexes as much as adding indexes.

Indexes aren't a "make my DB faster" magic wand. They have benefits and costs. If you are seeing performance gains from removing indexes, then I'm assuming your workload is very heavy on writes/updates compared to reads.

Too many indexes can cause significant performance problems if RAM is short. If the indexes are actually used (rather than sitting idle on disk because other indexes are better choices for all your applications' typical queries) then they will “compete” for memory potentially causing a cache thrashing situation.

But yes, the issue with too many indexes is more often that they harm write performance.

A related issue is indexes that are too wide, either covering many columns or “including” them. As well as eating disk space they also eat extra memory (and potentially cause extra IO load) when used (less rows per page, so more pages loaded into RAM for the same query).

Both problems together, too many indexes many of which are too wide, usually comes from blindly accepting recommendations from automated tools (particularly when they are right that there is a problem, and it is a problem that a given index may solve, but fixing the queries so existing indexes are useful could have a much greater effect than adding the indexes).

Re: Things to know about databases

#55

Post by corpMaverick »

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.

Similarly. People don't use Object Modeling/Entity relation-ship diagrams anymore.

Every day, I see people struggling with problems that would be easy to understand if you had one. You don't even need to have an RDBMs. They are good just to model how things are related to each other.

Re: Things to know about databases

#56

Post by Diggsey »

googletron wrote: post #19
jwr wrote: post #18

Some of the explanations are questionable: I think they were overly simplified, and while I applaud the goal, some things just aren't that simple. I highly recommend reading https://jepsen.io/consistency and clicking on each model on the map. This is the best resource I found so far for understanding databases, especially distributed ones.

I would love the feedback, what was questionable? striking the balance is tough. jepsen's content is great.

One thing that can be surprising is that for "REPEATABLE READ", not all "reads" are actually repeatable.

There are at least two ways (that I'm aware of) that this can be violated. For example, if you run an update statement like this:

    UPDATE foo SET bar = bar + 1
Then the read of "bar" will always use the latest value, which may be different from the value other statements in the same transaction saw.

Re: Things to know about databases

#57

Post by aoms »

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.

You're speaking my language. After more than 20 years of custom software dev, this statement has so much merit.

Re: Things to know about databases

#58

Post by bcrosby95 »

spmurrayzzz wrote: post #42
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.

The premise here (I think, correct me if I'm mistaken) is that there are net-negative tradeoffs to using nosql/non-rdbms. If that assumption is true, then it follows that the same argument used in the last statement also applies— that if you're not at massive scale, then its likely the aforementioned tradeoff of not using RDBMS is likely de minimis. (This assumes that the tradeoffs are of the magnitude that they only…

> (This assumes that the tradeoffs are of the magnitude that they only manifest impact at scale, hard to address that without concrete examples though)

The tradeoff is usually flexibility. You run into flexibility problems anytime requirements change. Scale doesn't factor in.

Re: Things to know about databases

#59

Post by jrm4 »

charcircuit wrote: post #46
jrm4 wrote: post #24

To go big picture; I'm kind of glad databases are largely like cars in this respect, in ways that other software tooling isn't. Which is to say they're frequently good enough such that the human working with them on whatever level can safely not know a lot of these details and get a LOT done. Kudos to whoever deserves them here.

Isn't that true for almost all software? You only need to know the implementation of a small subset of parts. I would say databases are worse since you need to know how they are implemented else you will start making O(rows) queries or doing other inefficient stuff.

Going broadly (which is all I can do because I teach this stuff and don't build in depth) -- "the database" is the part I can most easily "abstract" away as if it were walled off?

As opposed to aspirationally discrete classifications that end up being porous, e.g. MVC, "Object Oriented" etc.

Re: Things to know about databases

#60

Post by randomdata »

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 well enough. But those additional layers come with a lot of added complexity and undesirable dependencies that most would prefer to see live in the DBMS itself instead.

At least among the RDBMSes we've heard of, there does not seem to be much interest in improving the APIs at the service level to make them more compelling to use natively like alternative offerings outside of the relational space have done.

Post reply on HN