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 json or 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:

  1. Does this approach make sense?
  2. Is there a more efficient / useful way to store the raw record?
  3. What's the best way to store the hash value? varchar?
  • 2
    Hash has a fixed length so you don't need var. It only fails if they changed the data and still the same hash - not likely. Unless they purposefully produced a hash collision then good approach.
    – paparazzo
    Oct 5 '16 at 18:37
  • This seems a valid solution except from the update part. If you calculate the hash of a new incoming raw record, this will never be equal to any hash stored in the table, so you will never update anything. In this case you have to implement an algorithm that knows what record you would like to update and checks if the incoming hash is different, but this doesn't make sense at all to me. May be there are some other details you didn't write? Anyway, text is the right data type for hash. It's the fastest and it's flexible for future schema updates.
    – pietrop
    Oct 14 '16 at 9:54

Your Answer

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

Browse other questions tagged or ask your own question.