Tuesday, January 26, 2010

PostgreSQL: The Universal Database Management System

I'm glad you asked, since I've been pondering this for a while.  $subject is my new project slogan.  Now I'm not sure whether we can actually use it, because a) it's stolen from Debian, and b) another (commercial, proprietary) database product already uses the "universal database" line.

I have come to appreciate that the "universality" of a software proposition can be a killer feature.  For example, Debian GNU/Linux, the "universal operating system", might not be the operating system that is the easiest to approach or use, but once you get to know it, the fact that it works well and the same way on server, desktop, and embedded ensures that you never have to worry about what operating system to use for a particular task.  Or Python, it's perhaps not the most geeky nor the most enterprisy programming language, but you can use it for servers, GUIs, scripting, system administration, like few other languages.  It might as well be the "universal programming language".  A lot of other software is not nearly universal, which means that whenever you move into a new area, you have to learn a lot of things from scratch and cannot easily apply and extend past experiences.  And often the results are then poor and expensive.

The nice thing about PostgreSQL is that you never have to worry about whether to use it, because you can be pretty sure that it will fit the job.  Even if you don't care whether something is "open source" or "most advanced".  But it will fit the job.  The only well-known exception is embedded databases, and frankly I think we should try to address that.


  1. With the exception of:
    * Very dynamic data sets (prototyping or early stages of development)
    * Large data sets either from writes, memory limitations on single machine or just single machines issues
    * Special data structures (ex: graphs)

    As far as a database system goes PostgreSQL it almost has to many features, I think of PostgreSQL as more of a platform. Especially in the context of embedded systems comparing it to something like SQLite. With your general point I agree it is hard to go wrong by picking PostgreSQL for data.

  2. Lateef,

    As of 8.4, PostgreSQL works fine for graphs, too, bearing in mind that graph theory is littered with nasty complexity terms, which limit the size and speed of any system which actually solves the problems there.

  3. > The only well-known exception is embedded databases, and frankly I think we should try to address that.

    Gonna be tough. SQLite is pretty well entrenched there.

  4. Lateef,

    I'm not sure what's better for dynamic data sets than postgres. If you just need to serialize your data structures, there are plenty of ORMs to do that for you.

    However, it's often very useful to start developing the schema early, because it really can catch a lot of errors.

  5. > As far as a database system goes PostgreSQL it almost has to many features, I think of PostgreSQL as more of a platform.

    Exactly. Just like Linux has too many modules or Debian has too many packages. It doesn't make it easy to learn, but sooner or later someone is going to need the obscure functionality, and then it's nice to be able to rely on it being there.

  6. @David
    I assume you where referring to 'WITH RECURSIVE' (http://www.postgresql.org/docs/8.4/static/queries-with.html).
    +1 For graph support
    -1 For KISS
    Thanks for pointing that just upgrading to PG 8.4 this month so will try to learn new features.

    I agree with you ORMs are really helpful. When I am designing schema I spend most of my brain cycles deciding if I want cleaner design or performance.
    +1 for code maintainability
    -1 for premature optimization

    I was more referring to slipper slope of using more advanced features than you need to in PostgreSQL. One of the things I love about PostgreSQL is the DBA not required. The main issue I see is business logic slipper slopes into stored procs, triggers et all. I am sure there is an exception for an application where the business logic _should_ live in the database and I also know that this is not the fault of PostgreSQL it is us developers who do this to ourselves. It seem to me the larger and more access the data set the less features you want to use, for the performance and maintainability of code. However with all these features the opposite happens.

  7. I'd love to see postgresql become embeddable. I've seen too many projects where mysql or sqlite were chosen instead.
    They're fine products, but they also encourage the "dumb data store, all logic belong in the app" approach, and "varchar(255) for everything" schemas.

  8. "universal" could mean "speaks lots of flavours of SQL". That would be useful.

  9. Why didn't you propose your idea on the list (I didn't find it) ?

    It's quite nice and makes sense : it even seems to fits the Debian/Ubuntu with PostgreSQL/EntrepriseDB...