Contact:
Hans-Jürgen Schönig, CEO
+43 (0)664 3933 974Contact us now!
Hans-Jürgen Schönig, CEO
+43 (0)664 3933 974Contact us now!Advanced PostgreSQL optimization
| Duration: | 3 Days |
| Level: | Professional |
| Price: | contact us today |
| Audience: | This training course has been designed for people who want to gain in-depth knowledge about PostgreSQL. We will deal with advanced topics related to performance tuning and optimization Knowledge of SQL is necessary to participate in this training. |
| Date: | 18th — 20th June 2012 |
Content:
PostgreSQL storage
- On-disk storage
- Heaps and disk layout
- xlog: Functionality and layout
- clog: The PostgreSQL commit log
- Additional storage areas
- Persistence
- Tablespaces
- Optimizing read and write access
Memory management
- Caching
- 2Q clocksweep vs LRU
- Practical implications of caching
- Sorting and aggregation
- Temporary tables
- Memory contexts
Indexing
- PostgreSQL index types
- Internal index organization
- Partial indexes
- Functional indexing
- Full text search
- Fuzzy matching
Execution plans
- Types of SQL optimization
- Exhaustive search
- Genetic optimization
- Rule based optimization
- Using EXPLAIN
- Interpretation of EXPLAIN output
- The PostgreSQL cost model
- ANALYZE
- Reading pg_stats
Internal optimization
- Views and subselects
- Equality constraints
- Optimization of aggregates
- HashAggregates vs. GroupAggregates
- DISTINCT vs. GROUP BY
- Optimization of IN-statements
- Optimization of ORDER BY-statements
- Join order and outer joins
- Optimizing set-operations
- Optimizing procedures and SQL functions
Genetic optimization
- The limits of optimization
- GEQO
Transactions and locking
- Transaction isolation
- Optimizing locking
Partitioning
- Efficient cleanup
- Constraint exclusion