What makes PostgreSQL special?
PostgreSQL is the most popular relational database for new projects, and for good reason: a rich type system, strong constraints, JSON support, and decades of reliability. Going deep on one database — rather than skimming many — makes you genuinely productive.
Why it matters
Your schema is the contract your whole application leans on. Postgres lets you push correctness into the database with types and constraints, so bad data cannot get in even when application code has a bug. Teams expect a backend engineer to model data well, not just store blobs.
What to learn
- Core types: text, numeric, boolean, timestamptz, uuid
- JSONB for semi-structured data, and its trade-offs
- Primary keys, foreign keys, unique, and check constraints
- Schemas, naming, and migrations
- Enums and generated columns
psqlfor exploring a database- When to normalize and when to denormalize
Common pitfall
Storing timestamps without a time zone, or as strings. Use timestamptz so
Postgres stores an unambiguous instant and converts on the way out. Naive
local-time strings cause off-by-hours bugs the moment a second region or
daylight saving is involved.
Resources
Primary (free):
- PostgreSQL — Data types · docs
- PostgreSQL — Constraints · docs
- PostgreSQL — JSON types · docs
Practice
Design a schema for a small blog: users, posts, and comments. Add foreign keys
with sensible on-delete behavior, a unique constraint on user email, a check
constraint on a rating column, and timestamptz created-at columns. Insert a
row that violates each constraint and confirm Postgres rejects it.
Outcomes
- Choose appropriate Postgres types for real-world fields.
- Enforce data integrity with keys, unique, and check constraints.
- Decide when JSONB beats extra columns or tables.
- Explore and modify a database from
psql.