I'm planning to store cryptographic hashes (BLAKE3) in a column of a postgresql table. I'm going to be doing a large number of operations of roughly this form: - Receive a large number of hashes via a web API call. - Check which hashes aren't already in the database. - Send back a bitmap to the user of which hashes they need to send. - Receive data from the user corresponding to those hashes. - Store the data (not in postgresql). - Add the new hashes to the database, along with a tiny value indicating what group of objects they were stored in. A few questions: - Is there a way to tell postgresql "this column contains cryptographic hashes, so you can do hash joins using any subset of the bits, without having to hash them again"? If not, should there be? - Is `bit(256)` the right type to use to store 32-byte hash values with no additional overhead? - What would be the simplest way, given an input array of hashes (which I may have to pass in as an array and use `unnest`), to filter out all the values that already exist, *and* generate a corresponding bitmap in the same order for present/not-present for the entire array (to send back to the user)? Filtering seems easy enough, but generating the bitmap less so. - Does it make more sense to store the values as one row per value, or as one row per group of values? I know that postgresql can store an entire array in one column; could that efficiently support operations like "tell me which of these objects don't exist in any array in this column" or "for all of these objects, tell me all the group-id values for rows containing them"? Thank you, Josh Triplett
