
Ask HN: What are must know DBA concepts for developers? - chrisdsaldivar
Could you tag your responses as beginner&#x2F;intermediate&#x2F;advanced concepts?
======
thorin
Understand ACID and the isolation levels available in your database server.

Understand transactions.

Read an explain plan and understand basic indexing.

Test recovery of your server and implement a standby/ failover.

Understand the pros and cons of where to put the logic of the application (db
objects, stored proc, backend services, ui etc).

Understand the pros and cons of using an orm and how you might tune or scale
an app using one.

~~~
rpod
Do you have any pointers as to where one might learn more about the topics you
mentioned? I'd be interested in reading more about #5 in particular

~~~
thorin
One of the problems is that people will give different answers depending on
their expertise and there are very few people who have deep knowledge of
database development and backend development and supporting large production
systems because in the larger enterprise organizations people tend to
specialise. I have around 20 years experience using Oracle database and can
kind of translate that to other database servers e.g. postgres, sql server
etc. I have a bit of knowledge of writing services in c#, ruby or java.
Previously I would have advocated putting all the logic into the database but
I'm starting to realise some of the compromises involved.

Reading the Oracle or postgres documentation gives a lot of information about
the internals, tuning, acid etc although it can be a bit dry...

~~~
rpod
That's an interesting perspective. Coming from a background of primarily
developing backend services and using databases as a necessary tool, I usually
approach it the other way and steer clear of stored procedures, triggers and
the like. I guess it's also a matter of using the tools that you're most
comfortable with.

~~~
thorin
Of course. At least in the UK and probably elsewhere large businesses, banks,
government etc often have a large centralized systems based on a single or set
of relational databases. A lot of the logic was controlled on the database
either by constraints/checks etc or stored procedure access so it didn't have
to to be replicated across the many disparate systems which connect to it.
This is slowly changing, very slowly in some cases!

I can understand why that might be a surprise if you've been working for <10
years and mainly on new SAAS style products.

------
Foober223
On the deployment side of things...

Many developers don't get the concept of deploying a patch. They envision a
script for each DB object stored in source control. Mimicking the the Java
class files for all their objects. That's fine for code which can be re-built
from scratch. But you can't re-build your DB, it must be patched to preserve
the precious data.

It works fine for DB release #1. But when DB release #2 comes around there's
no way to deploy the DB changes. Special diff tools are brought in to generate
the patch. The auto generated patch drops a column.. ooops.

------
mattmanser
intermediate/advanced

Know that most databases can spit out a "worst performing queries" and
"overall most expensive" reports. The method varies per engine. Always
illuminating, and usually not the queries you were expecting.

Know how to turn logging on/off and where the logs go.

Know there are a bunch of system tables that keep data about all your tables
in it in most engines, information_schema tables. Rows, size, columns, etc.
These are actually defined in the SQL spec, but various SQL vendors have even
more available, e.g. there's a load of extra stored procedures in SQL Server.

Be able to generate and read a query plan report for your engine, which will
tell you what's causing a slow query.

