Data Types & Schema Design

numeric types, precisely

Type	Use for
`integer` (4 bytes) / `bigint` (8 bytes)	whole numbers — use `bigint` for anything that might exceed ~2.1 billion, like a high-volume event-log primary key
`numeric(precision, scale)`	exact decimal — always for money; never `float`, which rounds
`real` / `double precision`	approximate floating point — scientific/measurement data where exactness isn't the point

-- float rounding, the reason money is never a float:
SELECT 0.1::float + 0.2::float;   -- 0.30000000000000004
SELECT 0.1::numeric + 0.2::numeric; -- 0.3, exactly

text: don't overthink varchar(n)

In PostgreSQL specifically, text and varchar(n) have identical performance — there's no storage or speed penalty to text. Use varchar(n) only when the length limit is a genuine business rule you want the database to enforce; otherwise, text plus a CHECK constraint if needed is more flexible.

date/time: always timestamptz

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  happened_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

timestamptz stores the instant in UTC internally and converts to/from the client's timezone on the way in and out; plain timestamp stores whatever local time you gave it with no timezone context at all, which becomes ambiguous the moment your app, database server, or users span more than one timezone. Use timestamptz by default.

arrays and composite-ish columns

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

INSERT INTO posts (tags) VALUES (ARRAY['sql', 'postgresql', 'tutorial']);
SELECT * FROM posts WHERE 'sql' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['sql'];  -- contains

Native array columns are a genuine PostgreSQL feature most other engines lack. Useful for small, unordered, denormalized lists (tags, feature flags); for anything relational (needs its own attributes, needs to be queried/joined heavily on its own), a proper child table is usually still the better call — see Normalization.

constraints

Constraint	Enforces
`PRIMARY KEY`	unique, not null, one per table — the row's identity
`FOREIGN KEY ... REFERENCES`	a column's value must exist in another table's referenced column
`UNIQUE`	no duplicate values in this column (or column combination)
`NOT NULL`	column can never be NULL
`CHECK (expression)`	arbitrary boolean condition, e.g. `CHECK (price >= 0)`

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  amount NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'shipped', 'cancelled'))
);

A foreign key is checked on every insert/update, and by default blocks deleting a referenced row that still has children — add ON DELETE CASCADE (delete children too) or ON DELETE SET NULL depending on the relationship's meaning.

where to go from here

Normalization & Design Patterns — how to decide what belongs in which table in the first place.
JSONB — for genuinely unstructured or highly variable data instead of a rigid column set.