Todays release marks the end of life for PostgreSQL 8.4, after 5 years of service.
I've found it interesting that PostgreSQL 8.4 was probably the first release to actually make it possible to run fairly busy PostgreSQL installations without any hands-on at all. There were many important parts to 8.4 that made that happen, such as much better standby database support (though not hot standby yet - that came in 9.0) and better statistics and monitoring systems. 8.4 also came with Recursive CTEs (WITH-queries) and Window Aggregates, both of which made a whole new set of applications possible.
But I think nothing actually brought about this "run without hands-on" as much as the new code for Visibility Map (VM) and in particular the automatic sizing of the Free Space Map (FSM). Anybody who deployed 8.3 or earlier in any kind of busy environment knows the pain of trying to configure max_fsm_pages correctly. It was almost impossible to get it right, and the value of right kept changing with your load and data. And the worst part of it all was that if you got it wrong you were in trouble - there wasn't really any remedy other than taking your database offline (or at least read-only) for manual full database vacuums (there were tricks to get around the vacuum specifics, but the core problem was there). So what happened was that a lot of those people who knew what to do just increased that number to something that was "big enough", which usually meant "way too big" and thus wasting resources. And even with that, sometimes ran into it not being big enough because the workload changed.
In fact, I think more or less every single system I did reviews for customers for on those versions had a substantially wrong number in max_fsm_pages - usually the default value because they had no idea what to do with it - and were paying the price for it.
Extra funny is that I've been talking to Heikki (who wrote the dynamic FSM and VM code) a few times recently, and he's still surprised that these changes had such a big impact on real life deployments. Probably because it's one of those things that is usually not a big problem at all in small installations, developer installations, and testing systems, but can quickly snowball in real life production. I think many of us didn't realize before the release actually made it out there how useful it would be.
This feature, together with things like Recursive CTEs and Window Functions really makes 8.4 one of the landmark PostgreSQL releases. It's easier to point to releases like 9.0 which had Streaming Replication and Hot Standby because they are sexier and more marketable features, but infrastructure like this is more important than we often think.
8.4 got rid of some of the hardest things to tune. Let's stay on the path of trying to keep the system simple enough to use that it doesn't need handholding, and look forward to the upcoming 9.4 release!
You have it slightly backwards - as I wrote, that settings died with 8.3. 8.4 was the first one that didn't have it.
So your "since 9.0 came along this was no longer necessary" should really be "since 8.4 came along this was no longer necessary" :)
(And no, I wasn't surprised either.)
Thanks Magnus for this post. 8.4 has been a fantastic release!
Magnus, thanks for helping in setting up our 8.4 database. It was an excllent release too. It ran with surpisingly little need for maintenanace until we finally upgraded to 9.2.
Reading comprehension fail. Ah well. :)
New comments can no longer be posted on this entry.
What I never understood about max_fsm_pages, was why it was set so low in the first place. The memory usage, even for high settings in the millions, is almost negligible in comparison to shared_buffers.
In my second job using PostgreSQL in '05, I started with a company that was at 98% space used on their primary database storage. In my first week, I basically had to save the database. I wrote a script to full vacuum every table from smallest to largest, because they didn't have enough free space to do it any other way. In the end, the database was 1/3 of its original size; since I set max_fsm_pages much higher than necessary, it stayed that way forevermore.
Since 9.0 came along, this was no longer necessary, and I believe it's one of the reasons adoption has accelerated. No other database would bloat itself into destruction due to a single misconfigured GUC. For smaller companies without a dedicated DBA, that was a monumentally insidious time-bomb.
Unlike Heikki, I'm not at all surprised that several customers had max_fsm_pages wrong. I'd even go so far as to suggest most installations of PostgreSQL had it set too low. I'm extremely glad that setting dies along with 8.4.