So, about 9 months ago, I worked with a team of researchers. They were building a pretty hardcore global distributed system, and associated management infrastructure. My job was to simply advise them on issues revolving around how they use their database back end. For the most part, I just made suggestions here and there about normalizing their schema and helping them adjust a few queries to deal with the normalization. But there was this one thing….
I told them that if they had certain views of the data that were used all over the place by their applications, they might consider creating “views” in the database to provide that view of the data without denormalizing the data in the base tables. The team quickly caught on, and I left them to it. A few days later, it was clear that I had created a monster. One of the team members had created some views that were… unique. He was using them as a means of storing not only complex queries, but also some logic to manipulate the data, and format it for him. And, of course, since different functions might use the same basic data, just formatted or manipulated in a different way, there must’ve been lots of these views. And… well, I think you get the picture.
I pointed out that this was bad news on so many levels that to move in this direction was just No Bueno(tm) all over the place. There was like a week-long email thread involving most of the development team at the time, and in the end, I told them that if they still wanted to go this route, after everything I had told them about database design and usage in general, then at least they were making that decision with the knowledge of the possible consequences, and so my job, really, was done. I knew I had at least convinced one of the guys, and he fought tooth and nail with the other guy (they sat right next to eachother). I figured they’d come to some compromise and find a sane way to move forward. My time with them was over and I moved head first right into another project.
Since that time, there was a little turnover in the group. A couple of guys left, and a *few* new guys were brought on. Just a couple of weeks ago, I spoke to one of them about doing a database cluster to work around some performance issues. Then, I got an email from another guy saying they were trying to find the source of some database slowness. This is months after I left the group, so I never dreamed that this had anything to do with “the views”.
Then I got this IM from some nick I didn’t recognize saying “after all these months of fighting, we’ve finally proven that you were right all along”. The guy called me by name, but I didn’t know the nick, so I said “um. What?”. And he proceeded to tell me that, after removing the “views of death” that were put in place in spite of the many super-long email-based database lectures I had sent, their database performance woes were gone. The load on their database server went from “maxed out” to under 5% immediately. The load graph was astounding.
I had no idea, and in fact could not fathom, that these views would ever make it to production. Vindication is good.
For the record, it’s not views themselves that are bad. They’re *good*. In fact, I’m the one who suggested using views! It was the misuse of views that killed the database.
Technorati Tags: database, sql, views, sqlviews, postgresql, pgsql,
