Version controlling a database's contents

Question

I'm working on a web project that involves user-editable content, and I'd like to be able to do version-tracking of the actual content, which lives in a database. Basically, I want to implement wiki-style change histories.

Doing some background research, I see a lot of documentation about how to version your database schema (mine is actually already controlled), but any existing strategies about how to track your database content changes are lost in the avalanche of schema versioning stuff, at least in my searches.

I can think of a few ways to implement my own change tracking, but they all seem rather crude:

• Save the entire row on each change, relate row back to source id with a Primary key (what I'm leaning towards currently, it's the simplest). Lots of small changes could produce a lot of table bloat, though.
• save before/after/user/timestamp for each change, with a column name to relate the change back to the relevant column.
• save before/after/user/timestamp with a table for each column (would result in too many tables).
• save diffs/user/timestamp for each change with a column (this would mean that you'd have to walk the entire intervening change history to go back to a certain date).

What is the best approach here? Rolling my own seems like I'm probably reinventing someone else's (better) codebase.

---

Bonus points for PostgreSQL.

Answer 1

The technique I have normally used is to save the complete record, with an end_timestamp field. There is a business rule that only one row can have a null end_timestamp, and this is of course the currently active content.

If you adopt this system, I strongly recommend you add an index or constraint to enforce the rule. This is easy with Oracle, as a unique index can contain one and only one null. Other databases may be more of a problem. Having the database enforce the rule will keep your code honest.

You are quite correct that lots of small changes will create bloat, but you need to trade this off against code and reporting simplicity.

Answer 2

Note that if you use Microsoft SQL Server, there is already a feature for that called Change Data Capture. You still will need to write code to access the previous revisions later (CDC creates specific views for that), but at least you don't have to change the schema of your tables, nor implement the change tracking itself.

Under the hood, what happens is that:

• CDC creates an additional table containing the revisions,

• Your original table is used as it was before, that is any update is reflected in this table directly,

• The CDC table stores only the changed values, meaning that data duplication is kept to a minimum.

The fact that changes are stored in a different table has two major consequences:

• Selects from the original table are as fast as without CDC. If I remember well, CDC happens after the update, so updates are equally fast (although I don't remember well how CDC manages data consistency).

• Some changes to the schema of the original table lead to CDC removal. For instance, if you add a column, CDC don't know how to handle that. On the other hand, adding an index or a constraint should be fine. This quickly becomes an issue if you enable CDC on a table which is subject to frequent changes. There might be a solution allowing to change the schema without losing CDC, but I haven't searched for it.

Answer 3

Solve the problem "philosophically" and in code first. And then "negotiate" with code and database to make it happen.

As as example, if you're dealing with generic articles, an initial concept for an article might look like this:

class Article {
  public Int32 Id;
  public String Body;
}

And at the next most basic level, I want to keep a list of revisions:

class Article {
  public Int32 Id;
  public String Body;
  public List<String> Revisions;
}

And it might dawn on me that the current body is just the latest revision. And that means two things: I need each Revision to be dated or numbered:

class Revision {
  public Int32 Id;
  public Article ParentArticle;
  public DateTime Created;
  public String Body;
}

And ... and article's current body doesn't need to be distinct from the latest revision:

class Article {
  public Int32 Id;
  public String Body {
    get {
      return (Revisions.OrderByDesc(r => r.Created))[0];
    }
    set {
      Revisions.Add(new Revision(value));
    }
  }
  public List<Revision> Revisions;
}

A few details are missing; but it illustrates that you probably want two entities. One represents the article (or other header-type), and the other is a list of revisions (grouping whatever fields make good "philosophical" sense to group). You don't need special database constraints initially, because your code doesn't care about any of the revisions in and of themselves -- they're properties of an article that knows about revisions.

So, you don't need to worry about flagging revisions in any special way or leaning on a database constraint to mark the "current" article. You just timestamp them (even an auto-inc'd ID would be OK), make them related to their parent Article, and let the article be in charge of knowing the "latest" one is the most relevant one.

And you let an ORM handle the less philosophical details -- or you hide them in a custom utility class if you're not using an out-of-the-box ORM.

Much much later, after you've done some stress testing, you may think about making that revision property lazy-load, or having your Body attribute lazy-load only the topmost revision. But, your data structure in this case shouldn't have to change to accommodate those optimizations.

Answer 4

There's a PostgreSQL wiki page for an audit tracking trigger which walks you through how to set up an audit log that will do what you need.

It tracks the full original data of a change, as well as the list of new values for updates (for inserts and deletes, there's only one value). If you want to restore an old version, you can grab the copy of the original data from the audit record. Note that if your data involves foreign keys, those records may also have to be rolled back to maintain consistency.

Generally speaking, if your database application spends most of its time on just the current data, I think you are better off tracking alternate versions in a separate table from the current data. This will keep your active table indices more manageable.

If the rows you are tracking are very big and space is a serious concern, you could try to break down the changes and store minimal diffs/patches, but that's definitely more work to cover all your kinds of datatypes. I have done this before, and it was a pain to rebuild old versions of data by walking through all of the changes backwards, one at a time.

Answer 5

Well, I wound up just going with the simplest option, a trigger that copies the old version of a row to a per-table history log.

If I wind up with too much database bloat, I can look at possibly collapsing some of the minor history changes, if needed.

The solution wound up being rather messy, since I wanted to generate the trigger functions automatically. I'm SQLAlchemy, so I was able to produce the history table by doing some inheritance hijinks, which was nice, but the actual trigger functions wound up requiring some string munging to properly generate PostgreSQL functions properly, and map the columns from one table to another correctly.

Anyways, it's all on github here.

Answer 6

For standard approaches, have a look at:

• Hibernate Envers
• Spring Data Envers
• JaVers

Even if you don't use these, you might get some inspiration.

For a real-world example of version-controlled database content, download the MusicBrainz database and have a look at the tables edit, edit_data, edit_artist etc.