Arrays and enums: when each beats a string

Native arrays: a real first-class type

Postgres arrays aren’t a hack — any type can be an array (int[], text[], even jsonb[]), with literal syntax and a rich operator set. They shine for a small, ordered, single-table list that you never join to: a row’s tags, a set of flags, a path.

CREATE TABLE products (
  id   bigint PRIMARY KEY,
  tags text[] NOT NULL DEFAULT '{}'        -- array literal: empty array
);

INSERT INTO products (id, tags) VALUES (1, '{sale,featured,new}');

SELECT * FROM products WHERE 'sale' = ANY(tags);      -- membership test
SELECT * FROM products WHERE tags @> '{sale,new}';    -- contains ALL of these
SELECT id, unnest(tags) AS tag FROM products;         -- explode to rows
SELECT array_agg(id) FROM products WHERE 'sale' = ANY(tags);  -- collapse to array

The core operators: ANY(arr) / ALL(arr) for “matches some / every element”, @> containment (“contains all of”), unnest() to explode an array into rows, and array_agg() to collapse rows back into an array. And arrays are indexable — a GIN index on a text[] makes @> and = ANY containment queries fast, exactly like JSONB.

CREATE INDEX ON products USING gin (tags);   -- now @> and = ANY use the index

When you find yourself storing a list of related ids in an array, pause here — the catch that decides everything: you can’t put a foreign key on an array element. A tag_id int[] can hold ids that don’t exist in any tags table, and Postgres won’t stop it. Arrays trade referential integrity and join performance for locality and simplicity. Great for a self-contained list; wrong for a relationship.

Enums: ordered and compact

An enum is a custom type with a fixed, ordered set of label values. It’s compact (stored as a 4-byte oid, not the full text) and the order is meaningful — comparisons follow declaration order, which is perfect for things like severity or workflow stages.

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered');

CREATE TABLE orders (
  id     bigint PRIMARY KEY,
  status order_status NOT NULL DEFAULT 'pending'
);

-- ordering follows declaration order, not alphabetical:
SELECT * FROM orders WHERE status >= 'shipped';   -- shipped + delivered

That ordering is the enum’s superpower over a CHECK (status IN (...)) on a text column — you can’t naturally order arbitrary strings by workflow stage, but enum comparisons just work.

The enum ALTER pain

Here’s the tradeoff that bites in production. ADD VALUE to an enum is easy and online in modern Postgres:

ALTER TYPE order_status ADD VALUE 'returned';            -- appended, fine
ALTER TYPE order_status ADD VALUE 'cancelled' BEFORE 'paid';  -- positioned, also fine

But you cannot remove or rename-away a value without effectively rebuilding the type: you’d create a new enum, alter every column that uses it, drop the old type, and recreate any dependent views and functions — a real migration, not a one-liner. Enums are a one-way ratchet: easy to add to, painful to shrink. If your value set is genuinely fixed for the life of the system (severity levels, blood types), the enum’s compactness and ordering are pure win. If it churns — categories the business keeps reorganizing — the rigidity becomes a recurring tax.

Enum vs CHECK vs lookup table

Three ways to model “a column from a small set”, on a spectrum from rigid-and-fast to flexible-and-relational:

• Enum — value set is fixed and ordering matters. Compact, fast, self-documenting; ALTER pain on removal.
• CHECK (status IN ('a','b')) on a text column — small set, no ordering need, easy to edit (just alter the constraint). No order, slightly larger storage, no metadata.
• Lookup table (statuses(code text PK, label text, sort_order int, is_active bool)) with a foreign key — the relational answer when the set evolves, needs metadata, or must be foreign-keyed. You pay a join, but you can add/remove/relabel values as ordinary INSERT/UPDATE/DELETE and attach as many attributes as you like.

The senior heuristic: fixed + ordered → enum; small + flexible → CHECK; evolving or needs metadata → lookup table. Defaulting to a lookup table is rarely wrong; defaulting to an enum on a churning value set is a future migration you scheduled for yourself.