MIME-Version: 1.0
References: <CAFCRh-8FOZiycyfX4uPB8MTHQTxqNVuW0pdKBuFNQneEZy1PwQ@mail.gmail.com>
 <CAHyXU0ztigAau2x5zut6tdjNVXENEGR00F-Mvx4X-20zFz-NnQ@mail.gmail.com>
In-Reply-To: <CAHyXU0ztigAau2x5zut6tdjNVXENEGR00F-Mvx4X-20zFz-NnQ@mail.gmail.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Fri, 11 Jul 2025 11:10:58 +0200
Message-ID: <CAFCRh-8T-9FncVBJx2dPEY2=QR17NWwgrxE1LNHyufijPz_Ygg@mail.gmail.com>
Subject: Re: Aggregate versions of hashing functions (md5, sha1, etc...)
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-8T-9FncVBJx2dPEY2%3DQR17NWwgrxE1LNHyufijPz_Ygg%40mail.gmail.com>
Precedence: bulk

On Thu, Jul 10, 2025 at 9:03=E2=80=AFPM Merlin Moncure <mmoncure@gmail.com>=
 wrote:
> On Thu, Jul 10, 2025 at 5:48=E2=80=AFAM Dominique Devienne <ddevienne@gma=
il.com> wrote:
>> We store scientific information in PostgreSQL, and some of that is
>> bytea and large, thus we must "chunk it" both for performance, and not
>> be limited to 1GB (we do exceed that, in rare occasions).
>
> Does it have to match the md5 of the 'unchunked' variant exactly?

Well, yeah, ideally.

> If not, maybe you can rig a custom aggregate that would just "hash amend"

Sure, there are various ways to get a deterministic hash of such a
chunked-across-rows (logical) value. But it always involves hashing
the hash, which statistically might not be ideal. But more
importantly, it departs from the normal "serial" hashing of the full
logical hash. With the full hash, I can use various FS OS tools and
SQLite aggregate-UDFs and TBD PostgreSQL aggregate-Digests to compare
those hashes in natural and consistent ways. Working around lack of
aggregate digests in PostgreSQL forces to replicate those work-arounds
at the FS and SQLite levels, for comparisons. I.e. Not good.

> I had to do something very similar with compression, I had a need to be a=
ble to compress bytea values with lz4 at SQL level due to limited support f=
or extensions in the cloud.  it works great...if a bit slow, and requires t=
he database to handle the decompression.

The slow part is what I want to avoid. I explored various ways to
work-around true server-side aggregate hashing, and none are
satisfactory nor performant enough. This is really the kind of
primitive that must be built-in to be efficient enough to be "useful".
(and I'd argue compression, deduplication, etc... are similar issues.
BLOBs / BYTEAs are too often "limited" in RDBMSs, with people saying
store them outside the DB, and I always find so strange, and a bit of
a cope out to making it performant enough and/or full features enough.
A bit of a rant here, sorry ;)).