Slashdot Mirror
PostgreSQL 8.3 Released
jadavis writes "The release of the long-awaited PostgreSQL version 8.3 has been announced. The new feature list includes HOT, which dramatically improves performance for databases with high update activity; asynchronous commit; built-in full text search; large database features such as synchronized scans and reduced storage overhead; built-in SQL/XML support; spread checkpoints; and many more (too many major new features to list here). See the release notes for full details."
Would that be POSTGR~1.SQL?
The one thing that has stopped me from picking up Postgresql yet is that I can't do cross database joins on the same server. Should a user have the same permissions on two separate databases on the same server, a properly constructed query should be able to join across multiple DB's but they still don't implement this yet that I am aware of.
This is my sig. There are many like it but this one is mine.
I'm a postgresql fan, I've considered it a superior database for years.
However, it seems every client I come into contact with (I am a freelance software designer) seems to believe mysql is the only open source database available and certainly the best one for all jobs.
Mysql is great (or at least, was great) for fast connection times and speed but for a stable, feature-rich excellent database, postgresql has always been ideal.
It's just a shame no one seems to be aware of it.
someone will make a comment regarding how sad the story of Postgres's popularity is, and how they've seen German folk music with more of a following.
this was a new feature for Oracle with 10g R2 also - and as a DBA I can only shake my head and ask "why?" Why would you want to drop the durability part of ACID? Why would you risk losing data for speed? There are so many ways to tune things and speed things up without taking such drastic measures. I know I'd fight tooth & nail before I'd turn this on in anything I managed. I just hate to think that someone with less understanding is going to think of it as a 'go-faster' button and then blame postgres when they lose something important.
It's hard to believe that's how Micronians are made. Why don't we see it right now by having you both kiss one another?
8.3 had me at "full-text search".
Now, please excuse me while Postgres 8.3 and I go take a little alone-time in a dark closet.
As far as databases go I don't think it gets much better than Postgres and I reckon over the years I've tried most popular databases. What I really don't understand though is why Postgres doesn't own more of the database market. Sure it was a bit slower than MySQL a few years ago but the benifits that you reaped for that slightly slower speed far outweighed the cost. The difference know is, I would say, much small and less significant.
I can only assume that MySQL keeps it's large market share because it has commercial backing and therefore good support. I'm sure there are plenty of products taht don't require that level of support though.
I used to have a better sig but it broke.
There are so many Open Source databases (MySQL, MaxDB the last GPL version, Firebird, Postgresql, Ingres Community Edition, hsqldb and H2) that it is hard to know which ones implement what, which ones are useful for what, or which ones are optimal for what. Simple benchmarks (a) rarely are, and (b) usually want to promote one specific product over another. There are standardized tests, for a small fortune and by a relatively closed group who probably don't have a fraction of the range of experiences of databases in the real world, so cannot possibly be trusted to authenticate a standard or measure that could be used to compare databases.
We could really do with some serious side-by-side evaluations of these database engines, or at least decide what such evaluations would need to be to be actually useful. (Hey, maybe CmdrTaco can add a comparison section, get it sponsored by Which? or some other consumer guide, and have some of us run evaluations. It'd have to be done well to not be flamebait, which I think might rule me out, but if it could be done, it would be hellishly useful.)
It's a small world and it smells funny; I'd buy another if it wasn't for the money; Take back what I paid (SoM)
Are you thinking like you'd do in SQL server (IIRC) or MySQL, where you have a db reference in the table list (i.e., SELECT * from db1.table1, db2.table2 WHERE [join clause])?
you'd probably just want to use a schema for that; the concept maps more or less the same way.
The 8.3 release notes list the Bucardo project http://bucardo.org/ for multi-master replication. I haven't used it... is there something that it is lacking that you think would be addressed by bringing it into the core code base?
Asynchronous commit is very useful in applications where the thing that's important about the data is its statistical distribution, and not the individual data points per se.
Yeah, if only for those darn inconvenient facts demonstrating that PostgreSQL is faster than MySQL, particularly under load. Note that the benchmark was PostgreSQL 8.2. Now note that 8.3 is up to twice as fast as 8.2. I think the polarity on your order of magnitude performance difference should be reversed.
Of course, if you actually care about data integrity and database features, there's not contest at all. But the performance gap is now non-existent, if not completely reversed.
Higher Logics: where programming meets science.
Oh give it a fucking rest. MySQL is 10-15% faster on simple queries, with few threads, on a single disk.
And that's only with MyISAM (in which case, why bother with a database server? SQLite is probably enough for your needs).
sic transit gloria mundi
Going from 8.2 to 8.3 in postgresql is not a 'minor' release. It's quite a major step with a lot of new features.
Would it make a difference to you if they bumped up the version number to 9?
8.2 was released over a year ago - this is not a minor revision.
sic transit gloria mundi
"A dump/restore using pg_dump is required for those wishing to migrate data from any previous release"
Postgres have a habit of making you do this to upgrade and it really sucks. I understand the reasons behind it, but that does not reduce the amount of suck, especially for a large database.
Does anyone know of a good, semi-recently written book on PostgreSQL? Everything I find is from at least 3 years ago. Is it that PostgreSQL hasn't changed much, barring this release, in the past few years?
\033:wq!
Fast
And
Sorta
Transactional
PostgreSQL is
Fast
And
Really
Transactional
And under heavy loads with normalized db's PostgreSQL's planner does *much* better than MySQL's.
LedgerSMB: Open source Accounting/ERP
I have used PostgreSQL as my primary db since 2000 (version 6.5!) and I have watched it for a while.
PostgreSQL had a number of problems in the past which made it hard to work with including:
1) No ALTER TABLE DROP COLUMN support and other things needed for prototyping (fixed in 7.2 iirc)
2) Issues with dependency tracking in text SQL dumps (fixed in 8.0) meaning that some times one had to hack dumps to get them to restore properly.
3) maintenance tasks required exclusive locks on tables (corrected sometime in 7.x)
4) other things which generally made it somewhat user-unfriendly.
Note that all of the above issues have been corrected as of three years ago. However by that point a lot of the small less serious appliations were more or less wedded to MySQL. For large, shared databanks, PostgreSQL and Ingress II (and maybe Firebird) are the only serious choices. MySQL fails to be useful as soon as you end up requiring the sort of thing described in the title of Codd's original paper....
LedgerSMB: Open source Accounting/ERP
If PostgreSQL changed their name to OurSQL it would be easy to remember, and a sound a lot less selfish than MySQL.
This issue is a bit more complicated than you think.
I think the disconnect is that PostgreSQL has a different definition of schema than you are using.
Dewey, what part of this looks like authorities should be involved?
Hi, I read that "MySQL does not uses several CPUs to execute single query - only multiple connections may benefit from several CPUs.". That was written January 6 2004 by Peter Zaitsev, then a full-time developer at MySQL AB, www.mysql.com. I found the quote at http://lists.mysql.com/benchmarks/45
Does anyone know if PostgreSQL supports a dual or quad CPU when it comes to executing a single query, or if MySQL now supports it?
The reason I ask is that I have a database with tens of millions of records and even 'simple' queries take a long time. Would it be beneficial to buy a 8 core machine, i.e. dual quad, over a single quad cpu?
Thanks for any tips or links!
You have made two very serious flaws with your thinking about how and why you chose MySQL. In general it's rampant with PHB thinking.
There is the obvious discussion you didn't have when you decided MySQL was better for your company. Why would you take something as important as your company database and leave it to a vendor to support? You have no in-house knowledge of your database. You have no back-up in the event that vendor gets into a contract dispute with you. And every time you need their support on anything you have to go through this slow interface of vendor management rather than just leaning over to the company employee named Roy who lives and breathes MySQL or PostgreSQL and say, "Hey Roy! Somethings wrong with the database. I need you to fix it right now. I'll get you a pizza for lunch."
The other error in your thinking is more elusive but this reminds me of a conversation I had with a room full of people regarding the expansion of Java at the potential cost of Perl developers.
"I can get a whole boatload of Java developers from overseas with a phone call and 72 hours. It's very hard to find 20 or 30 Perl developers for a project."
Nobody in that room had ever worked on a project requiring more than 5 developers. And they had worked on some largest Perl projects out there.
Subsequently I have found a lot of really bad Java developers.
I can have a similar discussion with MCSE in place of "Java developers".
The flaw is that you assume popular support equates itself to good support. I think the exact opposite is more likely to be true. If someone is in need of a quick job and quick bucks, they will take the fastest and lowest entry barrier solution. First comes Am-Way. But with a week of sitting in classes for each they can quickly become a Java certified developer, MySQL DBA, or dot-NET developers. All this in three weeks time and with the credentials to prove it. Are they any good at what they do? Not likely.
The contrary group of people are those who use PostgreSQL over MySQL or SQL Server, Linux over Windows, Perl/Python/Ruby over dot-NET or Java. They stay here and learn this less popular software because they enjoy it and find it interesting. It's generally not a decision based purely on money. It could be argued that Ruby isn't in the same camp as Perl & Python because it's having something of a feeding frenzy in the PHB camp. But that's Rails, not Ruby, and they are different.
It is fair odds that if I put out a couple inquiries I could find PostgreSQL support in Detroit, MI (not a software haven) within 72 hours and based on it's lack of a Marketing and PR campaign the person who shows up at the door will know more about PostgreSQL and how to get it working smoothly than the MySQL counterpart who shows up at my door. And I will only need one of them as opposed to hiring 2 or 3.
I'm a great fan of postgres but I ran into an irritating limitation recently; I replicate a database over a large number of very small nodes using slony. I really don't care about the integrity of the slaves - they're read-only to their clients and should I suspect they're corrupt I just reboot (they live in memory and the OS lives on a 1 Gb read-only flash drive). But postgres insists on having a WAL directory (pg_xlog) with chunks of 16MB in it. And that's big if you live in 128MB of ramdisk, and you can't turn that off. I mean, from my reasoning - the WAL isn't really used unless you do recovery; the versions of the data are in the db itself (otherwise we wouldn't need vacuum, now would we ?) So why can't I just configure postgres to not use WAL ? And then if the db is corrupt we just die. No, say the guys on IRC, you just have to recompile it with its hard-defined value of 16MB down to something lower. Yeah right. I'm not interested in hacks - I want a versatile RDBMS.
Religion is what happens when nature strikes and groupthink goes wrong.
The real advantage that MySQL has right now is the number of packages that are MySQL centric.
You need a Wiki? Odd are that it supports MySQL and can be made to work with Postgres. Need a content management system? Odds are that it was written for MySQL and might work with Postgres. So you probably already have MySQL in house so people tend to go with MySQL even when Postgres is a better solution even if for no other reason than why maintain two databases.
The only reason why we use Postgres for several of our in house programs is because I wrote those before we put up an in house Wiki or other web stuff. Postgres offered row locking back then when MySQL didn't. I loved transactions when I finally got to use them.
The sad thing is that if I did it over today I would probably pick MySQL since it is so popular and now supports row locking and transactions.
Postgres is the better system but it lacks the depth of support that MySQL does. Sort of like BSD vs Windows.
See my blog http://ilovecookes.blogspot.com/ for light hearted technical information.
A PostgreSQL schema is just a namespace qualifier; it functions just like MySQL's cross-database joins and is conceptually similar. It isn't a full copy of your database DDL.
One of the issues with this discussion is that there is a disjunct in the terminology used by MySQL users and that used by the rest of us.
:-)
MySQL uses "database" and "schema" synomymously (note that their "information schema" is a separate "database"). In that sense, PostgreSQL has long had cross-schema joins in the same way MySQL does. It is just slightly harder to set things up so that you create tables in the right schemas. (hint: SET search_path='schema_name');
In this way, I do a *lot* of work using cross-schema queries. They work like a charm on PostgreSQL.
What PostgreSQL calls a "database" is analogous to the entire cluster of "databases" on MySQL. There is no analogy to "PostgreSQL cluster" on MySQL (i.e. a set of databases controlled by the same process, each of which contains multiple schemata). For that matter, last time I checked, there was no equivalent on Oracle either.
So "PostgreSQL doesn't have an equivalent to MySQL's cross-db queries" is an issue of terminology rather than substance.
At the same time, as nconway points out, PostgreSQL's MED solutions do not optimize well across distributed queries (i.e. where parts of the queries have to go through connections to other databases/servers). For MySQL users, these are like Federated tables, and I would expect similar (or worse) optimization problems in this area on MySQL. Hence when you are trying to use PostgreSQL as a distributed database, you are in for headaches. Fortunately this is not what the OP asked for
LedgerSMB: Open source Accounting/ERP
I guess nobody appreciates humor here. Well, not if it's against the fanboys.
I'll stand by it though-- switching to MySQL from Postgres made my life significantly simpler:
1) you can install MySQL easily
2) MySQL has great vendor support
3) my experience is that MySQL performs significantly better in the general case (i.e. I'm not spending my entire life tweaking performance)
Many an honest thing were said in jest, I suppose.