PostgreSQL for Modern Architectures and AI-Driven Applications
Description
This course explores PostgreSQL 18 as a modern data platform for cloud-native, event-driven, and AI-enabled applications. Participants will move beyond traditional CRUD usage and learn how to use PostgreSQL for modern SQL analytics, JSON and semi-structured data, time-series workloads, message pipelines, full-text search, and AI embeddings.
The course also introduces the PostgreSQL ecosystem, including cloud-managed services and NewSQL/PostgreSQL-compatible distributed databases, helping participants choose the right PostgreSQL-based solution for scalable and resilient architectures. By the end of the course, learners will understand how to design and query PostgreSQL-backed systems that support real-time analytics, search, and AI workflows using native PostgreSQL features.
Indicative Duration: 8 training hours
*Duration is adjusted based on the final scope and the target audience.
Scope
| 1. PostgreSQL Ecosystem, Cloud & NewSQL Offerings | • PostgreSQL distributions • Cloud-managed PostgreSQL • NewSQL/PostgreSQL-based distributed databases • Key differences: managed vs self-hosted • High availability, Replication, Horizontal scaling • Choosing the right Postgres product for modern apps and AI workflows |
|
| 2. Modern PostgreSQL & SQL | • Modern SQL: CTEs • Recursive queries • Lateral joins • Window functions; JSON/JSONB storage & querying; JSON path queries; UPSERT / INSERT ON CONFLICT |
|
| 3. Time-Series | • Time Series Data • Time-series fundamental • Partitioning & Hypertables • Aggregations & sliding windows using CTEs; BRIN indexes for large sequential datasets |
|
| 4. Message Queues & Event Pipelines | • Message queues (LISTEN/NOTIFY, event tables) • Event sourcing & triggers Feeding pipelines with change data • pg_cron for scheduled tasks |
|
| 5. Full-Text Search & Indexing | • Full-text search (tsvector, tsquery, ranking) • Indexing strategies (GIN, GiST, covering indexes) • Generated/computed columns for search & analytics |
|
| 6. AI & Embeddings | • pgvector: storing • Indexing & querying embeddings • Semantic search + analytics app scenario; Integrating JSON • Vectors (pgvector) • Full-text search • Time-series • Events • Best practices: Indexing, Partitioning, Replication • Using generated columns for AI features |
|
Learning Objectives
Upon completion of the course participants will be able to:
- Design and deploy modern PostgreSQL architectures, selecting between managed, self-hosted, and distributed (NewSQL) solutions
- Implement advanced SQL features and indexing strategies to support scalable, high-performance applications
- Build time-series, event-driven, and full-text search capabilities using native PostgreSQL features
- Integrate vector embeddings (pgvector) to enable semantic search and AI-powered data workflows
- Apply replication, partitioning, and optimization best practices for production-ready systems
Target Audience
- Roles: Backend Engineers, Database Engineers, Data Engineers, Solution Architects, AI Engineers
- Seniority: Mid-Level to Senior Professionals
Prerequisite Knowledge
- Basic SQL
- Basic aggregates (COUNT, SUM, AVG)
- Relational database concepts (Tables, primary keys, foreign keys, indexes, transactions and ACID basics)
- Basic PostgreSQL familiarity
Recommended (but not mandatory):
- Backend development experience (e.g. familiarity with any backend language (Java, Python, Node.js, etc.)
- Understanding how applications interact with databases
- Basic cloud concepts (e.g. managed databases vs self-hosted, containers or cloud services)
- Data modeling basics, normalization vs denormalization, understanding of time-based data (logs, events, metrics)
Delivery Method
Sessions can be delivered via the following formats:
- Live Online – Interactive virtual sessions via video conferencing
- On-Site – At your organization’s premises
- In-Person – At Code.Hub’s training center
- Hybrid – A combination of online and in-person sessions
The training methodology combines presentations, live demonstrations, hands-on exercises and interactive discussions to ensure participants actively practice in realistic work scenarios.
