Re: 4B row limit for CLOB tables

On Thu, 29 Jan 2015 10:41:58 -0800
Steve Atkins <steve(at)blighty(dot)com> wrote:
>
> >> Is this fact mentioned in the documentation anywhere? Is there an
> >> official source for this? (If not, maybe consider this a feature
> >> request to mention it in the documentation on BLOB).
> >> Cheers and thanks.
> >> -roger
>
> I'm not sure whether it's mentioned explicitly, but large objects are
> referenced by an OID, which is a 32 bit value (and a global resource).
>
> If you had 4B BLOBs, though, running out of OIDs would probably be
> the least of your worries.

Because of how other RDBMs systems use BLOB-ish types, I think a lot of
people get confused about when to use bytea vs. a large object in
PostgreSQL ... and as a result, end up using large objects more often
than is really necessary.

Large objects are for LARGE data ... keep in mind that a bytea column
can store up to 4G of data. While that seems excessive, it's perfectly
reasonable to use it to store images and other data that's frequently
in the "several megabytes" range. In general, if you can transfer the
entirety of the data in a single shot, then bytea will work fine for
you.

Large objects are for something more like streaming media, where it's
impractical to store the entire file in memory, even for a short time.
I.e. you'd read ~100k from the DB into application memory, do
processing on that data, then discard it and read another 100k. While
large objects certainly fill a nitch and for some uses are the only
way to make things work, I have never actually seen an implementation
where large objects were the right solution to the problem. (Of course,
I've never worked in the movie or music industry) but I've found
that bytea is usually the correct storage method for things like PDF
files, wordprocessor files, images, etc ... anything where the entire
file needs to be delivered before it can be used.

--
Bill Moran