PostgreSQL values to unique dictionary

Question

I have table with one billion rows and more than 50 columns. I need to reduce size and speed up queries, backup, exports, etc. Some columns contain f.e. only hundreds of distinct values which are long URLs (text data type), used application names and similar duplicate information.

Is there some PG tool, script for PostgreSQL 9.3+ which can easily for selected columns create dictionaries of distinct values to other tables and after that update original values with SmallInt identificator from that dictionary? Do I have to write SQL for that manually?

TableOriginal
1;VeryLongURLText
2;VeryLongURLText
3;LoooongURLText
4;LoooongURLText
5;LoooongURLText
TableDictionary
1;VeryLongURLText
2;LoooongURLText
TableUpdated
1;1
2;1
3;2
3;2
3;2

Thank you.

• dkocich: postgresql.org/docs/9.5/static/sql-createdomain.html is Create domain the function which should be used?
• user1822: A domain is a kind of data type. It's a short hand for commonly used column types where you can e.g. enforce check constraints that should be applied to all columns that store the same type of information.
• Ezequiel Tolnay: For a billion rows you will better create all of your dictionaries first, and then make a copy of your original table using all of the dictionaries. Also, consider using smallint (i.e. int2) rather than int4 for dictionaries with few expected values.

Answer 1

Do I have to write SQL for that manually?

Yes, but it's not that hard:

create table original (id integer, url text);
insert into original 
values
(1,'VeryLongURLText'),
(2,'VeryLongURLText'),
(3,'LoooongURLText'),
(4,'LoooongURLText'),
(5,'LoooongURLText');

create the dictionary

create table dictionary (id serial, url text);
insert into dictionary (url)
select distinct url
from original;

This creates the table with the following content:

id | data 
---+----------------
 1 | LoooongURLText 
 2 | VeryLongURLText

Now create a new table based on the dictionary:

create table compressed 
as
select o.id, o.some_column, o.other_column, d.id as dictionary_id
from original o
 join dictionary d on o.url = d.url;

As your goal is to reduce the space overhead it's better to create new table with the dictionary id rather then altering the existing one. This will also be a lot faster then updating all rows from the existing table (with a billion rows this will however still take some time)

• Marco: In your compressed table you still have the URL. You should not use o.* but name all fields except the url field. @DavidK Do not forget to change your applications to use this new structure.
• user1822: @marco: you are right, corrected ;)