Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

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

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)
over 4 years ago · Santiago Trujillo
1 answers
Answer question

0

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.

over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!