| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic |
|---|---|---|---|---|---|---|---|
Query Store best practices |
This article describes best practices for Query Store in Azure Database for PostgreSQL - Flexible Server. |
markingmyname |
maghan |
04/27/2024 |
postgresql |
flexible-server |
conceptual |
[!INCLUDE applies-to-postgresql-flexible-server]
This article outlines best practices for using Query Store in Azure Database for PostgreSQL flexible server.
Let Query Store capture the data that matters to you.
| pg_qs.query_capture_mode | Scenario |
|---|---|
| All | Analyze your workload thoroughly in terms of all queries (top-level or nested) and their execution frequencies and other statistics. Identify new queries in your workload. Detect if ad hoc queries are used, to identify opportunities for user defined parameterization or automatic parameterization. All comes with an increased resource consumption cost. |
| Top | Focus your attention on top-level queries - those issued by clients. Doesn't include nested statements (statements executed inside a procedure or a function). |
| None | If set to None, Query Store won't capture any new queries. You've already captured a query set and time window that you want to investigate and you want to eliminate the distractions that other queries may introduce. None is suitable for testing and bench-marking environments. None should be used with caution as you might miss the opportunity to track and optimize important new queries. |
Note
pg_qs.query_capture_mode supersedes pgms_wait_sampling.query_capture_mode. If pg_qs.query_capture_mode is none, the pgms_wait_sampling.query_capture_mode setting has no effect.
The pg_qs.retention_period_in_days parameter specifies in days the data retention period for Query Store. Older query and statistics data is deleted. By default, Query Store is configured to retain the data for seven days. Avoid keeping historical data you don't plan to use. Increase the value if you need to keep data longer.
- Learn how to get or set parameters using the Azure portal or the Azure CLI.