Re: Index on (fixed size) bytea value - Mailing list pgsql-performance

From Les
Subject Re: Index on (fixed size) bytea value
Date
Msg-id CAKXe9UCKJfjtgniRmBZfz6rMvyML38xVZ6tebEui-KiHpC+ArQ@mail.gmail.com
Whole thread Raw
In response to Re: Index on (fixed size) bytea value  (Laurenz Albe <laurenz.albe@cybertec.at>)
List pgsql-performance

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.

Oh I see. So if I want to save some space for future columns, then storing about 7500 bytes in the "block bytea" column would be close to optimal, utilizing more than 90% of the block space. I guess that the fillfactor setting will have no effect on this table, and it does not matter if I set it or not.

pgsql-performance by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: Index on (fixed size) bytea value
Next
From: nicolas paris
Date:
Subject: Re: Merge David and Goliath tables efficiently