Drupal: MySQL vs. PostgreSQL
I'm not very familiar with the use of PostgreSQL with Drupal, as I tend to use Pressflow with MySQL on most projects. I read about the advantages and disadvantages, but not sure how to compare a clustered DB with PostgreSQL and Pressfow running on Pantheon with MySQL optimized, perhaps on multiple DB servers. It looks like there are various issues with contributed modules & PostgreSQL, and that doesn't sound like much fun to deal with. So now I have a potential client who needs help with PostgreSQL in the mix, and I'm not sure if I should get involved. Here's the client's development/production environment: Debian Lenny (5.0) host environment Debian packaging for deployment Drupal 6 framework PostgreSQL 8.3 database Git source control Apache Solr 1.4 for search and related artist matching Amazon S3 + CloudFront for storage and streaming of videos FFMpeg for encoding of uploaded videos JWPlayer for playback (looking to replace this in a future phase)) Apache and Nginx web servers (although development can be done with just Apache) PHP 5.2 They know what they're doing! Here's what I read about PostgreSQL on Drupal.org: (From http://drupal.org/node/33171)
First off you have to ask yourself is it really worth it? In most cases no absolutely not, MySQL is just fine for sites that are small and want to remain that way, this includes personal sites and blogs etc.
PostgreSQL's real strength at least in my opinion comes from its ability to seamlessly cluster, thereby distributing the load among many DB servers rather than bogging down a single server. If you have a big website with lots of traffic and have the resources for multiple servers, then PostgreSQL is definitely the way to go.
This potential client has a lot of data, with the potential for a lot of traffic. Their current configuration may be ideal, as far as I know right now. And I've been really happy with staging development on WebEnabled, but they don't support PostgreSQL yet. I don't even have PostgreSQL installed on my local development environment! So I'm asking the Drupal community about this one. Perhaps I need to learn more about PostgreSQL, or I just need to stick with what I know best (as an intermediate at MySQL). And I may need some help from Developers who do know more. What do you think? Thank you, Kevin --- Quevin, LLC Quevin.com twitter.com/Quevin linkedin.com/in/quevin
On Wed, Jul 21, 2010 at 4:50 AM, Kevin Davison <kevin@quevin.com> wrote:
I'm not very familiar with the use of PostgreSQL with Drupal, as I tend to use Pressflow with MySQL on most projects. I read about the advantages and disadvantages, but not sure how to compare a clustered DB with PostgreSQL and Pressfow running on Pantheon with MySQL optimized, perhaps on multiple DB servers. It looks like there are various issues with contributed modules & PostgreSQL, and that doesn't sound like much fun to deal with. So now I have a potential client who needs help with PostgreSQL in the mix, and I'm not sure if I should get involved. Here's the client's development/production environment:
As per my personal experience, I feel Postgresql is much much faster then Mysql (both properly configured and optimized, include compiler optimizations). My site loads at lightning speed on Postgresql, with Mysql it used to near take about a minute. Please other Mysql fans, don't start flame wars here, its just my personal experience. Further, don't use Postgresql unless you have good knowledge of SQL and PHP, because many modules have SQL syntax which works only on Mysql, so you need to tweak those scripts to get them working with Postgresql (I have submitted a few patches for the same). -- Regards, Nilesh Govindarajan Facebook: http://www.facebook.com/nilesh.gr Twitter: http://twitter.com/nileshgr Website: http://www.itech7.com VPS Hosting: http://j.mp/arHk5e
Le mercredi 21 juillet 2010 à 11:49 +0530, Nilesh Govindarajan a écrit :
On Wed, Jul 21, 2010 at 4:50 AM, Kevin Davison <kevin@quevin.com> wrote:
I'm not very familiar with the use of PostgreSQL with Drupal, as I tend to use Pressflow with MySQL on most projects. I read about the advantages and disadvantages, but not sure how to compare a clustered DB with PostgreSQL and Pressfow running on Pantheon with MySQL optimized, perhaps on multiple DB servers. It looks like there are various issues with contributed modules & PostgreSQL, and that doesn't sound like much fun to deal with. So now I have a potential client who needs help with PostgreSQL in the mix, and I'm not sure if I should get involved. Here's the client's development/production environment:
As per my personal experience, I feel Postgresql is much much faster then Mysql (both properly configured and optimized, include compiler optimizations). My site loads at lightning speed on Postgresql, with Mysql it used to near take about a minute. Please other Mysql fans, don't start flame wars here, its just my personal experience.
Further, don't use Postgresql unless you have good knowledge of SQL and PHP, because many modules have SQL syntax which works only on Mysql, so you need to tweak those scripts to get them working with Postgresql (I have submitted a few patches for the same).
My preference goes for PostgreSQL also. Performances highly depends on schema, I don't know for Drupal. A common urban legend says that PostgreSQL is a lot faster on huge volumetry, and I tend to believe this is true, if you consider that PostgreSQL is full ACID compliant where MySQL isn't, so per definition a lot more robust than MySQL for a long term usage. Also, PostgreSQL connections consume less RAM than MySQL connections buffers (may be untrue with PDO, not sure). And yes, this is quite easy to write standard SQL from MySQL specific syntax (there are no much left out there). Pierre.
On Tue, 20 Jul 2010 16:20:13 -0700 Kevin Davison <kevin@quevin.com> wrote: I'm using Postgresql nearly exclusively. I think the real burden of using Postgresql is seeing your suggestion get into core. That's mainly because Drupal world is still MySQL centric at many levels. Fixing modules is generally quite trivial and once you chose "good modules" you generally find good maintainers that are willing to accept patches promptly.
Here's what I read about PostgreSQL on Drupal.org:
(From http://drupal.org/node/33171)
I've found this a bit misleading and make the whole process look harder than it generally is. What I generally do is: - give a look to hook_install to see there is any evident compatibility problem just to avoid to have to clean up if the module didn't install cleanly. - install it - play a bit with the module - grep db_query fixes are generally trivial, once you pass them upstream you're done.
First off you have to ask yourself is it really worth it? In most cases no absolutely not, MySQL is just fine for sites that are small and want to remain that way, this includes personal sites and blogs etc.
PostgreSQL's real strength at least in my opinion comes from its ability to seamlessly cluster, thereby distributing the load among many DB servers rather than bogging down a single server. If you have a big website with lots of traffic and have the resources for multiple servers, then PostgreSQL is definitely the way to go.
Postgresql is strong on writes and coherency (at the DB level and dev level). If you're used to MySQL... you may not appreciate too much as a developer the higher level of coherency in PostgreSQL. If you're already used to PostgreSQL you won't be willing to feel the pain of dealing with MySQL quirks. Then there is coherency at the DB level (ACID, pk, fk, concurrency etc...) that's another area where PostgreSQL is strong. In Drupal world many features of RDBMS can't still be exploited fully (on delete cascade, transactions...)... Dealing with coherency and concurrency is hard, if you have strong requirement about coherency and you're coding your own stuff, PostgreSQL may be better suited for your needs... but then... you'll end up writing stuff that won't be easy to be ported to MySQL, if you plan to make them public to exploit open source development the community of interested developers *may* be smaller. Load balancing solutions in MySQL and PostgreSQL world are quite different. MySQL simplest balancing solution is easier to set up than PostgreSQL simplest balancing solution... but then if you go beyond choosing the simpler... you'll have to chose on detailed requirement. PostgreSQL 9.0 should make clustering simpler. PostgreSQL is known to run VERY large DB. If you don't have strong coherency and write concurrency requirement MySQL may be your best choice. -- Ivan Sergio Borgonovo http://www.webthatworks.it
+1 to all that Ivan said. Nothing to add, PostgreSQL is superior on all aspects, MySQL really sucks, and we know it since the 4.x version. Pierre.
On Wed, 21 Jul 2010 11:29:14 +0200 Pierre Rineau <pierre.rineau@makina-corpus.com> wrote:
+1 to all that Ivan said. Nothing to add, PostgreSQL is superior on all aspects, MySQL really sucks, and we know it since the 4.x version.
I wouldn't say MySQL sucks (not openly at least and without any argument [1]) if you just need a SQL interface to the file system. That's what most CMS use case really need. 8<--8<--8<--8<--cut here and conserve--8<--8<--8<--8<-- That's why in most cases MySQL is still the best choice for Drupal. Most cases may not be the same as your case and may no be the most profitable case. 8<--8<--8<--8<--8<--8<--8<--8<--8<--8<--8<--8<--8<--8<- I tend to use MySQL on window sites and PostgreSQL where I've an application behind. Right now I'm not so much into social web sites where you may also have several writes so till now it has been easy to chose among the two. Unfortunately for MySQL there are a lot of new contenders in that space that seem to offer more and pretend less. The only advantage and disadvantage too of MySQL is being more mature than other newer DB in that space. Since Drupal market itself as a framework with a CMS built around or a CMS with a framework inside, it's important Drupal stay DB agnostic still letting you exploit the features of each particular DB... so you can use Drupal in many context not just as a mostly read CMS... For many social applications newer data storage are looking more and more interesting than MySQL while for business that requires transactions and coherency (that generally are quite lucrative) PostgreSQL still seems the only contender in the open tool set. [1] it's just more fun to say loudly that vi rulez and emacs sucks because that's a topic that makes any argument on both sides superfluous and you can concentrate on the flames -- Ivan Sergio Borgonovo http://www.webthatworks.it
Le mercredi 21 juillet 2010 à 15:24 +0200, Ivan Sergio Borgonovo a écrit :
On Wed, 21 Jul 2010 11:29:14 +0200 Pierre Rineau <pierre.rineau@makina-corpus.com> wrote:
MySQL is not an SQL database layer. A real SQL database layer must, at least, be ACID compliant :D <Flame ware mode engaged/> Pierre.
Le mercredi 21 juillet 2010 à 15:46 +0200, Pierre Rineau a écrit :
Le mercredi 21 juillet 2010 à 15:24 +0200, Ivan Sergio Borgonovo a écrit :
On Wed, 21 Jul 2010 11:29:14 +0200 Pierre Rineau <pierre.rineau@makina-corpus.com> wrote:
MySQL is not an SQL database layer. A real SQL database layer must, at least, be ACID compliant :D
<Flame ware mode engaged/>
Pierre.
Oh, forgot: still waiting for MySQL 6 Falcon engine :D Pierre.
On Wed, 21 Jul 2010 15:46:44 +0200 Pierre Rineau <pierre.rineau@makina-corpus.com> wrote:
Le mercredi 21 juillet 2010 à 15:24 +0200, Ivan Sergio Borgonovo a écrit :
On Wed, 21 Jul 2010 11:29:14 +0200 Pierre Rineau <pierre.rineau@makina-corpus.com> wrote:
MySQL is not an SQL database layer. A real SQL database layer must, at least, be ACID compliant :D
It depends on what you call SQL. Depending on definition of SQL many DB including postgresql aren't SQL compliant. [1] Most of drupal modules fail to work on DB other than MySQL simply because they liberally and for no reason use MySQL quirks at the SQL language "layer", let alone any consideration for ACID stuff. Drupal itself just recently gained basic support for transactions. I still have to discover if in D7 eg. modules that add details to users can be wrapped in a transaction so that users don't get created mangled, same for nodes etc... Then if there is a mature support for transactions in core all this will have to percolate into modules otherwise it won't be that useful. This will be much trickier than seeing module developers write nice SQL. Support for transactions will make Postgresql much more popular and maybe a better choice for more sites. [1] we could digress about when DB implementations divert from standards for a reason (implementing features or avoiding SQL standard dark areas) or when they do it for ignorance, laziness, boldness... the same approach reflects in DB users... -- Ivan Sergio Borgonovo http://www.webthatworks.it
Le mercredi 21 juillet 2010 à 16:56 +0200, Ivan Sergio Borgonovo a écrit :
It depends on what you call SQL. Depending on definition of SQL many DB including postgresql aren't SQL compliant. [1]
Most of drupal modules fail to work on DB other than MySQL simply because they liberally and for no reason use MySQL quirks at the SQL language "layer", let alone any consideration for ACID stuff. Drupal itself just recently gained basic support for transactions.
I still have to discover if in D7 eg. modules that add details to users can be wrapped in a transaction so that users don't get created mangled, same for nodes etc...
Then if there is a mature support for transactions in core all this will have to percolate into modules otherwise it won't be that useful. This will be much trickier than seeing module developers write nice SQL.
Support for transactions will make Postgresql much more popular and maybe a better choice for more sites.
[1] we could digress about when DB implementations divert from standards for a reason (implementing features or avoiding SQL standard dark areas) or when they do it for ignorance, laziness, boldness... the same approach reflects in DB users...
Don't worry, it was just a flame :) SQL standard has never been fully supported by any DBMS I know (and, by the way, SQL is only a language after all). D7 database layer supports foreign key definition, but like the old MyISAM engine, just ignore it (it's only semantics for the schema API). And for transactions, I don't know, I hope they did the twice magical functions start_transaction() and stop_transaction() (I really hope they did it). <flame warning="If you are expecting good info, stop reading here!"> I worked on business applications strongly database oriented, and did a lot of triggers and procedures with MySQL, this is HELL! There is some really annoying stuff with MySQL, each time you do a complex query ("complex" is relative), you have to test it disabling the MySQL internal query optimizer, because this big guy often does crap. Also, when playing with databases that have some years behind them, you sometime find MyISAM and InnoDB tables playing together, just try to debug why arbitrary simple queries fail with, as the only error message, a number (yes, foreign keys on InnoDB tables pointing to MyISAM tables, it just does not work, even worst, it crashes some queries arbitrary, when playing with MyISAM only you can still define them but they are just ignored and all works perfectly, except for data consistency).
SELECT name FROM dbms WHERE working_with IS LIKE '%FUCKING%HELL%'; MySQL. 1 row(s).
</flame> Pierre.
On 7/21/10 10:23 AM, Pierre Rineau wrote:
D7 database layer supports foreign key definition, but like the old MyISAM engine, just ignore it (it's only semantics for the schema API). And for transactions, I don't know, I hope they did the twice magical functions start_transaction() and stop_transaction() (I really hope they did it).
Drupal 6 aims to be DB agnostic. Drupal 7 aims to be storage-engine agnostic, meaning we can't even guarantee that the backend is SQL of any kind. That makes relying on DB features of that sort (cascade delete, etc.) impossible. D7 does include transaction support based on the design used by PressFlow. You create a transaction object, and then everything is in a transaction until the end of the function when the transaction object goes out of scope. Details are documented: http://api.scratch.drupal.org/api/drupal/includes--database--database.inc/gr... http://drupal.org/node/355875 (I think the former is more up to date.) --Larry Garfield
On Wednesday 21 of July 2010 16:46:44 Pierre Rineau wrote:
MySQL is not an SQL database layer. A real SQL database layer must, at least, be ACID compliant :D
<Flame ware mode engaged/>
Pierre.
It's strange that no one talks about MySQL's InnoDB engine, which is fully ACID compliant. An outside reader would possibly conclude from this conversation that MySQL = MyISAM, which is not ACID compliant. All it takes is to add/change a line in my.cnf to make InnoDB the default storage engine. I remember reading that MySQL 5.5 will make InnoDB the default storage engine. Oh well, maybe then will be the time when most people understand that MySQL is really ACID compliant. I haven't worked with Postgresql before, from talks and internet posts I read that it's a very strong DBMS with many features. But let's also not make false arguments about MySQL. -- # Vasileios Lourdas, # Informatics Engineer, Thessaloniki (Greece) # http://www.lourdas.name
On 7/21/10 10:22 AM, Vasileios Lourdas wrote:
On Wednesday 21 of July 2010 16:46:44 Pierre Rineau wrote:
MySQL is not an SQL database layer. A real SQL database layer must, at least, be ACID compliant :D
<Flame ware mode engaged/>
Pierre.
It's strange that no one talks about MySQL's InnoDB engine, which is fully ACID compliant. An outside reader would possibly conclude from this conversation that MySQL = MyISAM, which is not ACID compliant. All it takes is to add/change a line in my.cnf to make InnoDB the default storage engine. I remember reading that MySQL 5.5 will make InnoDB the default storage engine. Oh well, maybe then will be the time when most people understand that MySQL is really ACID compliant.
InnoDB is the default MySQL engine in Drupal 7 if available. Really, most of the FUD about MySQL is based on MySQL 3, which was a glorified card catalog with an SQL interface at best. Modern MySQL really is a relational database with a strong feature set that's still pretty darned fast. The decision between MySQL and Postgres should be made primarily based on the existing skillset of your DB admin. Drupal's queries are generally simple enough structurally that the DB architecture is not going to make a huge difference, assuming the DB server is properly setup and tuned. --Larry Garfield
On Wed, 21 Jul 2010 10:35:48 -0500 "larry@garfieldtech.com" <larry@garfieldtech.com> wrote:
The decision between MySQL and Postgres should be made primarily based on the existing skillset of your DB admin. Drupal's queries
That's like saying all problems can be solved with a hammer because you're a hammer master. Drupal seems to be so successful because it is a development tool with a really good CMS as an example application ;) It can be wrapped around many other applications... and different applications may have different requirements. Different DB are more suited to different tasks. Maybe you already have an application running on one DB and Drupal is just the web UI. The fact that the core CMS application doesn't need specific characteristics of any DB doesn't mean it's not legit to develop modules that actually make use of some characteristics that are available to just some of them. MySQL doesn't have full text search on InnoDB nor a mature GIS data/index (if any??). I don't know how easy it is to develop C extensions for MySQL (if possible???)... So postgresql for example has dblink, hstore (a sort of EAV), support for trigrams... and you can program your own if you need it (I wrote a simple text similarity extension based on tsearch2 exactly for one of my Drupal projects and I hadn't used C for more than 10 lines in years). -- Ivan Sergio Borgonovo http://www.webthatworks.it
On 7/21/10 2:55 PM, Ivan Sergio Borgonovo wrote:
On Wed, 21 Jul 2010 10:35:48 -0500 "larry@garfieldtech.com"<larry@garfieldtech.com> wrote:
The decision between MySQL and Postgres should be made primarily based on the existing skillset of your DB admin. Drupal's queries
That's like saying all problems can be solved with a hammer because you're a hammer master. Drupal seems to be so successful because it is a development tool with a really good CMS as an example application ;)
If we want to get into wacky analogies, then that's the wrong one. :-) Hammer vs. screwdriver is using an SQL DB vs. MongoDB. Different tools for different types of problems. MySQL vs. Postgres is, in most cases, more akin to whether your high-performance car should be a Mercedes or BMW. If you can get parts from your mechanic more easily for the Mercedes, then that's what you want. They're both still nice cars.
It can be wrapped around many other applications... and different applications may have different requirements. Different DB are more suited to different tasks. Maybe you already have an application running on one DB and Drupal is just the web UI.
The fact that the core CMS application doesn't need specific characteristics of any DB doesn't mean it's not legit to develop modules that actually make use of some characteristics that are available to just some of them.
If there's specific edge case functionality you need, sure. But that's a very small edge case, usually site-specific. For general use, either one will work fine if properly configured and tuned (where is where the "what your DB admin knows" comes in).
MySQL doesn't have full text search on InnoDB nor a mature GIS data/index (if any??). I don't know how easy it is to develop C extensions for MySQL (if possible???)... So postgresql for example has dblink, hstore (a sort of EAV), support for trigrams... and you can program your own if you need it (I wrote a simple text similarity extension based on tsearch2 exactly for one of my Drupal projects and I hadn't used C for more than 10 lines in years).
MySQL does have a GIS plugin, although I've never worked with it. I'm pretty sure you can build extensions for it as well, but I have never needed to do that, either. But now you are talking about extended features of Postgres vs. extended features of MySQL, which is a completely different question than which one performs better at running Drupal. --Larry Garfield
Le mercredi 21 juillet 2010 à 18:22 +0300, Vasileios Lourdas a écrit :
On Wednesday 21 of July 2010 16:46:44 Pierre Rineau wrote: It's strange that no one talks about MySQL's InnoDB engine, which is fully ACID compliant.
Depends on the bugs you face, transactions, multiple users (with different logins) and query cache together still does some weird things sometime. Pierre.
participants (6)
-
Ivan Sergio Borgonovo -
Kevin Davison -
larry＠garfieldtech.com -
Nilesh Govindarajan -
Pierre Rineau -
Vasileios Lourdas