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

231–240 of 247 posts

Re: Things to know about databases

#231

Post by Jemaclus »

SulphurSmell wrote: post #225
Jemaclus wrote: post #173

Earlier quoted context omitted.

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

Ah, that makes a lot of sense. Thank you for the explanation!

Re: Things to know about databases

#232

Post by Jemaclus »

goto11 wrote: post #201
Jemaclus wrote: post #173

Earlier quoted context omitted.

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

The article probably means: Define anything as non-nullable which can be non-nullable. Unfortunately SQL defaults to nullable, so there is a tendency to define too many columns as nullable. Normalization can also reduce the need for nullable columns in base tables (but you will get them back if you perform an outer join, so it is not a panacea). But if a columns truly has unknown values, NULL's are the best ways to r…

Gotcha. That makes sense. Thank you!

Re: Things to know about databases

#233

Post by eurasiantiger »

tomnipotent wrote: post #200
TheaomBen wrote: post #135

Earlier quoted context omitted.

Also https://www.postgresql.org/docs/current/functions-json.html

With just a splash of row_to_json and json_agg, you can JSON encode your entire query in PG. SELECT json_agg(row_to_json(t)) FROM information_schema.tables as t;

The dealbreaker here is having to do this dance for every nested field on every entity, and having to write a separate backend query for each separate front-end use-case for a single entity.

It just isn’t feasible to write everything out when the schema has >30 entities, some with N:M relations, and when queries routinely go several levels deep to fetch dozens of joined fields at every level. The boilerplate overhead is too much.

A natively GraphQL database makes such queries a magnitude less verbose to write out, and all the queries can stay in the frontend (or can become persisted queries on a GraphQL ”middle-end” server).

Re: Things to know about databases

#234

Post by nijave »

irrational wrote: post #83
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…

The NULL issue is so true. We migrated a large database from Oracle to Postgres. It took 2 years. By far and away the biggest issue was rewriting queries to account for the (correct) way Postgres handles NULLs versus how Oracle does it. Also, in my experience, the database is almost always the main cause of any performance issues. I would much rather hire someone who is very good at making the database perform well t…

>Also, in my experience, the database is almost always the main cause of any performance issues

More generically, state stores are almost always bottlenecks (they tend to be harder to scale without some tradeoff)

Re: Things to know about databases

#235

Post by krab »

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)

At multiple companies I worked for, they concluded the backwards schema changes are not worth the risks and testing overhead. It's usually quick enough to issue a hotfix.

With rollback, you always risk losing data - remember, you're doing it when something didn't go as you expected. What are the odds, the rollback will break something further?

These were all Postgres shops, so schema changes within a transaction - that can be rolled back safely if it fails in the middle.

Re: Things to know about databases

#236

Post by spacemanmatt »

SulphurSmell wrote: post #226
Aeolun wrote: post #214

Earlier quoted context omitted.

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.

> At least not in the actual application schema.

And that's how the scratchpad schema became mission-critical ;)

Re: Things to know about databases

#237

Post by spacemanmatt »

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 makes sense in a few cases. keycloak and other middleware are smart to support several RDBMS.

Re: Things to know about databases

#238

Post by galaxyLogic »

citrin_ru wrote: post #215
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.

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/

Exactly. Isn't it a sad state of affairs that you can not write plain pure standards-compliant SQL and be done with it?

It's a bit like if you wrote C and could only compile your program with Oracle's C-compiler. Where is progress in SQL standardization going?

Re: Things to know about databases

#239

Post by nlnn »

snarfy wrote: post #81
nlnn wrote: post #71

Earlier quoted context omitted.

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…

It's pretty old problem as they are competing ideas. It's OLTP vs OLAP. Postgres is designed for OLTP.

Yeah, I think some of the problems are when both or needed on the same data, or when the use case changes over time.

E.g. our customers are stored in Postgres, so let's also log their actions there linked to the user table.

5 years on someone decides we need to run analytical queries across years of 200M logged actions, joined with other data in the DB.

So now we either have to live with horrible performance, migrate the logs to something suitable for OLAP (and lose all the benefits of a solid RDBMS), or have some syncing/export process to duplicate somewhere suitable for querying.

Re: Things to know about databases

#240

Post by friendzis »

SulphurSmell wrote: post #93
Yhippa wrote: post #76

Earlier quoted context omitted.

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…

> YMMV. I think today I probably would try to keep the database as vanilla as possible.

Sure, YMMV.

In any non trivial dataset a lot of fields are effectively computed. For example merged entries: in order to get correct and whole data, one needs to consult some merge mapping, which can be easy to forget and tricky to get right - you have more than one relational identifier. This is not strictly business logic, but rather data assembly logic.

Similarly, a value can easily be spread over multiple fields (and tables) and it is crucial from data integrity standpoint to always update them in tandem if applicable. Again, this is very easy to screw up in client code, because the hidden relationship can be non-obvious. On one hand this is business logic, on the other hand violating this implicit relationship will result in non-agreeing data. This occurs for example when data represents parallel states.

Effectively, stored code acts as some kind of gateway API stored concurrently with the data. Sure, some peculiarities can be implemented with functions and triggers, but IMO those are just different sides of the same coin. In the end really depends on the dataset and what it represents.

Database stored code is a tool. Used appropriately it solves problems, used inappropriately it causes problems. If I were to design a database today, I would too try and make do without stored code, but would not try to twist data model so that it fits the relational model of RDBMSes.

Post reply on HN