Generate hash id for records in DB

user_14157410 0
Viewed 486

I have a set of unit tests that saves data in DB (postgres) whenever they run. once in a while, because of some duplicate data, the machine performance is slowed, so we need to clean the db (the data is not important but it needs to be saved for a while for internal process).

I thought, as a temporary solution, to extend the time period from deleting the data, to generate a hashing id in my for each record that is saved in DB, to avoid duplication(so if the hashed id exists, it wouldn't be saved).

I know that a different set of data might produce same hashed id - it's ok, I'll handle this logic.

I need to generate those hashed id's, in java, it needs to be in range of type long. java's built in method "Objects.hash()" produces results of type int. any other solutions I looked for are using UUID or any other hashing algorithms which produces sequence of characters.

hashcode hash postgresql java
2 Answers

Postgres 14 adds hash functions for records. One of them returns bigint:

hash_record_extended(record, bigint) --> bigint

It produces a bigint hash for records, just what you are looking for. (But generated in Postgres, not in Java.)
See discussion in these threads in pgsql-hackers:

A pretty big deal, if you ask me. But it's not advertised in the release notes, nor is it documented in the manual. It's meant for internal use to support UNION [DISTINCT], hash joins and hash partitioning.

But I see nothing to keep you from using it for your purpose. It's an incremental improvement to this related solution (currently based on Postgres 13):

A detailed assessment of collision probabilities for bigint hashes is attached there. TLDR: pretty unlikely up to a couple millions of rows.

user_9398600

A simple 64 bits hash can be implement by combining CRC32 with Adler32.

This is an example in Java:

package com.example;

import java.util.zip.Adler32;
import java.util.zip.CRC32;

public class MySimpleHash {

    /**
     * Calculate a 64 bits hash by combining CRC32 with Adler32.
     * 
     * @param bytes a byte array
     * @return a hash number
     */
    public static long getHash(byte[] bytes) {

        CRC32 crc32 = new CRC32();
        Adler32 adl32 = new Adler32();

        crc32.update(bytes);
        adl32.update(bytes);

        long crc = crc32.getValue();
        long adl = adl32.getValue();

        return (crc << 32) | adl;
    }

    public static void main(String[] args) {
        String string = "This is a test string";
        long hash = getHash(string.getBytes());
        System.out.println("output: " + hash);
    }
}

output: 7732385261082445741

This is another example that does the same in Python:

#!/usr/bin/python3

import zlib

def get_hash(bytes):
    return zlib.crc32(bytes) << 32 | zlib.adler32(bytes)

string = "This is a test string"
hash = get_hash(string.encode())
print("output:", hash)

output: 7732385261082445741

I have a Gist that compares some strategies to generate 32 bit and 64 bit hashes: https://gist.github.com/fabiolimace/507eac3d35900050eeb9772e5b1871ba

user_10566100
Related