PostgreSQL Performance and PGWest

If you’re looking for a heavy dose of information about PostgreSQL performance tuning, you’re going to find the next month very interesting.  We at 2ndQuadrant have been working on two books about PostgreSQL 9.0 this year.  You can pre-order those right now, and as I’m staring at a home printed copy of my PostgreSQL 9.0 High Performance book right next to me at the moment, I can assure you that one is quite finished and soon to be released.  Weighing in at a final count of 442 pages, I hope there’s a few things there for just about everyone.  I just read Simon and Hannu’s PostgreSQL 9 Administration Cookbook over the weekend too, also due to be published soon, and it has a complementary set of information and a more task-driven focus.

Since my head is just overflowing with information on this topic after all that, at the upcoming PGWest conference I’m joining the “3 talk Greg” club, founded by Greg Sabino Mullane at the 2009 PGEast.  The PGWest conference schedule is up.  I thought I’d take some time to talk about what I’m discussing, and how that fits into what a mythical “Performance Track” at the conference might look like, if there was such a thing.

Tuesday

Josh Berkus’s “GUCs: a Three-Hour Tour” is a solid introduction to PostgreSQL configuration.  If you’ve seen this popular talk already or are already pretty familiar with the postgresql.conf file, Brent Friedman’s “Normalization workshop” would be a good alternative that focuses on the sort of larger design issues that can make your database slow.

The next slot is pretty packed with coverage.  For those interested in more level-level, hardware related aspects of performance, Jignesh Shah’s “Running Postgres 9.0 on Virtualized Environment” should have plenty of that.  If you already know a fair amount about how statistics in PostgreSQL work, Nathan Boley’s “Improving Planner Prediction Accuracy with Custom Selectivity Functions” should stretch your brain out a bit in that area.  If you’re more of an architect type, “The Thinking Person’s Guide to Data Warehouse Design” from Robin Schumacher is probably your speed instead.

For the 2PM one, if you’re using Rails than “PostgreSQL as a secret weapon for high-performance Ruby on Rails applications” from Gleb Arshinov is where you want to be.  Architect types should appreciate “Practical Postgres Sharding: Scaling to the Horizon” by Casey Duncan.  And if you’re more of an operations or admin person, Gabrielle Roth’s “Survey of PostgreSQL Monitoring Tools” should help you sort out a pretty complicated area.

The final Tuesday spot has “The PostgreSQL Query Planner” by Robert Haas, which has been a favorite everywhere he’s presented it.  If you want to learn how queries are executed and optimized in way that leads to improving their performance, and haven’t seen this one yet, you want to be at that talk.

Wednesday

Wednesday kicks off with my “PostgreSQL Performance Pitfalls” as an option.  This is intended to be a talk aimed at newcomers to PostgreSQL.  If you already know things like how to configure your postgresql.conf and when you’re supposed to VACUUM and ANALYZE, you may be a bit bored and want to catch another talk instead.

For the first of the afternoon sessions, “PostgreSQL Clustering with Red Hat Cluster Suite” from Devrim Gündüz will cover something a lot of people don’t think about very much:  how things tend to go wrong when you try to add redundant servers to your system, and the sort of add-on software needed to prevent the worst of those.  Vital stuff if you’re involved in the low-level administration of a database that must be reliable.  More on the development side, you might instead enjoy “Database-driven cache invalidation” by Magnus Hagander.  As the fastest way to get a page out of a database is not to have to ask for it in the first place, this sort of caching infrastructure can really save if your business or web site has a lot of visitors to it.  Deciding which of these talks to attend will be one of the harder decisions for me to make as I expect both to be informative in interesting performance topics.

Closing out the day, Bruce Momjian’s “MVCC Unmasked” will be covering a vital performance aspect of PostgreSQL that I spend lots of time teaching people, especially developers.  Admins might prefer “Using the PostgreSQL System Catalogs” by Robert Haas instead, which will get more into interesting monitoring approaches.  And if you’re interested in replication, there’s discussions of both Londiste and Slony here.  This is another hard spot to pick a talk from.

Thursday

The final day of the show is a bit easier to focus on performance for.  My “Scaling PostgreSQL with Hot Standby” kicks off the day covering how you can use the new PostgreSQL 9.0 features to build multi-node deployements that spread your load around usefully.

At 10AM, newcomers would be advised to catch “Rapid Upgrades With Pg_Upgrade” from Bruce Momjian, because keeping up with PostgreSQL version upgrades is a really important topic the way performance features tend to improve in each new release.  I expect to be at the much more heavy talk about “Database Operating Systems” by Dan Koren instead.  And at 11, “Scaling PostgreSQL with GridSQL” from Jim Mlodgenski will be a good introduction to this often misunderstood performance scaling software.  Jim was one of the reviewers of my book, and he just had to straighten me out on that topic last month.

After lunch is my final talk, “Righting Your Writes”.  This is intened to be an intense discussion of low-level database and filesystem internals and how they interact with one another.  If that sounds more complicated than you’re looking for, “Dumb Simple PostgreSQL Performance” by Joshua D. Drake will be a much less stressful look at common setup issues.

And after that…well the talks get a bit more specialized, and you’ll have to sort through those without my suggestions.  Hope you can make it to the conference, there’s lots of good content to be seen, and I’m hoping I’ll even have the early copies of my book available by then too.