2

I have a postgres database that has the following structure:enter image description here

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:

enter image description here

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?

Mr. President
  • 121
  • 1

1 Answers1

0

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.

BigSmoke
  • 684
  • 5
  • 12