Status fields: ALTER TYPE vs. INSERT INTO? I have a simple table with a status column:

create type status as enum (
  'approved',
  'pending_approval',
  'rejected'
);

create table document (
  id          serial primary key,
  uuid        uuid unique not null default uuid_generate_v4(),
  title       text not null,
  content     text,
  status      status not null,
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now(),
  deleted_at  timestamptz
);


This is the beginning of a small workflow-esque application I'm doing. Statuses, however, can be defined by the users of the application. What are the cons of using ALTER TYPE instead of creating a new table and using JOIN? I really don't want to add a JOIN to my queries just because of a status field.
What are the cons of using ALTER TYPE instead of creating a new table and using JOIN? I really don't want to add a JOIN to my queries just because of a status field.


This is your typical pro/con enum vs normalization question. 

ENUM type


Great if the range is fixed or only requires modification by the DBA.
Get a really nice query syntax which optimized well (bottom assumes status is an ENUM)

SELECT * FROM foo WHERE status = 'INVALID';

Sorts well and permits easy range within the type. All enums are sorted and have ordinality
Store in an 4 byte-size
Can eliminate a JOIN
PostgreSQL has ENUM types, not macros (like MySQL). This means you don't take a hit in maintenance. If you have five tables using your ENUM type you can still manage just that one type and not five tables.


Normalized / JOIN method


Always preferred if the user needs to be able to modify the domain/data
Always preferred if the domain/data needs to shrink (remove values)
Will provide easier modification with an ORM
More traditional
Can be smaller than ENUM (for instance smallint is two bytes, enum is always four bytes).