Concept of execution plan
– Estimated plan and real plan
– Optimizer parameters
Visualization of the execution plan
– EXPLAIN command
– Estimated costs and real costs
Execution phases of a query
– Browsing of tables
– Sorting
– Joins
– Aggregates
– Functions

Workshop: Understand the execution plan of a query with a view to its optimization

The SQL / MED standard
Notion of Foreign Data Wrapper
– Remote server
– User correspondence
– Foreign table
– Import of schemas
Notion of pushdown of requests

Workshop: Access to a remote PostgreSQL server

Checkpoints
– Concept of checkpoints
– Bgwriter process – Checkpoint
statistics
– Logging of checkpoints
– Configuration of checkpoint activity
– Linux kernel and checkpoints
Transaction logs
– Principle of operation
– Unit size, minimum and maximum space
– Archiving of transaction

Workshop: Optimized configuration of checkpoints and transaction logs

Presentation of declarative partitioning
Types of partitioning
Managing a partitioned table
Restrictions, joins, aggregates and partitions
Limitations according to versions

Lab: Implement a partitioned table

Presentation of the types of replications
Global principle of replication
Data
retention – Retention of transaction logs
– Infrastructure of replication slots
Physical replication
– Log-Shipping and Streaming Replication
– Synchronization of instances: pg_basebackup
Logical replication

Problem
Choice of Pg-Pool II (Linux)
– Presentation
– Features
Installation of Pg-Pool II
Configuration

Workshop: Implementing a load balancing cluster

Passing of the certification (if foreseen in the financing)