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