Chapter 9: Data Types — Choosing the Right One

Choosing the right data type affects storage size, query performance, data integrity, and what operations you can perform. Get it wrong and you'll pay for it forever (migrations are painful).

Numeric Types

Type	Size	Range	Use When
`SMALLINT`	2 bytes	-32,768 to 32,767	Status codes, small counters
`INTEGER`	4 bytes	-2.1B to 2.1B	Most IDs, counts, general use
`BIGINT`	8 bytes	-9.2×10¹⁸ to 9.2×10¹⁸	Large IDs, timestamps as int
`NUMERIC(p,s)`	Variable	Exact, user-defined precision	Money, financial calculations
`REAL`	4 bytes	~6 decimal digits precision	Scientific data (approx OK)
`DOUBLE PRECISION`	8 bytes	~15 decimal digits precision	Scientific data (approx OK)
`SERIAL`	4 bytes	Auto-increment INTEGER	Primary keys
`BIGSERIAL`	8 bytes	Auto-increment BIGINT	High-volume PKs

⚠️ Never Use FLOAT for Money

0.1 + 0.2 = 0.30000000000000004 in floating point. Use NUMERIC(10,2) for money, or store cents as INTEGER (e.g., $19.99 → 1999).

String Types

Type	Behavior	Use When
`CHAR(n)`	Fixed-length, padded with spaces	Fixed-format codes (ISO country: CHAR(2))
`VARCHAR(n)`	Variable-length, max n chars	Most strings with known max length
`TEXT`	Variable-length, unlimited	Long text, no length constraint needed

💡 PostgreSQL Tip

In PostgreSQL, VARCHAR(n) and TEXT have identical performance. The only difference is the length check. If you don't have a natural max length, just use TEXT. Add a CHECK constraint if you need validation.

Date/Time Types

Type	Stores	Example
`DATE`	Date only	2024-03-15
`TIME`	Time only	14:30:00
`TIMESTAMP`	Date + time (no timezone)	2024-03-15 14:30:00
`TIMESTAMPTZ`	Date + time + timezone	2024-03-15 14:30:00+02
`INTERVAL`	Duration	3 days 04:05:06

⚠️ Always Use TIMESTAMPTZ

Use TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) for any point-in-time data. Plain TIMESTAMP doesn't store timezone info — if your servers are in different timezones, you'll get wrong results. TIMESTAMPTZ stores everything in UTC internally and converts on display.

Boolean

-- BOOLEAN: true, false, or NULL
CREATE TABLE features (
    name    TEXT,
    enabled BOOLEAN NOT NULL DEFAULT false
);

SELECT * FROM features WHERE enabled;  -- shorthand for = true

UUID

-- UUID: 128-bit universally unique identifier
-- Example: 550e8400-e29b-41d4-a716-446655440000

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INTEGER NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

💡 UUID vs SERIAL for Primary Keys

SERIAL/BIGSERIAL: smaller (4-8 bytes), sequential (good for indexes), but reveals row count and creation order
UUID: larger (16 bytes), random (slightly worse index performance), but globally unique across systems — essential for distributed systems and microservices

JSON/JSONB (PostgreSQL)

-- JSONB: binary JSON, indexable, queryable
CREATE TABLE events (
    id   SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

INSERT INTO events (data) VALUES
    ('{"type": "login", "user": "alice", "ip": "1.2.3.4"}');

-- Query JSON fields
SELECT data->'user' FROM events WHERE data->>'type' = 'login';

Arrays (PostgreSQL)

CREATE TABLE posts (
    id   SERIAL PRIMARY KEY,
    tags TEXT[]
);

INSERT INTO posts (tags) VALUES ('{grpc, protobuf, networking}');

-- Find posts with a specific tag
SELECT * FROM posts WHERE 'grpc' = ANY(tags);

Quick Decision Guide

Data	Use	Don't Use
Money	NUMERIC(12,2) or INTEGER (cents)	FLOAT, DOUBLE
IDs (single system)	SERIAL / BIGSERIAL	VARCHAR
IDs (distributed)	UUID	SERIAL
Timestamps	TIMESTAMPTZ	TIMESTAMP, VARCHAR, INTEGER
Yes/No	BOOLEAN	INTEGER, CHAR(1)
Short strings	VARCHAR(n) or TEXT	CHAR(n) unless truly fixed
Semi-structured	JSONB	TEXT (then parsing in app)

Key Takeaways

Use the smallest type that fits your data (saves storage and improves cache efficiency)
NUMERIC for money, never FLOAT
TIMESTAMPTZ for all timestamps (timezone-aware)
UUID for distributed systems, SERIAL for single-database apps
JSONB when you need flexible schema within a relational table
Wrong type choice = painful migration later. Think carefully upfront.

← Previous: Subqueries & CTEs Next: Normalization →