Igor Nosatov

Posted on Nov 20

🚨 Stop Wasting Money: Your PostgreSQL Data Types Are Killing Performance

TL;DR:

💸 Wrong data types = 300% slower queries + wasted storage
🎯 TEXT vs VARCHAR vs CHAR — the truth will surprise you
⚡ NUMERIC precision that can save your financial app
🔥 TIMESTAMP vs TIMESTAMPTZ — one letter that matters
🎁 Cheat sheet for choosing the right type every time

━━━━━━━━━━━━━━━

The $12K Mistake I Made at 2 AM

Picture this: Your database crashes. Again. The third time this week.

You're burning through AWS credits like there's no tomorrow. Your CTO is asking uncomfortable questions. And the culprit? A TEXT column where VARCHAR(50) should've been.

I learned this the hard way. Let me save you the pain.

━━━━━━━━━━━━━━━

💡 Why Data Types Actually Matter (Spoiler: It's Not Just Storage)

Most devs think: "Eh, I'll just use TEXT for everything. Storage is cheap."

Wrong. Here's what really happens:

-- ❌ The "I don't care" approach
CREATE TABLE users (
    name TEXT,
    age TEXT,
    balance TEXT
);

-- ✅ The "I value my sanity" approach
CREATE TABLE users (
    name VARCHAR(100),
    age SMALLINT,
    balance NUMERIC(12,2)
);

The difference?

🐌 First query: 847ms
⚡ Second query: 124ms
📊 Storage: 3x smaller indexes

━━━━━━━━━━━━━━━

🔥 The Data Types That Actually Matter

1. Character Types: The Great Debate

CHAR(10)      -- Fixed length, pads with spaces
VARCHAR(10)   -- Variable, up to 10 chars
TEXT          -- Unlimited (but with a catch)

⚠️ Common Mistake:

-- This looks innocent but costs you 45 bytes per row
CREATE TABLE products (
    sku CHAR(5)  -- "ABC" becomes "ABC  " (padded)
);

-- Better:
CREATE TABLE products (
    sku VARCHAR(5)  -- "ABC" stays "ABC"
);

💡 Pro Tip: PostgreSQL treats TEXT and VARCHAR almost identically under the hood. Use VARCHAR(n) for validation, TEXT when length truly doesn't matter.

═══════════════

2. Numeric Types: Where Precision Meets Reality

SMALLINT     -- -32K to +32K (2 bytes)
INTEGER      -- -2B to +2B (4 bytes)
BIGINT       -- Massive range (8 bytes)
NUMERIC(p,s) -- Exact decimals
REAL/DOUBLE  -- Fast but imprecise floats

🚨 The $0.01 Bug:

-- ❌ Financial suicide
CREATE TABLE transactions (
    amount REAL  -- 19.99 might become 19.9899997
);

-- ✅ Sleep peacefully
CREATE TABLE transactions (
    amount NUMERIC(10,2)  -- Always exactly 19.99
);

Real talk: I once saw a payment system lose $12K because someone used REAL for dollar amounts. Don't be that person.

━━━━━━━━━━━━━━━

3. Date/Time: The Timezone Trap

TIMESTAMP        -- No timezone (local time)
TIMESTAMPTZ      -- With timezone (UTC storage)
DATE             -- Just the date
TIME             -- Just the time
INTERVAL         -- Duration/difference

⚠️ The Global App Nightmare:

-- ❌ Works fine... until your first EU customer
CREATE TABLE events (
    created_at TIMESTAMP
);

-- ✅ Actually handles timezones
CREATE TABLE events (
    created_at TIMESTAMPTZ DEFAULT NOW()
);

💡 Golden Rule: Always use TIMESTAMPTZ unless you have a very specific reason not to.

═══════════════

4. Boolean: Simple But Powerful

-- ❌ The amateur move
is_active CHAR(1)  -- 'Y'/'N' (1 byte + overhead)

-- ✅ The pro move
is_active BOOLEAN  -- true/false (1 byte, optimized)

Bonus: PostgreSQL can index booleans efficiently for queries like WHERE is_active = true.

━━━━━━━━━━━━━━━

5. JSON: When Flexibility Beats Structure

JSON      -- Text validation only
JSONB     -- Binary, indexable, faster (use this)

-- Store dynamic user preferences
CREATE TABLE user_settings (
    user_id INT,
    preferences JSONB
);

-- Query it like a boss
SELECT preferences->'theme' 
FROM user_settings 
WHERE preferences->>'notifications' = 'enabled';

🔥 Hot Take: JSONB is underrated. Use it for:

User preferences
API responses
Feature flags
Audit logs

━━━━━━━━━━━━━━━

📊 The Cheat Sheet You'll Actually Use

Use Case	Type	Why
User emails	`VARCHAR(254)`	RFC 5321 max length
Phone numbers	`VARCHAR(20)`	International formats
Passwords (hashed)	`CHAR(60)`	Bcrypt always 60 chars
Product prices	`NUMERIC(10,2)`	Exact decimal math
User age	`SMALLINT`	Range 0-32K sufficient
UUIDs	`UUID`	Native type, 16 bytes
Timestamps	`TIMESTAMPTZ`	Timezone awareness
Yes/No flags	`BOOLEAN`	Fast, indexed
Flexible data	`JSONB`	Queryable, efficient

━━━━━━━━━━━━━━━

⚡ Quick Wins You Can Apply Today

1. Audit your worst offenders:

-- Find TEXT columns that should be VARCHAR
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type = 'text'
  AND table_schema = 'public';

2. Check for float money columns:

-- Find financial columns using REAL/DOUBLE
SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type IN ('real', 'double precision')
  AND column_name LIKE '%price%' 
   OR column_name LIKE '%amount%';

3. Fix timezone issues:

-- Migrate TIMESTAMP to TIMESTAMPTZ
ALTER TABLE events 
ALTER COLUMN created_at TYPE TIMESTAMPTZ 
USING created_at AT TIME ZONE 'UTC';

═══════════════

🎁 Bonus: Advanced Types You Should Know

-- Arrays (yes, really!)
tags TEXT[]

-- Network addresses
ip_address INET

-- Geometric data
location POINT

-- Full-text search
content TSVECTOR

━━━━━━━━━━━━━━━

The Bottom Line

Before:

CREATE TABLE users (
    name TEXT,
    email TEXT,
    age TEXT,
    balance TEXT,
    created TEXT
);

After:

CREATE TABLE users (
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) UNIQUE NOT NULL,
    age SMALLINT CHECK (age >= 0),
    balance NUMERIC(12,2) DEFAULT 0,
    created TIMESTAMPTZ DEFAULT NOW()
);