Skip to main content.

Why I Don't Develop for Postgres

06/9-09 at 11.15 by: Robert Campbell

From time to time I get sporadic, but very oppinionated complaints about this addon module to CMSMS or that addon module to CMSMS and problems with Postgres.  The people making these 'bug reports' are typically very adamant that this is a "serious bug" and that it should be fixed "ASAP".  I usually end up getting numerous responses from only a few people about these "bug reports", and I usually appear to be quite arrogant because I am firm in my stand against Postgres.  Here I'll try to put this issue to rest.

The first reason I don't support Postgres has to be because the vast majority of CMSMS users use mysql.  Wether this be by choice, performance, historical experience, or because it's what their hosts provide, the reason is irrelevant.   I would hazard a wild guess that at least 90% of the CMSMS users use MySQL, and would continue to do so even if ALL of the functionality from ALL of the addons operated equally on both databases.   Therefore, it would be inefficient of me to spend considerable amount of time installing Postgres, creating another CMSMS install (for each version of CMSMS, installing and testing my work on both environments just to satisfy maybe 10% of the user base.

Now, some people will try to start the argument that 'MySQL is used because CMSMS doesn't support postgres that well'.  It's a chicken and egg argument.   Even in 1995 when I started developing for CMSMS we could count the people that even wanted to use postgres on 1 hand.   Now it may take two hands.

Secondly, even though CMSMS uses the adodb database abstraction library to provide a reasonable level of database independance from the start, it is still possible, and sometimes necessary to write database specific queries to implement certain functionality.   Examples are the 'SHOW TABLES' command that mysql supports, and the 'REGEXP' query operator.   Postgres doesn't support these, or at least not in the same way, and there is no 'common' command to perform the same functions.  Thirdly, Postgres is much more strict about some advanced queries that use limits, or subqueries or group parameters.

The 'SHOW TABLES" command can be used to implement an on-demand backup system for mysql, or to test if a particular table exists.  The SitePusher and NMS modules use this functionality.

The 'REGEXP' query operator allows me to query if certain fields in a table match a regular expression.  This is handy for user controlled filtering. A number of my addon modules use this functionality to provide filtering that myself and numerous other users find handy.   Sure I could use the 'LIKE' operator just as easily, or full text searching, but many customers have explicitly asked for regular expression type searches.  Therefore that functionality goes in, and Postgres support doesn't work.

The stricter syntax requirements of Postgres are not in themselves a problem, usually, except possibly in the case of LIMIT statements, or subqueries having LIMIT statements.  In the past Mysql supported the "LIMIT <start>,<count>" syntax, where Postgres required "LIMIT <count> OFFSET <start>".  Later versions of mysql now support that syntax... also the adodb library we use handles that abstraction in most cases.... except for in the case of subqueries.

Additionally, Postgres is more strict on grouped queries, requiring a more formal query than that required by mysql.   It's yet another step to go through.

Thirdly, occasionally there have been problems with 'case sensitivity' in table names.  Mysql seems to be case insensitive, and Postgres seems to be case sensitive.  Now clearly not being consistent with table names and how I access them is definately something I could change.   But it works fine on mysql.

I will say that the third reason I don't support postgres has to be due to the legacy of my programming experience in PHP, of CMSMS and of postgres.

In the early days of CMSMS (back in the 0.7.9 days), I was just venturing in to database programming, the stuff I had done before was all in C/C++ using custom database libraries, and/or berkely.  I had very little experience programming in any RDBMS (except for some experience with PL/SQL which left a bad taste in my mouth).  Therefore when I started devloping for CMSMS I had a bit of a learning curve to get over.  And a bunch of the queries and commands I wrote wouldn't work on postgres... there are still quite a few of these queries around.   It would take some effort to go through and test everything, fix, test again...

Lastly, as a sole developer, writing and maintaining tens of thousands of lines of code per year, I can't hope to support every permutation and combination of environment, and ensure equal functionality everywhere... The time just isn't there to setup duplicate environments, test each peace of functionality on each environment, fix any issues and test again.

Now somebody is gonna add a comment here saying something like "If Postgres is much more strict, why don't you do your primary development for postgres, then port back to MySQL"... Lets nip that argument in the bud before it starts.   I mentioned above that I think at least 90% of the user base uses mysql, therefore the proper thing to do would be to mimic as much as possilbe the environment are users are running under.   Also, If using postgres for my primary database, I'd still have to go back and test in mysql to handle the 90% user case... that doesn't make sense.

Therefore, I hope you can now see why Postgres support is not a big priority for me with regards to CMSMS, and will probably continue to be neglected.  Unless that is, somebody gives me a significant reason to change my way of working.

19/4-10 at 12.52 by: pb.
Sorry. CMSMS is full of bugs like "select key, item1, item2, item3, sum(item3) from table group by key" which is not correct in ANY database and it does not mather that the MySql did not complain. When the patch is sent to you, the answer was "it's not needed to fix it since it works in MySql". Developers of CMSMS are ignorants with no respect to users and rejecting bug reports. Are you ignoring 8% of all users? OK. What other do you ignore now? What other will you ignore in the future? In the future YOU will be ignored.
25/3-10 at 05.22 by: theo
There are two crucial questions about this: 1. If you don't want to support PostgreSQL, why CMSMS have option to run on that DB? Is it marketing decision or what? This is deception on users in some way... 2. Ok, you don't want to take care about making your code PostgreSQL compliant, I understand this, because we are all only people with limited time, etc... But why you don't receive and apply patches, that will make your code PostgreSQL compliant without harming functionality in MySQL (typicaly LIMIT x OFFSET y intead of LIMIT y,x)? Sorry, this is not only kind of aversion, this is ignorance or arogance maybe. To be honest, after using CMSMS 5 year or so, I decide to move for another CMS, because PostgreSQL is better database in many ways (such as clearer licence for example), and this blogpost is one of the main reason why to do this. However I wish to you and to rest of team a lot of satisfied users, nice features, and as little as possible bugs.
08/9-09 at 11.31 by: calguy1000
@Robert: Yes, it is based on the idea that the majority of people generally don't care about which database they use, or prefer mysql because of ease of use, familiarity, or whatever as I said in the original post. History shows that many more people use mysql over postgres. I found stats on this that compared database usage... and it was like 33.7% of total usage for mysql, and less than 8% for postgres.... which means that we're correctly categring to the ~80% of the people that we should. Regarding using the 'advanced capabilties' of postgres, like recursive queries or aggregates... using that would give me the opposite problem: functionality that works on postgres, but not on mysql, and that's not feasable. I'm not painting myself into a corner, because if a time comes when my priorities are changed (due to a contract, or the general increase in demand for postgres), then I would install and tackle the postgres issue. Until such time though...
08/9-09 at 09.03 by: Robert Treat
Well, there are many other reasons to support Postgres than the ones you mentioned above; like personal skills development, or how solid multi-db support now would make future support of MySQL forks like MariaDB or Drizzle easier to support, or that running on Postgres should allow CMSMS users to scale vertically with more ease, or that adding Postgres support can enhance applications in ways that either don't work or a cumbersome on MySQL (Think recursive queries or running aggregates), but all of these can be argued away as well if you don't want to support Postgres. Reading this article, you seem to have put yourself into a corner. As far as I can tell, the only reason you would better support Postgres would be if a large number of people started using CMSMS with Postgres. But since you've already stated that you plan to neglect Postgres support, new people who come to CMSMS will almost surely choose MySQL, and those unwilling to use MySQL will simply go use something else with better Postgres support. This is based on the idea that the majority of people generally don't care which database is on the back end, they go with whichever is better supported. Consequently, I bet if you announced that you were switching to Postgres as your primary development platform for the next year, you'd see the Postgres numbers come up.
07/9-09 at 10.04 by: calguy1000
We discussed that a long time ago, and ended up leaving support for postgres into the core. Some developers do verify their modules on postgres. But typically they have very few modules with very little releases, so it's not a big deal for them.
06/9-09 at 11.48 by: Maarten
Those are valid arguments. But then why not leave postgres completely and only develop CMSMS for MySQL? I must say that I only use MySQL.
Written by:
Comment:
Write the chars you see in the square
This is a captcha-picture. It is used to prevent mass-access by robots. (see: www.captcha.net)