[postgis-devel] [raster] Memory management and IO concerns

[Mark Cave-Ayland]

On 28/06/11 18:50, Bryce L Nordgren wrote:

>     Unfortunately I don't think so; I think you'd have to disable TOAST
>     on the raster column and then implement some kind of custom grid
>     compression scheme to avoid having to decompress an entire raster as
>     part of the PG_DETOAST_DATUM() call. But someone else who knows
>     better may be able to correct me here.
>
>
> If this is correct, the size limitation on in-database rasters is not
> the full 32Tb (or whatever) of the Postgresql backend, but the amount of
> memory (and swap) present on the server, minus whatever is being used by
> other processes (including other raster operations) on the same server.
> Truly large images will have to be out-database rasters which GDAL
> understands how to "chunk"/"block"/"tile". Unless I'm missing something.
>
> If there is a way to "DETOAST" only part of a raster, it may be possible
> to develop a GDAL driver which understands the Postgresql server-side
> "SLICE"ing.
>
> Actually, I've already hit something else which may be a more stringent
> limit: when trying to load a single band of a Landsat image (not tiled,
> loaded as a single raster), I got an out of memory error. I assumed this
> was in the parser, as the SQL file generated by raster2pgsql.py used a
> single "INSERT" statement to plop in the entire 64Mb binary image. I
> hadn't thought of this before, but large rasters may need to be loaded
> via many "ST_SetRasterDataBlock" calls rather than one INSERT.

In actual fact, since raster is a type then you are more likely to get 
hit by the single column limit of 1Gb which doesn't seem like a lot:

http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F

Perhaps the raster team need to start looking at the large objects API 
instead: 
http://www.postgresql.org/docs/9.0/interactive/largeobjects.html which 
effectively gives you an independent database object accessible through 
a file handle-like API. Hence you can seek to an absolute position and 
then just read the parts of the image you are interested in.


ATB,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs