Postgresql performance comparison between arrays and joins Postgresql performance comparison between arrays and joins postgresql postgresql

Postgresql performance comparison between arrays and joins


These values will need to be frequently searched

Searched how? This is crucial.

Prefix pattern match only? Infix/suffix pattern matches too? Fuzzy string search / similarity matching? Stubbing and normalization for root words, de-pluralization? Synonym search? Is the data character sequences or natural language text? One language, or multiple different languages?

Hand-waving around "searched" makes any answer that ignores that part pretty much invalid.

so lookup performance is critical. Would the array solution be faster, or would it be faster to use joins to lookup the values in the separate table?

Impossible to be strictly sure without proper info on the data you're searching.

Searching text fields is much more flexible, giving you many options you don't have with an array search. It also generally reduces the amount of data that must be read.

In general, I strongly second Clodaldo: Design it right. Optimize later, if you need to.