Postgresql performance comparison between arrays and joins

Question

What are the performance implications in postgres of using an array to store values ​​compared to creating another table to store values ​​with a has-many relationship?

I have one table that should be able to store anywhere from about 1-100 different string values ​​in an array column or a separate table. These values ​​will often need to be searched for exact matches, so search performance is critical. Would the array solution work faster or would it be faster to use joins to find values ​​in a separate table?

Answer 1

These values ​​will often need to be searched.

Looking for how? It is important.

Just a prefix pattern? Does the Infix / suffix pattern match? Fuzzy string search / similarity? Stubbing and normalization for root words, de-pluralization? Looking for synonyms? Are data character sequences or natural language text? One language or several different languages?

Manual marking around the “search” makes any response that ignores this part largely invalid.

therefore, search performance is critical. Would the array solution work faster or would it be faster to use joins to find values ​​in a separate table?

You cannot be strictly sure without the right information about the data you are looking for.

, , . , .

, Clodaldo: . , .