Tables & indexes

The Tables & indexes tab shows the maintenance state of a database: where dead rows are piling up, which statistics are out of date, how close any table is to transaction ID wraparound, and which indexes are never used.

Everything here comes from the catalog and statistics views. QueryPilot never runs pgstattuple or any other scan of your data.

Where the data comes from

Source	Interval	Row cap
pg_stat_user_tables plus pg_class reloptions	60 s	1,000 tables
pg_stat_user_indexes plus index catalog flags	60 s	2,000 indexes

Tables are ordered by size, so the cap keeps the tables that matter. Relation sizes need SELECT on pg_catalog.pg_class; without it sizes are reported as unknown rather than guessed.

Tables

Column	Meaning
Live and dead rows	From the statistics collector. Dead rows are rows no longer visible but not yet reclaimed.
Dead row ratio	dead / (live + dead).
Autovacuum threshold	The effective threshold for that table: base threshold plus scale factor × live rows, using per-table reloptions when they override the server defaults. This is why “12.4M dead rows” alone means nothing, and “6× its threshold” does.
Modified since analyze	n_mod_since_analyze, against the autoanalyze threshold computed the same way.
Last vacuum / analyze	Manual and automatic, whichever is more recent.
Freeze age	age(relfrozenxid) as a share of the ~2.1 billion transaction limit at which PostgreSQL stops accepting writes.
Size	Table size, and its history over the window.

A table with autovacuum_enabled = false is flagged: it is usually a leftover from a bulk load, and nothing will clean it up until someone notices.

Indexes

Column	Meaning
Scans	Index scans in the window, and since statistics were last reset.
Size	Index size on disk.
Valid	Invalid indexes are usually left behind by a failed CREATE INDEX CONCURRENTLY. They take up space and are maintained on writes, but the planner cannot use them.
Flags	Primary key and unique constraints, which are never proposed for removal.

Related findings

The same data drives findings: table-bloat, stale-statistics, xid-wraparound, unused-index, duplicate-index and invalid-index. Each one links back to the table or index it is about, and most come with a recommendation you can run.

Bloat findings also name long-running transactions that may be holding vacuum back: vacuum cannot remove rows that an older transaction might still see, so the fix is sometimes to end a forgotten session rather than to vacuum harder.

API

Method	Path	Description
GET	/api/v1/databases/{id}/tables	Latest table maintenance state, indexes with their usage in the window, size and dead-row history, and the open table and index findings. Query: range or from/to, step.