Description

This course will teach you the different techniques to optimize your PostgreSQL servers and database applications. You will see how to improve the performance of SQL queries as well as that of your server through better configuration.

Who is this training for ?

Database administrators and system administrators.

Training objectives

Training program

• Main parameters
  • The different optimization parameters (connections, memory.
  • ).
  • Exercise: Modifying the memory parameters and analyzing the result.
• Processing algorithms
  • The PostgreSQL engine.
  • Details of the different query processing mechanisms.
  • Exercise: Comparison of performance by applying different processing algorithms for the same request.
• Query algorithms
  • Query processing method (statistics.
  • ).
  • The different types of algorithm (join, LOOP.
  • ).
  • Exercise: Comparison of performance by applying different query algorithms.
• Memory optimization
  • Configuration of memory parameters (shared_buffers.
  • ).
  • Method for calculating the value of shared_buffers.
• Cache mechanisms and access performance
  • Disk cache for data files.
  • Transaction log cache.
  • Free space cache.
  • Cache for temporary objects.
  • Exercise: Modification of different caches, memory and behavior analysis.
• Performance through APIs and connectors
  • Use of APIs (Java, PHP.
  • ).
  • Use of connectors (example: TranQL).
  • Optimize the management of resources.
  • Organization of tables with CLUSTER.
  • Configuration of operating system kernel resources.
  • Data distribution.
  • Free space management.
  • PostgreSQL isolation level (READ COMMITED.
  • ).
  • Lock levels.
  • Lock method under PostgreSQL (record, table.
  • ).
  • Stack size.