Aurora PostgreSQL
Performance
Database SQL Tuning Framework for (Aurora) PostgreSQL
To provide a simple/initial
Objective framework to monitor and tune
SQL Queries on PostgreSQL,
with a focus on Aurora
PostgreSQL
Lifecycle
Identify
Identify slow
candidates
Monitor Tune
Create a performance Tune statements
baseline
Identifying SQL Queries for
Optimization
pg_stat_statem
Provides a means for tracking
ent planning and execution
statistics of all SQL statements
PG Extension executed by a server.
pg_stat_statement
● must be loaded by adding pg_stat_statements to
shared_preload_libraries in [Link]
pg_stat_statement
● Ex: Listing queries taking an avg time > 1 millisecond
SELECT
queryid,
query,
mean_exec_time,
calls
FROM
pg_stat_statements
WHERE
mean_exec_time > 1
ORDER BY
mean_exec_time DESC;
PostgreSQL
Logging Use the PG Logging to log
queries based on metrics or
samples.
PostgreSQL Logging
● Logs are stored in textual form;
● Stored in a subfolder of PGDATA unless specified;
● SQL Statements and Transactions can be logged:
○ A min duration threshold can be specified,
○ A sample rate can be specified.
● Main Log Parameters ([Link]):
○ logging_collector (boolean): enables the logging collector, which is
a background process that captures log messages sent to stderr and
redirects them into log files.
○ log_destination (string): a list of desired log destinations separated
by commas.
○ log_min_duration_statement (integer): each completed statement
to be logged if the statement ran for at least the specified amount of
time.
○ log_min_duration_sample (integer): Allows sampling the duration
of completed statements that ran for at least the specified amount of
time.
○
PostgreSQL Logging
Performance analyzer, built for
pgBadger speed with fully detailed
reports based on your
PG External Tool PostgreSQL log files.
pgBadger
● Is a Perl program that uses a javascript library (flotr2) to draw
graphs.
● It carefully reads and extracts information from PostgreSQL logs,
producing a web dashboard with a summary of all the
information it has found in the logs.
● logging_collector must be enabled
pgBadger
Tuning SQL Queries
Reading and
Understandin
g Execution
Plans An overview on the PostgreSQL
execution plan
Execution Stages
1. Parsing
a. Verify syntax
b. Disassemble the query into:
i. main part: The list of involved tables and columns
ii. the clauses: To filter data
iii. sorting
2. Rewriting
a. Apply any syntactic rules to rewrite the original SQL statement into
what will be effectively executed
3. Optimization
a. Find the fastest path to the data.
i. indexes;
ii. direct path;
iii. etc
4. Execution
a. Effectively goes to the storage and retrieve (or insert) the data using
the access method decided by the executor.
Execution Stages
EXPLAIN vs EXPLAIN ANALYZE
● EXPLAIN: allows you to see how PostgreSQL is going to execute a
specific query.
EXPLAIN vs EXPLAIN ANALYZE
● EXPLAIN ANALYZE: The ANALYZE mode of EXPLAIN enhances the
command by effectively running the query to explain.
The optimizer
The DBA can only interact with the database in the optimization phase, trying
to help PostgreSQL better understand the statement and optimize it correctly
whenever PostgreSQL is not doing an optimal job.
The optimizer uses the concept of cost:
Example An example of a badly
estimated cost
A bad cost optimization
Query Performance - Bad Cost Estimation
Query Performance - Bad Cost Estimation
Query Performance - Bad Cost Estimation
Useful tools
Explain-
postgresql A useful tool to troubleshoot
query performance
[Link]
[Link]/
Explain-postgresql