3

I have a data set that needs to be looked up by RSA public keys. These keys are 392 text characters long.

What is the best way to make that a primary and/or unique key?

Should it be a BYTEA field?

Is it too long for a primary key? Should I store a shorter hashed version of it to index on and filter by first before checking for an exact match?

4
  • If I were you, I would try and insert some sample data and test the performance hit of the long test PK on it. If (and only if) I saw problems when reaching the expected amount of data, would I venture into some more complicated designs. I wouldn't possibly choose bytea as the column type, and hashing might come handy.
    – dezso
    Sep 15, 2015 at 20:21
  • Surely they are not really text? I imagine they are actually a long string of hexadecimal characters?
    – Hannah Vernon
    Sep 15, 2015 at 20:25
  • @MaxVernon - The public keys are actually a binary blob that has been base64 encoded
    – Kyle
    Sep 16, 2015 at 14:08
  • so perhaps converting them back to binary before storing them would be more efficient and result in a less-wide key?
    – Hannah Vernon
    Sep 16, 2015 at 14:09

1 Answer 1

2

I would say store them as bytea, but not for that reason. Simply because pg_crypto expects the keys as bytea. That establishes a convention. They likely have good reason for it: it seems intuitively faster.

Also there is no reason to store them as a PRIMARY KEY. You can sanely just store them in an indexed field.

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.