Slashdot Mirror
PostgreSQL 9.1 Released
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"
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. 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.
It is easier, it is still wrong. It should be opt-in to prevent people from using it for new installations.
It is not a subtle fucking difference. It is a huge big honking difference. Either you don't know the information which is null or you know it to be "".
Lots of companies are converting from Oracle Spatial to PostgreSQL plus PostGIS because it's faster and has better compliance to GIS standards. The text of the talk isn't available, but the FAA Airports GIS and PostgreSQL presentation was a typical report I was in the audience for. The FAA's first conversion happened very quickly: just export their data in a standard format, import into PostgreSQL, and tweak some queries. The result worked so much better that they've standardized on PostgreSQL for spatial applications at the FAA now. Internal projects needing a spatial database have to justify why they want the budget for Oracle Spatial, and it's default deny unless you have a really good reason.
The addition of synchronous replication to 9.1 has made it a pretty even match for Oracle's Data Guard now. The main bonus is that you can control the integrity level you want at the transaction level. So you can have a database with a mix of important data (only considered safe when on two nodes) and fast, best attempt eventual consistency data, all in one place. Nothing else can replace Oracle at the top end while still having a lean enough mode to be competitive with NoSQL database when integrity isn't the top priority.
We convert Oracle installs to PostgreSQL all the time at my day job. The main obstacles I keep seeing that don't have simple solutions are 1) using a lot of PL/SQL, 2) differences in query handling, such as OUTER JOIN behavior or reliance on optimizer hints, and 3) can't limit the resources used by individual users easily in PostgreSQL yet. I actually have a design outline for how to solve (3)...would only cost a fraction of a typical Oracle license to sponsor that feature. EnterpriseDB's version of Oracle comes with PL/SQL compatibility, but only in a commercial product that lags behind the open-source releases--and buying from them just switches which vendor you're locked into.
The reliability probably improved since ENIAC, but the the question still is "when it is going to fail" and not if it is going to fail. Because it is going to fail - it may be a drive, CPU, PSU, a network switch, an AC unit, the whole AWS data center ... something is going to fail.
The beauty of CAP theorem as I see it that it says "You can't get all three at the same time, face it." If you don't need the strong consistency (and with most apps you don't), then ditch it and it'll be much easier and cheaper to built and scale the system. I'd say once you realize this inner beauty, it clears your mind - something like a Zen of distributed computing.
If we grant that a substantial set of use cases where a full-scale, non-embedded, out-of-process database is called for but relational integrity is unimportant exist (something I grant only for the sake of the ongoing argument), that still leaves people who try to use it in situations outside that set. I once took maintainership of an accounting system for car dealerships built on top of a "database" without relational integrity. Cases where bugs in the software resulted in orphaned records and numbers that didn't add up were legion.
Saying you don't need relational integrity is right up there with saying you don't need bind variables because your hand-built escaping code is good enough. There might be cases where hand-built escaping is good enough, but encouraging J. Random Slashdot Reader to take that approach means it's going to be abused... and I assure you, the person who built that accounting system considered himself an expert.
Interesting documentation. Reading the links -- suffice to say that MySQL's multi-master clusters are vastly less safe than Oracle RAC. Depending on after-the-fact conflict resolution rather than having proper locking... really? (And with respect to NDB not supporting fsync() before reporting a commit complete -- yes, I can provide a link for that. Being in a cluster isn't good enough -- sometimes you lose a rack, or a full DC).
Yes, it's cheaper. Yes, it might be good enough for someone. But if someone needs RAC features enough to pay $50K in licensing fees for a tiny cluster, NDB just ain't gonna' cut it. And if I'm handling medical data (which I was last time I deployed RAC), I'm not going to risk massive liability by deciding that I can afford to get a COMMIT back before data is not just ACKed by multiple nodes but sitting on a platter.
PostgreSQL 9.1's synchronous streaming replication is a sane middle ground -- it's not multi-master, but it actually preserves the same semantics and performance characteristics (durability semantics being the most critical of those -- but at the same time, needing to rewrite everywhere your code uses savepoints or autoincrement or relies on referential integrity exceptions because you're switching to a database backend that doesn't support them isn't any fun either) you'd have in a non-clustered environment. Having it available six years ago would have saved me one helluva lot of money.
Really? Compared to RAC, wherein moving to a cluster involves no loss of semantics, using NDB looks a whole lot like half-assing it already.