I have a table like this:
CREATE TABLE BLOG(
CATEGORY CHAR(32)
);
Category is a column, which stored enum values.
For example, I have 1 millions of rows and 10 unique values of Category.
Let's populate the table with sample data:
CREATE TABLE TMP_STRINGS(
id SERIAL PRIMARY KEY,
CATEGORY CHAR(50)
);
insert into TMP_STRINGS values (1, 'category1'), (2, 'category2'), (3, 'category3'), (4, 'category4'), (5, 'category5'), (6, 'category6'), (7, 'category7'), (8, 'category8'), (9, 'category9');
insert into blog(category)
SELECT category FROM (SELECT floor(random() * 9 + 1)::int as n from generate_series(1,1000000)) AS T INNER JOIN tmp_strings ON T.n = tmp_strings.id;
This database is 42 MB:
SELECT pg_size_pretty(pg_total_relation_size('"blog"'));
A row of database is 44 byte:
SELECT octet_length(t.*::text) FROM blog AS t WHERE category='category1' LIMIT 1;
Categories can be encoded with smallint, like:
category1 -- 1
category2 -- 2
category3 -- 3
category4 -- 4
category5 -- 5
category6 -- 6
category7 -- 7
category8 -- 8
category9 -- 9
Is there any way to do this automatically? I mean some option or module for String interning (String pooling).