How can I add a new value to an ENUM in Postgres without locking the table?

Question

I've tried two approaches.

Approach 1: Create a new ENUM with the new value added and switch the data type in place:

-- Rename existing enum
ALTER TYPE animal_species RENAME TO animal_species_old;

-- Create new enum with new value
CREATE TYPE animal_species AS ENUM (
  'dog',
  'cat',
  'elephant'
);

-- Update the column of Animals to use the new enum
ALTER TABLE "Animals" ALTER COLUMN species SET DATA TYPE animal_species USING species::text::animal_species;

DROP TYPE animal_species_old;

Approach 2: Use a temporary column

-- Create new enum type with a new name (this will be the name of the enum from now on)
CREATE TYPE animal_type_enum AS ENUM (
  'dog',
  'cat',
  'elephant'
);

-- Create a temporary column
ALTER TABLE "Animals" ADD COLUMN species_new animal_species_enum;

-- Copy existing species into new column
UPDATE "Animals" SET species_new = species::text::animal_species_enum;

-- Drop old species column
ALTER TABLE "Animals" DROP COLUMN species;

-- Rename new column
ALTER TABLE "Animals" RENAME COLUMN species_new TO species;

-- Drop old enum
DROP TYPE animal_species;

In both cases, lock(s) were created and brought my application down. I believe the second way performed better than the first, but the downtime was still unacceptable. The table is in the millions of rows.

Note that I am very much open to using something other than an ENUM--I was thinking of creating a "Species" table with a foreign key "species_id" in "Animals", but as far as I can tell this would create the same locking problem (and might be even worse given the introduction of a new foreign key constraint).

Thanks for any help!

Answer 1

Approach 3, just add a new value to the enum:

ALTER TYPE animal_type_enum ADD VALUE 'snake';

---

If you frequently add or remove new lookup values, a separate lookup table is a much better choice.

Adding a new value is a simple INSERT operation that doesn't lock anything (especially not the table referencing the lookup table).

While the foreign key checks do add some overhead, they shouldn't matter that much (assuming the FK column is properly indexed) unless you do bulk INSERTs or DELETEs very frequently.

For single row INSERTs or DELETEs (or only "hundreds" of rows) you probably won't even notice the overhead of the FK lookup - especially if the lookup table is small and only contains a few rows.