Is it better to use tables instead of arrays field type in PostgreSql when arrays do not exceed 50 elements?

Question

Or better said: When to use array as a field data type in a table?

Which solution provides better search results?

Answer 1

I've considered this problem as well and the conclusion that I came to, is to use arrays when you want to eliminate table joins. The number of elements contained in each array isn't as important as the size of the tables involved. If there are only a few thousand rows in each table, then joining to get the 50 sub rows shouldn't be a big problem. If you get into 10's or 100's of thousands or rows, you're likely to start chewing through a lot of processor time and disk i/o though.

Answer 2

The tables will always provide better search results assuming you're querying something within the actual array. With a subtable, you can index the contents trivially, whereas with an array, you'd have to literally create 50 indexes (one for each potential element within the array).

Answer 3

I avoid arrays for 2 reasons:

• by storing more than one attribute value in a cell you violate the first normal form (theoretical);
• you have to perform some extra, non-SQL related, processing each time you need to work with individual elements of the arrays (practical, but a direct consequence of the theoretical one)