Category: PostgreSQL
Ability to write efficient SQL queries is the area of the intersection of Science and Art. Every single query is different and correct diagnosing the reasons of its long creation time requires the complex understanding of the system functioning. During the training Queries and applications optimization in PostgreSQL we focus on the full use of SQL language possibilities for creating complex reports. The course draws attention to the PostgreSQL system specifics, diagnostic tools and functionalities offered by the base, which allow to get the queries results faster.
The training is recommended to people, who work with PostgreSQL database and who want to learn how to recognise bottlenecks in communication with the database, isolate performance problems and improve their applications performance.
Our instructors have many years of experience with PostgreSQL, both as administrators and as programmers. One of our trainers is a PostgreSQL contributor and a regular speaker at the world’s largest conference on the topic.
Duration
2 days
Agenda
- Queries optimization
- Types and use of indexes
- btree indexes
- GIN and GiST indexes
- Partial and functional indexes
- SQL executor implementation
- Disk data access
- Creating JOINs
- Reading EXPLAIN command
- Optimizer estimates
- Statistics subsystem tuning
- Functions costs adjustment
- Inefficient SQL constructions and their alternatives
- _pg_locks_table reading, deadlocks
- Types and use of indexes
- Optimization of the database configuration
- Key parameters influencing performance
- Performance and reliability balancing
- Performance problems diagnosing
- Optimizing cooperation with the base
- Prepared statements, PREPARE construction
- Importing bulk data
- COPY command
- pgloader program
- Using connection pooling pgbouncer
Audience and prerequisites
We recommend that the participants have ability to create PostgreSQL queries at least at intermediate level.
Certificates
Course participants receive completion certificates signed by ALX.