Choosing Integer Types in MySQL & PostgreSQL

Choosing the right integer type depends on storage size, range, and performance. Here’s a guide to selecting the best type based on your needs. Integer Types Overview Data Type MySQL Range (Signed) PostgreSQL Range (Signed) Storage (Bytes) Best Used For TINYINT -128 to 127 (UNSIGNED: 0 to 255) ❌ Not available 1 Boolean values, small counters SMALLINT -32,768 to 32,767 -32,768 to 32,767 2 Small IDs, counts MEDIUMINT -8,388,608 to 8,388,607 ❌ Not available 3 Large counters in MySQL INT / INTEGER -2,147,483,648 to 2,147,483,647 Same as MySQL 4 Most general purpose ID or counter BIGINT -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 Same as MySQL 8 Large IDs, timestamps When to Use Each Integer Type? ✅ Use TINYINT (MySQL) when: ✔ Boolean values (0 or 1) ✔ Small flags or statuses (1-100) ✔ Example: CREATE TABLE users ( is_active TINYINT(1) NOT NULL ); ✅ Use SMALLINT when: ✔ Small numerical values, such as age, small counts, or ratings (-32,768 to 32,767) ✔ Example: CREATE TABLE products ( stock SMALLINT UNSIGNED NOT NULL -- Only positive values (0-65,535) ); ✅ Use MEDIUMINT (MySQL only) when: ✔ Larger counters than SMALLINT, but INT is overkill ✔ Example: CREATE TABLE orders ( order_count MEDIUMINT UNSIGNED NOT NULL ); ✅ Use INT / INTEGER when: ✔ General-purpose primary keys (auto-increment IDs) ✔ Larger counters (-2B to 2B range) ✔ Example: CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, salary INT NOT NULL ); ✅ Use BIGINT when: ✔ Storing timestamps, large IDs, or very large numbers ✔ Needed for social media users, financial records, or global IDs ✔ Example: CREATE TABLE transactions ( transaction_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, amount BIGINT NOT NULL ); Should I Use SIGNED or UNSIGNED? Use UNSIGNED if you only need positive numbers (doubles the max value). Use SIGNED if you need both positive & negative values. Example: -- UNSIGNED: allows 0 to 4,294,967,295 CREATE TABLE accounts ( balance INT UNSIGNED NOT NULL ); PostgreSQL-Specific Notes No TINYINT or MEDIUMINT → Use SMALLINT or INTEGER instead. Supports SERIAL types for auto-incrementing IDs: CREATE TABLE users ( id SERIAL PRIMARY KEY -- Equivalent to INT AUTO_INCREMENT ); Choosing the Right Integer Type Use Case MySQL Type PostgreSQL Type Boolean flags (0/1) TINYINT(1) BOOLEAN Small numbers (age, ratings) SMALLINT SMALLINT Medium counters MEDIUMINT INTEGER General IDs & primary keys INT AUTO_INCREMENT SERIAL Large counters (big user base, financial data) BIGINT BIGINT Here are real-world use cases for each integer type. 1. TINYINT (1 Byte) – Small Flags & Boolean Values Example: User Status & Ratings Used for binary states (0/1) or small numerical ranges. MySQL doesn’t have a BOOLEAN type, so TINYINT(1) is used instead. CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, is_active TINYINT(1) NOT NULL DEFAULT 1, -- 0 = inactive, 1 = active user_role TINYINT UNSIGNED NOT NULL DEFAULT 1 -- 1 = User, 2 = Admin, etc. ); Best for: ✔ Active/inactive status (0 or 1) ✔ User roles (1-10) ✔ Ratings (1-5 stars) 2. SMALLINT (2 Bytes) – Small Counters & Ranges Example: Number of Seats in a Theater Movie theaters have up to 65,535 seats, so SMALLINT UNSIGNED is perfect. CREATE TABLE cinemas ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, total_seats SMALLINT UNSIGNED NOT NULL CHECK (total_seats

Feb 23, 2025 - 02:15

Choosing Integer Types in MySQL & PostgreSQL

Choosing the right integer type depends on storage size, range, and performance. Here’s a guide to selecting the best type based on your needs.

Integer Types Overview

Data Type	MySQL Range (Signed)	PostgreSQL Range (Signed)	Storage (Bytes)	Best Used For
TINYINT	-128 to 127 (UNSIGNED: 0 to 255)	❌ Not available	1	Boolean values, small counters
SMALLINT	-32,768 to 32,767	-32,768 to 32,767	2	Small IDs, counts
MEDIUMINT	-8,388,608 to 8,388,607	❌ Not available	3	Large counters in MySQL
INT / INTEGER	-2,147,483,648 to 2,147,483,647	Same as MySQL	4	Most general purpose ID or counter
BIGINT	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	Same as MySQL	8	Large IDs, timestamps

When to Use Each Integer Type?

✅ Use TINYINT (MySQL) when:

✔ Boolean values (0 or 1)

✔ Small flags or statuses (1-100)

✔ Example:

   CREATE TABLE users (
       is_active TINYINT(1) NOT NULL
   );

✅ Use SMALLINT when:

✔ Small numerical values, such as age, small counts, or ratings (-32,768 to 32,767)

✔ Example:

   CREATE TABLE products (
       stock SMALLINT UNSIGNED NOT NULL  -- Only positive values (0-65,535)
   );

✅ Use MEDIUMINT (MySQL only) when:

✔ Larger counters than SMALLINT, but INT is overkill

✔ Example:

   CREATE TABLE orders (
       order_count MEDIUMINT UNSIGNED NOT NULL
   );

✅ Use INT / INTEGER when:

✔ General-purpose primary keys (auto-increment IDs)

✔ Larger counters (-2B to 2B range)

✔ Example:

   CREATE TABLE employees (
       id INT AUTO_INCREMENT PRIMARY KEY,
       salary INT NOT NULL
   );

✅ Use BIGINT when:

✔ Storing timestamps, large IDs, or very large numbers

✔ Needed for social media users, financial records, or global IDs

✔ Example:

   CREATE TABLE transactions (
       transaction_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       amount BIGINT NOT NULL
   );

Should I Use SIGNED or UNSIGNED?

Use UNSIGNED if you only need positive numbers (doubles the max value).
Use SIGNED if you need both positive & negative values.
Example:

  -- UNSIGNED: allows 0 to 4,294,967,295
  CREATE TABLE accounts (
      balance INT UNSIGNED NOT NULL
  );

PostgreSQL-Specific Notes

No TINYINT or MEDIUMINT → Use SMALLINT or INTEGER instead.
Supports SERIAL types for auto-incrementing IDs:

  CREATE TABLE users (
      id SERIAL PRIMARY KEY  -- Equivalent to INT AUTO_INCREMENT
  );

Choosing the Right Integer Type

Use Case	MySQL Type	PostgreSQL Type
Boolean flags (0/1)	`TINYINT(1)`	`BOOLEAN`
Small numbers (age, ratings)	`SMALLINT`	`SMALLINT`
Medium counters	`MEDIUMINT`	`INTEGER`
General IDs & primary keys	`INT AUTO_INCREMENT`	`SERIAL`
Large counters (big user base, financial data)	`BIGINT`	`BIGINT`

Here are real-world use cases for each integer type.

1. TINYINT (1 Byte) – Small Flags & Boolean Values

** Example: User Status & Ratings**

Used for binary states (0/1) or small numerical ranges.
MySQL doesn’t have a BOOLEAN type, so TINYINT(1) is used instead.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    is_active TINYINT(1) NOT NULL DEFAULT 1,  -- 0 = inactive, 1 = active
    user_role TINYINT UNSIGNED NOT NULL DEFAULT 1  -- 1 = User, 2 = Admin, etc.
);

