I have started working on a postgreSQL database. There is a table keeping the record of stocks and every time a record is updated, a copy of the original row needs to be kept, with a flag added to say that the record is no longer current. I am planning to implement this using a Trigger (which I am assuming is the best option).

In the above case, with a flag to say that a record is not the current one, what's the best way to ensure that reads from the table always used the latest records? Only checking the flag value with where condition?

  • The performance between writing and reading, what's your choice ? It'd take to time to update the latest value but then, select is fast. – Luan Huynh May 27 '19 at 7:15

If it were me, rather than flagging current, I would just time stamp each entry. Then, you would not need to update anything. You would only need to insert a new row. This way, you can query sets of data to plot against time, as a side benefit.

| improve this answer | |
  • so which one would give the best performance in a large table when querying for the latest records of an item. Querying by flag +condition or latest_timestamp+condition. – Vidhu May 26 '19 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.