INT vs VRCHAR vs ENUM

bechmark_setup.sql

BEGIN;

---------
-- INT --
---------

CREATE TABLE room_int (
  id UUID PRIMARY KEY,
  time TSTZRANGE NOT NULL,
  audience TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  backend INT NOT NULL
);

WITH backends AS (
  SELECT '{0,1}'::INT[] AS values
)
INSERT INTO room_int (id, time, audience, created_at, backend)
SELECT
  MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT)::UUID AS id,
  '[,)' AS time,
  'dev.svc.example.org' AS audience,
  NOW() AS created_at,
  backends.values[1 + FLOOR(RANDOM() * ARRAY_LENGTH(backends.values, 1))::INT] AS backend
FROM GENERATE_SERIES(1, 1000000) AS i,
     backends;

-------------
-- VARCHAR --
-------------

CREATE TABLE room_varchar (
  id UUID PRIMARY KEY,
  time TSTZRANGE NOT NULL,
  audience TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  backend VARCHAR(32) NOT NULL
);

WITH backends AS (
  SELECT '{none,janus}'::VARCHAR[] AS values
)
INSERT INTO room_varchar (id, time, audience, created_at, backend)
SELECT
  MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT)::UUID AS id,
  '[,)' AS time,
  'dev.svc.example.org' AS audience,
  NOW() AS created_at,
  backends.values[1 + FLOOR(RANDOM() * ARRAY_LENGTH(backends.values, 1))::INT] AS backend
FROM GENERATE_SERIES(1, 1000000) AS i,
     backends;

----------
-- ENUM --
----------

CREATE TYPE room_enum_backend AS ENUM ('none', 'janus');

CREATE TABLE room_enum (
  id UUID PRIMARY KEY,
  time TSTZRANGE NOT NULL,
  audience TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  backend room_enum_backend NOT NULL
);

WITH backends AS (
  SELECT '{none,janus}'::room_enum_backend[] AS values
)
INSERT INTO room_enum (id, time, audience, created_at, backend)
SELECT
  MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT)::UUID AS id,
  '[,)' AS time,
  'dev.svc.example.org' AS audience,
  NOW() AS created_at,
  backends.values[1 + FLOOR(RANDOM() * ARRAY_LENGTH(backends.values, 1))::INT] AS backend
FROM GENERATE_SERIES(1, 1000000) AS i,
     backends;

COMMIT;

benchmark.sql

EXPLAIN ANALYZE SELECT * FROM room_int WHERE backend = 1;
-- Seq Scan on room_int  (cost=0.00..23864.00 rows=497133 width=54) (actual time=0.054..143.247 rows=499851 loops=1)
--   Filter: (backend = 1)
--   Rows Removed by Filter: 500149
-- Planning Time: 0.069 ms
-- Execution Time: 172.262 ms

EXPLAIN ANALYZE SELECT * FROM room_varchar WHERE backend = 'janus';
-- Seq Scan on room_varchar  (cost=0.00..23864.00 rows=496000 width=55) (actual time=0.043..147.896 rows=499763 loops=1)
--   Filter: ((backend)::text = 'janus'::text)
--   Rows Removed by Filter: 500237
-- Planning Time: 0.058 ms
-- Execution Time: 176.790 ms

EXPLAIN ANALYZE SELECT * FROM room_enum WHERE backend = 'janus';
-- Seq Scan on room_enum  (cost=0.00..23864.00 rows=499533 width=54) (actual time=0.027..123.763 rows=500229 loops=1)
--   Filter: (backend = 'janus'::room_enum_backend)
--   Rows Removed by Filter: 499771
-- Planning Time: 0.084 ms
-- Execution Time: 153.067 ms

Size seems to be the same:

SELECT
  pg_size_pretty(pg_total_relation_size('room_int'))     AS room_int_size,
  pg_size_pretty(pg_total_relation_size('room_varchar')) AS room_varchar_size,
  pg_size_pretty(pg_total_relation_size('room_enum'))    AS room_enum_size;

room_int_size     | 149 MB
room_varchar_size | 149 MB
room_enum_size    | 149 MB