1---
2stage: Enablement
3group: Database
4info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#assignments
5---
6
7# Query performance guidelines
8
9This document describes various guidelines to follow when optimizing SQL queries.
10
11When you are optimizing your SQL queries, there are two dimensions to pay attention to:
12
131. The query execution time. This is paramount as it reflects how the user experiences GitLab.
141. The query plan. Optimizing the query plan is important in allowing queries to independently scale over time. Realizing that an index will keep a query performing well as the table grows before the query degrades is an example of why we analyze these plans.
15
16## Timing guidelines for queries
17
18| Query Type | Maximum Query Time | Notes |
19|----|----|---|
20| General queries | `100ms` | This is not a hard limit, but if a query is getting above it, it is important to spend time understanding why it can or cannot be optimized. |
21| Queries in a migration | `100ms` | This is different than the total [migration time](migration_style_guide.md#how-long-a-migration-should-take). |
22| Concurrent operations in a migration | `5min` | Concurrent operations do not block the database, but they block the GitLab update. This includes operations such as `add_concurrent_index` and `add_concurrent_foreign_key`. |
23| Background migrations | `1s` |  |
24| Service Ping | `1s` | See the [Service Ping docs](service_ping/implement.md) for more details. |
25
26- When analyzing your query's performance, pay attention to if the time you are seeing is on a [cold or warm cache](#cold-and-warm-cache). These guidelines apply for both cache types.
27- When working with batched queries, change the range and batch size to see how it effects the query timing and caching.
28- If an existing query is not performing well, make an effort to improve it. If it is too complex or would stall development, create a follow-up so it can be addressed in a timely manner. You can always ask the database reviewer or maintainer for help and guidance.
29
30## Cold and warm cache
31
32When evaluating query performance it is important to understand the difference between
33cold and warm cached queries.
34
35The first time a query is made, it is made on a "cold cache". Meaning it needs
36to read from disk. If you run the query again, the data can be read from the
37cache, or what PostgreSQL calls shared buffers. This is the "warm cache" query.
38
39When analyzing an [`EXPLAIN` plan](understanding_explain_plans.md), you can see
40the difference not only in the timing, but by looking at the output for `Buffers`
41by running your explain with `EXPLAIN(analyze, buffers)`. [Database Lab](understanding_explain_plans.md#database-lab-engine)
42will automatically include these options.
43
44If you are making a warm cache query, you will only see the `shared hits`.
45
46For example in #database-lab:
47
48```plaintext
49Shared buffers:
50  - hits: 36467 (~284.90 MiB) from the buffer pool
51  - reads: 0 from the OS file cache, including disk I/O
52```
53
54Or in the explain plan from `psql`:
55
56```sql
57Buffers: shared hit=7323
58```
59
60If the cache is cold, you will also see `reads`.
61
62In #database-lab:
63
64```plaintext
65Shared buffers:
66  - hits: 17204 (~134.40 MiB) from the buffer pool
67  - reads: 15229 (~119.00 MiB) from the OS file cache, including disk I/O
68```
69
70In `psql`:
71
72```sql
73Buffers: shared hit=7202 read=121
74```
75