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