Re: How to store "blobs" efficiently for small and large sizes, with random access

On 2022-Oct-19, Dominique Devienne wrote:

> Upfront, I have to state that I'm not keen on lo, because of security
> considerations. We store blobs in many different schemas, and users
> can access some schemas, and not others. So the fact the lo table is
> unique for the whole database would allow users to see blobs from any
> schema, as I understand it. Right? OTOH, lo has random access, which I
> also need...

Generally speaking, bytea sucks for random access, because if a TOAST
item is compressed, it has to be always read from the beginning in order
to decompress correctly. However, if you set
ALTER TABLE tab ALTER COLUMN byt SET STORAGE EXTERNAL
then compression is not used, and random access becomes fast.

https://www.postgresql.org/docs/15/sql-altertable.html

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"No hay hombre que no aspire a la plenitud, es decir,
la suma de experiencias de que un hombre es capaz"