Schema Data Types

title: Choose Appropriate Data Types impact: HIGH impactDescription: 50% storage reduction, faster comparisons tags: data-types, schema, storage, performance

Choose Appropriate Data Types

Using the right data types reduces storage, improves query performance, and prevents bugs.

Incorrect (wrong data types):

create table users (
  id int,                    -- Will overflow at 2.1 billion
  email varchar(255),        -- Unnecessary length limit
  created_at timestamp,      -- Missing timezone info
  is_active varchar(5),      -- String for boolean
  price varchar(20)          -- String for numeric
);

Correct (appropriate data types):

create table users (
  id bigint generated always as identity primary key,  -- 9 quintillion max
  email text,                     -- No artificial limit, same performance as varchar
  created_at timestamptz,         -- Always store timezone-aware timestamps
  is_active boolean default true, -- 1 byte vs variable string length
  price numeric(10,2)             -- Exact decimal arithmetic
);

Key guidelines:

-- IDs: use bigint, not int (future-proofing)
-- Strings: use text, not varchar(n) unless constraint needed
-- Time: use timestamptz, not timestamp
-- Money: use numeric, not float (precision matters)
-- Enums: use text with check constraint or create enum type

Reference: Data Types