Model Online / On-Site
Duration
Modules 5 modules

Course Objective & Scope

Many teams treat PostgreSQL as a basic CRUD store, overlooking its rich type system, specialised index families and expressive SQL engine. This course shows developers how to replace complex, chatty application-layer logic with fast, idiomatic PostgreSQL features.

Topics include PostgreSQL-native data types (UUID, Arrays, Ranges, JSONB), advanced querying (recursive CTEs, window functions, LATERAL joins, UPSERT, RETURNING), B-Tree/GIN/GiST/BRIN/partial and covering indexes, MVCC and transaction isolation, writing PL/pgSQL functions, procedures and triggers, declarative table partitioning, and reading EXPLAIN (ANALYZE, BUFFERS) plans.

Who Should Attend?

  • Backend and full-stack developers building applications backed by PostgreSQL
  • Data engineers and analysts writing analytical queries and data transformations
  • Development teams migrating to PostgreSQL from Oracle, SQL Server or MySQL
  • Prerequisite: basic familiarity with relational databases and SELECT/JOIN queries

Key Takeaways

  • Model schemas effectively using JSONB, Arrays, Range types and custom domains alongside relational tables
  • Express complex business logic in single round-trips with window functions, recursive CTEs, LATERAL joins and ON CONFLICT
  • Select the right index family (B-Tree, GIN, GiST, BRIN, partial, covering) for each access pattern
  • Work cleanly with MVCC, row-level locks (FOR UPDATE SKIP LOCKED) and isolation levels
  • Write maintainable PL/pgSQL routines and partition high-growth tables declaratively

Curriculum

PostgreSQL Data Types and Modern Schema Design
  • Identity columns (GENERATED AS IDENTITY vs UUID), time zones (timestamptz) and numeric types
  • PostgreSQL-specific types: Arrays, Ranges/Multiranges (tstzrange and non-overlapping EXCLUDE constraints)
  • JSON vs JSONB internals, JSONPath queries and hybrid relational/document schema design
Advanced SQL Querying Techniques
  • CTEs (WITH), MATERIALIZED hints and Recursive CTEs for hierarchical data
  • Window Functions (OVER, PARTITION BY, ROWS/RANGE frames) and the FILTER clause
  • Correlated top-N queries with LATERAL joins, INSERT ... ON CONFLICT (UPSERT) and RETURNING
Index Access Methods and Reading Execution Plans
  • How B-Tree, GIN (JSONB & Full-Text Search), GiST and BRIN indexes work under the hood
  • Composite column order, INCLUDE covering indexes (Index-Only Scans), partial and expression indexes
  • Reading Seq Scan, Index Scan and Bitmap Heap Scan nodes with EXPLAIN (ANALYZE, BUFFERS)
Transactions, MVCC and Concurrency Control
  • How Multi-Version Concurrency Control (MVCC) works: tuple visibility and update amplification
  • Read Committed, Repeatable Read and Serializable isolation levels and preventing race conditions
  • Row locking patterns (FOR UPDATE, NOWAIT, job queues with SKIP LOCKED) and deadlock prevention
PL/pgSQL Programming, Materialized Views and Partitioning
  • PL/pgSQL functions vs procedures, exception handling, SECURITY DEFINER and triggers
  • Accelerating read workloads with Materialized Views and REFRESH CONCURRENTLY
  • Declarative Table Partitioning (Range, List, Hash) and partition pruning rules

Other Courses in This Category

  • PostgreSQL Database Administration, High Availability and Operations

    PostgreSQL architecture, memory and WAL tuning, MVCC/Autovacuum and bloat control, PgBouncer connection pooling, PITR backups with pgBackRest, Streaming/Logical Replication and Patroni high availability.

    Databases Corporate
    Live Course
    5 modules
    View
  • MySQL and MariaDB Development, Tuning and Administration

    InnoDB storage engine and clustered index architecture, query and index optimisation, transaction and lock analysis, backups, GTID replication and high availability — for developer and DBA teams running MySQL and MariaDB.

    Databases Corporate
    Live Course
    5 modules
    View
  • Oracle Database Architecture, SQL Tuning and Administration

    Oracle Multitenant (CDB/PDB) architecture, SGA/PGA memory and background processes, tablespace/segment management, B-Tree/Bitmap indexes, partitioning, AWR/ASH tuning, RMAN backups and Data Guard essentials.

    Databases Corporate
    Live Course
    5 modules
    View
  • MongoDB Document Data Modelling, Aggregation and Operations

    BSON and document schema design patterns (embedding vs referencing), the Aggregation Pipeline, compound/multikey indexes and the ESR rule, multi-document ACID transactions, Change Streams, Replica Sets and Sharding.

    Databases Corporate
    Live Course
    5 modules
    View
  • In-Memory Data Structures, Caching and Messaging with Redis

    Redis data structures (Strings, Hashes, Lists, Sets, Sorted Sets, Streams, HyperLogLog), caching patterns (cache-aside, stampede protection), distributed locks, rate limiting, Lua/pipelining, RDB/AOF persistence and Redis Cluster.

    Databases Corporate
    Live Course
    5 modules
    View
  • Distributed NoSQL Databases with Apache Cassandra

    Masterless ring architecture, consistent hashing, tunable consistency (LOCAL_QUORUM), LSM-Tree/SSTable storage internals, query-driven CQL data modelling and cluster operations — for high-write distributed systems.

    Databases Corporate
    Live Course
    5 modules
    View

Get in touch