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.
I should know better than expect correctness in AS, but come on...
"Piter, too, is dead."
Sure would be nice to get bitmap indexes one of these days
God Fucking Damnit
PostgreSQL it is still missing.
Do you even lift?
These aren't the 'roids you're looking for.
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.
Armchair moderating ftw.
So how does this release compare with MySQL 3.23? Because my webhost is still using it, and I need to be able to argue that PostgreSQL 8.2 is infinitely better than MySQL 3.23 for them to provide this also.
Correct me if I am wrong, but wasn't MySQL 5.x supposed to include transactions and triggers among other things? I'll be the first to admit that I don't really keep up on the Postgre/MySQL battle, but you might want to keep up on current technology if you are going to make an inflammatory post like that.
How fast is it against MyISAM? (MySQL's main selling point for a lot of people)
I'm a fan of PG, but your post sounds like you're just trash-talking MySQL. (Don't get me wrong, MySQL is a joke for sure.) I'm also doubting the 23% increase in performance, but I haven't made any comparisons personally.
*whoosh* Parent post was trying to be sarcastic. Yes, the later versions of MySQL has most of those.
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.
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.
I don't know if I fall under the fanboy sign in this case. I know the differences between postgres and mysql, and use mysql more frequently than postgres.
In any case, I never argued that those things were bad for performance, but I did argue this:
More often than not, subselects and triggers make people lazy and generally patch up cases where non normalized data should be fixed. They encourage things like db/app bleedover, not fully understanding joins, and not fully implementing data normalization (where appropriate).
Fairly often, and certainly less so in mysql (but probably now moreso), I run across cases where postgres and oracle queries used slower subselects, bad non-normalized data or should-have-been-the-applications-job triggers where they were completely inappropriate and really demonstrated a lack of knowledge of any RDBMS.
In mysql, you can do something like this: update x set y=@k:=@k+1 order by z (syntax is probably a little off)
Can you do something similar in pg in a single query?
Along that line, what do PG users use for full text searching? Like for ~5mil rows each containing ~12 words.
fts - pretty simple (it stores duplicate words, and breaks down words too much, like AMAZING will store: MAZING AZING ZING ING NG which takes up way too much disk space)
tsearch2 - afaik doesn't support wildcard searches, like for AMAZ*
Do you use those? Or roll your own? Or what?
> 8.2 is positioned as a performance release.
We've only got a small database (17 million records or so), and PostgreSQL 8.1 has been handling it fine. But I'm still looking forward to seeing how 8.2 improves things.
And we're using it in another production system, too, which is going to get pretty big (I hope). Lively times!
The Army reading list
Equivalents to Query Browser and DBDesigner4/Workbench.
Use them. They rock. Query Browser does everything I used in phpMyAdmin and much more. DBDesigner4 and and it's (currently rather unstable) replacement, Workbench, are extremely useful for designing/modifying databases. I prefer PostgreSQL for speed, stability, and features, but I develop in MySQL just because of those tools.
"It ain't a war against drugs.it's a war against personal freedom" --Bill Hicks
I'd be surprised if DB/2 didn't also have it.
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
Apparently the submitter has not been visited by any of the plethora of reporting tools vendors who will tell you (without you asking) how crappy the built-in stuff is and how great their stuff is.
Also, given the text, isn't Oracle and DB2 also missing those critical SQL:2003 Window Functions?
I'm using postgresql since the 5.x days, when it indeed was slower than mysql.
But as a developper, I never accepted the shortcomings of the non-standard and really incomplete sql syntax of mysql.
The command line tool psql with tab-completion of sql syntax and less style output of query results convinced me to switch in a second.
PostgreSQL never let me down, whereas I often had problems with mysql databases. (e.g. non working databases after upgrades)
Not to mention the semi-free open-source license of mysql.
What's all the fuss about mysql again? Mysql is a commercial product that is and was inferior to postgresql since the very beginning. The performance gain was small compared to the missing features.
That's just my two cents, but I think the mysql guys did a great job marketing their product and fooling everybody into using mysql.
If you really have the knowledge of DBMS, you should be thankful for the options! You sound like abandoning features to force you into discipline.
Sometimes you have to balance development time against performance, not to mention the statements you as an administrator type by hand, where performance might not be an issue.
And in addition to that, I can assure you that there are lots of cases where subselects are REALLY fast in postgresql. Even faster than aggregates and group by. Never underestimate the power of the query optimization in postgresql, since 8.0 this is really good.
... is create a smallint index on an int column ;-)
Off-topic. default mod point for an 'Anonymous Coward' is zero... even if it is a good post :-) Most moderators don't like to give mod points to ACs because they would rather reward or punish registered users... otherwise it's like throwing away good mod points.
-- I ignore anonymous replies to my comments and postings.
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.
Not to mention the semi-free open-source license of mysql.
GPL?
I'm a rabid FreeBSD advocate, but MySQL performs badly under FreeBSD. This isn't so much a problem with FreeBSD as it is with MySQL, which is very Linux centric. I have no gripes however as I dumped MySQL before I dumped Linux, but I would recommend that if you are going to have a stand-alone server for MySQL that it should be running on a linux distro.
All the major databases have a way of temporarily disabling integrity checking for bulk processing; it's limited to a single transaction, and the integrity/validity checks are still performed after the task is complete.
That's vastly different from turning off all checking globally and silently eating problems.
GP asked a simple question about what replication strategies are used by pg shops, and some asshole like you responds in a tone like yours.
You could have just answered the question. It wasn't necessary to be a dick about it.
Also, you might be interested to read a bit about MySQL Cluster which is different from their replication solution. Pretty neat stuff.
Also, I do agree with you that GP gave no indication that MySQL was failing to meet their needs. MySQL doesn't meet everybody's needs and neither does pgsql (or Oracle for that matter). But changing databases for "fun" is a horrible use of resources.
They don't grade fathers, but if your daughter's a stripper, you fucked up. --Chris Rock
With Oracle and SQL Server you can turn off constraints for an indefinite period of time. It may be used mostly for single bulk transactions but I've seen it used for months at a time on Oracle.
Oh, ok I admit, I completely missed that and deserve a good flogging. He makes a good point when read in a sarcastic light.
I'm sure you could cope without, but it seems like this is actually included in some shape or form.. The release notes mention:
Aggregate-function improvements, including multiple-input aggregates and SQL:2003 statistical functions
If using this setting requires major remedies to revise applications and retune them, that may be no less work than redeploying on something that has mature support for data integrity...
If you're not part of the solution, you're part of the precipitate.
(From Wikipedia and archived MySQL manuals)
Do you even lift?
These aren't the 'roids you're looking for.
On a twenty server cluster of SPARC kit with tens of processors each, MySQL can't touch Oracle for distributive processing and load balancing. Second, the point of dropping constraints for a few months was to allow the the database design to reach maturity in a development environment. Before the project reached production, constraints were turned back on.
Does PostGre have something like PhpMyAdmin, a web-browser-based front-end to MySql? I got kinda used to it for creating and managing MySql schemas.
Table-ized A.I.