> On 16 Nov 2021, at 10:20, Laurenz Albe <laurenz.a...@cybertec.at> wrote: > > On Tue, 2021-11-16 at 13:56 +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 ? > > Yes, I would use one of these methods. > > The only feature I can think of that may help is partitioning: if you have > one partition > for the current data and one for the deleted data, then updating the flag > would > automatically move the row between partitions, so you don't need a trigger.
Are you building (something like) a data-vault? If so, keep in mind that you will have a row for every update, not just a single deleted row. Enriching the data can be really useful in such cases. For a data-vault at a previous employer, we determined how to treat new rows by comparing a (md5) hash of the new and old rows, adding the hash and a validity interval to the stored rows. Historic data went to a separate table for each respective current table. The current tables “inherited” the PK’s from the tables on the source systems (this was a data-warehouse DB). Obviously that same PK can not be applied to the historic tables where there _will_ be duplicates, although they should be at non-overlapping validity intervals. Alternatively, since this is time-series data, it would probably be a good idea to store that in a way optimised for that. TimescaleDB comes to mind, or arrays as per Pavel’s suggestion at https://stackoverflow.com/questions/68440130/time-series-data-on-postgresql. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
