INT vs BIGINT vs SMALLINT

Short answer: SMALLINT is 2 bytes (~±32K), INT is 4 bytes (~±2.1B), BIGINT is 8 bytes (~±9.2 quintillion). Use INT for almost everything; use BIGINT for IDs on tables that might exceed 2 billion rows or for monetary values stored as cents on accounts that might cross billion-dollar lifetime totals; use SMALLINT only for genuinely small ranges where the storage savings matter (rare on modern engines).

INT is the default. Foreign keys, counts, quantities, ages, anything that comfortably fits in ±2.1B. The 4-byte size matches CPU register size on most architectures, which gives it slight performance advantages on bulk operations.

BIGINT — when 2.1 billion is plausibly close:

• Surrogate primary keys on event-style tables (logs, clicks, transactions). SERIAL (4-byte) on a write-heavy table runs out faster than people expect.
• Money stored as integer minor units (cents): amount_cents BIGINT survives any account size; amount_cents INT caps at $21.4M.
• Microsecond/nanosecond timestamps stored as integers.
• Hash-derived IDs that need 64 bits.

SMALLINT — useful for genuinely small enumerations on huge tables where storage matters: a status code (1-10), a year-of-life column (0-150), a country index (200 values). Modern engines store INT efficiently enough that SMALLINT savings are usually negligible. Reach for it deliberately, not by default.

TINYINT / INT2 / INT8 are engine-specific aliases. TINYINT is 1 byte (0-255 unsigned, ±127 signed) on MySQL and SQL Server; doesn't exist in Postgres.

• INT primary keys on growth tables: the canonical "we'll fix it before we hit the limit" promise. By the time the table hits 1.5B rows, the ALTER TABLE to widen the key takes hours and locks writers. Default to BIGINT for any table where 2B rows is plausible (events, audit logs, transactions).
• INT for cents on monetary columns: INT caps at $21,474,836.47. Fine for individual transactions, dangerous for cumulative balances or aggregated revenue. Use BIGINT for cents, or use DECIMAL and skip the integer-cents trick. See DECIMAL vs FLOAT.
• Mixing INT and BIGINT in joins: silent type promotion to BIGINT works but breaks index usage on some older engines. Match the types on FK columns.
• Using unsigned INT to "double the range" on MySQL: works, but only MySQL has unsigned types, so moving the schema to Postgres or Snowflake later requires conversion. Better to use BIGINT from the start.

• Postgres: SMALLINT (2 bytes), INTEGER/INT/INT4 (4 bytes), BIGINT/INT8 (8 bytes). SERIAL, BIGSERIAL for auto-increment. No unsigned types.
• MySQL: TINYINT (1B), SMALLINT (2B), MEDIUMINT (3B — 24-bit), INT (4B), BIGINT (8B). Each has unsigned variant. AUTO_INCREMENT requires the column to be a key.
• SQL Server: TINYINT (1B, 0-255 only — no signed), SMALLINT, INT, BIGINT. IDENTITY(seed, increment) for auto-increment.
• SQLite: dynamic typing, so all integer types are stored as one variable-length INTEGER. The declared type is hint-only.
• Snowflake: all integer types (TINYINT, SMALLINT, INT, BIGINT) are aliases for NUMBER(38,0). No real storage difference. Auto-increment via AUTOINCREMENT or IDENTITY.
• BigQuery: only INT64 (8 bytes). Synonyms INTEGER, INT, SMALLINT, BIGINT are accepted but all map to INT64.
• DuckDB: TINYINT through HUGEINT (16 bytes). Auto-increment via SEQUENCE.