Why Integer types (smallint, integer, bigint) in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if your database could save space and avoid errors just by choosing the right number size automatically?

The Scenario

Imagine you are keeping track of the number of books in a library using a simple list on paper. You write down numbers without thinking about how big or small they might get.

Later, you realize some numbers are too big to fit in your small boxes, and some boxes are wasted because the numbers are tiny.

The Problem

Using the same size box for all numbers wastes space or causes errors when numbers are too big.

Manually guessing the right size for each number is slow and can lead to mistakes, like running out of space or using too much memory.

The Solution

Integer types like smallint, integer, and bigint let the database pick the right size box automatically.

This saves space and avoids errors by matching the number size to the right type.

Before vs After

✗ Before

CREATE TABLE books (count INTEGER); -- always uses 4 bytes

✓ After

CREATE TABLE books (count SMALLINT); -- uses 2 bytes for small numbers

What It Enables

Efficient storage and accurate handling of numbers of different sizes without wasting space or risking errors.

Real Life Example

A bank stores account balances: smallint for small accounts, integer for regular accounts, and bigint for very large balances, ensuring fast and safe data handling.

Key Takeaways

Integer types help match number size to storage space.

This prevents wasted space and errors from numbers too big or too small.

Choosing the right integer type improves database efficiency and reliability.