Xaprb

SQL Antipatterns, by Bill Karwin, Pragmatic Bookshelf, 2010. About 300 pages. Here’s a link to the publisher’s site.

I loved this book. (Disclosure: Bill is a colleague of mine.) This is the first book I’ve read from the Pragmatic Bookshelf, and if the rest are like this one, I want to read them. The quality of the writing is way above the average technical book. Techniques that feel gimmicky and forced in other books, such as fake stories to introduce each chapter, actually work here (because they’re real stories, not fake ones). Each topic is named in a memorable way and introduced very cleverly. Little quotes and excerpts are tastefully used to illustrate and reinforce the topics. The end result is that it’s a lot of fun to read. If you do nothing more than read the first and last page of each chapter, you’ll enjoy it and learn a lot at the same time.

The target audience here is developers who must work with databases, not database administrators per se. But if you’re a DBA and you’re having a hard time explaining something to a developer, you could do a lot worse than referring to this book. The content is database-agnostic, with specific mention of a broad variety of databases such as MySQL, Oracle, SQL Server, and more when appropriate.

There are four major categories of mistakes: logical database design, physical database design, queries, and development practices. These cover 25 “anti-patterns” showing how not to do things, and explaining why they’re wrong, when it might be a good idea to do it that way anyhow, and what your alternatives are.

For example, the first pattern is titled Jaywalking. The name is a reference to avoiding intersections, i.e. storing comma-delimited lists in a single column. The chapter explains what’s hard to do in a database that’s designed in this manner, and how to sniff out the use of this antipattern. Then it explains how to create an intersection table, and ends with a pithy recommendation to “store each value in its own column and row.” Pretty straightforward, and much more effective than a lecture on all the different varieties of normal forms.

Sometimes I thought of some other alternatives. For example, in avoiding FLOAT data types to prevent rounding errors for currency values, the book suggests using DECIMAL. But another option is storing an integer number of the smallest currency unit (pennies, in the US dollar currency). And I thought of lots more antipatterns, such as using a table as a queue, which often happens sneakily, without anyone realizing it. I am sure we could all suggest more antipatterns and alternatives. Anything can be taken to extremes; I think this book wisely stops before the point of burn-out.

Like Bill Karwin, I see developers make these mistakes constantly, so I recommend this book to every developer who interacts with a database. It might be a good book to buy for new team members, if you’re the team leader.

MySQL Replication by Russell Dyer, Silent Killdeer, 2010. About 180 pages.

This is a pocket-sized guide to setting up and managing MySQL replication. It is self-published and made via print-on-demand technology. Topics include how replication works, setting up replication, making backups, and administering replication after it’s working. There are several appendixes for replication-related functionality in the MySQL server and command-line tools.

This book doesn’t go into great depth, so don’t expect it to be a reference manual to replication internals or anything like that. It’s more of a how-to manual for beginners, walking through the basics of binary and relay logs, SQL and I/O threads, and so on.

I wish it were more comprehensive in some areas, and talked about tools such as Percona XtraBackup and Maatkit. I don’t think any book on replication today is complete without these tools. I also wish the text font had serifs and was colored black instead of a foggy gray color. This might be an artifact of the print-on-demand technology, I’m not sure.

Some readers will likely prefer the official MySQL manual to this book, while others might find the book to be a convenient and helpful reference. If I had to summarize the book in a sentence, I’d say that it tells readers the basics of setting up and administering replication, but excludes advanced topics, some of which I’d consider essential (e.g. Maatkit, Percona XtraBackup).

PostgreSQL 9 Administration Cookbook. By Simon Riggs and Hannu Krosing, Packt, 2010. Approximately 330 pages. (Link to publisher’s site.)

This is a good book for PostgreSQL database administrators to pick up, especially if you’re new to PostgreSQL but familiar with another system such as Oracle, SQL Server, or MySQL. It has more than 80 “recipes” that range from quick tips to moderately detailed discussion of how to accomplish specific tasks. The chapters and recipes are well organized, and you can either read the book from start to finish or jump to a specific recipe for quick help.

The major topics are introductory/overview, configuration, managing tables and data, security, managing the server itself, monitoring, maintenance, performance, backup and recovery, replication, and upgrades. The chapter list mimics that list pretty well, though I lumped some chapters together in my topic list.

Some of the same topics are covered in much greater detail in Greg Smith’s excellent PostgreSQL 9 High Performance, which I reviewed previously.

On the negative side, I can only remark that the cookbook format in general isn’t my favorite; each “recipe” is quite formulaic, with little sub-headings titled “getting ready, how to do it, how it works, there’s more, see also.” But it works pretty well nonetheless as a quick-reference guide.

Overall worth picking up, unless you’re quite knowledgeable about PostgreSQL already, in which case I wouldn’t expect you to learn much from this book.