Transactions in the Trenches

I was working on a product once, and fairly early in the dev cycle marketing hired a DBA. He showed up at my door one morning and said, “Hi, I’m here to optimize your database!”

“My what?”

“Your database. It needs optimizin’.” He grabbed a test version of the system from some place random (the system barely ran) and “tuned” it. The results were something like

– set up row caches of 5175 bytes for table A, 9032 bytes for table B, 30452 bytes for table C
– tweak a bunch of Oracle mumbo-jumbo (more random numbers)
– add indices for a bunch of columns
– break some rows up (…breaking the code, natch)
– do some muttering about third normal form (for, or against? I can’t remember)

… all numbers are made up here. That’s okay, his were, too.

The most common database errors I’ve seen are:

– Premature optimization (tuning too early, doing too much in stored procedures). Most of this stuff is just fear concretized as code.

– Pathological dependency on a particular vendor’s features (“Ooooh, shiny,” or simply more fear).

– Really truly horrible bloody awful and totally fubared interfaces to the database in code (highly abstracted stuff that slowwwwly boils down to a zillion selects and updates and so forth). “So . . . you did all this work, and you still had to hard-code the admin password?”

– Polling the database for modifications (using rows as an RPC mechanism)

– Simple crappy schema design (“Oh yeah, no one will have a first name of more than 20 characters”) that metastasizes into every nook and cranny of the rest of the system (see above).