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

201–210 of 247 posts

Re: Things to know about databases

#201

Post by goto11 »

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.

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 represent it. It is sometimes suggested to use "sentinel values" like empty string or -1 to represent missing values, but IMHO this is much worse than NULL's, since these will be treated as regular values by operators. When you have missing values, you want three-valued logic.

Re: Things to know about databases

#202

Post by jsiaajdsdaa »

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.

Who wants to shard MySQL once a week at Amazon levels of scale? I prefer a managed service with consistent hashing.

Re: Things to know about databases

#203

Post by tragomaskhalos »

SulphurSmell wrote: post #90
irrational wrote: post #83

Earlier quoted context omitted.

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…

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

Re: Things to know about databases

#205

Post by goto11 »

kccqzy wrote: post #184
SulphurSmell wrote: post #93

Earlier quoted context omitted.

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

A colleague of mine has a very strong opinion that any candidate who mentions using stored procedure in an interview without immediately disparaging it is an immediate no-hire. I sometimes wonder what kind of experience formed that opinion. (I personally never worked at a place with significant use of stored procedures.)

Your colleague is dumb. Stored procedures have advantages and disadvantages. For certain data processing operations it is much better to perform everything inside the database than transfer large amount of data back and forth to some external application.

But I have also seen organizations with the policy that any operation touching base tables should be encapsulated in a stored procedure. This makes development extremely cumbersome, especially if some DBA is gatekeeper for the stored procedures. Something like this might have burned your colleague.

Re: Things to know about databases

#206

Post by tpetry »

zvrba wrote: post #199
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.

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.

Re: Things to know about databases

#207

Post by FlyingSnake »

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.

This rings true in my experience. SQL knowledge has consistently helped me over my career. A simple exercise in designing the relational data model vastly improves the system architecture.

Re: Things to know about databases

#208

Post by extrememacaroni »

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.

Imagine hating the things that you use rather than a pdf with text, fascinating.

Re: Things to know about databases

#209

Post by teddyh »

Aeolun wrote: post #175
mmcnl wrote: post #130

Earlier quoted context omitted.

"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)

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.

He said usually.

Re: Things to know about databases

#210

Post by eikenberry »

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.

Pretty easy. RDBMs have a shit API (SQL is terrible) and the largest (PostgreSQL) have a shit HA story. IMO you should think why you are using an RDBMs.

I have used both and have never regretted NOT using an RDBMs. Maybe its a taste thing but I'd rather use a simple K/V database than a relational database any day.

Post reply on HN