2

From this post about Binary data performance in PostgreSQL, I read that storing (large) binary data can be very slow in PostgreSQL (compared to storing the same data in file systems). This seems to apply to PostGIS data such as rasters or geometries with many points. And there are many reports of slowness associated with pgRaster (e.g. Poor performance with storing large rasters in PostGIS and visualising in QGIS, Why is pgraster much slower?)

According to the linked post and this discussion, one reason why TOAST slows down access is that it can involve compressing and decompressing data. And there is mention of using the external storage strategy to speed up access as it disables compression.

Based on this, I would imagine that compression should be disabled by default. Because it messes up the ability to predict where coordinates and pixel values are from index numbers. I guess sequential search has to be used (instead of random access), and the whole BLOB has to be read into memory in order to to find a individual coordinate or pixel value.

These are just thoughts, but I don't really know much about PostGIS implementation/desgin.

My questions are:

Does storage strategy have a significant impact on the speed of data access?

Are PostGIS binary data (geometry and raster) external (uncompressed) by default?

If the impact is significant, is there a way to change the default storage strategy in PostGIS to, e.g. external?

tinlyx
  • 11,057
  • 18
  • 71
  • 119
  • 2
    This is a good question, but one even the core devs answered with "let's just do 'main' storage"; it seems reasoning might have been the storage size of uncompressed TOAST data, which may see an increase of > 50% (both for relation/TOAST and indexes) and usually is not at a premium, and the fact that only those objects would ever benefit that exceed the page size (and thus getting moved to compressed TOAST by default). – geozelot Jun 02 '21 at 07:03
  • 1
    The spatial index is independent from the storage type, so the stored geometry access is not systematically needed. When it is required, we either want the entire geometry (like for display purposes) in which case de-compression time is marginal, or we want to compute something with the geometry (intersection etc), in which case, since the geometry is big enough to be toasted, it would be much more efficient to save and work with a subdivided version of the geometry – JGH Jun 02 '21 at 11:49

0 Answers0