Arrays

PostgreSQL has built-in support for arrays, which are in the “Arrays” and the “Array Functions and Operators” chapters of the PostgreSQL documentation. What’s interesting about PostgreSQL is its ability to handle array elements directly from SQL. Due to GIN indexing, this function includes indexing functions.

Array vs. a lookup table

Arrays can be used to denormalize data and avoid lookup tables. A good rule of thumb for using them that way is that we mostly use the array as a whole, even if we might, at times, search for elements in the array. Heavier processing is going to be more complex than a lookup table.

Use case: User-defined tags

A classic example of a good use case for PostgreSQL arrays is user-defined tags. For the next example, 200,000 USA geolocated Tweets have been loaded into PostgreSQL thanks to the following script: