Message-ID: <335bca9eaf98a6865c8bfd8fc54b09caa055c7bf.camel@cybertec.at>
Subject: Re: Index on (fixed size) bytea value
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Les <nagylzs@gmail.com>, "David G. Johnston" <david.g.johnston@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Date: Tue, 20 Jun 2023 08:50:58 +0200
In-Reply-To: 
 <CAKXe9UA9gmFH8AeY6_LLiWDt=Z648c+3Wqb3uY7B455hZEAT_g@mail.gmail.com>
References: 
	<CAKXe9UAhXAZbs7jys68cC9rsKPM+YBK7pipps7Ww-f+dJW5bhg@mail.gmail.com>
	 <CAKFQuwYT=k3bLEQCHpYOcSHGS_R5BhW2tkaEPLOduqzdRSu6ZQ@mail.gmail.com>
	 <CAKXe9UA9gmFH8AeY6_LLiWDt=Z648c+3Wqb3uY7B455hZEAT_g@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/335bca9eaf98a6865c8bfd8fc54b09caa055c7bf.camel%40cybertec.at>
Precedence: bulk

On Tue, 2023-06-20 at 08:13 +0200, Les wrote:
> I'm aware of the TOAST, and how it works. I was referring to it ("I think=
 that it should
> be as large as possible, without hitting the toast.=C2=A0") I have design=
ed a separate "block"
> table specifically to avoid storing binary data in the TOAST. So my plan =
is not going to
> involve out-of-band storage.
>=20
> Just to make this very clear: a record in the block table would store a b=
lock, not the
> whole file. My question is to finding the optimal block size (without hit=
ting the toast),
> and finding the optimal hash algorithm=C2=A0for block de-duplication.

Then you would ALTER the column and SET STORAGE MAIN, so that it does not e=
ver use TOAST.

The size limit for a row would then be 8kB minus page header minus row head=
er, which
should be somewhere in the vicinity of 8140 bytes.

If you want your block size to be a power of two, the limit would be 4kB, w=
hich would waste
almost half your storage space.

Yours,
Laurenz Albe