Viewing entries tagged with cte. Return to full view.

The end of PostgreSQL 8.4 - a small tribute

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!

What's your favorite 8.4 feature?

Over the past month or so, I have been informally polling those of our customers I've been meeting with about which features in the upcoming 8.4 version of PostgreSQL they are most excited about, and most likely to have use for in the short-to-medium term. Part of the results were exactly what I expected, other parts were a bit more surprising.

First of all, the one feature that every single one said they were very much looking forward to is the updated Free Space Map implementation, which will remove two very annoying and difficult-to-get-right configuration parameters (max_fsm_pages and max_fsm_relations). So kudos to Heikki for that. Closely related to this feature is the Visibility Map, and it's reduction of VACUUM requirements. This is, however, not something a lot of our customers will have a direct use for - other than one or two, they don't actually have any issues with VACUUM (once they learned to remove VACUUM FULL from the nightly cronjobs).

When looking closer at the SQL level functionality that people have been excited above, I expected to see Common Table Expressions (CTEs), AKA Recursive Queries at the top of the list, based on requests on our lists in the future. However, it seems that at least to our customers, Window Aggregates are a lot more interesting than CTEs. I'm sure there are a lot of use-cases for recursive queries - there just seem to be even more of them for window aggregates.

Parallel restoring of dumps is also fairly high on peoples list, though most of the people that can really use it are deploying different ways to deal with it already. But they will definitely be using it once it's there. As well as the speedups of the PITR recovery and warm standby.

There are a lot of other features in the upcoming version as well. Some are small, some are large. There are still some smaller features (for example \ef in psql) that I keep missing a lot every time I end up working on an earlier version. They're all (well, most of them) great, but maybe not so obvious at first.

What's your favorite 8.4 feature?

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

PGConf.EU 2024
Oct 22-25, 2024
Athens, Greece
Nordic PGDay 2025
Mar 18, 2025
Copenhagen, Denmark

Past

PGConf NYC 2024
Sep 30-Oct 2, 2024
New York, USA
PGDay UK 2024
Sep 11, 2024
London, UK
PGConf.DEV 2024
May 28-31, 2024
Vancouver, Canada
PGDay Chicago 2024
Apr 26, 2024
Chicago, USA
SCaLE 2024
Mar 14-17, 2024
Pasadena, USA
More past conferences