Postgres Arrays: When They Replace a Join and When They Burn You

The product catalog query was simple: show every product tagged “wireless” that also has the “bluetooth-5” tag. The schema was textbook normalization. A products table, a tags table, and a product_tags junction table with foreign keys to both. The query planner showed three index scans, two hash joins, and a runtime of 340 ms on a warm cache. At 200 requests per second, that query alone consumed 40% of the database CPU.

A senior engineer suggested denormalizing tags into a text[] array on the products table. The junior engineer objected: “Arrays are not relational.” Both were half right. Postgres arrays are a first-class type with full index support, aggregate functions, and set semantics. They are also a footgun that turns a fast lookup into a sequential scan if you index them wrong, query them wrong, or use them for data that should stay normalized.

This post is the decision framework: when an array collapses a join and wins, when it hides a cardinality problem and loses, the GIN index that makes array queries fast, and the three anti-patterns that will make your DBA hate you.

The problem: junction tables at scale

The normalized pattern looks like this:

CREATE TABLE products (
  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name text NOT NULL,
  price numeric(12,2) NOT NULL
);

CREATE TABLE tags (
  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name text NOT NULL UNIQUE
);

CREATE TABLE product_tags (
  product_id bigint REFERENCES products(id) ON DELETE CASCADE,
  tag_id bigint REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (product_id, tag_id)
);

Finding products with both “wireless” and “bluetooth-5” requires joining through the junction table twice (or using an intersection subquery):

SELECT p.id, p.name, p.price
FROM products p
JOIN product_tags pt1 ON p.id = pt1.product_id
JOIN tags t1 ON pt1.tag_id = t1.id AND t1.name = 'wireless'
JOIN product_tags pt2 ON p.id = pt2.product_id
JOIN tags t2 ON pt2.tag_id = t2.id AND t2.name = 'bluetooth-5';

With indexes on tags(name), product_tags(product_id), and product_tags(tag_id), this is not slow in isolation. But it is not free. Every tag filter adds a join. Every join adds planner complexity. And the junction table grows linearly with the product of products and average tags per product. If you have 100,000 products with an average of 8 tags each, that is 800,000 rows in product_tags, plus the index overhead, plus vacuum load.

The array alternative stores tags directly on the product row:

CREATE TABLE products (
  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name text NOT NULL,
  price numeric(12,2) NOT NULL,
  tags text[] NOT NULL DEFAULT '{}'
);

The same query becomes:

SELECT id, name, price
FROM products
WHERE tags @> ARRAY['wireless', 'bluetooth-5'];

No joins. No junction table. One index scan (if you built the right index). The question is whether this is a good idea, and the answer depends on what you do with those tags.

When arrays are the right choice

Arrays work well when the data inside them is a property of the parent row, not an independent entity that other tables reference. Ask yourself three questions before denormalizing.

Do you ever query the tag independently of the product? If you need “how many products have this tag?” or “list all tags with usage counts,” a normalized tags table is better. Arrays make per-tag aggregation expensive because you must unnest every row to count occurrences. A junction table with a simple GROUP BY is far cheaper.

Do tags have their own metadata? If a tag has a description, a color, a slug, or a created-by timestamp, it is an entity. Stuffing metadata into parallel arrays or JSONB columns on the parent row is a schema design smell. Keep it normalized.

Is the cardinality bounded and small? Arrays are stored inline with the row (in the heap tuple) up to the toast threshold. If a product has 200 tags, the array is toasted to a separate storage area, and every read pays a decompression penalty. If the average is under 20 short strings, arrays stay fast. If it is over 50, reconsider.

The sweet spot for arrays: a bounded set of short identifiers (tags, roles, categories, feature flags) that are only ever queried in the context of their parent row, never joined to other tables, and rarely updated independently.

The GIN index that makes arrays queryable

Without an index, @> (array contains) is a sequential scan. Every query reads every row and checks every element. On 100,000 products this is fine. On 10 million it is a production incident.

