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

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)

I know this post is 15 years old at this point, but it was my top Google result, so I figured I'd chime in.

As always it depends on what your data distribution looks like and what your queries look like. A common use-case where arrays comes up is tags (think hash-tags, etc..). Even this post is tagged with [arrays] and [postgresql]. Tags typically have a fairly heavy tail distribution (a few tags account for the vast majority of occurrences). For this type of data, and unless you're just counting tags you'll almost always be better of using an array of strings. The rationale is that most of the time you probably care about tags associated with individual documents.

There is a great post about it on database soup. The conclusion is:

The overall winner is an array of text, with a GIN index. This is better for one-tag searches, worlds faster for two-tag searches, and competitive at other tasks. It's also the smallest representation, and becomes smaller and faster still if you actually put the array of tags in the documents table. Still, there are times that you would want to use the traditional child table with plain text tags: if you build tag clouds a lot or if you never search for two tags and your ORM can't deal with Postgres arrays.

Don't know how long these links stay live so I'll paste the results below: http://sqlfiddle.com/#!17/55761/2

TLDR; searching a table index and then joining is fast, BUT adding a GIN index (using gin__int_ops) to a single table with an array column can be faster. Additionally, the flexibility of being able to match "some" or a small number of your array values might be a better option e.g. a tagging system.

create table data (
    id serial primary key,
    tags int[],
    data jsonb
);

create table tags (
    id serial primary key,
    data_id int references data(id)
);

CREATE INDEX gin_tags ON data USING GIN(tags gin__int_ops); 

SET enable_seqscan to off;

with rand as (SELECT generate_series(1,100000) AS id)
insert into data (tags) select '{5}' from rand;

update data set tags = '{1}' where id = 47300;

with rand as (SELECT generate_series(1,100000) AS id)
INSERT INTO tags(data_id) select id from rand;

Running:

  select data.id, data.data, data.tags
  from data, tags where tags.data_id = data.id and tags.id = 47300;

and

  select data.id, data.data, data.tags
  from data where data.tags && '{1}';

Yields:

Record Count: 1; Execution Time: 3ms
QUERY PLAN
Nested Loop (cost=0.58..16.63 rows=1 width=61)
-> Index Scan using tags_pkey on tags (cost=0.29..8.31 rows=1 width=4)
Index Cond: (id = 47300)
-> Index Scan using data_pkey on data (cost=0.29..8.31 rows=1 width=61)
Index Cond: (id = tags.data_id)

and

Record Count: 1; Execution Time: 1ms
QUERY PLAN
Bitmap Heap Scan on data (cost=15.88..718.31 rows=500 width=61)
Recheck Cond: (tags && '{1}'::integer[])
-> Bitmap Index Scan on gin_tags (cost=0.00..15.75 rows=500 width=0)
Index Cond: (tags && '{1}'::integer[])

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).

I think that arrays have to be used for some custom data. But for foreign keys - it's better to use link table (or something else but column per key). This way you have data control at DB level and easy queries for join - you need for join even if you have them in arrays (for full data set) - but arrays much more complicated than "standart" SQL. P.S. Sorry bad english