Performance Tuning

Most performance tuning of PostgreSQL breaks down roughly along two lines: query processing and system tuning. SQL is declarative, so the code coming from the application tells the database what to compute but not how to compute it. Efficient query processing requires the database to choose the right algorithmic approach to solving your query problem. System tuning, on the other hand, occurs below the query processing layer: It involves deciding how to allocate resources such as memory between cache and buffers, setting the interval for flushing I/O, and other parameters.

Query processing

Various forms of inefficient processing can lead to performance problems in PostgreSQL. DBAs routinely search for offending queries by looking for transactions that run slowly or require significant levels of resources such as CPU time or I/O operations. They may review the query plans, attempting to adjust queries to find more efficient alternatives. They might try adding hints or by rewriting the query in some way. In some cases, more efficient processing requires adding indexes. It could also require removing them, if the keeping indexes updated outweighs their benefit. Database optimization is a global optimization problem since some changes may benefit multiple types of transactions or may improve some while degrading others.

AutoDBA has some performance tuning capabilities today and many more are on the roadmap. These optimizations can often be applied without changing the SQL query, e.g., by executing a more efficient query plan. Unlike other systems that make recommendations, AutoDBA uses feedback to apply improvements safely and automatically. It does not try an alternate query plan unless its model predicts an improvement, and it does not keep it running unless that improvement is realized.

System parameters

Hardware provisioning alone is not enough to make resources available to the database. PostgreSQL’s system parameters dictate how the database uses memory for caching and buffering and how it manages I/O. Optimal settings for these parameters are workload-dependent, and DBAs generally experiment to find the best configuration.

AutoDBA incorporates a system model of PostgreSQL that predicts how the workload will perform under various system parameter values. Using this model for guidance, it makes gradual adjustments and validates that changes in performance correspond to its predictions. Over time, AutoDBA’s system model improves, allowing it to identify optimal system parameter configurations more quickly and accurately.

Outliers are important in system performance: A system can have good mean latency, but if 1% of transactions are slow, customer experience can suffer. AutoDBA models, tracks, and optimizes for outlier performance, not only average performance.