from Hacker News

Rapid schema development with PostgreSQL

by pearkes on 10/18/13, 3:18 PM with 53 comments

  • by moron4hire on 10/18/13, 5:14 PM

    I'm failing to see the utility of hstore or json over adding nullable columns here, especially if the latest version makes adding a nullable column essentially instantaneous. It doesn't seem to simplifying the handling of missing/unavailable data, but does manage to complicate the SQL syntax.

    I have clients that use a variety of databases for a variety of reasons, not the least of which is "just because that is where our data is." All the value of most projects is in the data, and the value of data grows with age, because you cannot recreate the past. If you lose something or you fail to record something, then you can't ever get it back in a way that will stand up to audit scrutiny. This has the awful effect of making technology-specific details of databases get pushed into the business-decision realm, rather than the technology-decision realm.

    So complicating the SQL syntax is a significant issue for me. Sticking to as much standard, ANSI SQL as possible makes my programs more portable across RDBMSes. With some of the tools I've written, I can make a full transition from MySQL to MS SQL Server and back again and the application doesn't care. Having that sort of power makes upgrading your database a technology decision, not a business one.

    Yes, the features that Postgres have are nice, but to me they represent a very great chance of vendor lockin. I don't believe that the Postgres team will ever pull anything to make me hate them, but then I thought the same about Sun at one point, too.

  • by josephlord on 10/18/13, 4:25 PM

    Hmmm... I'm having evil thoughts about putting a whole web application including templating inside of Postgres and just exposing a few functions via a web interface.

    Don't worry I won't do it really but does anyone worry that Postgres is doing too much and that focus might be lost on being a reliable and fast relational DB? I haven't seen any signs of problems but I do have this slight concern with all the array/hstore/json features they have been adding recently.

  • by craigkerstiens on 10/18/13, 6:51 PM

    If you're looking to try Postgres as a full on document store there's more and more tooling around making this feasible. Here's one newer gem that makes it pretty straightforward for Rails: https://github.com/webnuts/post_json

  • by nsitarz on 10/18/13, 4:32 PM

    I'm pretty sure that "ALTER TABLE ... ADD COLUMN ... NULL;" holds an AccessExclusiveLock on the relation being altered for the duration of the transaction in PG 9.1 and 9.2. Is there some trick to the zero downtime schema changes that are mentioned in this slide deck? I only ask because I recently had to get creative with zero downtime schema migrations for my current project.

  • by CraigJPerry on 10/18/13, 10:09 PM

    When would the hybrid schema approach be useful?

    To my mind it doesn't offer any extra ability to add or remove columns over what we have already.

    E.g. I'd never write select * I'd always name columns in my query so that I can be immune to reordering or adding columns to the underlying table or view.