Best for:

✔ Active/inactive status (0 or 1)

✔ User roles (1-10)

✔ Ratings (1-5 stars)

2. SMALLINT (2 Bytes) – Small Counters & Ranges

** Example: Number of Seats in a Theater**

Movie theaters have up to 65,535 seats, so SMALLINT UNSIGNED is perfect.

CREATE TABLE cinemas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    total_seats SMALLINT UNSIGNED NOT NULL CHECK (total_seats <= 65535)
);

Best for:

✔ Population of a small town

✔ Page views per small website

✔ Number of students in a school

3. MEDIUMINT (3 Bytes, MySQL Only) – Large Counters

** Example: Tracking YouTube Views**

A YouTube video with millions of views needs a larger counter than SMALLINT.

CREATE TABLE videos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    view_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
);

Best for:

✔ Tracking medium-sized counts (e.g., video views, store visitors).

✔ Cheaper than INT for cases where SMALLINT is too small.

4. INT / INTEGER (4 Bytes) – Standard IDs & Large Counts

** Example: E-commerce Orders**

Amazon processes millions of orders, requiring INT (or BIGINT for very large-scale systems).

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    order_status TINYINT(1) NOT NULL DEFAULT 0  -- 0 = Pending, 1 = Shipped, etc.
);

Best for:

✔ Primary keys & auto-increments

✔ User IDs, order numbers, transaction IDs

✔ Bank balances (if not exceeding 2B)

5. BIGINT (8 Bytes) – Massive Scale Data

** Example: Storing Social Media User IDs (Facebook, Instagram, Twitter)**

Billions of users require BIGINT for unique IDs.

CREATE TABLE social_users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    followers_count BIGINT UNSIGNED NOT NULL DEFAULT 0
);

Best for:

✔ Tracking large social media followings

✔ Financial transactions & banking (large sums)

✔ Timestamps (storing Unix time: 1700000000 in seconds)

6. SPECIAL CASE: PostgreSQL SERIAL Types (Auto-Incrementing IDs)

PostgreSQL offers SERIAL, which automatically assigns unique numbers.

** Example: Banking Transactions**

CREATE TABLE bank_transactions (
    id BIGSERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Best for:

✔ Auto-incrementing IDs without managing sequences manually

Which Integer Type to Use?

Use Case	Best Integer Type	MySQL Example	PostgreSQL Equivalent
Boolean flags (0/1)	`TINYINT(1)`	`TINYINT(1)`	`BOOLEAN`
Small counters (ratings, seats)	`SMALLINT`	`SMALLINT(5) UNSIGNED`	`SMALLINT`
Medium-sized counters (video views, products)	`MEDIUMINT`	`MEDIUMINT UNSIGNED`	`INTEGER`
Standard IDs (users, orders, employees)	`INT`	`INT AUTO_INCREMENT`	`SERIAL`
Very large IDs (Facebook, TikTok users, timestamps)	`BIGINT`	`BIGINT AUTO_INCREMENT`	`BIGSERIAL`

Image Credit