Re: PDF files: to store in database or not

> On Dec 8, 2016, at 9:25 AM, Chris Travers <chris.travers@gmail.com> wrote:
>
> Assuming relatively small files, bytea makes much more sense than a large object.  However note that encoding and
decodingcan be relatively memory intensive depending on your environment.  This is not a problem with small files and I
wouldtypically start to worry when you get into the hundreds of mb in size.  At least in Perl, I expect decoding to
takeabout 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
towork 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
byteain chunks using a set returning function. In my case, this returns chunks to PHP that are immediately written to
thedownload stream so there is no need to have the entire document in RAM on the application side. I have included the
functionI 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;