How PostgreSQL handles Binary Large Objects (LOB/BLOB): types BYTEA, OID/pg_largeobjects and DATALINK

Hi,

I'd like to get more insight of how to handle (binary) Large Object
Types (LOB/BLOB/CLOB) in Postgres and I think we should clarify the
situation since to me there are some inconsistencies at least in the
docs (or my understanding). I could try to summarize e.g. in the
Postgres Wiki.

In current docs the "BLOB data type" is mentioned as unsupported
(chapter "D.2. Unsupported Features", item "T041-01, Basic object
support" [1])
But we have 1. type bytea, 2. oid/pg_largeobjects (at least in JDBC)
and now 3. type DATALINK [5].
=> Why not taking one of those as BLOB data type?

The Wiki page about ORACLE and BLOBs [2] seems to me little bit
outdated. It says "Binary large object support in Postgres is very
poor and unsuitable for use in a 24/7 environment, because you can't
dump them with pg_dump."
=> Still true? Do we want to dump them anyway?

The JDBC docs [3] says "PostgreSQL provides two distinct ways to store
binary data. Binary data can be stored in a table using the data type
bytea or by using the Large Object feature which stores the binary
data in a separate table in a special format and refers to that table
by storing a value of type oid in your table."
=> I assume the approach with a separate table (pg_largeobjects) is
also feasible in plain PostgreSQL schema/usage without JDBC involed?
=> Should'nt be there some reference in the JDBC docs to the solution
with module lo [3]?
=> Should one disable TOAST compression here by doing an "ALTER column
SET STORAGE EXTERNAL"?

There's the new data type DATALINK [5]. A DATALINK allows to reference
files from tables. Thanks to this, big files (like images) can be
externally saved while maintaining support of access control and
integrity mechanisms. Currently I only find something in the wiki [5]
but can't find a reference to DATALINK in the PostgreSQL docs (except
as reserved keyword).
=> Is this an omission in the docs?

So to make an initial summary, in PostgreSQL AFAIK there are at least
three common ways to manage Binary Large Objects (LOB/BLOB):

1. Column of type BYTEA which stores the LOB within the table
(respectively using TOAST).
2. Column of type OID in the user table and an internal separate table
which separates the LOB away (called pg_largeobjects with a foreign
key to oid and bytea ) Hint to module 'lo' [4].
3. DATALINK type which maintains a link to a specific file in external
storage (Example: "% CREATE TABLE mydata (myid Integer, myimage
DATALINK (40) FILE LINK CONTROL INTEGRITY ALL);" ).

I'm interested in any comments.

Yours, Stefan

[1] http://www.postgresql.org/docs/current/static/unsupported-features-sql-standard.html
[2] http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion#BLOBs
[3] http://jdbc.postgresql.org/documentation/head/binary-data.html
[4] http://www.postgresql.org/docs/current/interactive/lo.html
[5] http://wiki.postgresql.org/wiki/DATALINK