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.
Thanks for any hints or ideas....
David
