1

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).

  • 1
    The usual way is to store the category names in a separate table and create a foreign key to from the blog table to that table. Alternatively if the categories don't change very often, you could use an enum - but that is only an option if the categories are changed very rarely. – a_horse_with_no_name May 26 '18 at 16:42
  • @a_horse_with_no_name thank you for options. Also, I can store numbers and convert them to enums at program level. But I want to know, is there any string interning for postgres. – Max May 26 '18 at 17:06
  • The "interning" is done through a second lookup table. – a_horse_with_no_name May 26 '18 at 17:38
2

if you want an effect like strings stored as integers use an enum type, the SQL syntax for interacting with the table will be the same, except you'll need to cast if you want to do string operations on the new column;

CREATE TYPE example_category AS ENUM (
 'category1', 'category2', 'category3', 'category4', 'category5',
 'category6', 'category7', 'category8', 'category9');

ALTER TABLE tmp_strings ALTER COLUMN category 
  TYPE example_category USING category::example_category;

I think enums use 4 bytes (possibly 5?)

  • An elegant technique – Gaius May 27 '18 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.