Ah, query hints. For all those times when the database isn’t doing what you want, they’re a useful tool for forcing the query optimizer to perform your bidding. But in this case, not only is the road to Hell paved with good intentions, it’s paved with a frictionless slide directly into a wood chipper that empties handily into an active volcano. With query hints, be careful what you wish for, because—to the detriment of all you hold dear—you just might get it.
So you’ve decided to use PostgreSQL as the database for your sparkly new website running some variant of a LAMP stack. Or maybe you just got a new job and must now administer a PostgreSQL install so you excitedly did your research. You’ve read the install docs, tinkered on a VM, and you think you’ve got everything ready.
You’re wrong. You’re going to run out of disk space, your website will be slow, and you’ll go running to the PostgreSQL mailing lists in abject terror because you have no clue what is wrong.
With the introduction of PostgreSQL 8.4, Bruce Momjian, a significant core developer, contributed a tool that can actually upgrade an entire database cluster in place. The time required is essentially only that necessary to copy the data files from the old installation to the new one. On a quick RAID system, this can be an order of magnitude faster than a dump/restore. The main drawback is similar to Slony: disk space must effectively be doubled for this upgrade method.
PostgreSQL can be both a beautiful thing, and an infuriating mess.
Occasionally I look through the logs on one of our database servers to see if I can’t optimize some queries. It’s good practice, and is an excellent way to monitor basic system performance by watching for queries that take longer than might otherwise be possible. Sometimes performance can be fixed by tweaking an index, or manually rewriting the query and convincing a developer to integrate the changes.
Postgresql hates itself. I’m convinced of this, and have considered the idea frequently over the years. I roll it around in my mouth just to savor the taste, only to hope the flavor changes eventually. A couple things have advanced, though not quite what one might hope.
Parallel Restore With the introduction of PostgreSQL 8.4, the core developers have finally succumbed to the availability of multiple-CPU systems, and added parallel restores.
