Description
This course will allow readers to get a better understanding of PostgreSQL. The course aims to present the readers with a solid knowledge of PostgreSQL building blocks, including the plpgsql language and how it can be used to build stored procedures and triggers. Advanced features like Common Table Expression and Window Functions will be presented, allowing the user to improve her SQL skills and know how to write better and more readable queries.
The reader will know how to manage and understand its database cluster thanks to glance at the PostgreSQL catalog and statistic collector. Last, readers will learn how to handle master-slave replication, a core feature of PostgreSQL.
Module 1
Stored procedures
The plpgsql language
The DO block
Glance at plperl
Triggers
DML Trigger Types
Implementing triggers with plpgsql
Cursors
Introduction of cursors
Example of usage of a cursor
Module 2
Users and Permission Management
Users, Groups and Roles
Allowing permissions and denying permissions
Row Level Security
Rules
Introduction to the Query Rewrite System
An example of rule
Views
Dynamic views
Materialized Views
Module 3
Common Table Expression
Introduction to CTEs
An example of move
Recursive CTEs
Window Functions
Introduction to Window Functions
A few useful window functions
Module 4
Indexes
Configuring the Server
Monitor the database activity
pg_stat_activity
pg_locks
Autovacuum
Module 5
Point in Time Recovery
Streaming Replication
Glance at Logical Replication
Instructor: Luca Ferrari
Luca lives in Italy with his beautiful wife, his great son and two female cats.
Computer science passionate since the Commodore 64 age, he holds a Master Degree and a PhD in Computer Science. He is a PostgreSQL enthusiast, and has adopted this such database back at version 7.3. He was a co-founder of the Italian PostgreSQL Users’ Group, being also a former president of the latter. He has written several articles about PostgreSQL and teaches at several events and training courses.
Luca is also a Perl lover, an Operating System passionate, a Unix fan and performs as much tasks as possible within Emacs. He considers the Open Source the only sane way of doing software and services. His web site is available at http://fluca1978.github.io
