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

121–130 of 247 posts

Re: Things to know about databases

#121

Post by eurasiantiger »

N_A_T_E wrote: post #115
dgb23 wrote: post #109

Earlier quoted context omitted.

With SQL you kind of have two options/extremes that are unpleasant in their own way. You either model things in a very domain specific and classic fashion. Here you get the benefit of being quite declarative and ad-hoc queries are natural. Also your schema is stronger, as in it can catch more misuse by default. But this kind of schema tends to have _logical_ repetition and is so specific that change/evolution is quit…

Isn't that what the ActiveRecord pattern is supposed to be? (Something something n+1's and over fetching data.)

If only we had a generic GraphQL resolver for entity-based SQL schemas.

Oh wait, we do have Prisma. And it suffers from those same issues.

Re: Things to know about databases

#122

Post by wombatpm »

lysecret wrote: post #30
yla92 wrote: post #3

Great post. Also highly recommend Designing Data-Intensive Applications by Martin Kleppmann ( https://www.amazon.com/Designing-Data-Intensive-Applications... ). The sections on "Storage and Retrieval", "Replication", "Partitioning" and "Transactions" really opened up my eyes!

Absolutely loved the book. Can someone recommend similar books?

Database Design for Mere Mortals by Ray Hernandez

Re: Things to know about databases

#123

Post by Merad »

> a dirty read occurs when you perform a read, and another transaction updates the same row but doesn't commit the work, you perform another read, and you can access the uncommitted (dirty) value

It's even worse than this with MS SQL Server. When using the READ UNCOMMITTED isolation level it's actually possible to read corrupted data, e.g. you might read a string while it's being updated, so the result row you get contains a mix of the old value and new value of the column. SQL Server essentially does the "we got a badass over here" Neil deGrasse Tyson meme and throws data at you as fast as it can. Unfortunately I've worked on several projects where someone apparently thought that READ UNCOMMITTED was a magic "go fast" button for SQL and used it all throughout the app.

Re: Things to know about databases

#124

Post by SulphurSmell »

evilduck wrote: post #97
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 :).

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

Are you me? LOL

Re: Things to know about databases

#125

Post by jandrewrogers »

petergeoghegan wrote: post #98
jandrewrogers wrote: post #82

Earlier quoted context omitted.

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

You are understating the limitations of B+trees for real workloads. A common and growing problem is the lack of online indexing that scales, the particularly data model doesn't matter that much. Index construction throughput and scaling has been a serious problem at some pretty boring companies I've done work for.

Use of B+trees in new database kernels has definitely diminished. I'm not counting the installed base of SQLite etc. Ubiquity doesn't make something the pinnacle of technology -- just as often it means "legacy installed base". I still use PostgreSQL a lot and mod it when I need to but I am not under any illusions about its limitations.

A "modern" database kernel that can efficiently use modern hardware is going to be a thread-per-core architecture with all I/O and execution scheduling done in user space, and the ability to operate on modern storage densities found on database servers, which can exceed a petabyte of direct-attached storage. The implications of storage density and its interaction with indexing drive most of the real changes in the way database kernels are designed. You can find elements of this in open source, but mostly in big data platforms rather than proper database engines.

That said, no one builds new high-end databases for retail anymore, the economics don't make sense. All the money moved to more specialized implementations that cater to smaller audiences where you don't need to advertise. The kernels are fully general, and widely reused, but the interfaces and surrounding bits are purpose-built for particular workloads. Hell, my old storage engines are still used under license by that lot. The days of database billboards on the 101 are an anachronism.

Re: Things to know about databases

#126

Post by jandrewrogers »

heisjustsosmart wrote: post #102
petergeoghegan wrote: post #98

Earlier quoted context omitted.

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

Read this guy's past posts, you will save yourself a lot of time. He does a lot of this sort of thing.

...says the person who is confidently oblivious to the problems of mixing virtual methods and direct paged memory.

Re: Things to know about databases

#127

Post by jstarfish »

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.

I wholly agree with you, but I'll say this:

When it comes to prototyping, I'm not going to fuck with something like Java-- I'm going to reach for Python. If I don't know what I'm doing to begin with, I don't need something telling me all the ways in which I'm doing it wrong.

Same goes for SQL/NoSQL. I loosely know what I need to model and may revise it arbitrarily. SQL does not lend itself to this. NoSQL was designed for it.

NoSQL is the "fuck you dad you can't tell me what to do" of the database world. SQL is your annoying dad that tries to institute curfews and won't let you go to underage drinking parties. In the end, it's the latter you're going to be calling from a holding cell, but there's a lot more fun you can have with the former.

Re: Things to know about databases

#128

Post by ElectricalUnion »

eurasiantiger wrote: post #120
brightball wrote: post #91

Earlier quoted context omitted.

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.

Can you write an SQL query to return arbitrary JSON? Returning queried data in a nested form is a must nowadays.

> Can you write an SQL query to return arbitrary JSON? Returning queried data in a nested form is a must nowadays.

Yes. https://www.sqlite.org/json1.html

Re: Things to know about databases

#129

Post by nequo »

bakuninsbart wrote: post #112
brightball wrote: post #91

Earlier quoted context omitted.

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 great at what it is designed to do, and absolutely horrible for anything else. Sometimes I have had to use tools that only allow (a subset of) SQL for querying data. (Especially BI) Doing regex date validation in a json derived from a string in an sql dialect without function-definitions is horrendous. These kinds of "why the f do i have to use sql for this "-moments happened surprisingly often to me working a…

> simple things in Pandas or R, like changing column values based on multiple field value conditions, and it is a struggle requiring at least 17 brackets

With dplyr, wouldn’t this be mutate(x = case_when(… ~ y, TRUE ~ x)) or the same but with ifelse?

Re: Things to know about databases

#130

Post by mmcnl »

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…

"Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious." -- Fred Brooks, The Mythical Man Month (1975)
Post reply on HN