Explore advanced PostgreSQL capabilities through 20 guided, hands-on labs. You will move beyond basic table operations into relational design, specialized data types, query planning, transactions, programmable database logic, administration, extensions, connection pooling, and replication while working directly with SQL and command-line utilities.
Each lab isolates one operational or development topic in a small local environment. You will inspect query plans, create PL/pgSQL functions and triggers, produce and restore SQL backups, enable PostGIS, benchmark connections through PgBouncer, and build a streaming replica. The guided format makes complex features approachable while keeping their production tradeoffs visible.
What You Will Learn
- Model relationships with foreign keys and compare inner and outer joins
- Query JSONB, arrays, UUIDs, dates, time zones, CTEs, subqueries, and window functions
- Create indexes, inspect plans with
EXPLAIN, partition tables, and implement full-text search with GIN - Manage transactions, rollbacks, isolation levels, locks, views, and materialized views
- Develop stored functions, row triggers, event triggers, and PL/pgSQL exception handling
- Control roles and privileges and perform backup, restore,
VACUUM,ANALYZE, and log inspection - Enable PostGIS for point, distance, buffer, and intersection queries
- Configure PgBouncer pooling and set up and verify PostgreSQL streaming replication
Who This Course Is For
This course is for SQL learners, application developers, and aspiring database administrators who know basic PostgreSQL and want guided exposure to a broad set of advanced features. It is suitable for building practical familiarity, but its compact labs are not a substitute for designing and operating a production database platform.
Prerequisites: You should understand basic SQL statements, tables, keys, and queries and be comfortable using a Linux terminal. Prior experience with psql is recommended because many labs combine SQL with service and configuration commands.
Learning environment: Each lab provides an Ubuntu 22.04 VNC desktop with a local PostgreSQL server, terminal access, and required packages such as PostGIS or PgBouncer when relevant. Administrative exercises use local services and sudo; no cloud database or external account is required.
Frequently Asked Questions
Why is an “advanced” course labeled Beginner?
All 20 labs are marked Beginner because they provide explicit, step-by-step instructions. The features themselves are advanced in breadth, so basic SQL and PostgreSQL familiarity are still important; the course introduces each area rather than developing production-level depth.
Does the replication lab use multiple servers or provide high availability?
No. It runs a primary on port 5432 and a read-only streaming replica on port 5433 on the same VM. You practice WAL settings, pg_basebackup, startup, monitoring, and data propagation, but not multi-host networking, automated failover, or disaster recovery.
Do PostGIS and PgBouncer require external infrastructure?
No. The labs install and configure both locally. Spatial queries use a small cities table, and PgBouncer is tested on localhost with pgbench and its administrative statistics.
How closely do the administration labs match production operations?
They demonstrate commands and concepts with small disposable databases, including plain-SQL pg_dump backups and manual configuration edits. Capacity planning, security hardening, physical backups, point-in-time recovery, monitoring systems, and production change procedures are outside the course scope.
