Re: PDF files: to store in database or not

> On Dec 8, 2016, at 9:25 AM, Chris Travers <chris(dot)travers(at)gmail(dot)com> wrote:
>
> Assuming relatively small files, bytea makes much more sense than a large object. However note that encoding and decoding can be relatively memory intensive depending on your environment. This is not a problem with small files and I would typically start to worry when you get into the hundreds of mb in size. At least in Perl, I expect decoding to take about 8x the size of the final file in RAM.
>
> LOBs work best when you need a streaming interface (seek and friends) while bytea's are otherwise much more pleasant to work with.

Not much I can do on the Postgres side, but you can manage the amount of RAM needed on the client side by returning the bytea in chunks using a set returning function. In my case, this returns chunks to PHP that are immediately written to the download stream so there is no need to have the entire document in RAM on the application side. I have included the function I use below.

John DeSoi, Ph.D.

create or replace function blob_content_chunked(p_dbid integer)
returns setof bytea as $$
declare
v_chunk integer = 1048576;
v_start integer = 1;
v_data bytea;
v_size integer;
begin
select into v_data content from blob where dbid = p_dbid;
if found and v_data is not null then
v_size = octet_length(v_data);
if v_size <= v_chunk then
return next v_data;
else
for i in 1..v_size by v_chunk loop
return next substring(v_data from i for v_chunk);
end loop;
end if;
end if;
end;
$$ language plpgsql stable;