如何检测PostgreSQL表当前是否有读取操作及每秒读取量计算

Great question! Let's break this down into two key parts: verifying if a table is currently being read (so you can safely drop it) and calculating its read rate per second for deeper visibility.

Before dropping a table, you need to confirm no active or pending read operations are targeting it—otherwise, your drop might block or interrupt ongoing work. Here are the most reliable methods:

1. Check active queries targeting the table #

Use the pg_stat_activity view to find running or idle-in-transaction processes that are accessing your table:

SELECT pid, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE query ILIKE '%your_table_name%'
  AND state IN ('active', 'idle in transaction')
  AND query NOT LIKE '%pg_stat_activity%'; -- Exclude our own monitoring query

Pro tip: If your table is in a specific schema, use the full qualified name (schema_name.your_table_name) in the ILIKE clause to avoid false matches with other tables that share part of the name.

2. Check for held locks on the table #

Even if no active queries are running, a long-running transaction might still hold a shared lock on the table (from a previous SELECT). This will block your DROP TABLE command. Use pg_locks to check:

SELECT pid, mode, granted
FROM pg_locks
WHERE relation = 'your_table_name'::regclass
  AND mode IN ('ACCESS SHARE', 'SHARE', 'SHARE UPDATE EXCLUSIVE');

Any row with granted = true means a process is holding a read-related lock on the table—wait until those processes finish before proceeding.

3. Verify no reads over a period #

To be extra safe, monitor if the table gets accessed over a window of time using pg_stat_user_tables (cumulative stats):

SELECT seq_scan, idx_scan, seq_tup_read, idx_tup_fetch
FROM pg_stat_user_tables
WHERE relname = 'your_table_name';

Record these values, wait 5-10 minutes, then run the query again. If the scan counts and read row counts don't increase, the table isn't being read during that period.

Since PostgreSQL's stats are cumulative, you'll need to compare two time points to get a per-second rate:

1. Capture initial stats in a temporary table:

SELECT 
  seq_tup_read + idx_tup_fetch AS total_read_rows,
  now() AS capture_time
INTO TEMP TABLE temp_table_reads
FROM pg_stat_user_tables
WHERE relname = 'your_table_name';

2. Wait for a fixed interval (e.g., 10 seconds):

SELECT pg_sleep(10);

3. Calculate the per-second rate:

SELECT
  (current.total_read_rows - initial.total_read_rows) 
  / EXTRACT(EPOCH FROM (current.capture_time - initial.capture_time)) AS reads_per_second
FROM temp_table_reads initial,
     (SELECT 
        seq_tup_read + idx_tup_fetch AS total_read_rows,
        now() AS capture_time
      FROM pg_stat_user_tables
      WHERE relname = 'your_table_name') current;

Note: seq_tup_read counts rows read via sequential scans, while idx_tup_fetch counts rows fetched via index scans—adding them gives the total number of rows read from the table.

Once you've confirmed:

• No active queries targeting the table
• No held read locks
• Zero reads over your monitoring window

You can drop the table with confidence:

DROP TABLE IF EXISTS your_table_name CASCADE;

Use CASCADE only if you want to automatically drop dependent objects (like views) tied to the table—skip it if you need to keep those dependencies intact.

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