varchar(n) vs text + check migration benchmarks

gistfile1.txt

-- CREATE TABLE foo (
--   id int primary key,
--   x varchar(255) NOT NULL
-- );

-- CREATE TABLE bar (
--   id int primary key,
--   x text NOT NULL 
--     CHECK (char_length(x) <= 255)
-- );

-- INSERT INTO foo
-- SELECT i, md5(random()::text)
-- FROM generate_series(1,2e6) i;

-- INSERT INTO bar
-- SELECT i, md5(random()::text)
-- FROM generate_series(1,2e6) i;

-- reset
ALTER TABLE foo
ALTER COLUMN x TYPE varchar(255);

ALTER TABLE bar
DROP CONSTRAINT IF EXISTS bar_x_check;
ALTER TABLE bar
ADD CONSTRAINT bar_x_check CHECK (char_length(x) <= 255);

CREATE OR REPLACE FUNCTION test_varchar_migration_timing(test_name TEXT, char_size INT) RETURNS void AS $$
DECLARE
    start_time TIMESTAMP;
    end_time TIMESTAMP;
BEGIN
    start_time := clock_timestamp();
    EXECUTE format('ALTER TABLE foo ALTER COLUMN x TYPE varchar(%s)', char_size);
    end_time := clock_timestamp();
	
    RAISE NOTICE '%: %', test_name, end_time - start_time;
    RAISE NOTICE 'Locks: %', (SELECT array_agg(mode || ' - ' || granted) FROM pg_locks WHERE relation = 'foo'::regclass);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION test_text_migration_timing(test_name TEXT, max_length INT) RETURNS void AS $$
DECLARE
    start_time TIMESTAMP;
    end_time TIMESTAMP;
BEGIN
    start_time := clock_timestamp();
	ALTER TABLE bar DROP CONSTRAINT IF EXISTS bar_x_check;
    EXECUTE format('ALTER TABLE bar ADD CONSTRAINT bar_x_check CHECK (char_length(x) <= %s)', max_length);
    end_time := clock_timestamp();
    
    RAISE NOTICE '%: %', test_name, end_time - start_time;
    RAISE NOTICE 'Locks: %', (SELECT array_agg(mode || ' - ' || granted) FROM pg_locks WHERE relation = 'bar'::regclass);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION set_column_not_null(test_name TEXT, table_name TEXT, action TEXT) RETURNS void AS $$
DECLARE
    start_time TIMESTAMP;
    end_time TIMESTAMP;
BEGIN
    start_time := clock_timestamp();
    EXECUTE format('ALTER TABLE %I ALTER COLUMN x %s NOT NULL', table_name, action);
    end_time := clock_timestamp();

    RAISE NOTICE '%: %', test_name, end_time - start_time;
    RAISE NOTICE 'Locks: %', (SELECT array_agg(mode || ' - ' || granted) FROM pg_locks WHERE relation = table_name::regclass);
END;
$$ LANGUAGE plpgsql;

-- separate transactions to remove the locks
BEGIN; SELECT test_varchar_migration_timing('varchar nullable 255 -> nullable 500', 500); COMMIT;
BEGIN; SELECT test_varchar_migration_timing('varchar nullable 500 -> nullable 255', 255); COMMIT;
BEGIN; SELECT           set_column_not_null('varchar nullable 255 -> not null 255', 'foo', 'x', 'SET'); COMMIT;
BEGIN; SELECT test_varchar_migration_timing('varchar not null 255 -> not null 500', 500); COMMIT;
BEGIN; SELECT test_varchar_migration_timing('varchar not null 500 -> not null 255', 255); COMMIT;
BEGIN; SELECT           set_column_not_null('varchar not null 255 -> nullable 255', 'foo', 'x', 'DROP'); COMMIT;

BEGIN; SELECT test_text_migration_timing('text nullable 255 -> nullable 500', 500); COMMIT;
BEGIN; SELECT test_text_migration_timing('text nullable 500 -> nullable 255', 255); COMMIT;
BEGIN; SELECT        set_column_not_null('text nullable 255 -> not null 255', 'bar', 'x', 'SET'); COMMIT;
BEGIN; SELECT test_text_migration_timing('text not null 255 -> not null 500', 500); COMMIT;
BEGIN; SELECT test_text_migration_timing('text not null 500 -> not null 255', 255); COMMIT;
BEGIN; SELECT        set_column_not_null('text not null 255 -> nullable 255', 'bar', 'x', 'DROP'); COMMIT;