PostgreSQL from the ground up: model, query and index data

PostgreSQL is one of the most widely used databases in the world, trusted by startups and large companies alike for its reliability, standards compliance and rich feature set. This course teaches you to use it properly, from your first connection to designing schemas, writing real queries and understanding indexes.

What you will build

We model and query the database for a small online bookshop: customers, books, authors, orders and order items. Every concept is applied to this schema, so you see how pieces fit together in a realistic system.

• Install PostgreSQL locally or with Docker, and connect with psql
• Create tables with sensible data types, primary keys and foreign keys
• Protect data with NOT NULL, UNIQUE and CHECK constraints
• Write joins, aggregates, subqueries, common table expressions and window functions
• Change data safely with transactions, RETURNING and upserts
• Read query plans with EXPLAIN and add indexes that actually help
• Manage roles, take backups and use JSONB where it fits

Version and tools

Examples are tested on PostgreSQL 17 and 18, and nearly everything works on older supported versions too. You can run PostgreSQL with Docker or a native installer. We use the psql command line client throughout, and you can also use a graphical tool such as pgAdmin or DBeaver if you prefer.

Who it is for

Developers who use databases through an ORM and want to understand what happens underneath, analysts moving beyond spreadsheets, and students learning SQL. No database experience is required, though basic comfort with the command line helps.