If I want to track revision history (ie. keep an audit trail) on a foo table, I might add a trigger to it, such that every UPDATE too foo.some_text_column also results in an INSERT into a foo_history table.
My question is, in that foo_history table, is it better to save the entire contents of foo.some_text_column, or to save just a "diff" of the changes?
In other words, if I update 'abc' to 'def', is it better to keep the whole original text:
abc
or is it better to keep something more like ...
- abc
+ def
Obviously, the dif takes up more space for this short string, but when you have larger strings, with edits of just a few characters, the diff will be a lot smaller.
Also, I know databases are good at optimizing redundant data, so maybe storing the whole original text really isn't that bad? I'm just honestly not sure which option make more sense, technically, and would welcome an informed opinion.