Re: historical log of data records

On 2021-11-16 13:56:51 +0530, Sanjay Minni wrote:
> I need to keep a copy of old data as the rows are changed. 
>
> For a general RDBMS I could think of keeping all the data in the same table
> with a flag to indicate older copies of updated /  deleted rows or keep a
> parallel table and copy these rows into the parallel data under program /
> trigger control. Each has its pros and cons. 
>
> In Postgres would i have to follow the same methods or are there any features /
> packages available ?

There is an extension (I think it's this one:
https://pgxn.org/dist/temporal_tables/) which we've used for one
project so far. I'm not sure whether I would use it for further
projects: It does what it's supposed to do and it's nice not having to
reinvent the wheel. But it did cause some problems during a posgresql
upgrade (nothing insurmountable, just a bit of a hassle) and IIRC also
with Django migration scripts. So, given that it's easy to roll your own
I probably would (but I do suffer a bit from NIH syndrome in general, so
that's not surprising).

I would strongly advise to use triggers. In my experience implementing
that in the application logic is a recipe for inconsistencies. Someone
will always forget to duplicate a row or set the wrong timestamp or
whatever (BTDT).

I don't have a strong preference between just using valid_from/valid_to
fields in the same table(s) and using separate history tables. Both
work, one or the other may be slightly more convenient depending on the
use case. Since you mentioned that the purpose is auditing, I'd probably
lean towards separate tables in your case.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"