fr
en
PostgreSQL advanced administration and tuning
Database slowdowns affect applications and their users. With PostgreSQL, structure performance analysis and examine tuning options. Develop a diagnostic approach to substantiate your choices and verify their effects.
- Duration
- 4 days 28 hours
- Code
- POS02FR Code
Presentation
This course helps participants operate a PostgreSQL database by exploring advanced database administration concepts in detail.
You will master the techniques needed to achieve optimal PostgreSQL database performance.
Objectives
This course deepens and enhances your PostgreSQL database knowledge through the following topics:
- Advanced postgresql.conf and pg_hba.conf settings.
- Advanced transaction management and session management.
- Server crashes.
- Unix/Linux system tuning and postgresql.conf tuning settings.
- Advanced statistics management and performance issues: vacuum, autovacuum and reindexing.
- Lock management and query execution plans.
Program
Advanced postgresql.conf settings
- Memory settings.
- Logging settings.
- Locales, collations and character sets.
- Connection settings.
- Archiving settings.
- WAL settings.
Advanced pg_hba.conf settings
PostgreSQL authentication.
- File structure.
- Options.
- Applying changes.
Advanced transaction management
- xmin and xmax.
- Checkpoints.
User session management
- Stopping a query.
- Terminating a session.
- Session settings.
Practical case: a server crash and loss of a PostgreSQL cluster
- Loss of a table.
- Loss of a schema.
- Loss of the entire cluster.
- PITR recovery.
Unix/Linux system tuning
Linux system concepts for preparing PostgreSQL engine installation and configuration.
- Shared memory configuration.
- Semaphore configuration.
- Memory overcommit.
- The sysctl tool.
postgresql.conf tuning settings
Configuring the PostgreSQL engine to improve query performance.
- Memory settings.
- Connection settings.
- Archiving settings.
- WAL settings.
- Optimiser settings.
Advanced statistics management
Understanding the optimiser and statistics.
- Table-level statistics.
- Internal statistics.
Performance issues: vacuum, autovacuum and reindexing
Configuring vacuum for very large tables.
- Vacuum.
- Autovacuum.
- Reindexing.
Lock management
Understanding and responding to locked objects.
- Lock levels.
- Identifying locks.
- Transaction isolation.
- Explicit locking.
Query execution plans
Becoming familiar with the statistics-based optimiser and query issues.
- Planner.
- EXPLAIN.
Audience
This course is suitable for individuals who are comfortable in a PostgreSQL environment and already have significant experience with the engine.
Prerequisites
Operational knowledge of PostgreSQL or completion of the PostgreSQL administration course.
Teaching and assessment methods
- Initial skills assessment
- Training materials provided to participants
- Continuous assessment throughout the course
- End-of-course feedback questionnaire
- Combination of theory and practical application
- Attendance records
- Post-course follow-up evaluation
Dates and sessions
Choose the date and delivery format that suit you.
No upcoming sessions are currently available.