Choose how to apply a DDL change by measuring the table it targets.
promptread-only tools
plan-schema-change(change, [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.
Plan this schema change against what this database actually is. The change: ALTER TABLE shop.orders ADD COLUMN invoice_no bigint Target: shop.orders This server is read-only and will run none of it. What you produce is a plan for an operator to apply. Do not answer from a recipe. The safe method for a change is a property of the table in front of you, not of the statement: the same DDL that is instant on one table is an outage on another. A table with no rows can be rewritten in place and nobody notices; the same rewrite at a billion rows is hours under an exclusive lock. Partitioning is the extreme case -- trivial before there is data, a migration project with a cutover after it. Reaching for the safest-at-scale approach on a small table is complexity nobody needs, and reaching for the simple one on a large table is the outage. Measure first, then choose, and say which reading decided it. 1. Call checkPrivileges. tableStats and tableSize are what this whole plan rests on; if either is degraded here, say so rather than estimating, because they are what separates a metadata change from a full rewrite. 2. Identify every object the change touches, then measure each of them -- not only the one named as the target. A statement that names one table routinely locks more than one: a foreign key locks the table it references as well as the table it is added to, a partitioned table means the parent and every partition, and a column that other tables reference cannot be considered alone. The object you did not name is often the busier one, and its lock is the one that surprises people. Read the change itself for the objects it names, and tableDetails for the ones the catalog knows about -- foreign keys in both directions, and inheritance or partition parents. For each of them: - tableStats: the row count, and n_distinct and most_common_vals when the change involves a default, an index or a partition key -- most_common_vals is the empirical answer to what value the rows already hold, and histogram_bounds low, mid and high are the observed extremes, which is the range a new constraint or default has to be true across - columnHistogram on the partition key, if this is a partitioning change. Choosing boundaries without the distribution is guessing, and the guess is usually uniform when the data is not: a table partitioned by month is a table with one enormous partition wherever the traffic actually was. The bounds are equal-frequency, so every consecutive pair delimits roughly the same number of rows, and taking every Nth bound gives boundaries that divide the existing data evenly. Say which bounds you used, because that is what makes the choice checkable later against how the data has moved since - tableSize: what a rewrite would actually move. Use the measured size, not the estimate; this is the number that turns "instant" into "an hour" - tableDetails: the indexes, constraints, inbound foreign keys and triggers already there, and whether it is already partitioned. Each one multiplies the cost of a rewrite, and some rule out approaches outright - the scan and tuple counters in tableStats: how busy it is. A lock window costs nothing on a table nothing is touching - currentActivity and currentLocks: the oldest running transaction, and whether anything already holds or waits for a lock on any of these objects. A brief exclusive lock request waits behind a long transaction, and everything arriving after it waits behind that -- which is how a millisecond operation becomes an outage. Check this for every object the change touches, since one busy parent is enough to stall the whole statement - serverSettings for the server version, since what is possible and what it costs both moved across majors - listTopology and replicationSlots if the change rewrites: the WAL a rewrite generates has to reach every replica and pass through every slot - listPublications, always. Logical replication does not carry DDL: if the table is published, this statement changes the publisher and nothing else, and the subscriber keeps the old shape until someone changes it too. The failure is silent until the next row arrives and apply stops. Two things decide the plan -- whether the table is published at all, and whether the change touches the primary key or the unique index serving as REPLICA IDENTITY, because a published table without one cannot replicate UPDATE or DELETE at all 3. From those numbers, classify the change before writing any DDL. Say which of the three it is -- metadata only, a scan without a rewrite, or a full rewrite of the table and its indexes -- and separately what lock it needs and for how long. The lock level alone settles nothing: a strong lock held for a millisecond is safe, and a weak one held for an hour may not be. Duration is the number that matters, and duration comes from the measurements. 4. Choose the method by matching that cost against what this table can tolerate. Above some size the incremental approach is the only one available; below it, it is machinery for nothing. State the size or rate at which your answer would flip, so the reasoning can be checked against a different table later. 5. Give the plan as ordered DDL. Against each statement say the lock it takes, whether it rewrites, what must not be running alongside it, and roughly how long at this table's measured size. If the table is published, the plan has two sides and the order between them is part of it: additive changes go on the subscriber first, so it can accept a row that already carries the new column; destructive ones go on the publisher first, so it stops sending what the subscriber is about to lose. Getting that backwards stops apply rather than degrading it. Say which side each statement runs on, and end with subscriptionStats on the subscriber as the check that apply survived -- a zero apply_error_count after the change is the only evidence that it did. 6. Say what the revert looks like, and flag anything irreversible as irreversible whatever the size.
{
"jsonrpc": "2.0",
"id": 1,
"method": "prompts/get",
"params": {
"name": "plan-schema-change",
"arguments": {
"change": "ALTER TABLE shop.orders ADD COLUMN invoice_no bigint",
"schema": "shop",
"table": "orders"
}
}
}