PG中事务隔离级别语句被PGHero标记为长查询的问题咨询

Hey there! Let's break down your questions one by one to get to the bottom of this:

1. Is it normal for this SET statement to show up as long-running? #

Typically, SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; is a super lightweight command—it just tweaks your session's transaction behavior, no heavy lifting like table scans or data writes involved.

That said, PGHero aggregates total cumulative execution time for each statement (pulling data straight from PostgreSQL's pg_stat_statements extension). So if this command runs hundreds or thousands of times, the total time adds up, making it look "long-running" in overall stats—even if individual runs only take a few milliseconds.

To confirm this, run this query directly in your PostgreSQL instance:

SELECT calls, total_time, mean_time, max_time 
FROM pg_stat_statements 
WHERE query LIKE '%SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ%';

• If mean_time is very low (like <1ms), the "long total time" is just from high call volume—not slow individual executions. That's totally normal if the command runs frequently.
• If mean_time or max_time is unexpectedly high, that could point to rare issues like session contention or resource bottlenecks when the command runs.

2. Could PGHero 2 have a统计 error? #

Short answer: Unlikely. PGHero doesn't calculate or modify timing data on its own—it relies entirely on the pg_stat_statements extension for all its stats. If the numbers look off, first verify the raw data using the query above. If the raw PostgreSQL data matches what PGHero shows, the tool is working correctly—the issue is with how often the command runs, not PGHero's reporting.

3. Does Sequelize have issues with improper resource release here? #

This depends on how you've configured Sequelize. Let's cover the key scenarios:

• Global isolation level setup: If you set isolationLevel: 'REPEATABLE READ' in your main Sequelize connection config, it should run this SET command once per connection when the connection is first added to the pool—not on every query or transaction. If it's running more often than that, double-check your config (you might accidentally be setting the isolation level per query instead of globally).
• Connection pool leaks: If connections aren't being released back to the pool properly, you'll end up with more connections being created than necessary—each running the SET command. To check this, run SELECT count(*) FROM pg_stat_activity; and compare it to your Sequelize pool's max setting. If the count consistently exceeds max, you likely have hanging connections (e.g., unclosed transactions, forgotten await on database operations).
• Transaction-specific isolation: If you set the isolation level per transaction (like await sequelize.transaction({ isolationLevel: 'REPEATABLE READ' }, ...)), Sequelize will run the SET command for every transaction. This is expected behavior, but high transaction volume will make the cumulative time add up quickly.

Quick Sequelize checks: #

• Review your config to ensure you're setting the isolation level globally (in the main connection options) if that's your intent, not per transaction/query.
• Make sure all database operations (including transactions) are properly awaited and closed—unhandled promises or abandoned transactions can leave connections stuck in the pool.

内容的提问来源于stack exchange，提问作者Shahor