Re: When much of a DB is large objects - PG 8.4

> the bytea limit is 1gb (as are all datums in postgres).
> pg_largeobject can go up to 2gb, but in either case you'd likely run
> into severe performance/scalability issues long before objects began
> approaching those size because of memory usage and other issues. With
> 100kb objects though, you should be all right.
>

Thanks, Merlin. Yes, I am not worried so much about the size limits of
a given field or row (not yet anyway).

But I am concerned that all my data across all my tables really ends up
in a single pg_largeobject table, which seems like it could be a
bottleneck. Since it's blobs and generally big, I figure repeated
access to the table doesn't really even benefit from caching that much
like if I had a small table that was being hit a lot. I am worried
about the overall table size of pg_largeobject as blobs are inserted in
my various tables, but they only get an OID stored, whereas
pg_largeobject gets all of the data across all of those tables.

I am concerned with backups/restores, crash recovery, partitioning
options, etc. if most of my data is stored in the single pg_largeobject
table. Can it be partitioned? How many blobs can it hold before it
won't perform well?

And is there any performance gain if I had several pg_largeobject-like
tables that I built myself using bytea types as it does? I mean, does
PG perform any better if my queries are across a series of tables all
with their own byteas rather than using LOs?

> libpq supports a binary protocol mode which allows you to execute
> queries sending bytea without escaping. (I'm not familiar with the
> jdbc driver, but I'd imagine it should support it in some fashion). l
> would start researching there: find out if the jdbc driver supports
> binary queries and use them if possible. If you can't or won't be use
> jdbc in this way, your options are to stick with large objects or try
> and figure out another way to get data into the database.
>
Thanks. I'll see what I can learn about bytea escaping in JDBC as I
don't see anything obvious in its JDBC-specific PG docs. Perhaps it no
longer suffers from each byte being converted into escaped characters,
which really balloons already big enough data for the transfer
protocols, and that would be great. Of course, it's all moot if there
would not be any real performance gain to be had by having these objects
stored across multiple tables rather than all being in pg_largeobject
(most such data is not deleted, though it is often updated, so vacuumlo
running daily generally isn't a problem for us).

David