PostgreSQL 9.1 Released 148
With his first posted submission, sega_sai writes "The new version of open source database PostgreSQL was released. This version provides many important and interesting features such as synchronous replication, serializable snapshot isolation, support of per-column collations, K-nearest neighbor indexing, foreign data wrappers, support of SELinux permission controls and many others. The complete list of changes is available here"
Re:vs Oracle? (Score:5, Informative)
So...how does PostgreSQL compete with Oracle nowadays as far as features go (specifically, spatial, and data-guard-like replication)?
I can't speak for Oracle, but if you're interested in spatial stuff you should have a look at PostGIS [refractions.net]. We've recently been using it to store tons (magnitude of several million) of points and polygons, and we're very happy with it. We've got about hundred simultaneous users connecting to the WFS in peak hours, and it bears the load pretty well if you properly index your tables. I can't speak much for updates, since our database updates in bursts (we import new data every X weeks). I can't go too much into detail about the type of data other than that it's polygons, points, and mostly distance calculations and intersections.
We briefly looked at Oracle Spatial for a while, looked at the pricetag and the project budget and made the decision to try the PostgreSQL+PostGIS combination and see how far it'd get us. We were pleasantly surprised. I had some experience with PostgreSQL before in the 7.X releases in a previous lifetime but in the end wasn't all that pleased with it, especially on busy servers. Nowadays, I'm running 9.0 and I'm pretty much content about it. Replication wise we've got a PITR setup up and running which is more than enough for our purposes. It's pretty well documented, but be sure to test everything, etc etc etc... It doesn't quite hold your hand when you're setting it up, so double check everything.
I'm sure that there will be people on here that have more extensive experience with PostgreSQL (and Oracle) to fill you in on the juicy details, but in general I'm pretty pleased with it so far. It scratches my particular itch, and does so without all too much headaches.
Re:not excited (Score:3, Informative)
I am not excited about any of these changes unfortunately, they are somewhat specialized, though having synchronous replication and serializable transaction isolation sounds more useful than other stuff.
Synchronous replication for many is a must have. In many cases this single feature was preventing adoption of PostgreSQL as many applications require synchronous replication support.
Per column collation is another feature which is commonly required in complex applications; especially if they are multilingual capable.
Extensions support is very important and will benefit anyone who uses any contrib or third party extension for PostgreSQL. Unless your database work starts and stop at the most basic of features, this is a something you'll likely use, if not today, then tomorrow.
Serializable isolation level support, for many, is a critical feature. I agree most people likely won't benefit, but its an important feature to round out support for all database applications and loads. It means PostgreSQL is now an option for whom it wasn't previously.
Unlogged tables is extremely important, super cool, and very powerful. Many applications require time consuming kludges to work around this previously lacking feature. For many applications this is a make it or break it feature from a performance perspective.
For many geo-location applications, but even beyond that, nearest-k is an extremely important feature which not only simplifies code, but also reduces development and test while also providing for a nice performance boost.
And of course, the SE Linux stuff is very important for customers (you'd be surprised how many there are, including the NSA) who consider this the most important feature of the release.
But there are real things that are missing. Most obvious is distributing of one SQL request into parallel processes or threads to speed up query execution on multi-core systems (which are all multi-core today).
You want EnterpriseDB. It already has this. As for the rest of your rant, pragmatically, it just doesn't happen very often. Assuming you're not just trolling, what did the PostgreSQL Perf guys have to say about your issue. Generally these types of issues are considered planner bugs (unlike all other SQL vendors) and if possible, will gladly create a fix, if able/appropriate. But 99% of this time, this is just one of those things people love to lie and troll about.
There are so many ways to improve performance and really kick it up, and instead there are more features added. I think database performance is now more important for PostgreSQL than features (unless this means introducing parallelization of single SQL requests.)
Except that PostgreSQL is already one of the fastest databases available for 90% of the likely workloads. Performance, in of itself, typically isn't the primary focus right now simply because, for the vast majority of users, it already spanks or is at least on par with most every other option.
You can modify the size, which is 16MB by default, but you can only modify the size when you configure the source code before compiling it: configure --with-wal-segsize=1 - this configures the segments to 1MB, which allows the second drive to last that much longer if all you are doing is using a second drive to keep dynamic backup (and that asynchronous backup method, by the way, the problem that they are solving with "synchronous replication", it's that you either have these segments fill up, and then the segment is written to disk, or you wait until time expires for segment to be written to disk if you set checkpoint_timeout). I imagine treating fixed sized segments is easier than generating segments that are of exact size equal to amount of data that was produced in a time period, but it's a waste of disk though.
Pragmatically, just not an issue. Which is why its a compile time option and not a runtime option.
Re:vs Oracle? (Score:4, Informative)
This isn't an optimization problem. As noted elsewhere in the comments here, Oracle handles string nulls oddly [slashdot.org]. Because of that, joins happen differently against a null value vs. an empty string. That will produce different query results in Oracle vs. PostgreSQL. It's one of those really annoying conversion quirks, either going to Oracle or escaping from it.