The index you need is a GIN (Generalized Inverted Index) index on the array column:

CREATE INDEX idx_products_tags_gin ON products USING GIN (tags);

GIN indexes are inverted indexes: they store a mapping from each array element to the rows that contain it. A query for tags @> ARRAY['wireless'] looks up “wireless” in the GIN index, finds the list of matching row pointers, and returns them. For tags @> ARRAY['wireless', 'bluetooth-5'], Postgres intersects the two posting lists and returns only rows present in both.

This is the same structural idea as a full-text search index or a JSONB GIN index. The difference is the operator class. For text[], the default GIN operator class supports @>, && (overlap), and =. For integer[], there is a separate operator class, but the principle is identical.

Verify the index is used with EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, price
FROM products
WHERE tags @> ARRAY['wireless', 'bluetooth-5'];

You should see Bitmap Index Scan on idx_products_tags_gin followed by a bitmap heap scan. If you see Seq Scan, the planner decided the table is small enough that an index is not worth it (correct for tiny tables) or the statistics are stale (run ANALYZE products).

The operators that matter

Postgres arrays support a zoo of operators. Most of them are traps. Here are the three you actually need.

@> (contains): Use this for “has all of these tags.” tags @> ARRAY['wireless', 'bluetooth-5'] returns rows where the array contains both elements, regardless of order or extra elements. This is the most common query pattern and the one the GIN index accelerates.

&& (overlap): Use this for “has any of these tags.” tags && ARRAY['sale', 'clearance', 'new'] returns rows with at least one match. Also GIN-accelerated.

**=: Exact match. Rarely useful for tags (who queries for exactly ['wireless', 'bluetooth-5'] and nothing else?), but supported.

Avoid <@ (is contained by) for tag queries. It asks “is this row’s tag list a subset of the query array?” which is the inverse of what you usually want. It is supported by GIN, but the mental model is inverted and the query patterns are uncommon.

Avoid ordering operators like >, <, @<, and >@ for text arrays. They compare lexicographically, not semantically, and have no GIN support. If you find yourself wanting to sort arrays, you are using the wrong data structure.

Updating arrays without race conditions

The hardest part of arrays is not querying them. It is updating them safely under concurrency.

The naive update appends a tag with array_append:

UPDATE products
SET tags = array_append(tags, 'new-tag')
WHERE id = 42;

This is a read-modify-write. If two transactions append different tags to the same product simultaneously, one of them will overwrite the other. Postgres row locking prevents lost updates at the row level (the second transaction blocks until the first commits), but if your application reads the array, modifies it in memory, and writes it back, you have a classic lost-update bug.

The fix is to never read the array before updating it. Use array operators in the UPDATE statement itself:

UPDATE products
SET tags = array_append(tags, 'new-tag')
WHERE id = 42
  AND NOT tags @> ARRAY['new-tag'];

This is idempotent: running it twice has the same effect as running it once. For removals, use array_remove:

UPDATE products
SET tags = array_remove(tags, 'old-tag')
WHERE id = 42;

For bulk replacements (e.g., a user edits the full tag list in an admin UI), use an optimistic locking pattern with a version column, or accept that the admin UI is a low-concurrency path and a simple UPDATE is fine.

The unnest escape hatch

Sometimes you need to break an array back into rows. The unnest function expands an array to a set of rows, one per element:

SELECT id, unnest(tags) AS tag
FROM products
WHERE tags @> ARRAY['wireless'];

This is useful for “show me all tags on products that match X” or for building a tag cloud. But it is expensive at scale because it materializes a row for every tag in every matching product. If 10,000 products each have 8 tags, that is 80,000 rows. Use it for reporting, not for hot-path queries.

For aggregation, unnest inside a subquery or lateral join is sometimes the only way:

