Cloud SQL Insights, in depth

How Query Insights sees your workload

Query Insights runs inside the managed instance and collects statistics for every query that runs. The collection is cheap because it aggregates rather than logging every statement. Three ideas are central.

Normalization. Literals are replaced with placeholders, so SELECT * FROM orders WHERE customer_id = 42 and the same query with = 77 count as one query shape. This is what makes a top-N list meaningful: ten thousand distinct literal values collapse into one row that shows total time, call count and average latency. The cost is that you lose the specific values, so a slow outlier caused by one huge customer looks like a slightly worse average.

Dimensions. Every sample is attributed to a database, a user, optionally a client address and optionally a set of application tags. The database load chart can be filtered and grouped by any of these, so you can see that 70 percent of load comes from the reporting user, or from one client IP, before you look at any SQL text.

Sampled plans. For a sample of executions, Insights records the execution plan, showing the operators chosen, rows produced at each step and where time went. You do not need to reproduce the query with EXPLAIN ANALYZE on production to see why it is slow; the plan from the slow period is already stored.

Enabling it and sizing the settings

Insights is configured per instance and may not be enabled on yours. You can enable it in the console under the instance's Query Insights page, or with gcloud:

gcloud sql instances patch orders-pg \
  --insights-config-query-insights-enabled \
  --insights-config-record-application-tags \
  --insights-config-record-client-address \
  --insights-config-query-string-length=2048 \
  --insights-config-query-plans-per-minute=5

Each flag maps to a trade-off, and the edition of the instance changes the allowed range. The figures below are from the PostgreSQL documentation:

Enterprise Plus adds features on top of the basic views. Google's documentation lists wait event analysis, the ability to terminate a session or long-running transaction from the active queries view, index advisor recommendations and an AI-assisted troubleshooting preview. Treat these as edition-specific and check that your edition and engine support them before you plan a runbook around them.

A practical starting point: raise the query string length on Enterprise to 2,048 or 4,500 if your ORM generates long column lists, because a truncated query that cuts off before the WHERE clause is very hard to identify. Leave plan sampling at the default until you are chasing a specific intermittent problem.

Tagging queries with sqlcommenter

Without tags, the top-queries list tells you that a normalized SELECT ... FROM line_items JOIN products ... is slow. With tags, it tells you the query came from the checkout controller in the orders service on route /api/cart/{id}. That turns a database investigation into a code review.

Insights reads tags in the open-source sqlcommenter format: a SQL comment appended to the statement, holding key/value pairs with URL-encoded values in single quotes. The documented keys are action, controller, framework, route, application and db_driver. sqlcommenter libraries exist for several frameworks and add the comment automatically. If yours is not covered, the format is simple enough to emit yourself:

from urllib.parse import quote

def sqlcomment(**tags):
    # sqlcommenter format: keys sorted, values URL-encoded and single-quoted
    parts = [f"{k}='{quote(str(v), safe='')}'" for k, v in sorted(tags.items())]
    return "/*" + ",".join(parts) + "*/"

def tagged(sql, **tags):
    # psycopg uses %-style placeholders, so escape the %-encoded tag values
    return f"{sql.rstrip().rstrip(';')} {sqlcomment(**tags).replace('%', '%%')}"

q = tagged(
    "SELECT li.sku, li.qty, p.price FROM line_items li "
    "JOIN products p ON p.sku = li.sku WHERE li.cart_id = %s",
    application="orders", controller="checkout",
    route="/api/cart/{id}", framework="flask", db_driver="psycopg",
)
cur.execute(q, (cart_id,))
# ... WHERE li.cart_id = %s /*application='orders',controller='checkout',...*/

Two rules keep tags useful. First, tag values must be low-cardinality: use the route template, never the concrete URL with an ID in it, or every request becomes its own tag value and the tag view becomes noise. Second, put the comment at the end of the statement, after the SQL. Some poolers and proxies strip leading comments, and a comment in the middle of a query can interfere with prepared-statement caching in some drivers.

A repeatable diagnosis workflow

Insights rewards a fixed sequence: go from the broad view to the narrow one, and do not open a query plan until you know it is the right query.

1. Database load chart. Pick the time window of the incident. Load is shown as time spent, broken down by CPU and waiting. Group by database, then by user, then by client address. You are looking for the dimension whose share changed, not the one that is largest in absolute terms.
2. Top queries. Sort by total time for the window, not by average latency. A query averaging 3 ms called 40,000 times a minute costs more than a 2-second report that runs once an hour. Compare against the same window a day earlier: a new query near the top is usually a deploy, while a familiar query that got slower is usually data growth or a plan change.
3. Top tags. If tags are on, switch to the tag view and see which route or controller owns the extra load. This often gives you the answer before you read SQL.
4. Query details. For the suspect query, read latency over time, rows returned versus rows scanned, and call count. A rise in rows scanned with flat rows returned points to a lost index path.
5. Sampled plans. Open plans from before and after the change. Look for a sequential scan replacing an index scan, a nested loop over a much larger outer input, or a sort spilling to disk.
6. Confirm outside Insights. Check with pg_stat_statements, EXPLAIN on a replica or clone, and the application's own traces. Insights is sampled, so use it to choose the hypothesis and other tools to prove it.

Worked example: a regression after a deploy

