PostgreSQL

Overview

About PostgreSQL

PostgreSQL is a free, open-source relational database known for its reliability, standards compliance, and extensibility, and this course walks through it in the order most developers actually learn it. It starts with Getting Started with PostgreSQL (installing the server and finding your way around psql), then Querying Data with SQL and Data Types & Table Design so you can read, write, and structure data properly.

Next comes Joins, CTEs & Window Functions, the tools for answering real questions across many tables, followed by Indexes & Query Performance, where you learn to read query plans with EXPLAIN ANALYZE and choose between B-tree, GIN, GiST, and BRIN indexes. Transactions & Concurrency covers ACID guarantees, isolation levels, MVCC, and locking.

JSONB, Search & Extensions covers storing semi-structured data, full-text search, and extensions such as pg_trgm and PostGIS, while Views, Functions & Triggers covers moving logic into the database with PL/pgSQL.

Finally, Security & Administration covers roles, privileges, row-level security, and backups, and Replication & Scaling covers partitioning, connection pooling, streaming replication, and monitoring: the skills that separate a database that works on your laptop from one that holds up in production.

Content

10 modules
01

Getting Started with PostgreSQL

What PostgreSQL is, how to install it, and how to connect and run your first commands with psql and a GUI client.

InstallationpsqlpgAdmin
02

Querying Data with SQL

Reading and changing data with SELECT, INSERT, UPDATE, and DELETE, including filtering, sorting, grouping, and aggregates.

SELECTWHEREGROUP BY
03

Data Types & Table Design

Choosing the right data types, defining constraints and keys, organising tables into schemas, and normalising your data.

Data TypesConstraintsNormalization
04

Joins, CTEs & Window Functions

Combining tables with joins, breaking queries into readable steps with subqueries and CTEs, and ranking or comparing rows with window functions.

JOINCTEWindow Functions
05

Indexes & Query Performance

Reading query plans, picking the right index type for the job, and keeping tables healthy with VACUUM and ANALYZE.

EXPLAIN ANALYZEB-treeGIN
06

Transactions & Concurrency

How ACID guarantees work, what the isolation levels mean in practice, and how MVCC and locks keep concurrent users from colliding.

ACIDIsolation LevelsMVCC
07

JSONB, Search & Extensions

Storing and querying JSON documents, adding full-text and fuzzy search, and extending PostgreSQL with popular extensions.

JSONBFull-Text SearchPostGIS
08

Views, Functions & Triggers

Reusing logic with views and materialized views, then writing functions, procedures, and triggers in PL/pgSQL.

ViewsPL/pgSQLTriggers
09

Security & Administration

Managing roles and privileges, locking down access with pg_hba.conf and row-level security, and backing up and restoring data.

RolesRow-Level Securitypg_dump
10

Replication & Scaling

Partitioning large tables, pooling connections, setting up replicas, and monitoring a production database.

PartitioningPgBouncerStreaming Replication

What You Can Do After This Course

By the end of the curriculum you'll have worked through 5 hands-on projects and covered the core of what a junior to mid-level role working with PostgreSQL expects. Concretely, you'll be able to:

Set up and navigate PostgreSQL: install a server, connect with psql or a GUI client, and manage databases and schemas.

Write confident SQL: query, filter, aggregate, and combine data across tables with joins, CTEs, and window functions.

Design solid schemas: pick the right data types, enforce integrity with constraints and keys, and normalise where it helps.

Tune slow queries: read EXPLAIN output, add the right indexes, and keep tables healthy with VACUUM.

Keep data correct under load: use transactions, choose sensible isolation levels, and avoid common locking pitfalls.

Go beyond plain tables: store JSONB, run full-text search, and put logic in views, functions, and triggers.

Run it in production: secure access with roles and row-level security, back up and restore, and scale with partitioning, pooling, and replicas.