5 votes

An immutable blog engine with PostgreSQL

3 comments

  1. [2]
    Greg
    (edited )
    Link
    Conceptually I really like this. I'm a big fan of immutable data both from a technical reasoning point of view, and as a way to assuage my own anxiety about losing the wrong thing and coming to...

    Conceptually I really like this. I'm a big fan of immutable data both from a technical reasoning point of view, and as a way to assuage my own anxiety about losing the wrong thing and coming to need it later. I did once inherit a project that used Datomic in production and unfortunately it was absolutely painful to use. Whether that's a reflection on the technology, the specific project, or my own understanding is something I couldn't say - at a guess, I'd say a bit of each.

    Unless I missed it in the article, I imagine you'd want some kind of trigger to explicitly block delete/update queries on rows in the immutable tables - assuming that can be done cleanly, which I'm reasonably sure it can, I can see some solid uses for this pattern.


    It seems like it should be as simple as a "before update" trigger that throws an error every time, but I have a healthy scepticism of anything in a DB engine being that straightforward. Concurrent transactions, WALs, vacuum operations, and the rest tend to complicate these things in unexpected ways.

    1 vote
    1. vord
      Link Parent
      No need for a trigger....just revoke delete/update permissions from the user in question (insert DBA rant about lack of permission management in a lot of stuff). Although, a trigger could be used...

      No need for a trigger....just revoke delete/update permissions from the user in question (insert DBA rant about lack of permission management in a lot of stuff).

      Although, a trigger could be used for a slightly different pattern, one you almost hit...

      • Normal table, no sequencing. The data in the table is the current data.
      • Add a before update/delete trigger that inserts the old data into a history table.

      This can be quite helpful, in part because it can greatly simplify indexing and querying. It's also easily back-ported onto existing applications that don't build in sequencing.

      2 votes
  2. vord
    Link
    The technique described here is a very common usage pattern. Keeping historical data is very important for things like ERP systems, and immutability is very important there. Disadvantage is that...

    The technique described here is a very common usage pattern. Keeping historical data is very important for things like ERP systems, and immutability is very important there.

    Disadvantage is that it consumes more space, however it's mitigated by being able to archive the historical information at a specified date.