MIME-Version: 1.0
References: <ZhP6OmFXob6W3uLK@localhost>
In-Reply-To: <ZhP6OmFXob6W3uLK@localhost>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Mon, 8 Apr 2024 10:43:51 -0400
Message-ID: <CAKAnmmLMa-q3F4K42J=yN15VFHyqs5JNbW=tsYQE=ZmdQGVwzg@mail.gmail.com>
Subject: Re: Storing and comparing columns of cryptographic hashes?
To: Josh Triplett <josh@joshtriplett.org>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000ac0d17061596d727"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLMa-q3F4K42J%3DyN15VFHyqs5JNbW%3DtsYQE%3DZmdQGVwzg%40mail.gmail.com>
Precedence: bulk

--000000000000ac0d17061596d727
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Apr 8, 2024 at 10:08=E2=80=AFAM Josh Triplett <josh@joshtriplett.or=
g> wrote:

> - 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?
>

No, and no. (if I understand your question correctly). You could use a
functional index, I suppose, but seems premature optimization.


> - Is `bit(256)` the right type to use to store 32-byte hash values with n=
o
> additional overhead?
>

No, you would want bytea. I would store the value in a TEXT field, unless
you really worried about space savings. The hexadecimal value will be far
easier to debug and work with, and you can use a simple b-tree index.

- 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.
>

Something like this:

SELECT array_agg(case when t.bhash is null then 1 else 0 end)
from unnest(array['blakehash1', 'blakehash2', etc...]) as a(x)
left join mytable t on t.bhash =3D a.x;


> - Does it make more sense to store the values as one row per value, or
>   as one row per group of values?


Hard to answer without knowing more, but I'd lean towards simple and one
row per value.

Your proposal (query db, do external work, update db) also sets of lots of
concurrency red flags, so be mindful of that.

Cheers,
Greg

--000000000000ac0d17061596d727
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Apr 8, 2024 at 10:08=E2=80=AFAM J=
osh Triplett &lt;<a href=3D"mailto:josh@joshtriplett.org">josh@joshtriplett=
.org</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">- Is there a way to tell postgresql &quot;=
this column contains cryptographic hashes, so you can do hash joins using a=
ny subset of the bits, without=C2=A0having to hash them again&quot;? If not=
, should there be?<br></blockquote><div><br></div><div>No, and no. (if I un=
derstand=C2=A0your question correctly). You could use a functional index, I=
 suppose, but seems premature optimization.</div><div>=C2=A0<br></div><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:=
1px solid rgb(204,204,204);padding-left:1ex">
- Is `bit(256)` the right type to use to store 32-byte hash values with no =
additional overhead?<br></blockquote><div><br></div><div>No, you would want=
 bytea. I would store the value in a TEXT field, unless you really worried =
about space savings. The hexadecimal value will be far easier to debug and =
work with, and you can use a simple b-tree index.</div><div><br></div><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:=
1px solid rgb(204,204,204);padding-left:1ex">- What would be the simplest w=
ay, given an input array of hashes (which<br>
=C2=A0 I may have to pass in as an array and use `unnest`), to filter out a=
ll<br>
=C2=A0 the values that already exist, *and* generate a corresponding bitmap=
<br>
=C2=A0 in the same order for present/not-present for the entire array (to<b=
r>
=C2=A0 send back to the user)? Filtering seems easy enough, but generating<=
br>
=C2=A0 the bitmap less so.<br>
</blockquote><div><br></div><div>Something like this:</div><div><br></div><=
div>SELECT array_agg(case when t.bhash is null then 1 else 0 end)</div><div=
>from unnest(array[&#39;blakehash1&#39;, &#39;blakehash2&#39;, etc...]) as =
a(x)</div><div>left join mytable t on t.bhash =3D a.x;</div><div>=C2=A0<br>=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex">- Does it make more=
 sense to store the values as one row per value, or<br>
=C2=A0 as one row per group of values?</blockquote><div><br></div><div>Hard=
 to answer without knowing more, but I&#39;d lean towards simple and one ro=
w per value.</div><div><br></div><div>Your proposal (query db, do external =
work, update db) also sets of lots of concurrency red flags, so be mindful =
of that.</div><div><br></div><div>Cheers,</div><div>Greg</div><div><br></di=
v></div></div>

--000000000000ac0d17061596d727--