PostgreSQL developer Online training

 

Module 1: Foundations & Setup (The “Why” and “How”)

Objective: Understand PostgreSQL’s place in the database world and set up a working environment.

• 1.1 Introduction to PostgreSQL
  • What is PostgreSQL? History and key features.
  • PostgreSQL vs. Other SQL and NoSQL databases (brief comparison).
  • Use cases and community strength.
• 1.2 Installation & Configuration (Overview)
  • Installation methods (Package managers, Docker, cloud providers like AWS RDS/Aurora, Google Cloud SQL).
  • Core configuration files (postgresql.conf, pg_hba.conf) – what developers need to know.
• 1.3 Connecting to PostgreSQL
  • The psql command-line tool: Your best friend.
  • Basic psql commands (\l, \c, \dt, \d+, \?, \e).
  • Connecting from GUI tools (pgAdmin, DBeaver) – optional demo.

---

Module 2: SQL Core for PostgreSQL Developers

Objective: Master the essential SQL needed for application development, with PostgreSQL-specific flavors.

• 2.1 Database & Schema Operations
  • CREATE DATABASE, CREATE SCHEMA.
  • Understanding the search_path.
• 2.2 Data Definition Language (DDL) Deep Dive
  • CREATE TABLE: Data types (beyond standard: SERIAL, UUID, JSONB, arrays, TIMESTAMPTZ).
  • Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL.
  • ALTER TABLE: Adding/dropping columns, changing types.
  • DROP TABLE vs. TRUNCATE TABLE.
• 2.3 Data Manipulation Language (DML) Mastery
  • INSERT, UPDATE, DELETE statements.
  • RETURNING clause: The PostgreSQL superpower for developers.
  • UPSERT (INSERT ... ON CONFLICT ... DO UPDATE): Handle conflicts gracefully.
• 2.4 Querying Data (DQL)
  • SELECT: Filtering (WHERE), sorting (ORDER BY), limiting (LIMIT/OFFSET).
  • Joins: INNER JOIN, LEFT/RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN.
  • Aggregation and Grouping: GROUP BY, HAVING, aggregate functions (COUNT, SUM, STRING_AGG).
  • Window Functions: ROW_NUMBER(), RANK(), LAG()/LEAD() for advanced analytics without sub-queries.
• 2.5 Common Table Expressions (CTEs)
  • Non-recursive CTEs for organizing complex queries.
  • Recursive CTEs for hierarchical data (e.g., tree structures, org charts).

---

Module 3: PostgreSQL-Specific Advanced Features

Objective: Leverage features that make PostgreSQL unique and powerful.

• 3.1 Working with JSON/JSONB
  • JSON vs. JSONB: When to use which.
  • Querying JSONB data: ->, ->>, #>> operators.
  • Indexing JSONB (GIN indexes) for performance.
  • Converting relational data to JSON (json_agg, row_to_json).
• 3.2 Full-Text Search (FTS)
  • Introduction to FTS: Beating LIKE and ILIKE.
  • Building tsvector and writing tsquery.
  • Creating full-text search indexes.
• 3.3 Geospatial with PostGIS (Overview)
  • Introduction to the PostGIS extension.
  • Basic geometric types and queries (distance, intersection).
  • (Note: This could be a full course itself; here it’s an awareness module).

---

Module 4: Programming inside the Database

Objective: Move logic closer to the data for performance and consistency.

• 4.1 Views & Materialized Views
  • Creating views to simplify complex queries.
  • Materialized Views for pre-computed, cached results (and REFRESH).
• 4.2 Functions (Stored Procedures)
  • Creating user-defined functions.
  • Language choices: SQL, PL/pgSQL (procedural), and others (PL/Python, etc.).
  • Writing basic PL/pgSQL functions: structure, variables, conditionals, loops.
• 4.3 Triggers
  • What are triggers and when to use them (e.g., audit logs, data validation).
  • Creating trigger functions in PL/pgSQL.
  • BEFORE vs. AFTER triggers. FOR EACH ROW vs. FOR EACH STATEMENT.

---

Module 5: Performance & Optimization

Objective: Write efficient queries and understand how to diagnose bottlenecks.

• 5.1 The Query Planner & EXPLAIN
  • How PostgreSQL executes a query.
  • Using EXPLAIN, EXPLAIN ANALYZE, BUFFERS to read query plans.
  • Identifying sequential scans, costly nodes, and bottlenecks.
• 5.2 Indexing Strategies
  • How indexes work (B-Tree fundamentals).
  • Types of indexes: B-Tree, GIN, GiST, BRIN, Hash (and when to use them).
  • Multi-column indexes and index-only scans.
  • The impact of indexes on INSERT/UPDATE/DELETE.
• 5.3 Concurrency and Locking
  • MVCC (Multi-Version Concurrency Control): How PostgreSQL avoids locks.
  • Common lock types and how to identify locking issues (pg_locks).
  • Handling race conditions and deadlocks.

---

Module 6: Application Development Patterns

Objective: Apply PostgreSQL knowledge in real-world application contexts.

• 6.1 Connecting from Applications
  • Using connection pools (e.g., PgBouncer) vs. application-level pools.
  • Best practices for managing connections in your app.
• 6.2 Managing Schema Changes (Migrations)
  • The importance of migration tools.
  • Overview of tools like Flyway, Liquibase, Sqitch.
  • Writing safe, reversible migrations.
• 6.3 Security Fundamentals
  • Roles, Users, and Groups.
  • Granting and revoking privileges (GRANT, REVOKE) at a granular level.
  • SQL Injection and how to prevent it (using parameterized queries).

---

Module 7: Beyond the Basics & Next Steps

Objective: Glimpse into advanced topics and the broader ecosystem.

• 7.1 Logical & Streaming Replication
  • Overview for high availability and read scalability.
  • Publication and Subscription.
• 7.2 Monitoring & Logging
  • Key metrics to watch (e.g., from pg_stat_database, pg_stat_statements).
  • Configuring logs for useful developer insight.
• 7.3 Course Recap & Best Practices Summary
• 7.4 Resources for Further Learning
  • Official PostgreSQL documentation.
  • Key blogs, books, and communities.