How significant are changes in Postgres column data types with regards to memory and performance?

user_11271048 0
Viewed 350

Quoting the documentation:

The type integer is the common choice, as it offers the best balance between range, storage size, and performance. The smallint type is generally only used if disk space is at a premium. The bigint type should only be used if the range of the integer type is insufficient, because the latter is definitely faster.

My understanding is that different data types require different allocations of memory. So I am assuming, less-space means less seeks required to fetch data (theoretically, at least).I am not sure about reads, writes and computations.

How significant are changes in data types with regards to:

  1. Storage
  2. Writing to tables
  3. Reading from tables
  4. Performing calculations (eg. adding two columns)
postgresql
1 Answers

The size of a smallint is 2 bytes, an integer has 4 bytes, and a bigint 8. So the basic space requirements are obvious.

However, there is alignment to consider: an integer must start at an offset divisible by 4, and a bigint at an offset divisible by 8. So unless you arrange the table columns carefully, you may lose all space gained by choosing a smaller data type to empty padding bytes.

The impact on read or write I/O for the table is proportional to the space savings, so typically marginal. For an index the impact may be higher, because short index keys mean high fan-out, fewer internal pages and lower depth of the index tree.

Calculations using the various integers are performed by the hardware, so they are all very fast. Differences should be measurable, but not extreme.

All in all, it is hardly ever worth the effort to choose a smaller integer data type. The pain you have to suffer if (for example) your primary key sequence runs out of integer values outweighs any benefit of saving space by far.

user_64643080
Related