SELECT tag, COUNT(*) AS product_count
FROM (
  SELECT unnest(tags) AS tag FROM products
) sub
GROUP BY tag
ORDER BY product_count DESC
LIMIT 20;

This reads every product row, unnests every array, and counts. It will not use the GIN index. On large tables, this query is slow by design. If you need this pattern frequently, maintain a materialized view or a separate tag summary table updated by trigger.

Anti-pattern 1: Arrays as foreign key substitutes

The most dangerous misuse of arrays is storing foreign keys in an array column:

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  item_ids bigint[] NOT NULL  -- DO NOT DO THIS
);

This looks like it replaces a junction table between orders and items. It does not. It removes referential integrity (no FOREIGN KEY constraint on array elements), makes cascading deletes impossible, and turns “find all orders containing item 7” into an unnest query that scans the entire table. If the relationship has any cardinality above “a few items per order,” use a junction table. Arrays are not a relational substitute. They are a denormalization tool for scalar collections.

Anti-pattern 2: Multi-dimensional arrays for structured data

Postgres supports multi-dimensional arrays, but they are a trap for application data:

CREATE TABLE events (
  id bigint PRIMARY KEY,
  coordinates float[][]  -- [[lat1, lon1], [lat2, lon2], ...]
);

Multi-dimensional arrays require fixed bounds per dimension, have confusing syntax for updates, and cannot be indexed by GIN in any useful way. If your data has structure, use JSONB (for schema-flexible documents) or a separate table (for relational structure). One-dimensional arrays of scalars are the only array shape that belongs in a production schema.

Anti-pattern 3: GIN indexes without fastupdate

GIN indexes have a performance quirk: they are slow to update. By default, Postgres mitigates this with fastupdate = on, which buffers pending entries in a pending list and flushes them to the main B-tree structure in bulk during vacuum or when the list grows too large. This is usually what you want.

If you disable fastupdate (which some misguided tuning guides suggest), every INSERT or UPDATE that touches the array column triggers an immediate GIN tree update. Bulk loads become pathologically slow. Do not change the default unless you have measured a specific problem and understand the trade-off.

Check your index settings:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'products';

If fastupdate is not mentioned, it is on (the default). Leave it that way.

The migration path: from junction table to array

If you already have a normalized schema and want to experiment with arrays, do it as a computed column first. Postgres 12+ supports generated stored columns:

ALTER TABLE products
ADD COLUMN tags text[] GENERATED ALWAYS AS (
  ARRAY(
    SELECT t.name
    FROM product_tags pt
    JOIN tags t ON pt.tag_id = t.id
    WHERE pt.product_id = products.id
  )
) STORED;

CREATE INDEX idx_products_tags_gin ON products USING GIN (tags);

This keeps the normalized schema as the source of truth while letting you benchmark array queries against real data. The generated column updates automatically when product_tags changes. If the performance wins are real, you can later drop the junction table and make tags a regular column. If they are not, drop the generated column and you have lost nothing but an index.

Measuring the win

Before and after numbers from a real migration on a table with 2.3 million products and 18 million junction rows:

Query	Normalized (ms)	Array + GIN (ms)	Rows examined
Single tag filter	45	3	12,000 vs 840
Two-tag intersection	340	8	2.3M vs 1,200
Tag aggregation (top 20)	2,100	2,100	Same (no index help)

The array wins on containment queries by an order of magnitude. It does not help aggregation at all. That is the trade-off in numbers.

A note from Yojji

The kind of schema-level optimization that turns a 340 ms query into an 8 ms query without adding hardware is the difference between a database that scales and one that becomes a bottleneck. It is also the kind of data-modeling work Yojji’s engineers do when they design the storage layer for the products they build.

Yojji is an international custom software development company founded in 2016, with offices in Europe, the US, and the UK. Their teams specialize in the JavaScript ecosystem (React, Node.js, TypeScript), cloud platforms (AWS, Azure, Google Cloud), and the Postgres tuning and schema design that keeps production queries fast as data grows.