According to Can PostgreSQL index array columns?, PostgreSQL can index array columns.
Can it do searches on an array column as efficiently as it does for non array types?
For example, suppose you have a row from a questions table (like SO):
title: ...
content:...
tags: [ 'postgresql', 'indexing', 'arrays' ]
And you want to find questions with the tag 'postgresql'. Would storing the relationship in a join table be faster for searching?
And yes, each column would have an index.
GIN and GiST indexes are generally bigger than a simple b-tree, and take longer to scan. GIN is faster than GiST at the cost of very expensive updates.
If you store your tags in an array column then any update to the row will generally require an update to the index on the array. Under some circumstances HOT will permit this to be skipped, but it's not something you can rely on. So you'll have more index updates and more index bloat.
On the other hand, you're avoiding the need to scan a b-tree for the IDs of the desired objects then fetch them from the main table via a join. You're also saving a fair bit of space by using an array instead of paying the 28 byte per row overhead for each tag in the join table.
If your insert and update rate on the main table is fairly low - including changes to tags - then GIN might be a suitable choice. Otherwise I'd probably go for a typical b-tree on a join table, with a secondary index on (tag, object_id) so that index-only scans can be used to look up the object(s) that have a given tag.
In the end the best thing to do is benchmark it for a simulation of your workload.
