The upcoming version of PostGIS - PostGIS 1.5 will be an exciting one. It has native geodetic support in the form of the new geography type, similar in concept to SQL Server's geography support. For windows users, we have experimental binary builds hot off the presses for PostgreSQL 8.3 and 8.4
Paul has more information about the technical details of it and an example use case. At this point the geography portion of PostGIS 1.5 is pretty much complete and we are now stress testing the functionality we have in place. We have native area, length, perimeter, indexable proximity distance checks based on a geodetic model and a slightly more advanced spatial index support algorithm. So right now it takes care of the number one fundamental question of casual GIS users too short on time to learn about spatial reference systems, spheroids and geoids. That being How do I get fairly accurate proximity and measurement information from long lat data and do it fast and quick?. This also paves the way for PostGIS 2.0 where we will be extending 3D support since some of the lessons learned will be applied to that.
Although the geography data type doesn't have all the jazzy geometric processing functions of the geometry type, casting to the geometry type is easy and, creating them for most common use cases is fairly trivial as Paul has demonstrated with his ST_Buffer example. We had lots of heated debates about this because these tricks don't work all the time and some of us (both on dev and long time postgis users) were concerned about the corner cases where these tricks start to show their sores and polluting our really good native geodetic functions with these questionable workarounds. With Paul being on the side of "think of the children" and many of us being on the side of "think of the adults". We finally did approach each other and come to some sort of truce that we'd allow Paul to put some of his frosting in and put aside the rest of it for people to decide how much of a helping they wanted.. We didn't quite get to the point of virtual fist fights this time around.
We'll be doing another comparison in the coming months on our Boston GIS site comparing the geometry and geodetic spatial support of Oracle, SQL Server, and PostgreSQL/PostGIS. I should say there is no clear winner in the geodetic front. SQL server 2008 has more native geodetic functions, but also more restrictions, and the fact you can't easily transform to another spatial ref puts it at a bit of a disadvantage to make use of the extra geometry functions it has. On the up side it has a lot more native geodetic functions that are not trivial to build. In so doing, it does put a restriction on the size of the geometry you can have which PostGIS doesn't. Oracle locator has a lot fewer functions period (and Oracle enterprise is just too pricey), but locator does do some things really really well.
Simon Greener did a talk at FOSS4G 2009 comparing Oracle and PostGIS/PostgreSQL.