The release of the first beta of version 9.1 of the open source PostgreSQL database has opened a new era in enterprise-class reliability and data integrity that can compete with the big names, say its developers. CIO recently interviewed Josh Berkus, Kevin Grittner, Dimitri Fontaine and Robert Haas about PostgreSQL 9.1 and its future.
PostgreSQL is by far my favorite database to work with. The developers have done an amazing job, it’s incredibly robust, stable, fast, and usually is light enough on recourses (though others are lighter). All in all, a very well rounded db. My first pick when starting a SQL project (followed by SQLite)
Every time I want to use a SQL-database I come back to PostgreSQL, it easily is the most standard compliant, most consistent database of them all.
With PostgreSQL 9.1 it has built in replication, combined with pg_pool II or maybe the new replication features in PostgreSQL 9.2 it does everything 98% of the people would want or need.
Ohh, and it’s free and open source. 🙂
Some people use MySQL, I tried that, I don’t want it anymore. They are now starting to add features again (like memcached compatible protocol support), which is good, competition is good.
I’ve read the same thing a lot of times, but then I try to switch and find PostgreSQL a lot slower.
Try to run a basic Drupal 7 (for example) site with both databases on the same hardware and the speed difference is 2:1 comparing a default MySQL with an “optimized” PostgreSQL. I may have made some mistakes in the optimizations because I’m no expert on Postgre, but it’s faster than the default config.
It would be nice to get better defaults, and a simplified “basic optimizations guide” of any kind.
The software may be the best, and the documentation may be good, but it lacks proper “starter/basic guides”.
Edited: quoted the wrong section.
Edited 2011-05-07 02:26 UTC
One question: are you using InnoDB or MyISAM tables on the MySQL version? If you are using MyISAM, then the comparison is apples-to-oranges, because ISAM has almost no data consistency checks built into it, so of course it will be faster for basic operations.
Secondly, it is unfortunate that most PHP web software is built around MySQL first with PostgreSQL added as an afterthought. MySQL leads you into some (at times bizarre) design decisions that just don’t make sense with PostgreSQL (or most SQL DBMSs)
Also, historically PostgreSQL’s big Achilles’ Tendon has been the performance of COUNT(), because of how Postgres handles MVCC. If Drupal relies on COUNT() in many places (which wouldn’t surprise me), that alone might account for the speed difference. For this reason, developers doing large databases in Postgres tend to use workarounds, such as a trigger which updates a standalone SEQUENCE value. There has been a lot of discussion in the PG dev community about better ways to handle COUNT() (covering indexes, for one), so this might be changing, if it hasn’t already been done in 9.1.
To tell you the truth, that was something I forgot, but
checked it right now an it’s using InnoDB
Don’t know how well Drupal7 uses PostgreSQL, but it was just an example of something I tried recently to determine how to improve my site speed. Tested it with MySQL, PostgreSQL and SQLite, and the speed compassion was 2:1:1.
Testing other CMSs the ratio varies, but MySQL is always faster.
Well, that’s something that most “end users” can’t control, since most people gets some software and set it to work without ever looking at the code.
In small databases made by myself I can’t feed any speed difference, but I’m positively sure my code is wasting time, but when you use something that “supports PostgreSQL” and there is a noticeable performance difference, it’s at least annoying.
I’m not saying it’s PostgreSQL’s fault, but if that’s a known sore spot and something many people use, it should get some attention to try and make it better.
Maybe when I get to test 9.x there will be some improvements.
Keep in mind that out of the box, PostgreSQL is configured very conservatively, using extremely “safe” defaults. It’s possible to greatly improve the performance by tuning it.
As I said, it was running an “optimized” config, or at least an attempt to it. Don’t know how well tuned it is.
Was this on Windows without the use of persistent connections ? Because any of those 2 also slow it down in comparison to the opposite.
MySQL oriented systems end up slower on all other DBMS’es. That will include a optimised for speed Oracle system.
And if you compare InnoDB performance to PostgreSQL performance you’ll find that InnoDB falls short. But with unoptimised software, there is no way of getting performance out of it.
As long as you do simple things like “select * from table” type of queries MySQL is often faster then Postgresql, but as things get more complicated with a couple of joins, or things like relational division Postgresql often is faster. My guess is that Drupal is designed with that in mind, meaning that you do the heavly lifting in PHP instead of in the database, and thus can’t make use of the additional speed Postgresql offer you.
Postgre is much more grown up than mysql. I frequently find myself pulling my hair out because of shortcomings of mysql and keep wishing I could use postgre in more hosted apps.
But mysql has become a defacto standard, and postgre isn’t usually an option. It’s very odd how the market chooses winners and losers, it rarely seems to be based on bringing the best product to market.
Sometimes postgre even rivals oracle in functionality. I don’t expect an open source database to convert too many oracle clients, but it sure would be nice to phase out mysql.
If you give oracle enough time i’m sure it will happen. They’ve run a bunch of sun’s other projects into the ground already.
As budgets get tight a lot of companies are moving sideways. Not to pure postgresql but to Postgres Pus from enterprisedb. Its Oracle interface compatible.
Next lot of features will make the dual between Oracle and postgresql hotter. Replication has been Postgresql historic weakness when compared to oracle.
Yes the global down turn fuels open source.
PostgreSQL 9.0 already got the first of 2 parts of integrated replication support. Consistent, fast and efficient (Continuous Streaming Replication).
PostgreSQL 9.1 will add more replication support which means it will wait for the transaction data to be copied to the slave server before letting the application know the data has been committed (this is slower ofcourse, but is optional).
PG-Pool II also allows for queries to be load-balanced over the master and slave server and already worked with PostgreSQL 9.0
Slony-I allows for replication on a per table (and similair boundries).
Complete multi-master consistent read-/write-scaling of PostgreSQL has been available in seprate projects, like http://postgres-xc.projects.postgresql.org/ but has made it in to normal PostgreSQL yet.
Some commercial companies like: EnterpriseDB and Greenplum also sell products and support based around PostgreSQL or products which extended the abilties of PostgreSQL.