Slashdot Mirror
PostgreSQL 8.2 Released
An anonymous reader writes to let us know that PostgreSQL 8.2 has been released (bits, release notes). 8.2 is positioned as a performance release. PostgreSQL it is still missing the SQL:2003 Window Functions that are critical in business reporting, so Oracle and DB2 will still win out for OLAP/data warehouse applications.
MySQL has been the dominant SQL server within the open source community. Between its non-standard SQL and it's lack of advanced features, many developers and DBAs are getting fed up. Thankfully, they've been able to turn to PostgreSQL.
At my firm, we switched some of our MySQL Enterprise databases over to PostgreSQL 8.1. What we found was pretty amazing: PostgreSQL outperformed MySQL by approximately 23% in terms of the number of queries it could handle per second. And this was with a very basic level of tuning! Our MySQL installations, on the other hand, had been tuned by three different consultants. Keep in mind that both were running on exactly the same system, under the same installation of FreeBSD. Were not sure exactly why there was such a remarkable increase in performance when using PostgreSQL, even without much tuning, but we're happy with it nonetheless. We're also happy to no longer being paying MySQL for support.
We're actually quite happy to get away from MySQL. The other developers I work with were quite sickened by the deal MySQL AB reached with SCO a while back. While we're strictly a BSD shop, we still think SCO's actions are quite distasteful, and we are willing to move away from companies that enter into deals with them.
According to the MySQL fanbois, Window Functions are bad for performance and not even useful. Just like subselects, data integrity, triggers, and transactions. Oh wait, MySQL 5 supports subselects. Subselects are no longer bad for performance.
Do you even lift?
These aren't the 'roids you're looking for.
Bitmap indexes will almost definitely be in 8.3. Gavin Sherry submitted a revised patch for them a few days ago.
PostgreSQL it is still missing the SQL:2003 Window Functions that are critical in business reporting, so Oracle and DB2 will still win out for OLAP/data warehouse applications.
Bullshit, pure and simple. This is nothing more than marketing-speak and you should be ashamed.
I'm not saying that SQL-2003 Window Functions are useless, I'm saying your statement about them being "critical" in business reporting is bullshit. Did no one do business reporting before this standard came out? What the hell did people do in 2002? Are all those MS-SQL Server 2000 and Oracle 8i servers going to fall down in shame? I think not.
I see these comments all the time, usually in marketing brochures from a software vendor touting a new feature. They make it sound like all other products are steaming piles of shit if they don't have whiz-bang-feature #16. They like avoiding any conversation that goes "But, I've been using your product and it works great. Are you telling me your product (last rev) is a steaming pile of shit? That implies if I upgrade, next year you're going to be telling me how THIS rev you are so loudly praising is also a steaming pile of shit."
Charles (had enough marketing-speak for this year)
Learning HOW to think is more important than learning WHAT to think.
... I'm also doubting the 23% increase in performance... FWIW, and YMMV, when you get hammered with many concurrent queries, it's much, much faster. At about 100 concurrent hits, about 50% faster: http://tweakers.net/reviews/657/6 Benchmark method here: http://tweakers.net/reviews/646/9Yes, it's missing description on how exactly they set up MySQL. MyISAM? innodb? So take it with a grain of salt.
Not only does mysql silently truncate (and I just tested this on mysql 5) If you insert 2006-2-30 into the date field, i just completes the insert and makes the date 0000-00-00. Go Go Data integrity!
What do PostgreSQL users do for replication? I'm a MySQL admin who would really like to be able to switch to PostgreSQL, but we need to be able to have several slaves hanging off a master, and have everything replicated in as real-time as possible (but asynchronously) to the slaves. I have spent some time looking for how to do this in PostgreSQL but have found each solution lacking. The "most popular free" one, according to the PostgreSQL faq, is "Slony-I", but from what I could find it doesn't replicate schema changes to the slaves. What happens to your replication when the slaves sees an update to a column/table that doesn't exist on the slave? Slony also doesn't replicate "large objects"; I don't know what they are, but as a MySQL admin who has been replicating our databases for many years, I have a hard time imagining adjusting to limitations like these.
Most of the other options I found were abandonware, undocumented, didn't work with PostgreSQL 8.x, etc. I looked at commercial solutions, but they were similarly a mess. Specifically, here is my survey:
* pgpool -- Max 2 servers, and they're not really in sync---commands like now() or rand() will be executed independently on the mirrored machines, causing them to have different data.
* Slony I -- DB schema changes not replicated, nor are "large objects"
* PGCluster -- Synchronous multi-master. We don't want synchronous, and don't need multi-master. Documentation patchy, didn't appear to be currently maintained.
* CommandPrompt "Mammoth" -- Documentation "in the works". PostgreSQL 8.0.7. Tables can't use "inheritance". Schema changes not replicated (at least not table creation, not sure about the rest). Only 1 db replicated, not all dbs. Tables must have primary keys. Have to list tables in config file.
* Bizgres/GreenPlum -- Buzzword-compliant website, but website was broken when I looked for details. The "Community" is inactive---forum is barely used, questions are unanswered.
* PostgrSQL Replicator -- Poorly documented. Only mentions up to 7.x. "News" is from 2001.
I'm not ragging on PostgreSQL: I'd really like to be able to migrate to it. I just fear that when replication is done in a third-party fashion, it loses the tight integration with the dbms necessary to make it work truly seamlessly, and that it isn't maintained as well as the core product.
Perhaps this comment is off-topic, since the post is about a new release of PostgreSQL, not asking for questions about its individual features. But this is the one feature I look for in each new release, and the fact that I couldn't find any good solution makes me wonder if it's because I missed the one great one that people actually use.
You have to be careful when you ask a question like that.
What's faster, a Ferrari or a semi-trailer truck? If you are transporting a bunch of bannanas, the Ferrari. If you are transproting 50,000 pounds of bannanas, the semi wins.
In other words, the problem with your question is there is no single thing that is "speed". There's only speed to do a certain class of tasks.
Post may contain irony: discontinue use if experiencing mood swings, nausea or elevated blood pressure.
However, shipping with that setting disabled doesn't do much to improve MySql's data integrity reputation.
Not only that, one of the major selling points of MySQL is that it has many applications. If you deviate from the standard configuration, many of those apps will break. That's one of the problems with the "configureware" mentality, just like in PHP, except that MySQL is lower on the stack so it's worse.
Social scientists are inspired by theories; scientists are humbled by facts.
How fast is it against MyISAM?
/dev/null. It runs fast as hell, and the data integrity is basically the same as MyISAM.
I've managed to get my PostgreSQL installation tuned to very high speeds simply by switching the database disk over to
Should be modded up.
Now for the MySQL fanboi's, I do have to ask: why not use SQLite for the same purpose? Either you need a dumb data store or you need a Real Database. If you need a dumb data store, why not go for the one that does the best job of being a minimal data store - and use SQLite? If you need Real Database features (and I do), MySQL just hasn't caught up to PostgreSQL, and is even losing ground, after all this time.
The hole in what I'm saying, of course, is replication. PostgreSQL 8.2 looks like it's making progress in this respect. I haven't played around with warm stand-by's, but I'm sure someday I'll need it. When I do, log shipping looks like it will do nicely!
Expanding a vast wasteland since 1996.
I worked a lot with Oracle, and then joined an open source project that started using PostgreSQL. The project is a billing system, so is data intensive. What a great little database PostgreSQL is. And that was back in th 7.x version.
Actually, jBilling http://www.jbilling.com/ now runs in many databases but still PostgreSQL is holding its ground against Oracle and other heavyweights. Those extra features that Oracle says you need and charges you an arm and a leg, are really not needed in most applications.
Cheers,
Paul C.
Sr Developer
http://www.jbilling.com/ - The Open Source Enterprise Billing System
actually, they used innodb, and yes, Postgres scales much better than MySQL, but MySQL is a little more streamlined for low-volume jobs.
--That's the point of being root, you can do anything you want, even if it's stupid.
It sounds like you just don't know how to deal with FreeBSD. That would explain the poor performance you experienced, and how it is completely contrary to what we've found.
For the heavest application at my last job, the load pattern was very query heavy, although the application stored intermediate results in temporary tables. This application is heavily threaded, creating two threads per user connection, plus the MySQL thread, so we're talking like 150 threads created & destroyed per second.
Our original platform was Solaris, and performance was excellent (well, excellent considering the dog-slow CPUs that Sun makes).
We eventually migrated to Linux, but this was possible only after the new thread libraries (well, new at the time). Performance then was quite good.
We found MySQL under FreeBSD basically unusable under heavy loads.
We never tweaked any of the systems. We did try a few thread libraries under FreeBSD, but they all sucked.