ContextQMD
Back to Genai Toolbox

postgres-list-table-stats

docs/en/integrations/postgres/tools/postgres-list-table-stats.md

1.1.06.4 KB
Original Source

About

The postgres-list-table-stats tool queries pg_stat_all_tables to provide comprehensive statistics about tables in the database. It calculates useful metrics like index scan ratio and dead row ratio to help identify performance issues and table bloat.

The tool returns a JSON array where each element represents statistics for a table, including scan metrics, row counts, and vacuum history. Results are sorted by sequential scans by default and limited to 50 rows.

Use Cases

  • Finding ineffective indexes: Identify tables with low idx_scan_ratio_percent to evaluate index strategy.
  • Detecting table bloat: Sort by dead_rows to find tables needing VACUUM.
  • Monitoring growth: Track total_size_bytes over time for capacity planning.
  • Audit maintenance: Check last_autovacuum and last_autoanalyze timestamps to ensure maintenance tasks are running.
  • Understanding workload: Examine seq_scan vs idx_scan ratios to understand query patterns.

Compatible Sources

{{< compatible-sources others="integrations/alloydb, integrations/cloud-sql-pg">}}

Parameters

parametertyperequireddefaultdescription
schema_namestringfalse"public"Optional: A specific schema name to filter by (supports partial matching)
table_namestringfalsenullOptional: A specific table name to filter by (supports partial matching)
ownerstringfalsenullOptional: A specific owner to filter by (supports partial matching)
sort_bystringfalsenullOptional: The column to sort by. Valid values: size, dead_rows, seq_scan, idx_scan (defaults to seq_scan)
limitintegerfalse50Optional: The maximum number of results to return

Example

yaml
kind: tool
name: list_table_stats
type: postgres-list-table-stats
source: postgres-source
description: "Lists table statistics including size, scans, and bloat metrics."

Example Requests

List default tables in public schema:

json
{}

Filter by specific table name:

json
{
  "table_name": "users"
}

Filter by owner and sort by size:

json
{
  "owner": "app_user",
  "sort_by": "size",
  "limit": 10
}

Find tables with high dead row ratio:

json
{
  "sort_by": "dead_rows",
  "limit": 20
}

Example Response

json
[
  {
    "schema_name": "public",
    "table_name": "users",
    "owner": "postgres",
    "total_size_bytes": 8388608,
    "seq_scan": 150,
    "idx_scan": 450,
    "idx_scan_ratio_percent": 75.0,
    "live_rows": 50000,
    "dead_rows": 1200,
    "dead_row_ratio_percent": 2.34,
    "n_tup_ins": 52000,
    "n_tup_upd": 12500,
    "n_tup_del": 800,
    "last_vacuum": "2025-11-27T10:30:00Z",
    "last_autovacuum": "2025-11-27T09:15:00Z",
    "last_autoanalyze": "2025-11-27T09:16:00Z"
  },
  {
    "schema_name": "public",
    "table_name": "orders",
    "owner": "postgres",
    "total_size_bytes": 16777216,
    "seq_scan": 50,
    "idx_scan": 1200,
    "idx_scan_ratio_percent": 96.0,
    "live_rows": 100000,
    "dead_rows": 5000,
    "dead_row_ratio_percent": 4.76,
    "n_tup_ins": 120000,
    "n_tup_upd": 45000,
    "n_tup_del": 15000,
    "last_vacuum": "2025-11-26T14:22:00Z",
    "last_autovacuum": "2025-11-27T02:30:00Z",
    "last_autoanalyze": "2025-11-27T02:31:00Z"
  }
]

Output Format

fieldtypedescription
schema_namestringName of the schema containing the table.
table_namestringName of the table.
ownerstringPostgreSQL user who owns the table.
total_size_bytesintegerTotal size of the table including all indexes in bytes.
seq_scanintegerNumber of sequential (full table) scans performed on this table.
idx_scanintegerNumber of index scans performed on this table.
idx_scan_ratio_percentdecimalPercentage of total scans (seq_scan + idx_scan) that used an index. A low ratio may indicate missing or ineffective indexes.
live_rowsintegerNumber of live (non-deleted) rows in the table.
dead_rowsintegerNumber of dead (deleted but not yet vacuumed) rows in the table.
dead_row_ratio_percentdecimalPercentage of dead rows relative to total rows. High values indicate potential table bloat.
n_tup_insintegerTotal number of rows inserted into this table.
n_tup_updintegerTotal number of rows updated in this table.
n_tup_delintegerTotal number of rows deleted from this table.
last_vacuumtimestampTimestamp of the last manual VACUUM operation on this table (null if never manually vacuumed).
last_autovacuumtimestampTimestamp of the last automatic vacuum operation on this table.
last_autoanalyzetimestampTimestamp of the last automatic analyze operation on this table.

Advanced Usage

Interpretation Guide

Index Scan Ratio (idx_scan_ratio_percent)

  • High ratio (> 80%): Table queries are efficiently using indexes. This is typically desirable.
  • Low ratio (< 20%): Many sequential scans indicate missing indexes or queries that cannot use existing indexes effectively. Consider adding indexes to frequently searched columns.
  • 0%: No index scans performed; all queries performed sequential scans. May warrant index investigation.

Dead Row Ratio (dead_row_ratio_percent)

  • < 2%: Healthy table with minimal bloat.
  • 2-5%: Moderate bloat; consider running VACUUM if not recent.
  • > 5%: High bloat; may benefit from manual VACUUM or VACUUM FULL.

Vacuum History

  • Null last_vacuum: Table has never been manually vacuumed; relies on autovacuum.
  • Recent last_autovacuum: Autovacuum is actively managing the table.
  • Stale timestamps: Consider running manual VACUUM and ANALYZE if maintenance windows exist.

Performance Considerations

  • Statistics are collected from pg_stat_all_tables, which resets on PostgreSQL restart.
  • Run ANALYZE on tables to update statistics for accurate query planning.
  • The tool defaults to limiting results to 50 rows; adjust the limit parameter for larger result sets.
  • Filtering by schema, table name, or owner uses LIKE pattern matching (supports partial matches).