Here is a realistic regression. An order service deploys at 14:00. By 14:20, CPU on the primary is at 85 percent and p95 checkout latency has risen from 120 ms to 900 ms. Nothing is down, and the database logs show no errors.

The load chart shows total load roughly doubled, almost all of it CPU, all from user orders_app. In top queries for 14:00 to 14:30, the leader by total time is a normalized query that was fourth the day before: 31 percent of load, average latency up from 4 ms to 38 ms, calls unchanged. The tag view attributes it to controller='checkout', route='/api/cart/{id}'. Query details show rows returned flat at about 12 per call, but the sampled plan after 14:00 shows a sequential scan on line_items filtered on cart_id, where the earlier plan used an index scan.

The cause turns out to be a migration in the deploy that rebuilt the line_items table and recreated its indexes on the old column order: (sku, cart_id) instead of (cart_id, sku). With a high-cardinality leading column, the planner will not use that index efficiently for a filter on the second column alone, so it scans. The fix is an index that leads with the filter column, built without blocking writes:

-- Build without taking a write-blocking lock; slower, safe on a live table
CREATE INDEX CONCURRENTLY IF NOT EXISTS line_items_cart_id_sku
    ON line_items (cart_id, sku);

-- A failed CONCURRENTLY build leaves an INVALID index behind; check:
SELECT indexrelid::regclass, indisvalid
FROM pg_index WHERE indrelid = 'line_items'::regclass;

ANALYZE line_items;

Within minutes of the index becoming valid, the query returns to its old average and CPU drops. Sorting by total time and comparing plans found the cause without reproducing anything.

Metrics, alerts and long-term history

The dashboard is for investigation; alerts need metrics. Insights publishes metrics to Cloud Monitoring, and for PostgreSQL their type strings start with cloudsql.googleapis.com/database/postgresql/insights. One example is the per-query latency distribution, database/postgresql/insights/perquery/latencies. You can browse the full list in Metrics Explorer by filtering on that prefix, which is better than guessing names.

A small script to pull the series for a dashboard, an SLO report or long-term storage might look like this. It uses the standard Monitoring client and filters only on the metric type, then prints whatever labels the series carries, so it does not assume label names:

import time
from google.cloud import monitoring_v3

PROJECT = "projects/my-project"
METRIC = "cloudsql.googleapis.com/database/postgresql/insights/perquery/latencies"

client = monitoring_v3.MetricServiceClient()
now = int(time.time())
interval = monitoring_v3.TimeInterval(
    {"end_time": {"seconds": now}, "start_time": {"seconds": now - 3600}}
)
series = client.list_time_series(
    request={
        "name": PROJECT,
        "filter": f'metric.type = "{METRIC}"',
        "interval": interval,
        "view": monitoring_v3.ListTimeSeriesRequest.TimeSeriesView.FULL,
    }
)
for ts in series:
    print(dict(ts.metric.labels), dict(ts.resource.labels), len(ts.points))

Retention inside Insights is 7 or 30 days depending on edition. If you want quarter-over-quarter baselines ("is checkout slower than it was before Black Friday?"), export the series somewhere durable, such as BigQuery, on a schedule. Alerting on overall instance CPU plus a per-query latency threshold for your top three critical query shapes is a reasonable first alert set. For the broader alerting model, see Cloud Monitoring.

Failure modes and blind spots

Insights is valuable because it is always on and cheap, but each design choice has a matching blind spot.

• Truncation. ORM queries with long select lists can exceed the configured length and be cut off before the predicate. Two different queries can then look identical. Raise the length, or tag them.
• Normalization hides skew. One tenant with 50 million rows and ten thousand tenants with 100 rows share one query shape. The average looks fine while one customer times out. Use the application's traces with tenant IDs for this kind of problem.
• Sampling misses rare events. A plan that goes bad once an hour may not be in the sampled plans at the default rate. Temporarily raise the plans-per-minute setting while you investigate, then lower it again.
• Distinct-query limits. The documentation describes higher cardinality limits on Enterprise Plus than on Enterprise. Workloads that generate many unique query shapes, such as dynamically built SQL or IN-lists of varying length, can push less frequent queries out of the tracked set.
• Not a log. Insights does not tell you which specific execution failed or which literal value it used. For forensic questions, use database logs or auto_explain with care.
• Privacy. Client addresses and query text can be personal or sensitive data. Decide deliberately whether to record client addresses, and avoid putting user identifiers in tag values.

Choosing between Insights and other tools

Query Insights overlaps with other tools, and the right choice depends on the question.

Most teams end up using all four: tracing to notice the problem, Insights to find the query, pg_stat_statements and EXPLAIN to confirm it, and logs to catch the specific bad execution. For PostgreSQL workloads that outgrow Cloud SQL, see AlloyDB; for following a request across services, see Cloud Trace.

What to do next

1. Enable Query Insights on one non-production instance with application tags and client address on, and confirm that queries appear within a few minutes.
2. Check the longest query your ORM generates and set the query string length so its predicate is not truncated.
3. Add sqlcommenter tags, either from a framework integration or the helper above, using route templates rather than concrete URLs.
4. Write down the six-step diagnosis sequence as a runbook, and rehearse it on a deliberately dropped index in staging.
5. Find the Insights metric types in Metrics Explorer, add a latency alert for your top three critical query shapes, and schedule an export if you need history beyond the retention window.
6. Decide, with whoever owns data protection, whether recording client addresses is acceptable in production.