Things to know about databases • Hacker News Board

Re: Things to know about databases

#221

Post by SulphurSmell » Tue, Jun 28, 2022, 2:48 PM UTC

tragomaskhalos wrote: post #203

SulphurSmell wrote: post #90

Earlier quoted context omitted.

>the database is almost always the main cause of any performance issues I would be careful with the term "cause". There is a symbiotic relationship between the application and the database. Or, if talking to a DBA...a database and its applications. Most databases can store any sets of arbitrary information...but how they are stored (read: structure) must take into account how the data is to be used. When the database…

As a corollary to this, there is the infamous: Client/front end guys: "you need to fix this query, it takes 10 minutes to run and the front end is timing out" DBA: "err, this query wants to return 50,000 rows" C/FEG: "yes, and?" DBA: "what in blue blazes is your UI, or a user, going to do with 50,000 rows?" C/FEG: "oh - we hadn't thought about that ..."

Ok, story time. Worked on a near-real-time system that processed millions of rows an hour. Devs routinely did a "select " and pulled 100K rows into an array on a local client. And they sorted it there*. The DBAs never looked into these because a)no resource issue, b)no perf issues and (most relevant) c) no one complained. One day this silliness was identified, and a quick meeting with the devs and DBA resulted. SQL 101. As if by magic, the client app was suddenly 3X more performant. The devs were applauded and there was much happiness in user-land. Only the SVP knew the truth...we left the sun shining on the developers. It was a rough week.

Re: Things to know about databases

#222

Post by randomdata » Tue, Jun 28, 2022, 2:55 PM UTC

SkeuomorphicBee wrote: post #219

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

I'm not sure a successor to SQL is even necessary, rather there would be benefit to some higher level interfaces provided by the RDMBS that compliment SQL to cut down on the egregious application boilerplate that becomes necessary to achieve common tasks using SQL directly.

SQL is essentially the assembly language of the database. That makes it very powerful, but sometimes you just want a language that gives you a "garbage collector" for free, while still being able to drop down to assembly when you need additional power.

There is no technical reason why an RDBMS can't support different modes for accessing data. We are just so used to putting that work into the application that it has become a hard sell to want to move it to the right place.

Re: Things to know about databases

#223

Post by vorpalhex » Tue, Jun 28, 2022, 2:59 PM UTC

AtNightWeCode wrote: post #32

vorpalhex wrote: post #26

Earlier quoted context omitted.

It's not that SQL is all that beastly, it's that most tutorials fail to explain the internals and basics and so you just see all these features and interfaces of the system and can't build a mental model of how the system works.

Well, SQL does come with liberties. I worked with expensive commercial software that destroys the performance of databases by doing everything from complicated ad hoc queries to massive amounts of point reads.

And a hammer will let you smash your own fingers.

All useful tools can be used incorrectly - and I agree SQL is one of the more frequently misused ones. I think a lot of that is that it's one of the more powerful tools.

Re: Things to know about databases

#224

Post by beckingz » Tue, Jun 28, 2022, 2:59 PM UTC

vladsanchez wrote: post #79

beckingz wrote: post #72

Earlier quoted context omitted.

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)

Oh that's unfortunate. No thank you.

Re: Things to know about databases

#225

Post by SulphurSmell » Tue, Jun 28, 2022, 3:20 PM UTC

Jemaclus wrote: post #173

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…

> And, if you can, please, limit the use of anything that can hold a NULL. I'm curious: what's the alternative to NULL? I'm struggling to think of a database where NULL wouldn't be super useful. It feels like NULL as a concept is almost required, but I think you're suggesting that's a faulty assumption. Would love to hear more about this.

Noticed I said "limit", and not "eliminate". The concept of NULLS in an RDBMS has been discussed and argued for decades. Three valued logic is generally not well understood, and as such, it's usually skipped over. Binary logic is easy...0 or 1. It's there, or not. OFF/ON. 3 valued logic introduces a third state: "unknown". It really means that there is no meaningful answer...not yet, anyway. In simple terms, NULL in RDBMS is dangerously often equated to 0 (zero) in numeric fields. Or "space" in character fields. Neither are true. On occasion, these might behave as such...but you are playing with fire here. What's worse, you can't compare a NULL to a NULL. NULL != NULL. Which is why you often see the "IS NULL" operator used in DML for such things. What it boils down to is that your applications need to pay careful attention when digging around (read: joining) tables with NULLS. Additional code logic is often required to ensure that things work the way you expect them to when NULLS are involved. Formal primary keys cannot NULL (this is enforced by the RDBMS) but it does not stop ad-hoc clever queries from including NULL columns as part of the "where..." clause. So what do? You can tell your DBA to ensure that all columns are NOT NULL. This really tightens things down, and makes some operations a bit more sane. However, if a column value is actually not known (yet!) then one is forced to populate it with data that may not be correct/relevant. These are often called "sentinal" values and can cause a mess of their own. There are use cases where a RDBMS schema with everything as NOT NULL can make sense. In my experience, databases whose data is never (directly) seen/input by actual people can work. When a human sees a field with "placeholder value" instead of just blank space..it is uncomfortable. My advice is to really understand why something might be NULL, and don't blindly add a mess of columns to a table as NULL because it's easy. Remember, that shit will live forever. Google around for "three valued logic" and start down the rabbit hole. Long-term (think: migrating from one RDBMS impl to another) you will absolutely find that NULLs don't behave the same. Various operations may or not be consistent from one to another...and this will break your apps. The key (haha) relationships modeled in your schema...if you strip all the unimportant stuff away...should avoid NULL. The flip side of this is to do a code scan (app side) and search for "is NULL" , "is NOT NULL" in the embedded SQL. Especially when there are a lot of "and ____ IS NOT NULL and ___IS NOT NULL" and so forth. This will indicate those parts of the database that are "hot spots" for NULL issues. I have seen SQL where 80% of the DML is taken up with NULL handling of some kind.

