Postgresql – If I add an additional column to handle special values, how will this impact the table size

postgresqltable

I have a table TABLE1 with 400,000 row (by now) that has 20 columns.

I have a column DETAIL that usually stores 100 to 300 characters, that's why its size is 1024, but I have an special case. I need to store 1900 chars for one row only.

The solution that I found is creating another column, DETAIL2 to store remaining characters, but I'm afraid that this will cause my table size to increase a lot (just one row would have DETAIL2, others will be NULL).

Is this the right solution or is there a better way to store the special case?

Best Answer

Change the datatype of Detail to VARCHAR(2000) or TEXT*. This will be enough to handle your 1900 length value plus a little padding for future exceptions. This will add no additional overhead beyond the additional storage needed for that particular row.

If you add another column you will add 1 byte to all of the rows (which isn't a big deal) but now you have to contend with two columns with different names but the same purpose.

Postgres documentation explains storage requirements in more detail: https://www.postgresql.org/docs/current/datatype-character.html

*The TEXT datatype is not ANSI compliant, if you do use it understand the implications.

Related Question