PostGIS ST_AsPNG faster than ST_PixelAsCentroids

Question

I'm hoping to export a PostGIS raster into a non-GIS enabled database. Ideally, I'd like to be able to have three columns in the output: latitude, longitude, raster_value. Easy enough.

Ideally, I'd accomplish this something like the following:

CREATE TABLE
    le_output
AS SELECT
    val,
    ST_AsText(geom) AS coord
FROM
    (
        SELECT
            (ST_PixelAsCentroids(rast, 1)).*
        FROM
            raster_table
    ) AS inside;

The problem is that this runs really slowly (hours). However, I can do something like this in a matter of 2 or 3 minutes:

SELECT
    ST_AsPNG(rast)
FROM
    raster_table;

However, if I go that route, now I have to do some post-processing in Python or some other language to open the PNG, process all the pixels, do the math against the raster grid and scale to find the lat/longs, etc. It's a huge pain I'd like to avoid.

Anybody have suggestions for a higher performance way to accomplish this? All I want is a list of raster pixel coordinates and their values, which seems like a trivial operation. I'm not sure why it's so darn fast with ST_AsPNG but so slow otherwise.

(For what it's worth, I've also tried joining the raster table to a list of every x/y coordinate location possible in the raster and using ST_Value. That's slow as molasses as well.)

Edit: As requested, the rasters are about 3500 x 3500 pixels, on average.

Answer 1

Vince is right, rasters are not supposed to be in a database as individual cell values. It's a waste of resources.

There are however valid reasons to store pixel values as separate entities. In science, Hierarchical data formats are doing a bit the same thing, where every cell has it's own XY value but also references to arrays of other data (like multiple bands in one raster). This goes in the direction of column storage and you could mimic that by at least storing all your raster values in one row with the help of arrays. So you instead of {X,Y,value} you do: {X:[values],Y:[values],value1:[values]} in one row. There are other disadvantages to it, but at least it may be quicker and still compatible with non-gis databases.

(I understand this is answer is missing a lot of detail but it is more like giving you a direction to look for)