CODEWITHSUNDEEP
databasepostgresqlindexingdatabase-performance
BrainCore
BrainCore

Reputation: 5412

Postgres: Advantages of combining columns

Background:

I have three columns with the following sizes in bytes (for a total of 8): 4 (int), 2 (small int), 2 (small int).

I'm creating a multicolumn (aka composite) index on these three columns (in the order specified above). I will be doing two types of select queries:

  1. Range look up based on the first 4-byte column. This first column will be monotonically increasing (timestamp).
  2. Key-ed look up where all three of these values are specified.

Question: Is there any benefit in Postgres to combining these three columns into one 8-byte bigint and dealing with the separation in the application layer?

I'm asking for a perspective on DB query and storage efficiency.

Upvotes: 2

Views: 139

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

I suspect that storage-wise any gains from combining will be tiny and will be offset by the limitations of so doing. Yes, you can combine but you cannot have any referential integrity checks against subparts of a field. I.e. tuple A can relate to tuple B, but A and B have to be subsets of the whole fields of the table. This is the basis of the atomicity requirement of 1NF.

Now, you can query inside the field using functions to extract the information you need, and you can even index the output of those functions if you know what you are doing. But this then uses more space than you might otherwise, and you still lose the referential integrity enforcement possibilities.

In general, space is a concern, but not at this level of optimization. Unless you have extremely specialized needs, combining the values poses more problems than it solves.

Upvotes: 1

Related Questions