Chapter 1 of 7 · 5 practices · 4 min · 4-question quiz
Schema design and data types
The choices that are expensive to change once data is in the table: column types, primary keys and when to reach for JSON.
Start: Store points in time as timestamptz5 practices in this chapter
- 1Store points in time as timestamptzTime bugs are some of the hardest to spot, because the data looks fine until servers, users or daylight saving rules disagree. Picking the right type up front removes a whole class of them.
- 2Choose types that match the data: text, numeric, bigintThe right type gives you validation and correct maths for free. The wrong one pushes checks into every piece of code that touches the column.
- 3Use identity columns instead of serialPrimary keys are hard to change once other tables point at them, so start with the modern default.
GENERATED ALWAYS AS IDENTITYis the SQL standard way to auto-number a column and the replacement… - 4Use jsonb for flexible data, not as a replacement for columns
jsonbis great for data whose shape really varies, such as third-party webhook payloads or per-customer settings. It is a poor fit for fields you filter, join or constrain on every request, because… - 5Use lowercase snake_case names and avoid quoted identifiersPostgreSQL folds unquoted names to lowercase. If an ORM creates a table called
"UserAccounts"with quotes, every hand-written query must quote it exactly, forever. Lowercasesnake_casenames…
Check yourself on this chapter
4 questions, right here once you are signed in. Your progress is saved as you read.