Hacker News Reader

Comment by alternize | Hacker News Reader

the problem seems only half solved - the author does not show how they select (only) the most recent record.

as soon as the app needs to generate a list of f.e. active users, this can get nasty pretty quick. sure, one could use an aggregate like max(version), but once there are joins or lots of records involved, this could become a performance hit...

EDIT: the records are kept in a separate table, so my arguments are moot.

View on HN

Why? They're modifying the main table, which isn't growing with each update. The changes are simply tracked in the history table.

oh right. small but important detail... thanks for pointing it out to me.

Valid question though, I've updated the post to be more explicit and added a couple more query examples.

I guess you could work around that by having null in the version column for the active/latest row. Then your queries become 'where version is null' if you want to skip superseded rows. You could then use partial indexes (where version is null) to handle unique constraints etc.

EDIT I hadn't finished reading the article, so I didn't see that they were using triggers and the history table. Using the approach I outlined you could keep everything in the same table (if you were that way inclined).

we tried keeping everything in the same table in some web app of ours. we used a version id and an extra versioning status field that kept track of the states "latest published", "latest draft", "archived" which kinda worked fine...

the disadvantage is bloat, as in our case the history data was rarely used at all and just there as a safeguard.

Reply on news.ycombinator.com