2

Postgres: 15.1

My goal is simple: I want to create an aggregated hash value for x rows from column c in table z.

Example Input:

SELECT hash_agg(c) AS checksum FROM z;

Example Output:

checksum
51jj5l1jl55

Unfortunately, this seems to be such an irrelevant use case, that it's not implemented by default.

To be more precise: I got the following function to aggregate all my values and hash them with md5:

SELECT md5(string_agg(c, '')) AS checksum FROM z;

This may work with around 5 rows, but I am expecting to crash the function due to the high input. It may also be inadequate for hashing a lot of data.

EDIT: I did manage to build an md5 aggregate function that does not need to build an extreme big array or string beforehand. (Please correct me if array_agg() can handle a million rows without being a memory concern)

As @Erwin Brandstetter points out, md5 might not be the best solution in terms of speed and i would love to use everything more efficient if the implementation of that algorithm is open source. It also seems to be a waste of processing power to rehash the existing state

md5(state || input::bytea)

If there is a way to 'update' the existing hash with new values, as if it would have been the same as if hashing the whole dataset from the beginning, i would be grateful. (And if it is not possible in Postgres, please tell me so i don't have to look any further)

DROP AGGREGATE IF EXISTS md5_agg(anyelement);
DROP FUNCTION IF EXISTS md5_agg_state_func;

CREATE OR REPLACE FUNCTION md5_agg_state_func(state bytea, input anyelement)
  RETURNS bytea AS $$
BEGIN
  IF input IS NOT NULL THEN
    RETURN md5(state || input::bytea);
  ELSE
    RETURN state;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION md5_agg_final_func(state bytea)
  RETURNS text AS $$
BEGIN
  RETURN encode(state, 'escape');
END;
$$ LANGUAGE plpgsql IMMUTABLE;


CREATE OR REPLACE AGGREGATE md5_agg(anyelement)
(
  sfunc = md5_agg_state_func,
  stype = bytea,
  finalfunc = md5_agg_final_func,
  initcond = ''
);
4
  • Can you highlight what your question is? Is the problem with your current implementations that the hash of 'hi' + 'there' is not equal to the hash of 'hithere'?
    – jjanes
    Jun 29, 2023 at 13:11
  • @jjanes Thanks for asking. I simply want to create a hash of a column in some million rows. This has to be somewhat memory efficient and fast. I know that (some) hash algorithms are able to 'update' their hash, like sha-256 which allows 'streaming' the input to keep the memory requirements low. I added a custom aggregate function to show where my thoughts are going. But Erwin Brandstetter just answered all my questions.
    – Yuki
    Jun 30, 2023 at 7:15
  • I think most hash functions allow streaming. It is just a question of how easy it is to adapt them into PostgreSQL aggregates. For the most part, it will take c coding to make a compiled extension, especially if you want it fast.
    – jjanes
    Jun 30, 2023 at 15:00
  • @jjanes that's what i thought too. I already saw implementations for my scenario emailed to people working on postgres but as we see, they didn't make it in production :(
    – Yuki
    Jul 2, 2023 at 12:42

1 Answer 1

1

Building reliable and performant hash functions is tricky. You have to observe null values, sort order, and other corner cases. On top of this, custom aggregate functions are typically substantially slower than built-in functions.

I would take another look at built-in functions. Postgres 14 or later has a hash function generating a bigint hash for every registered type, including array and row types. These are very fast and reliable. Unlike md5(), those have no cryptographic aspect, which is just wasted for your use case. Consider one of these:

bigint hash:

SELECT hash_array_extended(array_agg(c                      ORDER BY id), 0) AS set_hash1_int8 FROM z;

SELECT hash_array_extended(array_agg(hashtextextended(c, 0) ORDER BY id), 0) AS set_hash2_int8 FROM z;

uuid hash (=~ md5)

SELECT md5(array_agg(c                      ORDER BY id)::text)::uuid AS set_hash1_uuid FROM z;

SELECT md5(array_agg(hashtextextended(c, 0) ORDER BY id)::text)::uuid AS set_hash2_uuid FROM z;

SELECT md5(array_agg(md5(c)                 ORDER BY id)::text)::uuid AS set_hash3_uuid FROM z;

SELECT md5(array_agg(md5(c)::uuid           ORDER BY id)::text)::uuid AS set_hash4_uuid FROM z;

fiddle

Why uuid?

All work for any amount of null values, too. hash_array_extended() treats null as just another value. And the text representation of a Postgres array has a distinct string representation for null values.

My favorite is set_hash2_int8() above - if a bigint hash is sufficiently safe against hash collisions. It only relies on hashtextextended() and hash_array_extended(), without any type cast, thereby avoiding a potential source of instability. Some casts may depend on locale or other settings.

Also note the added ORDER BY. Without deterministic sort order, you would miss when two rows trade values. You can alternatively use a sorted subquery, which is typically faster. See:

Related (with more on those built-in hash functions, and on probabilities for hash collisions):

2
  • Thank you, i picked md5 because i didn't find something simpler. But i still see a problem in array_agg(). Wouldn't this first append every value found in my column, lets say 100k values, to a big memory blob and only after that run the hash algorithm? If so, is there a way to reduce the memory cost with a specialized aggregate function that operates like: hash the first value, take the second, hash it accordingly but keep a result not greater (in memory) than the first hash? Don't get me wrong, any other way would be okay. The memory should not exceed any postgres limits
    – Yuki
    Jun 29, 2023 at 7:07
  • 1
    @Yuki Yeah, an aggregate function building a single "running hash" would be far less memory-intensive. Sadly, there is no such function in standard Postgres. 100k rows result in an 800 kb array in my set_hash2_int8() before hashing that to 8 bytes. Shouldn't be a problem for modern hardware. There is also 4-byte hash function for every type, to cut that in half once more. But then hash collisions enter the realm of "possible". I expect custom aggregate functions to be much slower. But do test - and report your findings! Jun 29, 2023 at 23:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.