Integer types (smallint, integer, bigint) in PostgreSQL - Deep Dive

Overview - Integer types (smallint, integer, bigint)

What is it?

Integer types in PostgreSQL are used to store whole numbers without decimals. There are three main sizes: smallint, integer, and bigint. Each type can hold numbers within a specific range, with smallint being the smallest and bigint the largest. These types help the database efficiently store and process numeric data.

Why it matters

Using the right integer type saves storage space and improves performance. If you use a type too large, you waste space; too small, and you risk errors from numbers that don't fit. Without these types, databases would be slower and less efficient, making applications sluggish and costly.

Where it fits

Before learning integer types, you should understand basic data types and how databases store data. After this, you can learn about numeric operations, indexing, and performance tuning related to data types.

Mental Model

Core Idea

Integer types are like containers of different sizes that hold whole numbers within fixed limits to balance storage space and range.

Think of it like...

Think of integer types as jars of different sizes: a small jar (smallint) holds fewer candies (numbers), a medium jar (integer) holds more, and a large jar (bigint) holds the most. Choosing the right jar means you don’t waste space or run out of room.

┌───────────────┐
│  smallint     │ 2 bytes │ Range: -32,768 to 32,767
├───────────────┤
│  integer      │ 4 bytes │ Range: -2,147,483,648 to 2,147,483,647
├───────────────┤
│  bigint       │ 8 bytes │ Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
└───────────────┘

Build-Up - 7 Steps

1

Foundation

What are integer types?

Concept: Introduction to integer types as whole number storage in databases.

Integer types store numbers without fractions or decimals. PostgreSQL offers three sizes: smallint, integer, and bigint. Each uses a different number of bytes and supports different ranges of numbers.

Result

You understand that integer types are for whole numbers and that there are three sizes with different ranges.

Understanding that integers are whole numbers without decimals is the base for choosing the right type.

2

Foundation

Ranges and storage sizes

Concept: Each integer type has a fixed size in bytes and a specific range of values it can hold.

Smallint uses 2 bytes and stores numbers from -32,768 to 32,767. Integer uses 4 bytes and stores from -2,147,483,648 to 2,147,483,647. Bigint uses 8 bytes and stores very large numbers, up to about 9 quintillion.

Result

You know the exact storage size and range for each integer type.

Knowing the size and range helps prevent errors and optimize storage.

3

Intermediate

Choosing the right integer type

🤔Before reading on: do you think using bigint for all numbers is better than choosing smaller types? Commit to your answer.

Concept: Selecting the appropriate integer type based on the expected range of values.

If your numbers never exceed 32,767, use smallint to save space. For most common cases, integer is enough. Use bigint only when you expect very large numbers. Using bigger types than needed wastes storage and can slow down queries.

Result

You can pick the best integer type for your data needs.

Understanding the trade-off between range and storage size improves database efficiency.

4

Intermediate

Integer overflow and errors

🤔Before reading on: what happens if you insert a number too big for smallint? Will PostgreSQL store it, truncate it, or error? Commit to your answer.

Concept: What occurs when a number exceeds the range of the chosen integer type.

If you try to insert a number outside the allowed range, PostgreSQL throws an error and refuses the insert. It does not truncate or wrap the number. This protects data integrity but requires careful type choice.

Result

You understand that exceeding range causes errors, not silent data corruption.

Knowing this prevents bugs and data loss from unexpected number sizes.

5

Intermediate

Implicit type casting and arithmetic

Concept: How PostgreSQL handles arithmetic and type conversions between integer types.

When you mix integer types in calculations, PostgreSQL promotes smaller types to larger ones to avoid overflow. For example, smallint plus integer results in integer. This automatic casting helps avoid errors but can affect performance.

Result

You know how PostgreSQL manages mixed integer operations safely.

Understanding implicit casting helps predict query behavior and optimize performance.

6

Advanced

Storage and performance impact

🤔Before reading on: do you think using bigint instead of integer always slows down queries? Commit to your answer.

Concept: How integer type size affects storage space and query speed.

Smaller integer types use less disk space and memory, which can speed up queries and reduce I/O. However, modern CPUs handle 4- and 8-byte integers efficiently, so the difference is often small. Still, for very large tables, choosing the smallest adequate type improves performance.

Result

You understand the practical performance trade-offs of integer types.

Knowing when size matters helps optimize large-scale databases.

7

Expert

PostgreSQL internal integer representation

🤔Before reading on: do you think PostgreSQL stores integers as text or binary internally? Commit to your answer.

Concept: How PostgreSQL stores integer types internally in binary form for efficiency.

PostgreSQL stores integers in fixed-length binary format: 2 bytes for smallint, 4 bytes for integer, and 8 bytes for bigint. This binary storage allows fast arithmetic and indexing. Internally, integers are signed and use two's complement representation.

