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?




Thank you.

  • postgresql.org/docs/9.5/static/sql-createdomain.html is Create domain the function which should be used? – dkocich May 19 '16 at 7:30
  • 1
    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. – a_horse_with_no_name May 19 '16 at 8:11
  • 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. – Ezequiel Tolnay May 19 '16 at 8:55

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 

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

| improve this answer | |
  • 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. – Marco May 19 '16 at 11:29
  • @marco: you are right, corrected ;) – a_horse_with_no_name May 19 '16 at 11:35

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.