Re: Things to know about databases

#226

Post by SulphurSmell » Tue, Jun 28, 2022, 3:23 PM UTC

Aeolun wrote: post #214

alliao wrote: post #186

Earlier quoted context omitted.

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

I would be having words with the DBA about this. If there is a DBA. No DBA I would hire would allow this silliness. At least not in the actual application schema.

Re: Things to know about databases

#227

Post by SulphurSmell » Tue, Jun 28, 2022, 3:28 PM UTC

Aeolun wrote: post #176

richardw wrote: post #108

Earlier quoted context omitted.

Also an old timer. I’ve gone from mostly complex app and no DB logic to mostly heavily optimised DB and lots of procs. They protect the DB, make carefully crafted interfaces available and allow changes to the implementation. Except for eg infinitely scalable cloud data stores like Google’s, or for ML where it’s just massive data and you need a dumb-ish store of many GB of parquet.

> and allow changes to the implementation From my perspective this is a negative. You don’t want your queries to change after deployment. Procedures are also quite hard to type.

If I publish a stored proc as an API, it leaves me free (within reason) to alter the actual SQL supporting it. Like modern API design, as long as you don't remove functionality, then change it as required.

Re: Things to know about databases

#228

Post by SulphurSmell » Tue, Jun 28, 2022, 3:38 PM UTC

fipar wrote: post #183

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…

> As an old bastard, I would pass on one thing: Realize that any reasonably used database will likely outlast the applications leveraging it. I’ve been working with and on databases for a long, long time, and I’ve even written about things I think people should know about if they want to do this, yet I never came up with such great insight. This is so true it should be engraved somewhere. Hats off!

Thanks. Scar tissue sometimes breeds insight. In further conversation on this phenomenon, I would argue that "long lived databases" are not so as result of brilliant design. Rather, it happens because the database itself is neglected and largely misunderstood, and gets less investment. And they live on and on...managers come and go...no investment. And then, years later, some poor bastard is stuck with a hideous mess that can't go anywhere. Don't let this happen to you.

Re: Things to know about databases

#229

Post by Diggsey » Tue, Jun 28, 2022, 3:55 PM UTC

kerblang wrote: post #88

Diggsey wrote: post #56

Earlier quoted context omitted.

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.

Not sure what you're claiming here... Repeatable read isolation creates read locks so that other transactions cannot write to those records. Of course our own transaction has to first wait for outstanding writes to those records to commit before starting. Best as I know the goal is not to prevent one's own transaction from updating the records we read; the read locks will just get upgraded to write locks.

> Repeatable read isolation creates read locks so that other transactions cannot write to those records.

No it doesn't, that's just one possible implementation strategy. Postgres for example does not do this.

> Best as I know the goal is not to prevent one's own transaction from updating the records we read;

I'm talking about updates from other transactions. In postgres with REPEATABLE READ, the following transaction can be executed concurrently by two clients:

    BEGIN
    SELECT bar FROM foo WHERE id = 1;  -- Returns 0
    UPDATE foo SET bar = bar + 1 WHERE id = 1;
    COMMIT

Both clients can see a value of "0" from the first SELECT, but after both COMMIT, the value of "bar" will be "2". ie. the "read" of "bar" in "bar = bar + 1" for one of the transactions does not use snapshot isolation.

Re: Things to know about databases

#230

Post by eurasiantiger » Tue, Jun 28, 2022, 4:07 PM UTC

drpotato wrote: post #131

eurasiantiger wrote: post #121

Earlier quoted context omitted.

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.

Have you looked at PostGraphile? It’s doesn’t have n + 1 or over-fetching issues.

I have, and I would recommend it, if it generated a Postgre schema from a GraphQL schema and not the other way around.

The advantages of GraphQL are its integration of backend and frontend workflows, and that won’t happen with PostGraphile: a frontend needing a schema change needs to go through the backend instead of the backend extending to meet the frontend in the middle.