ENUM Traps

Concepts covered: Data Types, Key Generation

subscriptions.status launches with 3 values: trialing, active and cancelled. ENUM looks built to reject anything else, and it is, for a set that never changes; subscription statuses at any company have never stayed still for a year. The 3 storage designs for a constrained value differ in what the fourth value costs.

What ENUM gives and what it takes

In PostgreSQL an ENUM is a named type: CREATE TYPE subscription_status AS ENUM ('trialing','active','cancelled'). A value takes 4 bytes, an invalid insert fails, and ORDER BY status follows declaration order, which puts 'trialing' first where an alphabetical sort puts it last. ALTER TYPE ... ADD VALUE could not run inside a transaction block before version 12, and from 12 the new value is unusable until that transaction commits; renaming a value arrived in version 10. Removing a value has no statement: the path is a new type, an ALTER COLUMN ... TYPE ... USING cast that rewrites the table, and a drop of the old type.

MySQL stores an ENUM as a 1-based integer index into the declared list. Appending a value is an in-place change while the storage width holds; inserting one mid-list renumbers rows and rewrites the table, which on a billion rows locks writes for hours. Sharper still: in non-strict mode an invalid value is stored as '' with index 0, so an application typo writes a row whose status is nothing and whose insert succeeded.

ENUM
  • Values live in the schema; adding one is a DDL change deployed with a migration
  • Compact storage and a rejected insert on a bad value
  • No place for a display label, a sort weight or a retirement date
  • Right for sets fixed by definition: day of week, card suit
Lookup table with a foreign key
  • Values live in rows of subscription_statuses; adding one is an INSERT
  • Carries label, sort_order and retired_at beside the code
  • Costs one join, usually against a table of fewer than 50 rows
  • Right for any set a product manager has opinions about

Between the two sits status VARCHAR(20) with a CHECK listing the allowed values. Changing the list is a drop and re-add of the constraint, and PostgreSQL's NOT VALID skips the full-table scan at add time, so the swap takes milliseconds at any table size. The CHECK gives ENUM's rejection without ENUM's rebuild cost, and lacks only the labels and ordering a lookup table carries.

Booleans that hold 3 values

A nullable BOOLEAN holds true, false and NULL, and WHERE is_active = false skips the NULLs, so a report of inactive subscriptions undercounts by every row whose flag was never set; is_active BOOLEAN NOT NULL DEFAULT false removes the third state. Flag creep is the second failure: is_active, is_paused and is_cancelled have 8 combinations and 3 valid ones, and nothing stops a row being active and cancelled, so 3 mutually exclusive flags are one status column. Third, a boolean can hide a timestamp: cancelled_at TIMESTAMPTZ NULL records that and when, and cancelled_at IS NOT NULL replaces is_cancelled.

UUIDs as identifiers

When ids are generated outside the database, by a mobile client or a service that cannot wait for a sequence, UUIDs replace BIGINT. PostgreSQL's native uuid is 16 bytes; the same value in CHAR(36) is 36 bytes compared as text, which inflates every index and foreign key that references it. The version matters as much. A random UUIDv4 lands on a random index page, so InnoDB's clustered index splits pages continuously and a PostgreSQL btree fills with half-empty leaves. UUIDv7, standardised in RFC 9562 in 2024, leads with a millisecond timestamp so inserts append at the rightmost page the way a sequence does, and PostgreSQL 18 ships uuidv7() built in.

The platform moves subscription_id to a UUID so that ids can be generated client-side before the row exists. On PostgreSQL, which choice keeps the primary key index healthy?

About this lesson

This section is part of the Schema Types lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.