Re: Bytea(TOAST) vs large object facility(OID)

kosalram Babu Chellappa wrote:
> We have a requirement to store images/documents with an average size of 1-2MB on PostgreSQL database.
> We have PostgreSQL 9.2.4 running on Red hat linux 64 bit. We decided to setup a stand alone postgreSQL
> server without streaming replication to host the images/documents only. We are new to postgreSQL and
> we heard a lot of conversation about using Bytea vs Large object facility. We would be inserting and
> retrieving document as whole using java webservices call from hibernate/JPA interface into postgreSQL
> database. Is there any performance benchmark when using ByteA vs Large object facility? Is there a
> general guidance to use one of these?

I don't know anything about Hibernate, but since bytea is handled like
any other regular data type, it should not present a problem.
To handle large objects, you need to use the large object API of
PostgreSQL, which makes large objects different from other data types.

Second, large objects are stored in their own table, and the user table
only stores the object ID. When a row in the user table is deleted, the
large object won't go away automatically; you'd have to write a trigger
or something like that.

The real advantage of large objects comes when they are big enough that
you don't want to hold the whole thing in memory, but rather read and write
them in chunks.

Since this is not the case in your setup, I think that bytea is better for you.

Going back a step, do you really want a database just to hold images and
documents? That will be slower and more complicated than a simple file service,
which would be a better solution for that requirement.

Yours,
Laurenz Albe