My database has VARCHAR columns each containing 20k-60k distinct short texts. It is not feasbile (due to space constraints) to store these values in each row, they need to be externalized somehow. These values are never updated.
The classic approach would be to replace each of these columns with a separate lookup table and corresponding id - this however requires a lot of joins and a lot of similar tables.
Enums don't seem to be feasible for this problem, although they would remove the need for explicit joins: They cant be longer than 63 bytes and it is impossible to add new values in transactions until version 10.
I have looked in the documentation for CREATE TABLE - there seems to be no built-in support for this kind of deduplication. TOAST-Tables don't seem to contain unique values either.
Is there another approach I have missed?