A PostgreSQL powered database environment will help you increasing the profitability of your business and will help you to achieve your business goals.

Advanced PostgreSQL Optimization

The training course is dedicated to advanced developers who want to have more insight into the internals of PostgreSQL. Performance tuning will be covered and deep-inside knowledge will be provided.

Required knowledgee: Advanced SQL skills

Duration: 3 days

Content:
Storage concepts
On-disk storage areas
On-disk layout of heaps
XLog: The transaction log
CLog: The commit log
Additional storage areas
Persistent storage
Tablespaces
Optimizing reads and writes

Memory management
Caching
The internal caching algorithm
Database cache vs. kernel cache
ClockSweep vs. LRU vs. ARC
Practical consequences
Sorts and aggregates
Temporary tables
Memory contexts

Using indexes
PostgreSQL index types
The internal architecture of indexes
Partial indexes
Indexes on functions
Indexes on regular expressions
Fuzzy matching

Execution plans
Types of SQL optimization
Exhaustive search
Genetic algorithms
Other techniques in brief
Rule based optimization
Dynamic sampling
Greedy Nearest Neighbor algorithm
Hill-Climbing
Using explain
Understanding the output of explain
The PostgreSQL cost system
ANALYZE
Reading and understanding pg_stats
EXPLAIN ANALYZE

Internal optimization techniques
Views and subselects
Equality constraints
Optimization of Aggregates
HashAggregates vs. GroupAggregates
DISTINCT vs. GROUP BY
Optimizing IN-statements
Optimizing ORDER BY statements
Tweaking join orders
Processing outer joins
Optimizing set operations
Optimizing procedures and functions

Genetic query optimizationg
The limits of exhaustive searches
Using geqo efficiently
Avoiding geqo

Transactions and locking
Transaction Isolation
Optimizing lock behavior
Optimistic vs. pessimistic locking
FOR SHARE vs. FOR UPDATE

Partitioning
Constraint Exclusion
Efficient cleanup

<< back