PostgreSQL 9.0 Released

Contributing Editor

PostgreSQL, or Postgres to its friends, is the underdog of the web database world. The system is overshadowed by MySQL despite being a robust, fully-featured, open source relational database with a passionate community. There are few technical reasons for MySQL’s higher adoption rates: it simply arrived first and established strong links with PHP developers.

However, regardless of assurances from Oracle, MySQL’s future appears less certain. PostgreSQL, which has no single owner, is ideally placed to capitalize on any complications arising from the database giant.

PostgreSQL 9.0 has recently been released and there are several improvements and new features which could attract a larger following:

  • Hot Standby: a second standby database for read-only queries. It’s created by replaying the primary database’s binary log file.
  • Streaming Replication: secondary servers can connect to a master whenever they want and receive the records they need. Lag times are short and the load on the primary server is minimal.
  • enhanced stored procedure support
  • Improved permission management
  • Column triggers: triggers which only fire when a specific column is updated.
  • WHEN conditional triggers
  • deferrable unique constraints: mass updates to unique keys are now possible
  • Sorting in aggregates
  • unnecessary JOIN removal optimization
  • anonymous functions
  • enhanced stored procedure support
  • EXPLAIN output available in JSON, XML, or YAML format
  • a 64-bit Windows binary distribution
  • a new module which supports in-place upgrades

Organizations such as Yahoo, Skype, reddit.com, and MySpace all use PostgreSQL. Will you?

For more information about the release or the database in general, refer to postgresql.org.

Do you use PostgreSQL? Are you considering MySQL migration? How do the databases compare?

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Dave

    For most common applications, MySQL fits nice. It’s fast, well supported… but when backend features are needed Postgres is the man.

    It is faster than MySQL for intensive missions, and far way fully-featured. Just the inheritance among tables could put a period to this talk, but it goes further.

    XML support, vacuum process, really transactional and integrity built-in, complete functions… oh, other competitors have some of these also…

    How about genetic algorithm? Postgres has this implemented and matured even before Oracle. For who doesn’t know it, I’ll explain: postgre evolves and get faster as you use it. How much you use, how faster it gets. Simply. How? Well, it tries many different ways to run a query and record the results. With some use, it can see which decisions and paths makes it runs faster. Pretty smart.

    Yeah, Postgre is that cool. Since a long time ago. This is why it is my preferred for middle to high missions. For low missions I still stick with MySQL, ‘coz it is broadly available, cheap, and enough featured for the need.

  • heggaton

    I love that Postgres is still keeping up development. I prefer it as a DB but MySQL has much of the hosting market.

    When developing a custom app, I tend to use Postgres but when developing a generic app, it has to be MySQL all the way.

  • Conor

    Been a fan of PostgreSQL since way back to v6. Many from the MySQL camp complain about it being too complicated to configure and maintain. To be honest, I’ve never seen it. With inbuilt support in PHP (both pg_* functions and PDO) and wide ranging support for other languages, I think the current trouble in the MySQL camp at the moment will see a much greater adoptance of PostgreSQL in the months ahead.

    • http://www.optimalworks.net/ Craig Buckler

      I agree that Postgres is no more complicated than MySQL. It’s simply different. That may be in good ways, but it means MySQL developers need to adapt their existing knowledge.

      However, Oracle uncertainty and it’s new replication features could provide a brighter future for PostgreSQL on the web.

  • codeslinger.compsalot

    Postgres sounds great in theory. But a couple of years ago when I evaluated it I found that installation was a huge mess. I had two servers both running identical os versions and both supposedly configured the same/similar. On one server Postgres installed just fine, no problems, on the other server it kept complaining about this that and the other thing, I never did get it to work on the other server. This experience soured me greatly on wanting to have anything to do with it. I need it to be reliable and easy to install on a lot of different servers.

    Aside from that, I think it has a lot to offer and now that there is a new version I am willing to give it another try. The other thing though is that you do have to alter your methodology somewhat, it is not a drop in replacement for mySQL even though both of them are supposedly speaking ANSI, there is enough difference between them to require some code rewriting.

    I believe that another factor which hurts adoption is the name. Yes, I know it sounds kinda silly, but little things like poor choice of a name can have a negative impact on product acceptance. Having an obtuse unpronounceable name is not helping them to get the publicity they deserve.

    • http://www.optimalworks.net/ Craig Buckler

      Thanks Codeslinger. I’ve heard that Postgres can be tricky to install, but hadn’t experienced problems myself. Perhaps v9.0 is better?

      You’re right that Postgres is not a drop-in replacement for MySQL. To be fair, it’s not an easy task to replace any database with another — even if your application uses a generic DB layer such as PDO. However, there are many MySQL <-> PostgreSQL converters should you want to attempt it.

      You’re probably right about the name too. Correct pronunciation is hard enough! I thought MySQL was dumb too, but it’s kinda grown on me.

  • Sphamandla

    Currently using mysql database and am comfortable and satisfied with all features and functionality that comes with the database. On the other hand PostgreSQL 9.0 is very impressive it,well it always was so I’ve heard but i haven’t used it to that extend. It would come as no surprise if a noticeable migration is to be experienced in the coming weeks of the release of PostgreSQL 9.0

  • Robert Haas

    I’ve been using PostgreSQL for 10 years and have never had a problem installing it. In fact, I’ve rarely even needed to change the default configuration. Once or twice I’ve hit a bug in one of the drivers, but for the most part it’s just worked for me right out of the box.

    The thing that eventually led me to abandon MySQL after having used it for several projects was the poor query planner. I found that even with 6-8 table joins queries just ran too slowly. It was easier to port to PostgreSQL, which handled them much better. A lot has changed since then but I’ve not regretted making the switch.

  • Dave

    I’ve also been using PostgreSQL for over 10 years now. I wasn’t a previous MySQL user like Robert, but started out with PostgreSQL because I prefer to have my data integrity enforced by the database so I don’t have to build business rules into a middle tier, or multiple frontends. Postgres made that easy in comparison to MySQL, which happily accepted dates like 31st February or accepted invalid values and then returned something different.

    I understand that modern versions of MySQL have made improvements in these areas, but I’m far too attached to PostgreSQL’s advanced features to change now – for example, being able to write stored procedures in languages like C or Perl allows me to push more complex business rules into the backend, and abstract apps away from the details of the data storage itself.

  • Rob

    I’ve used MySQL for 5 years and just started with Postgres. The installation on the various platforms with the installers is every bit as easy and simple as with MySQL. But there are lots of differences – positive on the Postgres side. Lots more features (so no having to workaround missing MySQL features), performance is great, a WAY better software update system that automatically notifies, downloads, and installs approved updates, better performance diagnostic tools, etc. All in all, Postgres has much more to offer both developers and DBA’s.