listTableStats

read-onlyany connectionsweeps instancesweeps replication groupsweeps group

Synopsis

listTableStats(schema, [pattern])

Description

Return the statistics PostgreSQL keeps for every table in a schema: estimated row count, seq_scan and idx_scan counts, live and dead tuples, the cumulative insert, update, delete and HOT-update counts (n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd -- n_tup_hot_upd against n_tup_upd is the share of updates that stayed on their page, and on PostgreSQL 16 and later n_tup_newpage_upd is the share that had to move), rows modified since the last analyze, rows inserted since the last vacuum, and the manual and automatic vacuum and analyze times as four separate fields (last_vacuum and last_analyze are the manual ones, exactly as in pg_stat_user_tables -- a recent last_vacuum beside a null last_autovacuum means the table is being kept alive by hand and autovacuum is not reaching it). cumulative_statistics_missing is true where the catalog counts rows in a table and the cumulative statistics have none, with no vacuum or analyze on record.

That is statistics which are not there and not a table nobody maintains: crash recovery and an immediate shutdown discard them, a restore or an upgrade does not carry them over, and on a standby it is true of every table always, since vacuum and analyze are recorded on the primary. It is null for a partitioned parent or a view. tableStats on one such table says which of those this server is, and counters_since there says how far back the counters reach.

Reads the catalog and the statistics collector only -- no relation is opened and no file is measured. Carries no per-column histograms; name one table to tableStats for those. size_estimate is relpages*block_size (the server's BLCKSZ, 8192 unless it was built otherwise) and is only as fresh as estimated_from says: for measured sizes call listTableSizes.

Parameters

schema requiredstring
pattern optionalstring: only relations whose name contains this: a literal, case-insensitive substring, so _ and % match themselves and nothing is stemmed. On a large schema the whole list is more than a model should read at once

Also accepts connection, instance, replication_group, group, role, described once under arguments every tool takes.

Output

Keyed by table name; each value is that table's statistics counters and size estimate. An absent extension or a missing grant is reported as {error, hint} in place of the map.

Scope

The counters here are each server's own, so members of a replication group legitimately disagree and the answer is their sum, not the primary's copy. Dead tuples and the last vacuum and analyze times describe work that only happens on a primary; on a replica they are noise, not a second opinion.

Example mocked data

Request

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "listTableStats",
    "arguments": {
      "schema": "shop"
    }
  }
}

Result

{
  "orders": {
    "rows": 1204388,
    "size_estimate": 187351040,
    "seq_scan": 41,
    "idx_scan": 9832117,
    "n_dead_tup": 18344,
    "last_autovacuum": "2026-09-14T22:05:12Z",
    "cumulative_statistics_missing": false
  },
  "customers": {
    "rows": 88213,
    "size_estimate": 13705216,
    "seq_scan": 12,
    "idx_scan": 1200443,
    "n_dead_tup": 211,
    "last_autovacuum": "2026-09-13T08:40:01Z",
    "cumulative_statistics_missing": false
  }
}

Invented values on a fictional shop database, shaped by and checked against this tool's output schema. Real output is returned as structuredContent to clients that negotiate MCP 2025-06-18 or later.