When much of a DB is large objects - PG 8.4

Поиск
Список
Период
Сортировка
Искать
От
David Wall
Тема
When much of a DB is large objects - PG 8.4
Дата
Msg-id
4ADD0E32.7000704@computer.org
Список
Дерево обсуждения
When much of a DB is large objects - PG 8.4 David Wall <d.wall@computer.org>
Re: When much of a DB is large objects - PG 8.4 Merlin Moncure <mmoncure@gmail.com>
Re: When much of a DB is large objects - PG 8.4 David Wall <d.wall@computer.org>
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
В списке pgsql-general по дате отправления
От: Christophe Pettus
Дата:
Сообщение: Re: Mac OS Roman import?
От: Merlin Moncure
Дата:
FAQ