PostgreSQL Developer – 100+ Lab Exercises (Basic, Intermediate, Advanced)

🔰 Basic Level (30+ Exercises)

 

Goal: Build foundational knowledge of PostgreSQL database management, SQL syntax, and data manipulation.

 

Installation & Setup

  • Install PostgreSQL on Windows/Linux/macOS.

  • Configure pgAdmin for database management.

  • Create and connect to a PostgreSQL database.

  • Understand PostgreSQL architecture overview.

Basic SQL Queries

  • Write SELECT statements with filtering (WHERE).

  • Use ORDER BY and LIMIT clauses.

  • Insert, update, and delete data.

  • Use DISTINCT and aggregate functions (COUNT, SUM, AVG).

  • Basic string and date/time functions.

Table Design & Data Types

  • Create tables with appropriate data types.

  • Use constraints: NOT NULL, UNIQUE, PRIMARY KEY.

  • Define default values and check constraints.

  • Understand and use SERIAL and UUID types.

  • Alter tables to add/drop columns and constraints.

Basic Joins & Subqueries

  • Implement INNER JOIN, LEFT JOIN, RIGHT JOIN.

  • Write simple correlated and non-correlated subqueries.

  • Use EXISTS and IN predicates.

  • Practice UNION and UNION ALL.

 

🚀 Intermediate Level (40+ Exercises)

 

Goal: Develop advanced querying skills, indexing, and data integrity enforcement.

 

Advanced SQL Queries

  • Use window functions: ROW_NUMBER(), RANK(), LEAD(), LAG().

  • Advanced filtering with CASE expressions.

  • Recursive queries with CTEs (WITH clause).

  • Complex joins and multi-table queries.

  • Perform data pivoting and aggregation.

Indexing & Performance Tuning

  • Create and analyze B-tree, GIN, GiST, and BRIN indexes.

  • Understand EXPLAIN and EXPLAIN ANALYZE outputs.

  • Optimize query performance with indexes.

  • Use VACUUM and ANALYZE for maintenance.

  • Partition tables for performance and management.

Transactions & Concurrency

  • Implement transactions with BEGIN, COMMIT, ROLLBACK.

  • Set transaction isolation levels.

  • Manage locks and deadlocks.

  • Use advisory locks.

  • Practice multi-version concurrency control (MVCC).

Functions & Stored Procedures

  • Create user-defined functions in PL/pgSQL.

  • Use triggers to automate data actions.

  • Write stored procedures and control flow structures.

  • Handle exceptions in functions.

  • Implement security definer functions.

 

🧠 Advanced Level (40+ Exercises)

 

Goal: Master PostgreSQL administration, replication, security, and complex data types.

Advanced Data Types & Extensions

  • Use JSON and JSONB for semi-structured data.

  • Query and index JSONB data.

  • Work with arrays and hstore.

  • Use PostGIS for geospatial data.

  • Implement full-text search capabilities.

Replication & High Availability

  • Setup streaming replication (primary & standby).

  • Configure synchronous and asynchronous replication.

  • Perform failover and switchover.

  • Use pg_stat_replication for monitoring.

  • Understand logical replication and publication/subscription.

Backup, Restore & Security

  • Perform pg_dump and pg_restore operations.

  • Use continuous archiving and point-in-time recovery.

  • Manage roles, permissions, and row-level security.

  • Encrypt data at rest and in transit.

  • Audit database activities.

Advanced Administration

  • Automate maintenance tasks with cron and pgAgent.

  • Configure connection pooling with PgBouncer.

  • Tune PostgreSQL configuration parameters.

  • Use monitoring tools (pgAdmin, pgBadger, Prometheus).

  • Manage large datasets with table partitioning and clustering.

Capstone Projects

  • Design a multi-tenant SaaS database schema.

  • Implement a full-text search engine for product catalogs.

  • Build a geospatial application using PostGIS.

  • Setup a PostgreSQL cluster with replication and failover.

  • Create a real-time analytics dashboard with continuous aggregation.

 

✅ Tools & Platforms

  • PostgreSQL CLI, pgAdmin, DBeaver

  • PgBouncer, pgBadger, PostGIS

  • Linux/Unix shell scripting

  • Monitoring tools: Prometheus, Grafana

Download Syllabus 👇
Scroll to Top