I recently got the opportunity to take a look at Learning Heroku Postgres, a new book by Patrick Espake that seems intended to help new PostgreSQL database administrators get their data into the cloud. The chapters are short, concise, and the questionnaires at the end are a nice touch. But does it hit the mark? Almost.
Before I get too far into this review, I should point out that Heroku is a proprietary service that presents a modular deployment system for various programming languages, applications, administration, monitoring, and other related services.
Unlike a lot of programming languages that have loose typing, databases are extremely serious about data types. So serious in fact, many patently refuse to perform automatic type conversions in case the data being compared is not exactly equivalent afterwards. This is the case with Postgres (PostgreSQL) and surprisingly often, queries will suffer as a result. Fortunately this is something we can address!
There are easier ways to demonstrate this, but in the interests of making this more of an adventure, let’s use a join between two tables:
When working with a database, sometimes performance problems are both far more subtle, and much worse than a query itself might suggest. The topic of this week’s Postgres (PostgreSQL) performance killers article concerns the use of the IN clause, and how misusing it can catastrophically obliterate the database in mysterious ways.
To that end, we’ll use a slightly revised single-table test case since it’s served us pretty well so far:
For the second of my ten part series on hidden Postgres (PostgreSQL) performance killers, I’m going to talk about something called an anti-join. It’s not a well-known approach outside of the database world, but due to how it works, it can impart almost magical plan revisions that drastically improve query performance in the right scenario. Developers can add it to a growing bag of tricks when working on database-driven content, since it comes in handy more often than you might expect.
Yesterday I gave the developers at my company what I call a DBA Chat. It’s something I try to do every month to keep them apprised on various features, caveats, performance considerations, and so on. I find that educating the folks who regularly work with the database does wonders for application performance and my sanity. The benefit of this long format is that I can go over more information than a time constrained set of slides.
