Forgot to make my other point ...
Matthew Hagerty <matthew@venux.net> writes:
> ... But now instead of just needing to store a price, I need
> to store almost every field from one table into another to keep the history
> records accurate.
Well, is that wrong? If the history records represent past values
of the "master" record, that may be exactly what you need to do.
Perhaps you are concerned that there will be a lot of duplication
because history records are generated much more frequently than the
master data actually changes. If so, you might steal an idea from
the old "time travel" feature in Postgres: generate a new master record
whenever the master data changes, and mark each such record with its
beginning and ending times of validity. (A record is created with
begin time = 'now' and end time = '+infinity'; when it is superseded
the end time is set to 'now'; *no* other change is ever made to an
existing record.) Then the history records can link to the current-
at-the-time master record, and they only need to directly store whatever
detail data is not in the master record. You can also readily find
out what the master values were at any past time, whether there is a
history record for that particular time or not.
Whether this is worth the trouble depends on how much space you can save
in redundant history records and on the desired update properties ---
that is, do you want to be able to change the data of many history
records by modifying one copy of the master record? (Of course,
modifying a past master record violates the "time travel" abstraction,
but if you really need to be able to rewrite history you can.)
The time travel feature was really kinda cool. I think it is still
described in the Postgres documentation, even though it was removed a
while ago for performance reasons. (The space overhead was intolerable
for applications that didn't need time travel.)
regards, tom lane
