I have a postgres table that will be used to store data pulled from several remote sources / APIs. I will be syncing the data several times a day.
Since the data will vary wildly depending on the source, I'm thinking about using columns to store the common information (eg. source name, modification timestamp, etc.) and then put the raw record in a
jsonb column to be examined later.
I'm considering adding a column that will store a hash of the raw record (MD5 perhaps?) that I can use to quickly compare with the "new" record pulled from the source, so if the hash values don't match I will update the record, and if they match I will skip it.
There will be millions of rows, so storage / access efficiency is definitely a factor.
My questions are:
- Does this approach make sense?
- Is there a more efficient / useful way to store the raw record?
- What's the best way to store the hash value?