🧪SQL data types

Types are invariants you ship to disk

Choosing INT vs BIGINT, NUMERIC vs DOUBLE, or TIMESTAMPTZ vs TIMESTAMP is how you encode business rules at rest. Wrong types do not fail loudly, they corrupt analytics slowly (silent rounding, wrong time buckets, overflow wraps).

📊Pick a column family with the failure mode in mind

Money, identifiers, measurements, and events each want different representations.

Family	Common SQL types	Typical use	Watch out for
Exact integers	SMALLINT, INT, BIGINT	Counts, surrogate keys, instrument channel indices	Overflow if you pick INT for global IDs that exceed 2³¹
Fixed / arbitrary precision	NUMERIC(p,s), DECIMAL(p,s)	Money, concentrations, anything audited	Arithmetic slower than binary floats, still the right default for currency
Approximate	REAL, DOUBLE	Telemetry, imaging stats, ML features	Never use for currency; equality is fuzzy by design
Text	CHAR(n), VARCHAR(n), TEXT / CLOB	Names, accession codes, free text notes	Collation affects equality and index order, document it for search features
Temporal	DATE, TIME, TIMESTAMP, TIMESTAMPTZ	Run start/end, consent windows, instrument clocks	Store event time in UTC (`TIMESTAMPTZ`) when humans span regions
Binary	BYTEA, BLOB	Hashes, ciphertext, opaque blobs	Harder to diff in PRs, pair with application-layer validation

⚖️CHAR vs VARCHAR in practice

Fixed width is not “more strict”: it is mostly about padding and storage layout.

Type	Storage idea	When it helps
CHAR(n)	Pad to n characters on assignment/compare (dialect-specific rules)	Truly fixed codes (rare): plate rack slot codes with a rigid standard
VARCHAR(n)	Variable up to n logical characters/bytes depending on encoding	Almost all human-readable text; pick n from real max + margin, not “9999”

FLOAT for currency or titers

Binary floats cannot represent many decimal fractions exactly. Summing thousands of rows yields pennies that drift from Excel or your finance tool.

NUMERIC + explicit rounding at boundaries

Use NUMERIC(12,4) (or whatever precision your protocol defines). Round once at ingestion or once at presentation, document which.

⚡ Advanced / dialect note

BOOLEAN is first-class in PostgreSQL. In other engines you may store 0/1 with a CHECK constraint or use BIT. Always document the convention in your schema and in the API schema that mirrors it.

SQL

CAST for safe comparisons

SELECT sample_id,
       concentration::NUMERIC(10,4) AS concentration_mg_ml
FROM assay_results
WHERE concentration::NUMERIC > 0.5;

✓Illustrative result set

sample_id	concentration_mg_ml
S-1001	0.8123
S-1004	1.02

Wire format alignment

If your API uses ISO-8601 strings and protobuf google.protobuf.Timestamp, mirror that decision in the DB so casts are one-directional and logged.

Indexability

Expression-heavy types (JSON paths, computed text) need expression indexes or generated columns, plan those when you choose the type, not after launch.

🗂️Physical Storage & Overhead

Every byte counts when you have 10 billion rows.

The Null Bitmap

Does NULL take space? Yes. Every row in a modern engine (PostgreSQL, SQL Server) starts with a fixed-length header and a Null Bitmap. This bitmask uses 1 bit per column to track nulls. If you have 8 columns, you pay 1 byte per row for the bitmap, regardless of whether any field is actually NULL.

TOAST / External Storage

In PostgreSQL, if a row exceeds ~2KB, the engine moves large columns (TEXT, JSONB, BYTEA) to a separate "TOAST" table. The main row only keeps a 18-byte pointer. This keeps the main table scan fast but makes fetching large fields expensive.

Overflow Pages

In SQL Server, if VARCHAR columns exceed the 8KB page limit, they spill into Row-Overflow Data Pages. This adds a pointer overhead and a second I/O operation to read the full row.

📐Byte Budgeting

Typical storage footprint for common types.

Type	On-Disk Size	Mechanical Note
SMALLINT	2 bytes	Range -32,768 to 32,767. Ideal for status codes.
INT / INTEGER	4 bytes	Up to 2.1 billion. Risky for global primary keys.
BIGINT	8 bytes	Default for modern PKs. Virtually impossible to overflow.
NUMERIC(p,s)	Variable	Stored as a decimal string or custom binary. High precision, high CPU cost.
TIMESTAMPTZ	8 bytes	Stores UTC internally. Offset is handled by the client/session.

Migration habit

Widening a column from INT to BIGINT is a "Size-Only" migration in theory, but in practice, it double the storage of those columns and can trigger a full table rewrite (re-write all pages) depending on the engine. Plan for downtime or use online migration tools.

TL;DR

Data types fix domain, range, and storage: integers vs decimals for money, fixed-length vs variable text, timestamps with time zone vs naive local times. Wrong types cause silent rounding, overflow, or impossible comparisons. Align types with how the app serializes values and with index cardinality, narrower types usually mean smaller indexes and faster scans.