Does the PostgreSQL array inTOAST storage have random access times?
1
vote
2
answers
382
views
This question is related to PostgreSQL TOAST storage and the GIS.SE question: Should TOAST compression be disabled for PostGIS?
*Basically, I was wondering if there is any **guarantee** of constant time complexity (O(1)) for the random access of array elements?*
That is, to get
arr[n]
, is the worst case number of steps required a constant (i.e. O(1)
) or something else (O(log n)
etc.)?
I am asking because in certain data forms such as PostGIS lines or rasters, the main data is logically an array (of coordinates). It is known that access time for such data can be extremely long once the data amount exceed a certain limit (like 500 points). A possible reason is that data of such sizes are transferred to TOAST storage and potentially the data can be compressed (e.g. with the main
storage). It's not clear how PostgreSQL can predict the approximate location of an element and still offer random access time.
The access time for arrays in most programming languages is constant (O(1)
). And that is the point of using an array. Just out of curiosity:
*Do PostgreSQL arrays have constant access times?* (when and when not?)
Asked by tinlyx
(3820 rep)
Oct 12, 2021, 09:45 AM
Last activity: Oct 13, 2021, 11:15 AM
Last activity: Oct 13, 2021, 11:15 AM