I'm trying to store a large raster timeseries on PostGIS-2.2 / PostgreSQL 9.5. The data comes as a collection of HDF-EOS (basically HDF4) files. Each file contains 24 bands, representing hourly values of the whole grid for one day. There is 16 years of data (5844 files).
At present, I'm storing each day as a raster with 24 bands, basically because it's the easiest to implement the import for it. The raster is split into 12 tiles, 42x45 (this was decided by raster2pgsql's
-t auto option). I then add an extra field to the table for the date of the raster and after importing each file,
UPDATE swgdn SET raster_date='XXXXXX' WHERE raster_date IS NULL.
It is, in a word, slow. So far the import's been running for around 2.5 hours and it's processed nearly 3% of the import data. I'm not measuring it, but it feels like it's getting slower as more data is inserted.
Is this a reasonable strategy for storing this type of data? Is there one that will perform better?
Would storing the whole timeseries as bands of a single raster perform better? That would be 140256 bands (and likely to grow).
For reference, here's a sample of the script I'm using to import the data:
raster2pgsql -I -t auto -c 'HDF4_EOS:EOS_GRID:"file1.hdf":EOSGRID:SWGDN' swgdn | psql psql -c "alter table swgdn add raster_date text;" psql -c "update swgdn set raster_date='20000101';" raster2pgsql -I -t auto -a 'HDF4_EOS:EOS_GRID:"file2.hdf":EOSGRID:SWGDN' swgdn | psql psql -c "update swgdn set raster_date='20000102';"
and so on.