Choosing Keys and Types
Pick BIGSERIAL vs UUID primary keys, decide between TEXT and VARCHAR(n), and choose the right type for every column.
Choosing Keys and Types is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Picking a Primary Key
Three real choices:
- BIGSERIAL — monotonically increasing 64-bit integer
- UUID — 128-bit globally unique
- Natural key — a real-world identifier (email, ISBN)
When BIGSERIAL Wins
Default choice for most apps:
- Small (8 bytes), fast index, cache-friendly
- Monotonic → recent data clusters at the end of the index
- Readable in URLs (e.g.
/posts/42)
When UUID Wins
Pick UUID when:
- You need globally unique IDs (multi-datacenter, offline-first apps)
- You don't want IDs to leak business volume in URLs
- You want to generate the ID client-side before insert
Cost: 16 bytes (vs 8 for BIGINT), and v4 UUIDs randomise inserts in the index.
Why Natural Keys Are Risky
Emails change. SKUs get renamed. Country codes get split. Use a surrogate as the PK and a UNIQUE constraint on the natural key.
Column Types: Strings
For most text columns:
VARCHAR(n)— when you have a hard upper bound (emails, codes)TEXT— for free-form, long contentCITEXT— when comparison should always be case-insensitive (emails!)
Column Types: Numbers
For numeric columns:
BIGINTfor IDs and countersNUMERIC(p, s)for moneyREAL/DOUBLE PRECISIONfor science only
Column Types: Time
For time columns:
TIMESTAMPTZfor events (default)DATEfor calendar days (birthdates, holidays)TIMEfor time-of-day without a date (store opening hours)
Boolean vs Status
If a column has more than two future states, prefer a status text + CHECK over a boolean:
-- Will probably grow:
status TEXT NOT NULL CHECK (status IN ('draft','published','archived'))
-- vs the trap of "is_published BOOLEAN" when you later need a third state.Constraint Ride-Along
Every column should have:
- The narrowest type that fits
NOT NULLunless NULL has meaning- A
DEFAULTif the column is mostly the same value - A
CHECKif there's a domain rule - An index if it's used in WHERE/JOIN/ORDER
Storage Implications
Narrow types = smaller rows = more rows per page = better cache hit rate. Switching TEXT to VARCHAR(20) doesn't save space (same storage). Switching BIGINT to INT halves the column width.
Foreign Key Types Must Match
FK column types must match the referenced PK type exactly:
-- Wrong (silently won't use the index efficiently):
user_id INT REFERENCES users(id) -- where users.id is BIGINT
-- Right:
user_id BIGINT REFERENCES users(id)Recap
Type choice is design.
- BIGSERIAL or UUID for PK
- NUMERIC for money
- TIMESTAMPTZ for events
- TEXT-with-CHECK for evolving status
- NOT NULL by default
Quick Check
You're storing event timestamps for users in many time zones. Which type?
Frequently asked questions
Is the “Choosing Keys and Types” lesson free?
Yes — the full text of “Choosing Keys and Types” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Choosing Keys and Types”?
Pick BIGSERIAL vs UUID primary keys, decide between TEXT and VARCHAR(n), and choose the right type for every column. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Choosing Keys and Types” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.