WordPress and PostgreSQL (part 1)

Well, I just ran into what I con­sider a major road­block or maybe a fork in the road. I really want to move to using PostgreSQL exclu­sively, drop­ping MySQL. But WordPress is basi­cally being writ­ten for MySQL exclud­ing all other data­bases. This means I either have to give up on using PostgreSQL exclu­sively or give up using WordPress. And so the pon­der­ing proceeds.

I guess I have to exam­ine why I want to go to PostgreSQL exclu­sively and the corol­lary that WordPress can be replaced by some­thing else. Gonna start with the advan­tages and dis­ad­van­tages of both PostgreSQL and MySQL.

MySQL is the sim­ple solu­tion. It is well sup­ported by every­thing I want to use includ­ing WordPress and Drupal. It has a large devel­op­ment com­mu­nity despite its recent pass­ing of hands first to Sun and now to Oracle. I have been using it for a long time (long enough I can’t remem­ber how long, some­time before 2000). I know how to use it, con­fig­ure it, opti­mize it. I can write its own form of SQL with­out much need to refer to the MySQL doc­u­men­ta­tion. A lot of my old php code is writ­ten specif­i­cally for MySQL although my per­sonal php frame­work is  now writ­ten using PDO so I have some abstrac­tion going there. With the lat­est ver­sions of MySQL, it has finally reached a level of ACIDity that I want, trans­ac­tions etc.

On the other hand, MySQL is now owned by Oracle. I really don’t like the direc­tion they have already taken it, mak­ing the com­mu­nity ver­sion almost impos­si­ble to find on their web­site. The Developer Zone has been buried. This puts a seri­ous black cloud over MySQL future as an open source project in my opinion.

MySQL always felt lack­ing in some way. It still doesn’t have some fea­tures I would like it to have. Although it is get­ting closer to stan­dard based SQL, it has a ways to go in some areas. There are lit­tle nig­gly things that make me look else­where. In other  words, I am just uneasy about MySQL and am look­ing for an excuse to get away from it. If I stick with MySQL, some­thing like MariaDB or Drizzle to me is almost more appeal­ing to get away from Oracle’s heavy hand. Both MariaDB and Drizzle are based on or forked from MySQL. Drizzle is doing some inter­est­ing things going off in their own direc­tion whereas MariaDB looks to be more compatible.

PostgreSQL is one of those data­bases I have wanted to use instead of MySQL ever since I started using MySQL, espe­cially since MySQL didn’t sup­port trans­ac­tions. But at that time it was (or was per­ceived as) more dif­fi­cult to con­fig­ure and main­tain. It cer­tainly has dif­fer­ent ways of doing things and gen­er­ally seems to be more com­plex but that is because it has more capa­bil­i­ties. Documentation for PostgreSQL some­times feels like it is writ­ten for peo­ple who already know what they are doing just need­ing handy reminders and not for peo­ple unfa­mil­iar with PostgreSQL at all.

Now that I am using PostgreSQL, I see it wasn’t at all as dif­fi­cult as I thought but I still haven’t learned enough to feel com­fort­able with every­thing. Using phpp­gad­min has made things a bit eas­ier although the com­mand line tools give me more capa­bil­ity. PostgreSQL cer­tainly has a lot more lay­ers to work with such as schemas, trig­gers, and views (real ones unlike what MySQL has). And of course, it has its own way of doing SQL at times although much closer to the stan­dards than MySQL. Ironically, although I know a lot less about PostgreSQL I already feel more com­fort­able about using it than I do MySQL.

I haven’t done a lot of test­ing with regards to per­for­mance and resource use. I am not as con­cerned about per­for­mance as I am about resource use. My ini­tial tests seem to indi­cate that PostgreSQL uses less RAM to do the same thing despite the fact it actu­ally can do a lot more. But that may change as I use it more.

I will con­tinue this dis­cus­sion in part 2 as I look at WordPress and make a deci­sion to keep using it and MySQL or switch to some other blog­ging soft­ware that will use PostgreSQL.