> For the complete documentation index, see [llms.txt](https://docs.datasentinel.io/manual/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.datasentinel.io/manual/ai-connector/analyze-and-tune-workloads/tables-indexes-and-autovacuum.md).
# Review tables, indexes and autovacuum
Review the physical health of your tables and indexes: bloat, missing or unused indexes, and whether autovacuum keeps up.
Ask the assistant to survey the tables and indexes of one instance over a past window and report what needs attention. One analysis covers four questions at once: which tables carry the most dead tuples (a bloat proxy), which large tables are read by sequential scans and would benefit from an index, which indexes look unused or redundant, and whether autovacuum keeps pace with the write load.
Nothing is executed on the database. The survey reads the table and index statistics the platform collects every hour, so it needs a time window rather than real-time access.
"Which tables are bloated?": a real session of about five minutes, condensed into 43 seconds
{% hint style="info" %}
**Pick a representative window.** Table and index metrics are collected once per hour, so the window must span at least one hourly mark: the assistant widens a shorter window automatically and says so. An "unused index" or "autovacuum keeps up" verdict is only as good as the window it was read over: prefer several hours, ideally a full representative day, and never more than 24 hours.
{% endhint %}
## Example prompts
* **Bloat:**
* "Which tables are bloated on prod-eu-1 over the last 24 hours?"
* "Give me the detailed stats of the orders table in the demo database on prod-eu-1."
* **Missing indexes:**
* "Any missing or unused indexes on prod-eu-1?"
* "Why so many sequential scans on the billing database of prod-eu-2 this morning?"
* **Validate one index idea:**
* "Estimate whether an index on order\_details(order\_id) would change the delete's plan on prod-eu-1."
* **Autovacuum:**
* "Is autovacuum keeping up on prod-eu-1's zabbix database?"
* "Is a vacuum or index build running on prod-eu-1 right now, and how far along?"
The named command is `datasentinel tables`, followed by the instance and the window. See the [Skill commands reference](/manual/ai-connector/use-the-assistant/skill-commands.md#analyze-one-instance-over-a-past-window). A question about a vacuum or an index build in flight routes to `datasentinel maintenance` instead, described under [Look at live activity](/manual/ai-connector/use-the-assistant/skill-commands.md#look-at-live-activity).
## What the assistant looks at
The survey ranks tables and indexes on the axis that matches your question, then drills the one or two objects it flagged. Bloat is always assessed, whatever you asked: a clean bill is reported in one line rather than left out.
{% tabs %}
{% tab title="Bloat" %}
The windowed statistics carry no measured bloat figure. The assistant reads the dead-tuple count, the dead-to-live ratio, and the rows modified since the last analyze, and reports a **high dead-tuple ratio (bloat proxy)**, never a percentage it cannot measure.
The exact figure needs a live look at the table, offered as a next step when your account has real-time access (see [Live checks](#live-checks) below).
{% endtab %}
{% tab title="Missing indexes" %}
Sequential scans are read against table size: many scans on a small table are fine, and the signature of a missing index is a large table with a huge number of rows read sequentially. Each suspect is a lead, not a verdict: the next step drills the query behind the scans and reads its captured plan, as in [Optimize your top queries](/manual/ai-connector/analyze-and-tune-workloads/optimize-top-queries.md).
To validate one index idea before creating it, ask for the plan the planner would choose now, as in [Explain execution plans](/manual/ai-connector/analyze-and-tune-workloads/explain-execution-plans.md). The assistant states the trade-off of every index it proposes: the reads it saves against the write cost it adds.
{% endtab %}
{% tab title="Index hygiene" %}
An index with no scans over the window **appears unused over this window**, and the recommendation is to investigate it for dropping, never to drop it. An index used only by a nightly or weekly job looks unused in a short window, so the verdict is always qualified by the window it was read over.
Redundant indexes are candidates only: usage statistics carry no index definitions, so confirming a duplicate, or measuring the bloat of an index, needs a live check. A low share of HOT updates on a heavily updated table points at write amplification by the indexes already in place.
{% endtab %}
{% tab title="Autovacuum" %}
"Vacuums are running and dead tuples are high" is not a diagnosis. The assistant reads the trend of dead tuples across the hourly samples, not a single count, and separates four causes:
| What the evidence shows | Cause | Lever |
| --------------------------------------------------------------------- | ----------------------------------------- | -------------------------------------------------- |
| Vacuums infrequent, dead rows high | Scale factor too coarse for a large table | Per-table vacuum scale factor or threshold |
| Vacuums completing, dead rows high, an old open transaction confirmed | Pinned cleanup horizon | End that session; no autovacuum setting helps |
| Vacuums completing, dead rows high but stable across samples | Steady-state churn, vacuum keeps pace | None; the level is reported and the case is closed |
| Vacuums completing, dead rows climbing sample over sample | Reclaim genuinely behind | Vacuum cost limit, cost delay, or worker count |
A pinned horizon is never attributed without positive evidence of a holder. Confirming one needs a live look at the sessions, and even a clean session list cannot exclude it, because prepared transactions and replication slots are invisible to the assistant: those two stay flagged for an operator.
{% endtab %}
{% endtabs %}
Depending on your [communication style](/manual/ai-connector/readme.md#communication-style), the TECHNICAL style gives the parameter names and values to change, while the PLAIN style gives the same verdict as an escalation to raise with the database team, with no parameter names.
## Live checks
Three follow-ups need real-time access, and the assistant offers them only when your account has it.
* **Exact bloat figure:** a live read of one table returns its measured bloat, provided the `datasentinel_analytics` extension is installed on the instance. Without it, the live counters come back and the assistant says in one line that the measured part needs the extension.
* **Duplicate indexes and index bloat:** the same live read confirms a redundancy candidate against the actual index definitions and measures the bloat of each index.
* **Maintenance in progress:** a VACUUM, VACUUM FULL, CLUSTER, index build, ANALYZE, or COPY in flight, with its phase, how far along it is, and the cost it imposes while it runs.
{% hint style="warning" %}
**Needs real-time access.** Live checks go through the [Live360](https://app.gitbook.com/o/DaDQkGCM7r2TghFSBFLQ/s/yE16Xb3IemPxJWydtPOj/features/key-features/live360) module, the only part of the platform that connects directly to the PostgreSQL instance. Access to it is a flag set on each user profile, under [Users & Roles](https://app.gitbook.com/o/DaDQkGCM7r2TghFSBFLQ/s/yE16Xb3IemPxJWydtPOj/implementation/platform-usage/configuration/users-and-roles#live360-access). Without that flag, the assistant says so in one line and answers from the hourly statistics instead: whether maintenance ran over the window and what it cost, rather than how far along it is right now.
{% endhint %}
{% hint style="info" %}
**Continue from the menu.** A survey ends with a short menu of next steps tied to what it found: get the detailed stats of the most bloated table, find the queries that update it, drill the plan of the query behind a missing-index suspect, validate one index idea against a live plan, check the write cost of the indexes already in place, get autovacuum settings tailored to one table, confirm an unused index over a longer window, or check whether a vacuum is running on it right now. Pick an option to keep the investigation going without retyping the context.
{% endhint %}
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.
Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:
```
GET https://docs.datasentinel.io/manual/ai-connector/analyze-and-tune-workloads/tables-indexes-and-autovacuum.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
