I would appreciate if someone could clarify the aspects of using array columns.
We need to store up to ten related integer values per row and currently it is implemented as a varchar column that holds a string that is concatenated by a trigger function. Something like this:
FOR var IN (SELECT id FROM support_table WHERE...) LOOP str := concatenate string... END LOOP; UPDATE main_table SET id_string = str WHERE...
So we have a string like this "1201,1202,1203,201" in the main_table varchar column that is parsed by the client app. Recently I realized that it would be more natural to use the array column in the main table to store the values - no looping, concatenation, parsing, should take less space. After implementing it I run explain on selects from the main table to compare string vs. array and results are somewhat confusing.
EXPLAIN SELECT id_string FROM main_table WHERE... returns row width: 3 where actual value of the id_string = "1201,1202,1203,201"
EXPLAIN SELECT id_array FROM main_table WHERE... returns row width: 26 for the same values
It looks like array takes more space than a string containing the same values. Another strange thing is that for the varchar column explain shows width 3, it's to low. I thought that it might be related to TOAST but I understand that TOAST kicks in only if the row size is more than 2kb and
EXPLAIN SELECT * FROM main_table WHERE... returns row width: 251
Hence the questions:
-- Could someone help me to interpret the explain readings above?
-- Is storing integers in array is really more space efficient approach?
-- Is there a noticeable performance difference in reading array vs varchar columns? Creating id string is relatively rare operation in our case and if reading strings is faster may be it makes sense to have the overhead of string concatenation in the trigger.
-- Is it possible to estimate how much slower the string concatenation trigger function would be in comparison to one that insets into array column on up to 10 values per string/array? The trigger still will be executed fairly often.
