I decided that I need to start posting again, it’s been a while since I’ve taken the time to write. Now I’m using WordPress, instead of Serendipity, because of the overall quality and because of the import/export capabilities (caveat: WP isn’t perfect here, either… more on this later). This decision took some consideration because, as a PostgreSQL advocate, I naturally would want to choose software that works with PostgreSQL (Serendipity does; WordPress is MySQL-only).

It got me thinking a little bit about why I like PostgreSQL, when it matters, and when it doesn’t.

Let me start out by saying that, so far, I am quite impressed with the quality of WordPress. A lot goes into publishing software that has little or nothing to do with the database system chosen, and I think that they got most of those things right. One could go as far as to say that publishing software (for a single publication) has little use for a relational system at all:

• It’s a single application with its own data.
• It mostly just needs to fetch pages with words on them, it doesn’t need to draw sophisticated inferences from complex relationships.
• The data is largely independent. It’s unlikely that, for example, one article you post conflicts with another article in a way that a machine can hope to understand.
• Most importantly, the ultimate data is meant to be consumed by humans. Relational theory is largely based on the ability of machines to give you useful answers from data that is greater in volume than the consumer would prefer to read. If humans are reading it manually, what’s the point?

I won’t discuss here the benefits of using an established SQL system in general as opposed to flat files or some other non-SQL system  — I think those are apparent. However, I do think that there are some areas PostgreSQL specifically might offer a benefit over MySQL for this kind of publishing application:

• Full-text search over crash-safe tables.
• Languages like PL/Perl are indispensable when you have little text processing tasks and it’s easier to just do it before it gets back to the application.
• Triggers and functions in postgresql are very flexible (which can use the NOTIFY mechanism, or even open sockets to make a request), which allows virtually any kind of cache invalidation you can imagine. That might be convenient for high-traffic sites to refresh static content.
• Transactional DDL is ideal for no-downtime schema upgrades.
• Features like WITH RECURSIVE (Common Table Expressions, new in 8.4) might make it easier to do interesting things, like query based on a tree structure (like comments), or find interesting transitive relationships (for instance, following tags from one article to the next, as a big graph structure).

[ Disclaimer: I don't really follow MySQL development. Maybe they implemented some of these things and I didn't notice. Also, I don't know much about WordPress, so this post should not be interpreted to mean that the WP developers chose their database system poorly. ]

It would be nice if WordPress did support PostgreSQL. It’s just another application that’s getting a lot of attention, and it’s another opportunity where postgres is just sitting on the sidelines, because MySQL is good enough to get the job done. If postgresql were in the mix, developers would see those features in action and think: “Wow, that was easy. I didn’t know you could do that!”.

I think it’s a myth that the database system only matters if you are doing a 10-way join every other query. Postgres offers a lot of features that just make things easier: easier to administer, easier to write queries, easier to catch bugs in the application, easier to do simple manipulations (such as on dates or strings), etc. A DBMS doesn’t get much credit for working well on a small system, but I think it should — sometimes a small system stores very important data (especially when “small” for a modern computer is so large and getting larger).

It would also be nice if it supported FirebirdSQL — by all accounts a good database system with a lot to offer, but hasn’t quite caught on for these types of applications. FirebirdSQL is designed to work well as an embedded system (as well as a server), which sounds useful for an application where the user doesn’t want to know much about the database system behind it.

I’ll get back to the import and export capabilities in my next post.