Linked by Thom Holwerda on Fri 6th May 2011 21:00 UTC
Databases 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.
Thread beginning with comment 472120
To view parent comment, click here.
To read all comments associated with this story, please click here.
GatoLoko
Member since:
2005-11-13

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.


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

Reply Parent Score: 1

rycamor Member since:
2005-07-18

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.


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.

Reply Parent Score: 2

GatoLoko Member since:
2005-11-13

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.


To tell you the truth, that was something I forgot, but
checked it right now an it's using InnoDB

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)


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.

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.


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.

Reply Parent Score: 2

pantheraleo Member since:
2007-03-07

I've read the same thing a lot of times, but then I try to switch and find PostgreSQL a lot slower.


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.

Reply Parent Score: 2

GatoLoko Member since:
2005-11-13

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.

Reply Parent Score: 1

JAlexoid Member since:
2009-05-19

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.

Reply Parent Score: 2