Choosing Integer Types in MySQL & PostgreSQL

This content originally appeared on DEV Community and was authored by Kelly Okere

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

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

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?

Image Credit

This content originally appeared on DEV Community and was authored by Kelly Okere

Print Share Comment Cite Upload Translate Updates

APA

Kelly Okere | Sciencx (2025-02-23T00:11:36+00:00) Choosing Integer Types in MySQL & PostgreSQL. Retrieved from https://www.scien.cx/2025/02/23/choosing-integer-types-in-mysql-postgresql/

MLA

" » Choosing Integer Types in MySQL & PostgreSQL." Kelly Okere | Sciencx - Sunday February 23, 2025, https://www.scien.cx/2025/02/23/choosing-integer-types-in-mysql-postgresql/

HARVARD

Kelly Okere | Sciencx Sunday February 23, 2025 » Choosing Integer Types in MySQL & PostgreSQL., viewed ,<https://www.scien.cx/2025/02/23/choosing-integer-types-in-mysql-postgresql/>

VANCOUVER

Kelly Okere | Sciencx - » Choosing Integer Types in MySQL & PostgreSQL. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2025/02/23/choosing-integer-types-in-mysql-postgresql/

CHICAGO

" » Choosing Integer Types in MySQL & PostgreSQL." Kelly Okere | Sciencx - Accessed . https://www.scien.cx/2025/02/23/choosing-integer-types-in-mysql-postgresql/

IEEE

" » Choosing Integer Types in MySQL & PostgreSQL." Kelly Okere | Sciencx [Online]. Available: https://www.scien.cx/2025/02/23/choosing-integer-types-in-mysql-postgresql/. [Accessed: ]

rf:citation

» Choosing Integer Types in MySQL & PostgreSQL | Kelly Okere | Sciencx | https://www.scien.cx/2025/02/23/choosing-integer-types-in-mysql-postgresql/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.