Trace a Slow Multi-Tenant Query Back to One Tenant's Data

For: A backend engineer at a 30-person B2B SaaS company whose Datadog dashboard shows p99 query latency spiking every evening, but the slow-query log names only the shared query template — not which tenant's data volume or filter pattern is causing it — and their CTO wants a root-cause by end of week

If your p99 query latency spikes every evening but pg_stat_statements only shows the parameterized query shape, the fastest path to the culprit is: attach the tenant_id to application_name or a query comment, correlate it with pg_stat_statements and auto_explain, then re-run EXPLAIN (ANALYZE, BUFFERS) as that tenant's role so row-level security predicates actually fire. The trap most teams hit: RLS predicates are applied after the planner picks its access path in the abstract, so a query that looks index-safe in a generic EXPLAIN can silently sequential-scan for one specific tenant.

This tutorial walks through that trace end-to-end on PostgreSQL 14+. It assumes you already have RLS enabled, a tenant_id column on your tables, and superuser or equivalent access on a staging replica. Do not run steps 3-5 against production without a read replica.

Prerequisites

• PostgreSQL 14 or newer (works on 13 with minor syntax tweaks)
• pg_stat_statements and auto_explain loaded in shared_preload_libraries
• A tenant-scoped table with an RLS policy (we'll use invoices in examples)
• Read access to your application's connection pool config (PgBouncer, pgpool, or the driver)
• Datadog, Grafana, or any log aggregator that ingests Postgres logs

Step 1: Confirm the extensions are actually loaded

Half the time when engineers say "pg_stat_statements doesn't show tenant info" the extension is loaded but not tracking what they think. Verify:

SHOW shared_preload_libraries;
SELECT * FROM pg_extension WHERE extname IN ('pg_stat_statements');
SHOW pg_stat_statements.track;
SHOW auto_explain.log_min_duration;

Expected output:

 shared_preload_libraries
----------------------------------------
 pg_stat_statements,auto_explain

 pg_stat_statements.track
--------------------------
 top

 auto_explain.log_min_duration
-------------------------------
 500ms

If auto_explain.log_min_duration is -1, nothing gets logged. Set it to something like 500ms in postgresql.conf and reload. Also enable auto_explain.log_analyze = on and auto_explain.log_buffers = on — without these, the log line tells you nothing you didn't already know.

Step 2: Push tenant_id into every query so the log can identify it

This is the single change that closes the biggest gap in multi-tenant query performance debugging. Your slow-query log strips parameters, but it keeps comments. Add a tenant tag as a marginalia-style SQL comment from your application driver.

In Rails:

ActiveRecord::QueryLogs.tags = [
  { tenant_id: -> { Current.tenant&.id } },
  { request_id: -> { Current.request_id } }
]

In Node with Knex or a pg wrapper, prepend a comment before execution:

const tag = `/* tenant_id=${tenantId},route=${route} */ `;
await pg.query(tag + sql, params);

Also set application_name per connection when you check out from the pool. PgBouncer users: use application_name_add_host = 1 and set it via SET application_name after checkout in transaction pooling mode.

Verify a query now looks like this in pg_stat_activity:

SELECT query, application_name FROM pg_stat_activity WHERE state = 'active';

/* tenant_id=8842,route=invoices#index */ SELECT * FROM invoices WHERE status = $1 ORDER BY created_at DESC LIMIT 50

Step 3: Aggregate pg_stat_statements per tenant

pg_stat_statements normalizes the query text — it strips the comment during normalization. That's a problem. Two workarounds:

Option A (recommended): set pg_stat_statements.track = 'all' and enable compute_query_id = on, then parse the raw log lines from auto_explain (which preserves comments) and roll up in your log pipeline. This gives you pg_stat_statements per tenant without polluting the extension's normalization.

Option B: maintain a lightweight table:

CREATE TABLE tenant_query_stats (
  tenant_id     bigint,
  queryid       bigint,
  captured_at   timestamptz DEFAULT now(),
  total_time_ms double precision,
  calls         bigint,
  rows_returned bigint
);

Run a cron every minute that samples pg_stat_activity for currently-executing statements, extracts the tenant_id from the comment via regex, and joins to pg_stat_statements on queryid. It's sampling, not exhaustive, but for tail latency (what you actually care about) sampling is fine.

INSERT INTO tenant_query_stats (tenant_id, queryid, total_time_ms, calls)
SELECT
  (regexp_match(a.query, 'tenant_id=(\d+)'))[1]::bigint,
  a.query_id,
  EXTRACT(EPOCH FROM (now() - a.query_start)) * 1000,
  1
FROM pg_stat_activity a
WHERE a.state = 'active' AND a.query_id IS NOT NULL;

Step 4: Find the offending tenant

Now run the query you've been waiting to run. Which tenant is dragging p99?

SELECT
  tenant_id,
  queryid,
  count(*) AS samples,
  avg(total_time_ms) AS avg_ms,
  percentile_cont(0.99) WITHIN GROUP (ORDER BY total_time_ms) AS p99_ms
FROM tenant_query_stats
WHERE captured_at > now() - interval '2 hours'
GROUP BY tenant_id, queryid
HAVING count(*) > 10
ORDER BY p99_ms DESC
LIMIT 20;

Expected output:

 tenant_id | queryid    | samples | avg_ms  | p99_ms
-----------+------------+---------+---------+---------
      8842 | 2938471023 |     412 | 1834.22 | 6421.10
      8842 | 4471120983 |     388 |  912.44 | 2104.55
      1203 | 2938471023 |     201 |   14.20 |   42.10
       771 | 2938471023 |     198 |   12.87 |   38.44

The same queryid runs in ~40ms for most tenants and ~6 seconds for tenant 8842. This is the pattern that generic slow-query logs cannot show you. Now you have a suspect.

Step 5: Reproduce the plan as that tenant

Here's where the RLS gotcha bites. Running EXPLAIN as a superuser bypasses the RLS policy entirely, so you'll get a plan that has nothing to do with what happens in production. You must run EXPLAIN using the same role and SET-ings the app connection uses.

-- Bad: superuser bypass, misleading plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM invoices WHERE status = 'pending' ORDER BY created_at DESC LIMIT 50;

-- Good: impersonate the app role and set the tenant GUC your RLS policy reads
SET ROLE app_tenant;
SET app.current_tenant_id = '8842';

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM invoices WHERE status = 'pending' ORDER BY created_at DESC LIMIT 50;

Compare against a healthy tenant:

SET app.current_tenant_id = '1203';
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM invoices WHERE status = 'pending' ORDER BY created_at DESC LIMIT 50;

Typical output for tenant 8842:

Limit  (cost=0.00..48291.20 rows=50 width=284) (actual time=5821.443..5821.512 rows=50)
  ->  Seq Scan on invoices  (cost=0.00..1948217.00 rows=2018 width=284)
        Filter: ((status = 'pending') AND (tenant_id = current_setting('app.current_tenant_id')::bigint)
                 AND (EXISTS (SELECT 1 FROM tenant_features tf WHERE tf.tenant_id = invoices.tenant_id AND tf.feature = 'audit_v2')))
        Rows Removed by Filter: 8,412,993
Planning Time: 1.244 ms
Execution Time: 5821.601 ms

For tenant 1203:

Limit  (cost=0.42..18.29 rows=50 width=284) (actual time=0.031..12.104 rows=50)
  ->  Index Scan using idx_invoices_tenant_status_created on invoices
        Index Cond: ((tenant_id = 1203) AND (status = 'pending'))
Execution Time: 12.211 ms

There it is. Same query, same index available, completely different plan. Tenant 8842's RLS policy includes an EXISTS subquery against tenant_features that the planner cannot inline efficiently, so it falls back to a sequential scan. Tenant 1203 doesn't have that feature flag enabled, so their policy path is simpler and the planner uses the composite index.

Step 6: Confirm it's the RLS predicate, not data volume

Rule out the boring explanation first: is tenant 8842 just huge?

SELECT tenant_id, count(*), pg_size_pretty(sum(octet_length(t::text))::bigint)
FROM invoices t
GROUP BY tenant_id
ORDER BY count(*) DESC
LIMIT 10;

If 8842 has 40x the row count of every other tenant, size is the story and you need better partitioning. If 8842 is mid-pack in row count but still 400x slower, it's the RLS predicate — as our EXPLAIN confirmed.

Now inspect the policy:

SELECT polname, pg_get_expr(polqual, polrelid) AS using_clause
FROM pg_policy
WHERE polrelid = 'invoices'::regclass;

Expected output:

 polname             | using_clause
---------------------+----------------------------------------------------------
 invoices_tenant_iso | (tenant_id = current_setting('app.current_tenant_id')::bigint
                     |  AND (NOT audit_v2_required OR EXISTS (
                     |    SELECT 1 FROM tenant_features tf
                     |    WHERE tf.tenant_id = invoices.tenant_id
                     |    AND tf.feature = 'audit_v2')))

The EXISTS clause is the smoking gun. Postgres cannot use an index on invoices for a subquery that resolves per-row unless it can hoist the check into an initplan — and with an RLS predicate combined with the user's WHERE, the hoist doesn't happen reliably.

Step 7: Fix it

You have three real options. Each has tradeoffs.

Option 1: Denormalize the feature flag onto invoices. Add an audit_v2 boolean column, backfill it, keep it in sync via trigger or app-level write. RLS policy becomes a simple AND (audit_v2_required = false OR audit_v2 = true). Fast. Ugly. Requires disciplined writes. Best when the flag rarely changes.

Option 2: Move the feature check out of the RLS policy and into the application layer. RLS handles tenant isolation only. The app adds the audit_v2 filter to queries where it matters. Faster plans, but you lose the security guarantee that the DB enforces the feature boundary. Only viable if the audit_v2 check is a business rule, not a security rule.

Option 3: Rewrite the policy to use a stable function marked STABLE PARALLEL SAFE. Wrap the EXISTS in a function that caches per-transaction. This helps but doesn't fully fix the planner's inability to use the composite index.

For most SaaS shops, Option 1 is what actually ships. Test on a replica, run ANALYZE, then re-run Step 5.

Common errors

"EXPLAIN shows an index scan but production still sequential-scans"

You're running EXPLAIN as a role that bypasses RLS. Check SELECT current_user; and SELECT rolbypassrls FROM pg_roles WHERE rolname = current_user;. If it's t, your plan is a lie.

"pg_stat_statements.queryid keeps changing for the same query"

Query comments with unique IDs (like request_id) get included in normalization on older versions. Upgrade to PG 14+ where compute_query_id is stable, or strip request-scoped tags from the comment and keep only tenant_id.

"auto_explain isn't logging anything even though the query is slow"

It's loaded but not enabled for your session. auto_explain needs to be in shared_preload_libraries AND have its parameters set at server or database level, not just in a session. Also check auto_explain.log_min_duration is not -1.

"pg_stat_activity shows the query but query_id is NULL"

Set compute_query_id = on or compute_query_id = auto and reload. On PG 13 and below, you need pg_stat_statements loaded for query_id to appear anywhere.

"PgBouncer in transaction pooling mode loses my SET LOCAL"

Use SET LOCAL inside an explicit transaction, or switch to session pooling for connections that need per-tenant GUCs. Some teams solve this with a proxy layer that injects SET app.current_tenant_id as the first statement after checkout.

What this doesn't solve

Sampling pg_stat_activity misses fast queries that finish between polls. If your tail latency is composed of many 200ms queries rather than a few 6-second ones, you need pg_stat_kcache or pg_wait_sampling to get accurate per-tenant attribution. And if your workload is write-heavy with per-tenant lock contention, none of this helps — you need pg_locks instrumentation instead.

Teams building this kind of tenant-aware observability into large SaaS platforms often pair it with tenant-scoped rate limiting and noisy-neighbor isolation at the connection pool. If you're rebuilding a legacy multi-tenant system with these problems baked in, our team has done this work across accounting SaaS and other tenant-heavy platforms — see how we approach legacy modernization for context.

Frequently Asked Questions

Why doesn't pg_stat_statements show tenant_id by default?

Because it normalizes queries by stripping literals and comments to group similar statements together. That's the whole point of the extension — it would be useless if WHERE tenant_id = 1 and WHERE tenant_id = 2 were tracked separately. To get per-tenant stats you need to correlate the normalized queryid with tenant context captured elsewhere, usually via query comments plus pg_stat_activity sampling or auto_explain logs.

Can I just add an index to fix a slow multi-tenant query?

Sometimes, but not when row-level security is the cause. RLS predicates are applied after the planner picks its access path, so an EXISTS or subquery inside a policy can force a sequential scan even when a perfect index exists. Add the index, but also inspect the policy with pg_get_expr(polqual, polrelid) to see what the planner is actually working with.

Is row-level security a bad choice for multi-tenant SaaS then?

No — RLS is a strong isolation model and catches bugs that app-layer filtering misses. The failure mode is putting complex logic (feature flags, joins, subqueries) inside the policy. Keep RLS policies to simple equality checks against a session GUC, and push business logic into the query or the application layer.

How do I run this trace without impacting production?

Do the discovery on a read replica or a recent restore. Sampling pg_stat_activity is cheap and safe on production, but EXPLAIN ANALYZE on the offending query executes it, so run that on a replica. The one-minute cron sampling in Step 3 has negligible load — we've run it on databases handling five-figure queries per second without measurable impact.

What if the slow tenant is our biggest customer and we can't slow-roll a fix?

Short-term mitigations: route that tenant to a dedicated read replica, add a query hint via pg_hint_plan to force the index, or shard that tenant onto its own database. Long-term, fix the RLS policy. For architectural help on tenant isolation strategies at scale, contact CodeNicely for a personalized assessment.