Postgres array columns are one of those features that looks like a violation of relational principles and turns out to be the right tool for a narrow but real set of problems. The question is not whether arrays are correct in some abstract sense but whether they fit the access patterns of the workload. Knowing when arrays earn their cost and which operators make them queryable is the difference between a clean schema decision and an unmaintainable one a few quarters later.
What array columns actually do
A Postgres array column is a column whose values are arrays of a base type. The base type can be a built-in like text or integer, a domain type, a composite type, or even another array (Postgres supports nested arrays). The array is stored as a single value in the column, with the array dimensions and elements encoded in the underlying storage. The column is not a separate table; the array elements have no independent existence outside the row that owns them.
The storage cost is roughly proportional to the array length, with a small per-array overhead for the length and dimension metadata. Arrays that grow beyond the page size are TOASTed like other large values, with the same compression and out-of-line storage trade-offs. The query cost depends entirely on which operators are used and which indexes are available.
The three cases where arrays earn their cost
The first case is fixed-size or bounded sets of related values that are always accessed together. A row representing a daily metric might have an hourly_values column of type double precision[24] holding the 24 hourly samples for that day. The application always reads or writes the full array; it never queries for a specific hour across many days without also pulling the other 23 hours from the same rows. The array storage is more compact than a separate hourly_values table with 24 rows per day, and the access pattern matches the data layout.
The second case is tags or labels attached to a row where membership queries are the dominant access pattern. A blog post might have a tags column of type text[] holding the assigned tags. Queries like "find posts tagged with postgres" use the array containment operator and can be supported by a GIN index. The alternative is a separate post_tags table with one row per (post, tag) pair, which is more normalized but adds JOIN cost to every query that lists posts with their tags.
The third case is small, bounded lists where the order matters and the elements are not independently queryable. A row representing an itinerary might have a stops column of type point[] holding the geographic stops in order. The application reads the array in full to render the itinerary; it does not query for stops independently of the itinerary they belong to. The array preserves the order naturally; the alternative table would need a sequence column to encode the order.
The operators that make arrays queryable
The containment operators &&, @>, and <@ are the workhorses of array queries. The && operator returns true if the two arrays share any element (overlap). The @> operator returns true if the left array contains all elements of the right (left is superset). The <@ operator is the reverse: the left array is contained by the right (left is subset). These operators are supported by GIN indexes, which makes them fast even on large tables.
The ANY and ALL constructs convert an array to a value list usable in comparison expressions. WHERE value = ANY(arr) is equivalent to WHERE arr @> ARRAY[value], with the same query plan and index usage. The ANY form is sometimes more readable when the array is a parameter and the value is a column; the @> form is sometimes more readable when both are columns.
The unnest function expands an array into a set of rows, which is useful for joining with other tables or for aggregating across array elements. SELECT unnest(tags) AS tag, COUNT(*) FROM posts GROUP BY tag produces the tag-frequency distribution across all posts. The unnest is a set-returning function that can appear in the FROM clause, which makes the syntax natural for joins.
The array_agg aggregate is the reverse: it collects values from multiple rows into an array. SELECT post_id, array_agg(tag ORDER BY tag) FROM post_tags GROUP BY post_id produces the tags-per-post mapping in array form. The ORDER BY clause inside the aggregate is the cleanest way to produce a deterministically-ordered array, which matters for snapshot comparison and for stable output.
The array subscripting operators arr[1] through arr[N] access individual elements by 1-based index. The cardinality function returns the array length. The array_length(arr, dim) returns the length along a specific dimension for multi-dimensional arrays. These are the basic element-level operations and are useful in expressions but do not support indexing on the element level.
GIN indexes for array containment
The canonical index for array containment queries is the GIN index. CREATE INDEX ON posts USING GIN (tags) creates an inverted index where each tag value points to the set of rows containing it. Queries with @> and && predicates use the index to find candidate rows. The recheck step verifies the actual containment, but the bitmap scan from the index is the heavy lifting.
The GIN index cost is the standard inverted-index trade-off: writes are expensive because every distinct value in the array triggers an index update, but reads are fast for any containment query. The fastupdate option defers writes to a pending list and applies them in batches, which reduces write latency at the cost of a slightly delayed index. Large array columns with hundreds of elements per row push the GIN write cost up rapidly.
The GIN index does not support every query. WHERE arr[1] = 'value' cannot use a GIN index because the index does not know about positional access; it only knows about set membership. WHERE cardinality(arr) > 5 cannot use a GIN index for the same reason. These cases require either sequential scans or expression indexes on the specific computation, which is feasible but loses the GIN containment benefits.
When arrays are wrong
Arrays are wrong when the elements need to be queried independently of the parent row. If the application frequently asks "find all posts with this tag and this status," and tags and status both live as columns on the posts table, an array containment query plus a column predicate works fine. If the application also needs "find the post counts per tag broken down by date range," the array structure forces unnest in every query and the JOIN ergonomics get worse than a separate post_tags table.
Arrays are wrong when the elements have their own metadata. If a tag has its own description, color, or category, the metadata cannot live in the array; it needs a separate tags table with a foreign key from post_tags. Once the metadata table exists, the array column is redundant with the join table and the design pressure points toward removing the array.
Arrays are wrong when the cardinality is unbounded. An array column with arbitrary growth potential becomes a TOAST problem, an index size problem, and an UPDATE-amplification problem. Updating any element rewrites the whole array, which means even targeted updates carry the cost of the full array. Tables with high update rates and unbounded array columns tend to accumulate bloat faster than the autovacuum can manage.
The migration path
The migration from an array column to a join table is straightforward and worth knowing about because it is reasonably common. CREATE TABLE post_tags (post_id BIGINT REFERENCES posts, tag TEXT) followed by INSERT INTO post_tags SELECT id, unnest(tags) FROM posts populates the join table from the existing array column. The array column can then be dropped or kept temporarily as a read-only fallback during the application migration.
The migration from a join table to an array column is also straightforward. SELECT post_id, array_agg(tag ORDER BY tag) FROM post_tags GROUP BY post_id produces the array values per post, which can be inserted into a new column on the posts table. The choice depends on which access pattern dominates and which design constraint is more important.
Our use across the four products
DocuMint stores the list of templates a customer has access to as a text[] column on the customer record because the access pattern is "load all templates for this customer" and the list is bounded at a few dozen entries. The GIN index on the column supports the rare "find customers with access to template X" query without forcing a join. The trade-off has held up across two years of operation.
CronPing stores monitor tags as a text[] column on the monitors table. The GIN index supports the tag-filter UI in the dashboard. The trade-off here is tighter because monitors have higher write rates than templates, but the write cost has been acceptable.
FlagBit stores targeting rule attribute values as text[] columns for the IN operator. The arrays are bounded at the documented per-flag limit and queries always pull the full array as part of flag evaluation. The schema fits the access pattern cleanly.
WebhookVault stores received header names as text[] for fast lookup of "endpoints that received a specific header" queries, with the full header values living in a JSONB column. The split is awkward but reflects the actual access pattern, where the header names are queried for filtering and the values are read for inspection.
Three patterns that fail
The first pattern that fails is treating array columns as a substitute for normalization. Schemas that start with an array column and accumulate parallel array columns (tags, tag_priorities, tag_created_at, tag_created_by) end up reinventing a join table inside the row in the most painful possible way. The right design is to recognize the inflection point and migrate to a join table before the parallel-array trap closes.
The second pattern that fails is using arrays for unbounded growth. The TOAST cost is fine for occasional large values but becomes unmanageable when the array is updated frequently. The write amplification from rewriting the full array on each update dominates the workload. The fix is a separate table that allows targeted updates.
The third pattern that fails is mixing positional and set semantics. An array column where the position is meaningful (stops in an itinerary) and another set of queries treats it as a set (find itineraries that visit a specific stop) leads to confusion. Either the position matters and the queries should not treat the column as a set, or the position does not matter and the column should not be an ordered array. Picking one and sticking with it prevents the design from drifting into incoherence.
The deeper observation is that array columns are one of the cases where the relational orthodoxy and the operational reality come into productive tension. The orthodoxy says that repeating groups should be normalized; the operational reality says that some access patterns are naturally array-shaped and forcing them into normalized form costs more than it saves. The Postgres design choice to support arrays as first-class column types reflects the recognition that the access pattern should drive the schema, not the other way around, and the operator set around arrays makes the design choice work in practice.
Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.