From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Les <nagylzs@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Index on (fixed size) bytea value
Date: Tue, 20 Jun 2023 08:50:58 +0200
Message-ID: <335bca9eaf98a6865c8bfd8fc54b09caa055c7bf.camel@cybertec.at> (raw)
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>
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. ") I have designed 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.
>
> Just to make this very clear: a record in the block table would store a block, not the
> whole file. My question is to finding the optimal block size (without hitting the toast),
> and finding the optimal hash algorithm for block de-duplication.
Then you would ALTER the column and SET STORAGE MAIN, so that it does not ever use TOAST.
The size limit for a row would then be 8kB minus page header minus row header, 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, which would waste
almost half your storage space.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, nagylzs@gmail.com, david.g.johnston@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Index on (fixed size) bytea value
In-Reply-To: <335bca9eaf98a6865c8bfd8fc54b09caa055c7bf.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox