Sunday, November 1, 2009

Postgres... is it worth it?

In working on my side projects, I needed to pick a database. I decided to pick Postgres, and it has been nothing but problems since the beginning. It sucks, because half the problems don't really seem to be the fault of Postgres, though they all seem to stem from the issue that IS their fault.

So, when I first started with Postgres, right off the bat it is a pain. Every single time I need to install Postgres (usually on a new development machine), it is a pain in the butt! They provide a lot of flexibility for database user security. However, the last time I installed MySQL (which is admittedly a long time ago), you set it up, and it asks you for a root user password, and that's it. The rest seemed a snap, at least in the rose colored glasses of the past. With Postgres, each and every install requires me to search to figure out what settings to change in which config file just to get the damn users to be able to connect! Give me some reasonable defaults! If I try to connect to my database locally with a specific user, it would be awesome if the system would give full permissions to any databases I create with that user. Then things like rails would work out of the box, no configuration necessary. I will lock down the system to my hearts content if I need to, but make it JUST WORK first.

That was probably the extent of what I can actually blame Postgres for. It's a big fault in my opinion, though. Software that just works out of the box with no configuration necessary is nirvana. That is why Rails typically feels like heaven to me.

The next issue was probably HostMonster's fault. They give Postgres a back-seat treatment. They run an old Postgres version (I think 8.1), with the excuse that they need to wait till cPanel (their site management web app) upgrades what they support. Ok, I can live with that, but it sucks. When I actually tried to create a database, though, Rails couldn't connect to it! Grrr. HostMonster was on it though, and actually fixed my configuration issue. That was cool of them, go HostMonster! I have been a fan of their support, but that's not what this blog post is about. The issue had been a Rails configuration, I think changing how it was connecting... probably because HostMonster's user connection settings were set up a certain way, contrary to my Rails configuration.

Things were smooth until I needed to create a new database for a new website I wanted to create. It just didn't work. I couldn't create the database, and there was nothing I could do about it... possibly something HostMonster had done, but I didn't want to wait for a fix, so I took the dive and just used a MySQL database.

But I stuck with Postgres on my development machines. Why change? I still wanted it more than MySQL.

Until now.

I just upgraded to Karmic Koala on my netbook. I think it went well, except the upgrade from Postgres 8.3 to 8.4 didn't go so well (which was part of the Karmic upgrade). Both versions are now installed, and when I try to load Rails, I can't connect to the database. Running rake db:migrate even fails. No nirvana, no more Postgres. I'm fed up with the issues. I'm going to the dark side of MySQL, and I'm not going to come back until it is dead simple to set up a Postgres database with Rails.

Postgres intrigued me so much because of the recent drama surrounding MySQL... namely that the Evil Empire (Oracle) now owns them. Perhaps it's a bit superficial, but it's drama I didn't want to get caught up in. I want to know my database system will be open and free throughout the life of my products. However, MySQL makes things so easy that I just can't resist switching back. I will reconsider if it becomes easier to set up a database on Postgres out of the box.

Lesson? Make defaults that work towards achieving your users goals without them needing to dig into any documentation. If it's not easy to start, your users won't start, so you won't have users (at least not as many as you could have).


Scott Mead said...

Sounds like you were using the OS packaged version of Postgres. Honestly, the easiest way to use postgres is either to download the 'One-Click' installers.

If you're looking for a distribution of Postgres that is developer focused, you'll want to look at Postgres Plus Standard (released by EnterpriseDB for free) Postgres Plus is designed to get you up and running as quickly as possible with postgres without all the headaches that you talk about above.


Scott Mead
PostgreSQL 24x7 Support / Consulting

Mike Stone said...

Thanks for your comment, Scott! I most definitely was using the OS (Ubuntu) packaged version. It is disappointing to hear that there is likely a packaged version that would have suited my needs better. I must say, I question why the default Ubuntu package isn't the one that is the easiest to get started with minimal (preferably no) configuration. I don't know Linux distro packaging, but I would hope that Postgres has some say in how this package is configured and maintained.

After deciding to go back to MySQL, here is what I did... I installed a couple MySQL packages with apt-get. When I got the server package, it prompted me for a MySQL root user password, which I provided one. Considering this is a development machine, I just plug root and that password into rails, and I am up and running right away (well, after getting the mysql gem and installing the libmysql client dev binaries). Granted, using root is not the best idea, but for a development machine that is not exposed to the outside world, I'm not worried in the slightest (it will only ever have test data anyways).

Postgres would be ahead of the game if it prompted me for a postgres user password, set up the user automatically, and then allowed me to connect with that user in any of the most common ways. If this is how Postgres Plus Standard is shipped... awesome, that is how it should be. I have yet to try it because MySQL got me up and running. I have already moved on to more pressing issues in my development, though I will definitely consider this package the next time I need a database set up. Regardless, this is how the Ubuntu packages should be (along with any other major desktop Linux distro).

Regardless of if Ubuntu can be blamed for all of the issues I had, it still reflects poorly on Postgres in the eyes of the user. This means to me that it is an issue Postgres should be tackling head on, whether it means changing the default configurations in how Postgres is set up for downstream packages, or getting involved in how Postgres is packaged in the main distros like Ubuntu.

I would like to say that Postgres has been a pleasure to work with for me in everything except the initial configuration step. Unfortunately, that initial configuration has been a thorn in my side too long, and I just don't feel it is worth my time to figure it out when I know an alternative like MySQL is that much easier to get started with. Streamlining every possible aspect, especially initial installation/configuration investment, is a feature that, in my opinion, cannot be ignored.