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

On Mon, Oct 19, 2009 at 9:11 PM, David Wall <d(dot)wall(at)computer(dot)org> wrote:
> We have a system in which a large amount of the data is stored as large
> objects now in PG 8.4.  The relational aspects of these objects are stored
> using traditional tables, but we store a lot of binary data (files, images)
> and XML objects that are compressed and then encrypted.  The ACID properties
> of PG are great for this since we can ensure multiple updates are
> committed/rolled-back together.
>
> But, even if I have 30 tables that themselves all have OID types for the
> encrypted or binary data, most ends up in the pg_largeobject table, which
> makes partitioning and disk access complicated, and there's always some
> concern we'll end up maxing out the OIDs in that system table and what the
> performance of banging against one table invites.
>
> So my question is there a way to load balance/partition pg_largeobject
> across disks or otherwise ensure I'm getting the best performance from PG?
>  I know I can use BYTEA (forget its max size off hand, but note that it's
> not exactly JDBC friendly because of all the escaping to be done moving a
> large byte array).  Would I do well, perhaps to clone the idea of
> pg_largeobject for those objects that are say 100KB or less (many will be I
> suspect) and store them in a sequence of BYTEA rows in my own tables as way
> to allow the storage of these blobs across many tables instead of just
> pg_largeobject?  It probably wouldn't be as efficient as the large object
> code, but I'm sure it could be made to work.

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.

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.

merlin