Slashdot Log In
PostgreSQL 8.2 Released
Posted by
kdawson
on Tue Dec 05, 2006 07:10 PM
from the faster-so-they-say dept.
from the faster-so-they-say dept.
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.
This discussion has been archived.
No new comments can be posted.
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
Watch out, MySQL. (Score:5, Interesting)
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.
Re:Watch out, MySQL. (Score:4, Insightful)
For us, PostgreSQL is a lot slower than MySQL on the same hardware. But our workload is not typical by any stretch so YMMV.
Try comparing PostgreSQL and MySQL, both running on Linux and I'll think you'll be surprised.
Parent
Re: (Score:2)
Well, PostgreSQL launches a process per connection, so I don't see how that could explain the difference. Or are you saying that threading is slower than using processes?
Why are you so sure it's the threading, when he gave no details? If he had consultants coming in, most likely he would have a connection pool if that would have helped. You appear to have latched onto this explanation because MySQL must always be fa
Re:Watch out, MySQL. (Score:4, Interesting)
IMX, since about 7.3-7.4 PostgreSQL runs just as fast as MySQL under any significant load. It simply scales a lot better than MySQL seems to.
I will say that if you've just recently switched to PostgreSQL that you should be sure you read the documentation on configuring the server [postgresql.org]. While the default installation of MySQL is to use as much resources as necessary, PostgreSQL's default install is extremely conservative. By default it only allocated 1 MB (yes, one megabyte) for working memory. If you've got more than 32 MB of RAM, you're probably going to need to edit some config files to see any reasonable performance. Try running a VACUUM VERBOSE to determine how many pages or entries you need in your FSM. That's something that needs to be reconfigured on a production system after it's been in place for some time. If you do strange things like mass DELETEs or TRUNCATE TABLE, you'll also need to VACUUM more often.
The .org root DNS servers run on PostgreSQL, so it's not a problem with the RDBMS itself. Postgre has been repeatedly criticized for being so conservative with the default installation settings. I think they should have some configuration tools (in the Windows installer especially) that helps you to make somewhat more sane configuration settings.
The typical response from PostgreSQL devs on the subject is "yeah, if we turned off fsync [opengroup.org] on our DB it'd run real fast, too". This is partially why PostgreSQL seems to run slower than MySQL on databases that have lots of INSERT and DELETE queries.
I no longer see any reason to ever use MySQL. It's more popular, but I find PostgreSQL, Firebird, and SQLite cover the range of needs so much better. MySQL is great to learn on, but, well, it's just annoying once you really understand the first things about relational databases.
Parent
Re:They moved to FreeBSD from Linux. (Score:4, Interesting)
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.
Parent
Re: (Score:2)
Re: (Score:2)
Every time I see the words "MySQL" and "Enterprise" next to one another, it really gives me a good laugh. Why, it's almost as ridiculous as suggesting that SQL:2003 Window Functions are critical for business reporting.
Re:I think you're full of it. (Score:5, Informative)
Yes, it's missing description on how exactly they set up MySQL. MyISAM? innodb? So take it with a grain of salt.
Parent
Re:I think you're full of it. (Score:4, Informative)
Parent
Re: (Score:3, Informative)
Having used both, I can tell you phppgadmin is a bit more polished than phpmyadmin. Neither are particularly wonderful ways to interact with a database, but if you're stuck on a no-console web host, I'd much prefer to have the posgres/phppgadmin combo.
Re:Watch out, MySQL. (Score:4, Informative)
webmin anyone? [webmin.com] or
this if you want a non-web version [pgadmin.org]
Parent
What PostgesSQL really needs. (Score:2)
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.
Performance? (Score:3, Insightful)
Re: (Score:3, Interesting)
How fast is it against MyISAM?
I can't remember where I heard it or who said it, but I once heard someone say words about MySQL to the effect of "if you ignore all the things that make a real database a database, you can make it really fast." Now, I get that lots of web hosts use MySQL and that it is the dominant free database out there. However, there is lots of insight in that statement. Now, in 99% of the cases where MySQL is used, it probably works great with few hitches. However, I'd rather trust
Re:Performance? (Score:4, Informative)
Parent
Re: (Score:2, Informative)
Re:Performance? (Score:4, Insightful)
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.
Parent
Re: (Score:3, Insightful)
'Real' databases don't have a setting for 'screw data integrity'. Data integrity is kind of one of the central points of a relational database.
It just shows it's background as a toy, not a real database.
Re:Performance? (Score:4, Insightful)
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.
Parent
Re:Performance? (Score:5, Funny)
I've managed to get my PostgreSQL installation tuned to very high speeds simply by switching the database disk over to
Parent
Re:Performance? (Score:4, Insightful)
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!
Parent
Re: (Score:2)
Well, you should probably consider the planner too. After all, if it's using a dumb plan, or if it is lacking a "feature" that allows it to choose an efficient plan, even a "slow" database will be faster. Remember, optimizing the algorithm is usually much more important to performance than reducing the parsing time of a query.
Example: You need to go 15 places all over town today. Is it faster to take a Fararri and visit in a rand
Gotta love it... (Score:5, Insightful)
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)
Re: (Score:2, Insightful)
Of course, using the extra stuff the databases support (PL/SQL, T-SQL, etc), we manage. But for example, the "workaround" for the window functions are not only ugly, but often quite misunderstood, on top of being difficult to use through dynamic sql (if thats your cup of tea). I keep seeing people using inefficient paging methods in SQL Serv
Re: (Score:3, Interesting)
Practically the only informative part of this post is focusing on the perceived negative (which is a dubious one, IMHO).
Never mind that Postgres has actually turned out some nice feature advances in this release, although they don't make for good marketspeak bullet points. There have been advances in performance, table partitioning, clustering, query logic, user-defined functions, etc... pretty much every area of "enterprise" database dev
Re: (Score:2)
You are making some assumptions here. First, you are assuming that a feature cannot be implemented before it makes it into a standard, which is not necessarily the case. There are other paths, e.g. the idea gets published in a journal, the relational theory geeks at several leading vendors pick it up, several incompatible implementations are created by different vendors, and
Re: (Score:3, Interesting)
How would we know? We have never yet seen a DBMS that really implements the relational model (at least, not in the normal world of business software). Show me the word 'relational' in the SQL standard, anywhere. What we have is all sorts of incredible complication to work around the fact that SQL itself is a damaged and confused (and at times contradictory) approach to the prob
Re: (Score:2, Interesting)
Maybe it's because the thing can't be made to work, and its limitations (i.e. being equivalent to first-order logic, a limitation not in SQL DBMSs) make it silly even to keep trying.
Re: (Score:3, Interesting)
Ahh yes, the old canard. Actually, several companies and individuals have implemented the relational model MUCH more faithfully than the typical SQL vendor. The problem is not one of difficulty, but rather of popularity and marketing.
In fact, several solo-developer projects have implemented it on the logical level much better than your typical SQL vendor. The problem is that
Re: (Score:3, Interesting)
> > > managed to implement despite the 25 years it's been around?
> > Ahh yes, the old canard. Actually, several companies and individuals have
> > implemented the relational model MUCH more faithfully than the typical SQL
> > vendor.
> Name one, and make sure it's one that's disallowed NULLs completely. Date,
> Darwen and Pascal's fear of recording states of ignorance is ill-founded in
> r
Re: (Score:3, Interesting)
CJ Date actually tried to generalize the concept of NULLs into "special values" in a domain. He argued that NULLs cause confusion in 3VL because NULL can mean different things. Sometimes it means "unknown", other times it means "not applicable". And in an outer join, it's not clear at a
Re: (Score:2, Informative)
Postgres rocks (or keeps track of them in this case). It works, and it was done 100% free of window functions.
Re: (Score:2)
Replication? (Score:5, Informative)
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.
Re: (Score:2)
Re:Replication? (Score:5, Informative)
PITR recovery and log replication may work in 8.2; but I agree with the posters who complain that there is no easy replication for postgresql.
Parent
Re:Replication? (Score:5, Informative)
You're a DBA and you don't know what large objects are?
Oh, right. Not really a DBA
Let's see:
Others listed are older and not relevant.
Funny, I fear a database that has only rudimentary data integrity checks. Here's the real question for you: Why do you need replication? It doesn't magically work the way you think it does, even in MySQL [mysql.com] (see under "Problems Not Solved"). Quote: "MySQL's replication isn't the ideal vehicle for transmitting real-time or nearly real-time data". Every replicated database can lose synchronization and no one can honestly guarantee otherwise. Even Oracle.
Slony-I will pretty much give you what you already have. My guess is that you don't really need replication at all; hot standby servers will suffice in case of failure. The rest comes down to query tuning or faster hardware (or a database that does faster nontrivial queries, like PostgreSQL). (And don't complain about costs if you're already buying servers for replication. If you have real data that's making you money here, hardware is cheap; if you don't, you probably don't really need any of this to begin with.) If you need true realtime synchronization, replication is not an option.
Finally, while I'm not a MySQL fan, since you don't seem to give any real reason for wanting to migrate, why bother? You already have a working system and hardware investment. If it ain't broke, don't fix it. If it comes time to upgrade down the line, and the features justify the move, then maybe consider it.
In summary: meh.
Parent
Re: (Score:2)
Two: If you don't even know what large objects are, why do you have a problem with this?
Perhaps he thinks he misread that as the direct english meaning: "something large". Slony can store and replicate big stuff, it just won't replicate things that aren't tuples. But tuples in PostgreSQL can be big and efficient.
PostgreSQL replication will force you to consider the real consequences of your choices in various situations. MySQL replication will say that it's worki
Re: (Score:3, Informative)
Re:Replication? (Score:5, Informative)
That's a feature, not a bug. That means you can have DB1 be master for Table1 and slave (subscriber) for Table2, and DB2 be master for Table2 and slave (subscriber) for Table1. You can also chain subscriptions to make a hierarchy, which allows for very good scalability.
Oh, and if you want to replicate schema changes, use the Slony-I "execute script" command. It will lock down all the tables as necessary and synchronize the changes so that nothing gets out of order. Slony-I keeps everything transactionally consistent.
Slony also doesn't replicate "large objects"
Ignore that. A large object is basically an interface to a file over the PostgreSQL protocol. You don't need them to efficiently store large amounts of data. Put a GB into a text type if you want (or bytea type for binary data).
I encourage you to take a closer look at Slony-I. It's what the
Parent
Re: (Score:3, Informative)
The same as everybody else who stores text in a relational database. Use external indexing, such as Lucene, which actually has some features you'd want for non-trivial full text indexing and searching, such as stemming.
Awesome (Score:3, Interesting)
We've only got a small database (17 million records [blogs.com] 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 [getindi.com], too, which is going to get pretty big (I hope). Lively times!
Way to go PostgreSQL (Score:3, Informative)
Actually, jBilling http://www.jbilling.com/ [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/ [jbilling.com] - The Open Source Enterprise Billing System
Reporting (Score:3, Informative)
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?
Re:Real Men don't use Window Functions (Score:4, Funny)
Parent
Re: (Score:3, Interesting)
Earlier versions of the MySQL manual included claims that certain missing features (considered essential for SQL-compliant RDBMSs) were useless or even harmful, and that users were better off without them. One section, entitled "Reasons NOT to use Foreign Keys constraints" [sic], advised users that relational-integrity checking was difficult to use and complicated a database application, and that its only useful purpose was to allow client software to diagram the relationships between database tables. [13] Another section claimed that a DBMS lacking transactions can provide data-integrity assurances as reliably as one supporting transactions--conflating the issue of transactional integrity with that of saving data when the database server loses power. [14] Since these claims contradicted basic principles of relational database design, they caused MySQL to be ridiculed by some database experts. Regardless of whether they were right or not, these claims are omitted in more recent versions of the manual. MySQL today allows some support for previously-dismissed features of relational integrity checking and transactions.
(From Wikipedia [wikipedia.org] and archived MySQL manuals [univie.ac.at])
Re:bitmap? (Score:4, Informative)
Parent
Re: (Score:2)
PostgreSQL currently uses bitmap scans to combine indexes (which means fewer multi-column indexes are necessary), and also to reorder the results of an indexscan in disk block order so that it can get blocks in disk order with better cache behavior.
Re: (Score:2)
Re: (Score:2)