Re: Index on (fixed size) bytea value

2023-06-20 Thread Les
> > > 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

Re: Index on (fixed size) bytea value

2023-06-19 Thread Laurenz Albe
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.

Re: Index on (fixed size) bytea value

2023-06-19 Thread Les
David G. Johnston ezt írta (időpont: 2023. jún. 19., H, 22:30): > On Mon, Jun 19, 2023 at 1:05 PM Les wrote: > >> AFAIK PostgreSQL does not allow a row to occupy multiple blocks. >> > > Your plan is going to heavily involve out-of-band storage. Please read up > on it here: > > https://www.postg

Re: Index on (fixed size) bytea value

2023-06-19 Thread David G. Johnston
On Mon, Jun 19, 2023 at 1:05 PM Les wrote: > AFAIK PostgreSQL does not allow a row to occupy multiple blocks. > Your plan is going to heavily involve out-of-band storage. Please read up on it here: https://www.postgresql.org/docs/current/storage-toast.html I'm not looking for a definitive ans

Index on (fixed size) bytea value

2023-06-19 Thread Les
Dear fellow list members, I'm in the process of implementing a file storage system that is based on PostgreSQL and streaming replication. There will possibly be many similar files stored. I would like to implement block-level deduplication: each file consists of a series of blocks, and each uni