Re: TOAST, large objects and ACIDity

On 7/10/07, Benoit Mathieu <benoit(dot)mathieu(at)mist-technologies(dot)com> wrote:
> I saw postgres offers at least 2 method : bytea column with TOAST, or
> large objects API.

From the documentation:

> All large objects are placed in a single system table called pg_largeobject.
> PostgreSQL also supports a storage system called "TOAST" that automatically
> stores values larger than a single database page into a secondary storage area
> per table. This makes the large object facility partially obsolete. One remaining
> advantage of the large object facility is that it allows values up to 2 GB in size,
> whereas TOASTed fields can be at most 1 GB. Also, large objects can be
> randomly modified using a read/write API that is more efficient than performing
> such operations using TOAST.

My take: Stick with TOAST unless you need fast random access. TOAST is
faster, more consistently supported (eg., in Slony) and easier to work
with.

If you need fast random access, use the file system. Will you ever be
updating parts of the contents of a single file, or will you be
writing each file just once? If the latter, writing your own robust,
ACID-compliant file storage is fairly trivial.

> * I really care keeping my transaction fully ACID. Documentation on
> large objects doesn't explicitly say if lo_import an lo_export (and
> other primitives) are fully ACID. Some ideas ?

Since the lo_* functions are implemented as relational operations on
the internal large object table, they're ACID-compliant.

Alexander.