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.