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

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