We arrived at the conference site to find that the XML Data Warehousing had been canceled, so I spent that session in the Hackers’ Lounge attempting to continue work on pg_proctab, while getting kicked off the commie college wireless.
In Lists and Recursions and Trees, Oh My!, David Fetter gave us some example of old kludges to get row numbers out of Pg – “Not only is it slow, but it’s wrong” – but you may not notice that subtle wrongness in huge data sets. This really illustrated the value of testing your data.
After lunch, I went to Josh Berkus’s 5 steps to PostgreSQL Performance Tuning.
He gave us some rules of thumb for figuring out how much RAM & CPU you need, but also recommends hiring a hardware geek to design your system for you – because vendors lie. :) Try hardware out before you purchase it, or definitely test them within the warranty period. And, here’s another use case for pg_proctab (other than my own amusement): capacity planning.
Tip: Don’t use autovacuum for data warehousing applications, or where you have large number of writes happening at once. Manually vacuum those.
(An additional tip from me: if you’re using linux, try increasing the default readahead buffer from 1024K to at least 1M for an ~80% performance improvement. See our [in]famous file systems talk for the graphs to back this up.)
Thanks for another wonderful conference experience, PgPeeps! See you again soon!