Arrays
Explore how PostgreSQL supports arrays to handle denormalized data without lookup tables. Understand array usage through practical examples like user-defined tags in Twitter data and learn to query and aggregate array elements efficiently.
We'll cover the following...
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 ...