Result

You know the internal binary storage and representation of integers in PostgreSQL.

Understanding internal storage explains why integer operations are fast and how range limits arise.

Under the Hood

PostgreSQL stores integer types as fixed-size binary values using two's complement encoding. This means each integer type uses a fixed number of bytes (2, 4, or 8) to represent signed whole numbers. Arithmetic operations and comparisons work directly on these binary values, making them very fast. When data is read or written, PostgreSQL converts between this binary form and human-readable text.

Why designed this way?

Fixed-size binary storage was chosen for speed and simplicity. Two's complement is a standard way to represent signed integers in computers, allowing easy arithmetic and comparison. Alternatives like variable-length storage would slow down operations and complicate indexing. The three sizes balance storage efficiency and range for common use cases.

┌─────────────┐
│ Input Text  │
└──────┬──────┘
       │ Parse to binary
┌──────▼──────┐
│ Binary Int  │ 2/4/8 bytes
│ (two's comp)│
└──────┬──────┘
       │ Arithmetic & Indexing
┌──────▼──────┐
│ Storage on  │
│ Disk & RAM  │
└─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does using bigint always make your database faster? Commit yes or no.

Common Belief: Using bigint is always better because it can store bigger numbers and is faster.

Reality: Bigint uses more storage and can be slower than smaller integer types, especially on large datasets. It should only be used when needed.

Why it matters: Using bigint unnecessarily wastes space and can degrade performance, increasing costs and slowing queries.

Quick: Can you store decimal numbers in integer types? Commit yes or no.

Common Belief: Integer types can store decimal numbers by rounding or truncating.

Reality: Integer types only store whole numbers. Trying to insert decimals causes errors or truncation depending on context, but PostgreSQL does not automatically round decimals to integers.

Why it matters: Misusing integer types for decimals leads to data loss or errors, causing incorrect results.

Quick: If you insert a number too big for smallint, does PostgreSQL store it anyway? Commit yes or no.

Common Belief: PostgreSQL will store the number by truncating or wrapping around if it’s too big for smallint.

Reality: PostgreSQL throws an error and refuses to store numbers outside the allowed range for the type.

Why it matters: Assuming silent truncation leads to unexpected errors and data integrity problems.

Quick: Is integer the same as int in PostgreSQL? Commit yes or no.

Common Belief: Integer and int are different types with different sizes.

Reality: Integer and int are synonyms in PostgreSQL and mean the same 4-byte integer type.

Why it matters: Confusing these can cause misunderstandings when reading or writing SQL code.

Expert Zone

1

PostgreSQL uses two's complement binary representation internally, which affects how negative numbers and overflow behave.

2

When mixing integer types in expressions, PostgreSQL promotes smaller types to larger ones to avoid overflow, which can impact performance subtly.

3

Choosing integer types affects index size and query planning, influencing overall database performance beyond just storage.

When NOT to use

Avoid integer types when you need fractional numbers; use numeric or decimal types instead. Also, for very large numbers beyond bigint range, use numeric type. For boolean flags or small enumerations, consider using boolean or enum types instead of smallint.

Production Patterns

In production, use smallint for small ranges like status codes, integer for most IDs and counts, and bigint for large counters or IDs in distributed systems. Monitor data growth to adjust types before overflow errors occur. Use explicit casting to control arithmetic behavior and avoid surprises.

Connections

Data Types

Integer types are a subset of data types used to define how data is stored and interpreted.

Understanding integer types helps grasp the broader concept of data types and their role in database design.

Computer Architecture

Integer types in databases reflect how computers represent numbers in binary using fixed bytes.

Knowing computer number representation clarifies why integer ranges and sizes exist as they do.

Memory Management

Choosing integer sizes affects memory and storage allocation in databases.

Understanding memory management principles helps optimize database performance by selecting appropriate integer types.

Common Pitfalls

#1 Using smallint for a column that stores values larger than 32,767.

Wrong approach:

CREATE TABLE sales (amount smallint);
INSERT INTO sales VALUES (40000);

Correct approach:

CREATE TABLE sales (amount integer);
INSERT INTO sales VALUES (40000);

Root cause:

Misunderstanding the range limits of smallint leads to overflow errors.

#2 Using bigint for all integer columns without need.

Wrong approach:

CREATE TABLE users (id bigint, age bigint);

Correct approach:

CREATE TABLE users (id bigint, age smallint);

Root cause:

Not considering storage size and performance trade-offs causes inefficient database design.

#3 Trying to store decimal numbers in integer columns.

Wrong approach:

CREATE TABLE products (price integer);
INSERT INTO products VALUES (19.99);

Correct approach:

CREATE TABLE products (price numeric(5,2));
INSERT INTO products VALUES (19.99);

Root cause:

Confusing integer types with decimal types causes data loss or errors.

Key Takeaways

Integer types store whole numbers in fixed-size containers with specific ranges.

Choosing the right integer type balances storage efficiency and the range of values needed.

Exceeding an integer type’s range causes errors, not silent data corruption.

PostgreSQL stores integers in binary using two's complement for fast arithmetic.

Using unnecessarily large integer types wastes space and can reduce performance.

Practice

1. Which PostgreSQL integer type is best suited for storing very large whole numbers?

easy

A. bigint

B. smallint

C. integer

D. decimal

Solution

1. Step 1: Understand integer types and their sizes

   smallint stores small numbers, integer stores medium-sized numbers, and bigint stores very large numbers.

2. Step 2: Identify the type for very large whole numbers

   Since the question asks for very large whole numbers, bigint is the correct choice.

3. Final Answer:

   bigint -> Option A

4. Quick Check:

   Very large whole numbers = bigint [OK]

Hint: Biggest integer type is bigint for very large numbers [OK]

Common Mistakes:

• Confusing smallint with bigint
• Choosing decimal which is for decimals, not integers
• Picking integer which is medium size

2. Which of the following is the correct way to declare a column named age as a 2-byte integer in PostgreSQL?

easy

A. age INT2(2);

B. age INTEGER(2);

C. age BIGINT(2);

D. age SMALLINT;

Solution

1. Step 1: Recall PostgreSQL integer type syntax

   PostgreSQL uses SMALLINT for 2-byte integers. The syntax is simply column_name SMALLINT;.

2. Step 2: Evaluate each option

   age SMALLINT; uses correct syntax. The other options incorrectly use size in parentheses (like INT2(2), INTEGER(2), BIGINT(2)), which is invalid for integer types in PostgreSQL.

3. Final Answer:

   age SMALLINT; -> Option D

4. Quick Check:

   2-byte integer = SMALLINT syntax [OK]

Hint: Use SMALLINT keyword for 2-byte integers, no size in parentheses [OK]

Common Mistakes:

• Adding size in parentheses like INTEGER(2)
• Using BIGINT for small numbers
• Specifying size like INT2(2) for integer types

3. What will be the result of this PostgreSQL query?

CREATE TABLE test_numbers (num SMALLINT); INSERT INTO test_numbers VALUES (40000); SELECT * FROM test_numbers;

medium

A. 40000

B. Error: integer out of range

C. 0

D. NULL

Solution

1. Step 1: Understand SMALLINT range

   SMALLINT can store values from -32768 to 32767 only.

2. Step 2: Check inserted value against range

   40000 is greater than 32767, so inserting it causes an error.

3. Final Answer:

   Error: integer out of range -> Option B

4. Quick Check:

   40000 > SMALLINT max 32767 = error [OK]

Hint: Check value fits type range; else error occurs [OK]

Common Mistakes:

• Assuming value truncates silently
• Expecting 0 or NULL instead of error
• Confusing SMALLINT range with INTEGER

4. You wrote this table definition:

CREATE TABLE sales (id INTEGER, amount SMALLINT);

But when inserting a large amount like 40000, you get an error. How do you fix it?

medium

A. Change amount column type to INTEGER

B. Change id column type to SMALLINT

C. Insert smaller values only

D. Change amount column type to tinyint

Solution

1. Step 1: Identify cause of error

   Amount column is SMALLINT which maxes at 32767, so 40000 is too large.

2. Step 2: Choose appropriate type to hold 40000

   INTEGER can hold values up to 2,147,483,647, so changing amount to INTEGER fixes the error.

3. Final Answer:

   Change amount column type to INTEGER -> Option A

4. Quick Check:

   40000 fits INTEGER but not SMALLINT [OK]

Hint: Use INTEGER for values larger than 32767 [OK]

Common Mistakes:

• Changing unrelated column type
• Trying to insert smaller values only
• Choosing tinyint which is a MySQL type, not PostgreSQL

5. You need to store user IDs that can be very large numbers, possibly exceeding 2 billion. Which PostgreSQL integer type should you choose and why?

hard

A. smallint because it uses less storage

B. integer because it supports up to 2 billion

C. bigint because it supports numbers larger than 2 billion

D. serial because it auto-increments

Solution

1. Step 1: Understand size limits of integer types

   smallint max is 32767, integer max is about 2,147,483,647, bigint supports much larger numbers.

2. Step 2: Match requirement to type

   User IDs may exceed 2 billion, so integer is insufficient. bigint is needed to store larger values.

3. Final Answer:

   bigint because it supports numbers larger than 2 billion -> Option C

4. Quick Check:

   IDs > 2 billion need bigint [OK]

Hint: Use bigint for numbers larger than 2 billion [OK]

Common Mistakes:

• Choosing smallint for large IDs
• Assuming integer supports >2 billion
• Confusing serial with integer size