Decide whether a table is worth clustering, on which columns, and with what fill factor.
promptread-only tools
review-clustering(schema, table)The text below is exactly what prompts/get returns for the example arguments. A client inserts it as the opening message of a conversation, and the model then calls the tools it names.
Decide whether shop.orders is worth clustering, on which columns, and with what fill factor. This server is read-only and will run none of it. What you produce is a recommendation with the readings behind it, for an operator to apply. Clustering rewrites a table in the order of an index, so that rows read together sit together. It pays when queries fetch many rows of one value from the table and those rows are spread over many pages; it pays nothing when they are already together, or when the queries are answered from an index alone. The order is not maintained afterwards, so the gain decays at the rate the table changes. Measure each of those, and say which reading decided. 0. Call checkPrivileges. tableStats omits column statistics this role may not SELECT, and rowScatter fails without SELECT on the table; the review rests on both. 1. Call tableStats. For each column read physical_order_correlation: near 1 or -1 the table is already in that column's order, near 0 it is not. Note which column the table is ordered by now -- usually the key it was loaded by -- and which of the others have few distinct values or a few very common ones (n_distinct, most_common_vals). Those are the candidates. A low correlation says a column is out of order; it does not say that matters. 2. Find which columns queries filter on. Where pg_qualstats is installed, predicateStats lists the predicates actually evaluated on this table and how often. Otherwise read statementStats for the statements that touch it. A column nothing filters on is not a candidate however scattered it is. Check tableDetails for a covering index too: a query answered by an index-only scan does not read the table, and clustering the table does nothing for it. 3. Measure what the scatter costs. Call rowScatter with each candidate column and no value: for the column's most common values it samples the table and returns share_of_pages_percent and scatter_ratio. A value on most of the table's pages at a ratio of 30 means a query for it reads thirty times the pages it needs; a ratio near 1 means the rows are already together and there is nothing to gain. Then call rowScatter with a value that the queries in step 2 really ask for, since a typical value is rarer than the most common ones and the exact count is the figure to quote. If capped is true the counts are lower bounds; say so. The exact count packs by pg_class's rows per page, which bloat or stale statistics lower, so call tableBloat too: on a bloated table the ratio understates what clustering would gain. 4. Choose the order. Lead with the column the queries filter on most and whose scatter_ratio is highest, then the next, and end with the key so the order is total. Putting a second column after the first keeps rows together within each value of the first only; say what that does for queries on the second alone, which is usually little. 5. Estimate how fast the order decays. tableStats returns n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd and, from PostgreSQL 16, n_tup_newpage_upd since counters_since. Inserts land wherever there is room, not in order. An update leaves its page only when the new version does not fit there, and n_tup_newpage_upd counts exactly those; an update that is not HOT because it changed an indexed column still stays on its page when it fits. So the drift is n_tup_ins plus n_tup_newpage_upd. On PostgreSQL 14 and 15 there is no such counter: n_tup_upd minus n_tup_hot_upd is then an upper bound, and say that it is one. Against rows, over the time since counters_since, that is the share of the table that drifts between maintenance windows. A table that changes by a few percent a month keeps its order for a long time; one that turns over weekly does not, and clustering it is a recurring cost rather than a fix. 6. Decide the fill factor honestly. A lower fill factor leaves room for an updated row to stay on its page. That only helps when the update can be HOT, and it cannot when it changes a column that any index covers, including one an index predicate or expression mentions. Compare n_tup_hot_upd with n_tup_upd, and read tableDetails for which columns are indexed. Where the usual update touches an indexed column, a fill factor buys locality -- the new version lands near the old one -- and not cheaper updates; say which of the two you are recommending it for, and that each point below 100 is that share more pages for every scan. Where the table is only ever inserted into, leave it at 100. 7. State the cost of doing it. Call tableSize: CLUSTER writes a new copy of the table and rebuilds every index, so it needs free space of about total_size, and it holds an ACCESS EXCLUSIVE lock for the whole run, blocking reads as well as writes. Remember that these are logical bytes (see diskUsage's note). If that lock is not acceptable, say so and name the alternative as a matter for the operator -- an online rewrite tool, or building the ordered copy beside the original -- rather than recommending CLUSTER regardless. For the lock and the timing, take the plan to plan-schema-change. Finish with the recommendation in one paragraph: cluster or not; the column order; the fill factor and what it is for; the pages a typical query reads now and would read after; how long the order will last; and what it costs to apply. If the readings do not support clustering, say that -- it is the more common answer.
{
"jsonrpc": "2.0",
"id": 1,
"method": "prompts/get",
"params": {
"name": "review-clustering",
"arguments": {
"schema": "shop",
"table": "orders"
}
}
}