Storing rows vs unique column values

Question

I have a postgres database that has the following structure:

where the fields in column1 and column2 can take any value and where the order column indicates the order in which the original data was inserted. This table contains a lot of rows (with lots of duplicates) and I'm wondering whether there is a smarter way to store this data. Suppose that I were to create a unique id for each value per column and store the data like this:

Suppose that n is the number of rows in the first table and u the number of unique rows in the first table. If u is relatively large then the order column in the second table will have relatively short values in the order column fields, and if u is relatively large the fields in the order column in the second table will be quite wide. I would like to be able to compare the best storage method in both cases.

Question: I want to be able to compare these two tables to determine which storage method is better; is there a formula/method to find out how columns vs rows take up memory?

Answer 1

Your first design is a better than your second design. Normally, one would want to avoid storing multiple values in a single column in an idiosyncratic format that requires parsing. If you do want to store multiple values in a single column, it would be best to use PostgreSQL's built-in array support for that. Do note, though, that for efficient querying, like with you idiosyncratic idea, you would then likely end up needing indexes on an expression.