[postgis-users] Re: A PostGIS-Raster data proposal

[Marshall, Steve]

> I was fond of going to LO route in my flirting 
> with the idea, precisely because it made the use 
> of things like GDAL or (in the future) GRASS 
> libraries pretty straightforward -- grab a 
> file-handle and go.  Custom PgSQL types can 
> include an LO reference.  I never investigated 
> the transactional issues involved, that's an 
> interesting point.

I have been thinking about both LO's and TOAST as the underlying storage
for raster-in-DB.  Here's some more of my thoughts on the topic.

In addition to the transactional issues, there is another practical
isssue with using LO's for raster-in-DB: all the LO data for an entire
database is stored in one relation (pg_largeobject).  I have used LO's
for large amount of binary data in the past, and I ended up with a
pg_largeobject table that was hundreds of gigabytes in size.
pg_largeobject's primary index ended up being quite large, too (I think
several GB, but I can't remember).  With such a large table, vacuuming
(and hence space recovery) is very slow, and with such a large index,
access is also slow because it is hard to load the entire index into
core memory at once.

This table storage issue is making me lean towards TOAST, rather than
LO's, as the underlying storage for raster-in-DB.  LO's have a nicer I/O
interface, but TOAST stores data in multiple tables (one for every table
with at least one toastable column).  For large image repositories, the
multi-table storage will be huge advantage, in my opinion.  It feels
easier to me to expand the I/O access of TOAST than it does to save LO's
in multiple relations; however other people may see a better path
through this problem.  For reference, the I/O deficiency with TOAST is
that is does not support writing to random access locations, only random
access reads.

Regarding transactional integrity for random access writes, TOAST and LO
both have the same underlying storage model, which could be made to
allow partial updates without violating transactional integrity.  In
both cases, the data from one BLOB is stored in several "chunk" records
in an auxilliary table.  In principle, each "chunk" record can be
updated (replaced) independently, because updates operate on a per-tuple
basis. From my reading of the LO function "inv_write", it looks like
per-tuple updates is what LO's do; TOAST would require an interface to
do the same thing.   While it might seem wasteful to have to replace a
~2000 byte tuple just to flip a bit, it is much more efficient than have
to replace an entire 1GB image.

One other issue with both LO's and TOAST is a relatively low maximum
data size.  LO's are limited to 2GB, TOAST to 1GB.  While LO's are
better than TOAST in this regard, either max size is small enough that
it requires large raster data sets to be reorganized into tiles that can
meet these size requirements.  

Steve Marshall