Skip to content

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.

Advanced PostgreSQL

Course Summary

The Advanced PostgreSQL course offers a challenging experience that explores high availability and replication strategies, allowing you to build resilient database systems. Participants will explore PostgreSQL internals, learning how the database manages and executes queries efficiently. The course will also cover security best practices, advanced administration, and monitoring techniques to ensure PostgreSQL databases are not only performant but also secure and well-maintained. This course is the gateway to becoming a PostgreSQL expert and managing complex database environments with confidence.

Purpose
Manage complex database environments with PostgreSQL while ensuring security and well-maintained applications.
Audience
Experienced PostgreSQL users and database administrators
Role
Developers | Business Analysts | DB Administrators
Skill level
Advanced
Style
Lecture | Hands-on Activities
Duration
3 days
Related technologies
SQL

 

Productivity objectives
  • Explain high availability and its importance
  • Apply advanced querying techniques
  • Employ monitoring, security, and advanced administration

What you'll learn:

In this course, you'll learn:
  • Understanding high availability and its importance
    • Exploring different HA architectures and their benefits
    • Identifying potential points of failure in PostgreSQL setups
  • Setting up replication in PostgreSQL
    • Streaming replication for continuous data synchronization
      • Configuring and managing replication slots
      • Monitoring replication lag and performance
    • Logical replication for selective data syncing
      • Creating publication and subscription objects
      • Handling conflicts in logical replication
  • Recursive Queries for Hierarchical Data
    • Understanding recursive queries with recursive CTEs
    • Managing hierarchical data structures in PostgreSQL
  • Common Table Expressions (CTEs) and Window Functions
    • Using CTEs for simplifying complex queries
    • Leveraging window functions for advanced analytical queries
      • ROW_NUMBER(), RANK(), DENSE_RANK(), etc.
  • Utilizing Advanced Indexing Techniques
    • Multi-column indexes and their advantages
    • Partial and covering indexes for specific use cases
  • PostgreSQL Internals and Query Optimization
    • Understanding query planning and execution process in PostgreSQL
    • Analyzing query plans using EXPLAIN and EXPLAIN ANALYZE
    • Using pg_stat_statements for query performance analysis
  • Performance Tuning and Optimization
    • Identifying and resolving performance bottlenecks
    • Query optimization techniques (indexing, denormalization, etc.)
    • Optimizing configuration parameters for specific workloads
  • Monitoring and Alerting
    • Implementing proactive monitoring with tools like pg_stat_monitor
    • Setting up alerts for critical events and performance metrics
  • Security and Advanced Administration
    • Managing database roles, permissions, and access control
    • Implementing Row-Level Security (RLS) for fine-grained access control
    • Enabling SSL/TLS for secure connections and data encryption
    • Managing certificates and encryption keys
  • Backup and Recovery Strategies
    • Implementing regular backups with pg_dump and pg_dumpall
    • Configuring continuous archiving and Point-in-Time Recovery (PITR)
    • Performing backups in parallel for faster restores
  • Exploring PostgreSQL Internals for Advanced Administration
    • Understanding system catalogs and their importance
    • Performing advanced administrative tasks using internal mechanisms

Advanced PostgreSQL

real world content

Real-World Content

Project-focused demos and labs using your tool stack and environment, not some canned "training room" lab.
expert practitioners

Expert Practitioners

Industry experts that bring their battle scars into the classroom.
experiential learning

Experiential Learning

More coding than lecture, coupled with architectural and design discussions.
fully customized

Tailored Outlines

Once-size-fits-all doesn't apply to training teams. That's where we come in!

Dive in and learn more

When transforming your workforce, it’s important to have expert advice and tailored solutions. We can help. Tell us your unique needs and we'll explore ways to address them.

Let's chat

By filling out this form and clicking submit, you acknowledge our privacy policy.