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

211–220 of 247 posts

Re: Things to know about databases

#211

Post by HelloNurse »

VirusNewbie wrote: post #87
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.

It is very frustrating to work with engineers who don't understand the nuances of RDBMS and assume they can solve all the things. The small company I work for has 3B rows. We have a high write volume. Can you use an RDBMs database to solve this? Sure, but it would be a terrible waste of engineering effort.

A "high write volume" requires fast disks, and billions of rows require large disks. Two simple requirements that are the same for any relational or less relational database.

What's interesting is query performance, and a RDBMS supports explicit control over indexing (usually including analyzing execution plans to find out which queries are going to work well). Where do you see "a terrible waste of engineering effort"?

Re: Things to know about databases

#212

Post by motogpjimbo »

tomnipotent wrote: post #196
galaxyLogic wrote: post #194

Earlier quoted context omitted.

The basic idea is the same as in the article: Data is more important that apps and stored procedures really are just app-code. Stored procedures have the downside that they often work only in one vendor's database.

> Stored procedures have the downside that they often work only in one vendor's database. Doesn't really matter, because no one is writing database-agnostic SQL (unless that's part of your product). Any non-trivial implementation is going to require the use of proprietary SQL.

It has always amused me when developers try to make their application's database layer vendor-agnostic. Not only does that restrict you to the most vanilla dialect of SQL imaginable, it's also a pointless exercise generally because it is orders of magnitude more likely that your app will be replaced and the database kept than the other way round.

Re: Things to know about databases

#213

Post by motogpjimbo »

zippergz wrote: post #167
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…

This is one reason that ORMs which wish to own the database schema make me uncomfortable. How much fun is that schema going to be years down the road when that ORM is out of fashion, but you still need an app working with that data? Some are better than others at doing things in a sane way, of course.

Or even worse, when the ORM is written in a programming language your organisation no longer uses and is part of codebase that is no longer under development.

Re: Things to know about databases

#214

Post by Aeolun »

alliao wrote: post #186
Aeolun wrote: post #175

Earlier quoted context omitted.

This man has clearly never seen our database schema. Show me either flowcharts and/or tables, it doesn’t matter, I’ll continue to be mystified.

our devs created smash hits such as NounFunction NounFunctionTemporal NounCollectionFunction FunctionNoun_Function FunctionNoun_FunctionItem Noun_Collection Noun_Collection_Function Noun_Collection_FunctionItem i've taken out duplicating combinations where different teams wanted same things over time, but never looked up what others had done before and decided to spin their own...

Don’t forget

Noun_Collection_Function_20200406 Noun_Collection_Function_20200320_Backup

And

Noun_Collection_Function_ForJim

Re: Things to know about databases

#215

Post by citrin_ru »

tomnipotent wrote: post #196
galaxyLogic wrote: post #194

Earlier quoted context omitted.

The basic idea is the same as in the article: Data is more important that apps and stored procedures really are just app-code. Stored procedures have the downside that they often work only in one vendor's database.

> Stored procedures have the downside that they often work only in one vendor's database. Doesn't really matter, because no one is writing database-agnostic SQL (unless that's part of your product). Any non-trivial implementation is going to require the use of proprietary SQL.

Even if you think you use database-agnostic SQL unless you actually run tests with multiple DBMS you cannot be sure. One can write code which depends on some DB specific behavior unintentionally, Hyrum's Law [1] works for databases too.

[1] https://www.hyrumslaw.com/

Re: Things to know about databases

#216

Post by zvrba »

tpetry wrote: post #206
zvrba wrote: post #199

Earlier quoted context omitted.

I love the data model of RDBMS / SQL. I hate SQL _the language_. Verbose, irregular, attempting to mimic English and thus making it unintuitive and next to impossible to remember. 80% of my lookups into reference documentation is about syntax for things I don't use that often. Examples: GRANT TO, REVOKE FROM, DENY TO (oh yes, what's the difference between REVOKE and DENY? and did you know that you can REVOKE a DENY?)…

All of your examples are vendor specific extensions to SQL. Nothing of that is part of the standard. So you are effectively hating a specific implementation which is not that logical.

Yes, ANSI syntax is surely composable or dynamic: EXTRACT(YEAR FROM DATE '1998-03-07')

Re: Things to know about databases

#217

Post by kasey_junk »

motogpjimbo wrote: post #212
tomnipotent wrote: post #196

Earlier quoted context omitted.

> Stored procedures have the downside that they often work only in one vendor's database. Doesn't really matter, because no one is writing database-agnostic SQL (unless that's part of your product). Any non-trivial implementation is going to require the use of proprietary SQL.

It has always amused me when developers try to make their application's database layer vendor-agnostic. Not only does that restrict you to the most vanilla dialect of SQL imaginable, it's also a pointless exercise generally because it is orders of magnitude more likely that your app will be replaced and the database kept than the other way round.

It was much more important when software products were sold to companies that ran their own databases. You didn’t have the luxury of choosing which database your app would run on so half the job of a software dev was making running it on many different db so you didn’t lose the sale. It’s one of the many reasons saas became the dominant way to sell software.

Once you control the deployment environment it largely makes very little sense to spend much time on db agnosticism. But that’s also true of many other things that have left vestigial forms in our software today such as IOC containers, pluggable log libraries, configuration files instead of configuration code, etc.

Re: Things to know about databases

#218

Post by analyst74 »

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?

Databases designed to query large amounts of data tend to rely on partition key to allow parallel workloads (i.e. map reduce). And this partition data is stored like hash index.

Big query would be one example.

Re: Things to know about databases

#219

Post by SkeuomorphicBee »

brightball wrote: post #91
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…

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.

SQL is also a big constant of my programming career, I know it can do everything I need, but sometimes it is frustrating how its limitations make some simple things complicated.

For example, one of the biggest gripes I have with SQL is the fact that the table is the only output format, it doesn't allow outputting data with different cardinalities in a single query. The internal relational model of a RDBMS is extremely rich and powerful, but when extracting that data using SQL you have to leave all the richness behind and flatten the data into a simple dumb table. So as result we have to do much back and forth with separate queries based on results of previous ones (or even ugly hacks like concatenate higher cardinality data into a single field to then re-separate it in the application). A suitable successor for SQL should have a way to output a subset of the relational model with the relational part intact.

Re: Things to know about databases

#220

Post by ddorian43 »

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…

You never post anything constructive. Just that everything in open source sucks while you do fancy algorithms in your basement with spatial data at the boring facility. Anytime anybody asks for more you never reply with something constructive or link to a paper/algorithm or something.

The maximum you've told is "yeah do what scylladb does but you will still suck".

It just feels as advertisement and doesn't really add anything to the discussion I believe. All your comments are the same in all database threads.

endrant

Post reply on HN