Choose Appropriate Data Types

Why This Matters

Choosing the right data type affects storage size, query performance, and data integrity. Wrong choices waste space, slow down comparisons, and can cause precision errors.

• Storage efficiency: Using bigint when int suffices wastes 4 bytes per row
• Comparison speed: Smaller types are faster to compare and sort
• Precision: float can lose precision; numeric is exact
• Timezone handling: timestamp ignores timezones; timestamptz stores UTC

Common Mistakes

-- Using bigint when int is sufficient (wastes 4 bytes per row)
CREATE TABLE users (
  id BIGINT PRIMARY KEY,  -- Only need int for < 2B users
  age BIGINT,  -- Age fits in smallint (2 bytes)
  email VARCHAR(255)  -- text is better, no length limit
);

-- Using float for money (precision errors!)
CREATE TABLE orders (
  total FLOAT  -- Can lose precision: 0.1 + 0.2 ≠ 0.3
);

-- Using timestamp without timezone
CREATE TABLE events (
  created_at TIMESTAMP  -- Ambiguous, timezone issues
);

Best Practices

Integer Types: Choose the Right Size

-- Use int (4 bytes) unless you need > 2B values
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  age SMALLINT,  -- 2 bytes, range -32K to 32K
  email TEXT  -- No length limit, better than varchar
);

-- Use bigint only when needed (8 bytes)
CREATE TABLE analytics_events (
  id BIGINT GENERATED ALWAYS AS IDENTITY,  -- High-volume table
  user_id INT  -- FK to users.id
);

Text Types: Prefer TEXT over VARCHAR

-- TEXT is preferred: no length limit, same performance
CREATE TABLE posts (
  title TEXT,  -- Better than VARCHAR(255)
  content TEXT,
  slug TEXT UNIQUE
);

-- Only use VARCHAR when you need CHECK constraint on length
CREATE TABLE api_keys (
  key VARCHAR(64) CHECK (LENGTH(key) = 64)
);

Numeric Types: Use NUMERIC for Money

-- NUMERIC is exact, float is approximate
CREATE TABLE orders (
  total NUMERIC(10, 2),  -- Exact decimal: $123.45
  tax_rate NUMERIC(5, 4)  -- 0.0825 for 8.25%
);

-- Use float only for scientific calculations
CREATE TABLE sensor_readings (
  temperature DOUBLE PRECISION,  -- Approximate is fine
  pressure REAL
);

Timestamps: Always Use TIMESTAMPTZ

-- TIMESTAMPTZ stores UTC, converts on display
CREATE TABLE events (
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Queries work correctly across timezones
SET timezone = 'America/New_York';
SELECT created_at FROM events;  -- Shows in NY time

SET timezone = 'UTC';
SELECT created_at FROM events;  -- Shows in UTC

Common Mistakes

• Overusing bigint: Unless you expect billions of rows, int (range: -2B to 2B) is sufficient and saves 4 bytes per row.
• VARCHAR with arbitrary limits: VARCHAR(255) is a MySQL habit. In Postgres, TEXT has no performance penalty and no length limit.
• Using float for money: Floating-point arithmetic errors make 0.1 + 0.2 ≠ 0.3. Always use NUMERIC for financial data.
• timestamp without timezone: TIMESTAMP doesn't store timezone info, causing bugs when users are in different timezones. Always use TIMESTAMPTZ.
• Not using domain types: For repeated patterns like email or phone, create a domain type: CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+\.[^@]+$');