The MCP server and the skills library

DROP INDEX needs ACCESS EXCLUSIVE on the table. The drop itself is instant, but the lock request queues behind any running query touching the table, and every new query then queues behind the lock request. One slow SELECT turns an instant drop into a stall for all traffic.

DROP INDEX idx_orders_region;

Postgres indexes the referenced side of a foreign key (it must be unique) but never the referencing side. Without that index, every UPDATE or DELETE on the parent table sequential-scans the child table to enforce the constraint, fine in dev, a scan-per-row regression in production.

ALTER TABLE orders ADD CONSTRAINT fk FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;

A table without a primary key (or any unique constraint) works until it doesn't: logical replication rejects UPDATE/DELETE without a replica identity, targeted row operations degrade to full scans, and maintenance tooling that addresses rows by key can't help you. Adding a key later on a populated table is exactly the migration this tool exists to warn about.

CREATE TABLE audit_log (entry text, created_at timestamptz);

Unlogged tables skip WAL: writes are faster, but the table is truncated to empty on crash recovery and its contents never reach physical replicas. This will work, until the first failover or crash quietly empties it.

CREATE UNLOGGED TABLE session_cache (id bigint PRIMARY KEY);

CREATE TABLE AS runs its query to completion inside the migration's transaction: the copy's duration scales with the source data, the transaction stays open throughout (holding back vacuum and locks), and the copied rows are frozen at migration time, usually a backfill pretending to be schema.

CREATE TABLE orders_archive AS SELECT * FROM orders WHERE closed_at < '2024-01-01';

RESTART rewinds a sequence regardless of what values are already in use. If any existing row holds an id at or above the restart point, inserts start failing with duplicate-key errors at some unpredictable later moment.

ALTER SEQUENCE orders_id_seq RESTART WITH 1;

An ACCESS EXCLUSIVE request queues behind any long-running query, and every new query queues behind it. Without SET lock_timeout, one slow report turns a metadata-only ALTER into a site-wide stall of unbounded length. A short lock_timeout makes the migration fail fast and retryable instead.

ALTER TABLE orders ADD COLUMN region text;

Each ALTER TABLE statement acquires its own ACCESS EXCLUSIVE lock, queueing behind traffic every single time. One ALTER TABLE with comma-separated actions acquires the lock once and does all the work under it.

ALTER TABLE orders ADD COLUMN a text;
ALTER TABLE orders ADD COLUMN b text;

VALIDATE CONSTRAINT deliberately takes only SHARE UPDATE EXCLUSIVE so it can run without blocking, but in the same transaction as ACCESS EXCLUSIVE DDL, the exclusive locks are held while the validation scans the whole table. The non-blocking scan drags the blocking locks out with it.

ALTER TABLE orders ADD COLUMN region text;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;

REPLICA IDENTITY FULL writes the complete old row into WAL for every UPDATE and DELETE, and logical decoding must compare entire rows downstream. On write-heavy tables that is a permanent WAL and CPU tax; an index-based replica identity carries only the key.

ALTER TABLE orders REPLICA IDENTITY FULL;

With autovacuum_enabled = false, dead tuples accumulate unchecked (bloat, degrading scans) and the table still gets aggressive wraparound vacuums eventually, at the worst possible moment. Tuning autovacuum beats disabling it in almost every case.

ALTER TABLE orders SET (autovacuum_enabled = false);

RENAME VALUE changes the label in place: every app instance still deployed with the old label gets an invalid-enum error on insert or comparison until the rollout completes, the enum flavor of the rename deploy-window break.

ALTER TYPE order_status RENAME VALUE 'pending' TO 'awaiting';

Plain timestamp stores wall-clock digits with no zone: the moment it crosses a server, client, or DST boundary, the same value means different instants. timestamptz stores an absolute instant and costs the same 8 bytes.

CREATE TABLE events (occurred_at timestamp);

char(n) space-pads every value to n and strips the padding in surprising places, comparisons and lengths behave differently from every other string type, with no storage or speed benefit over text in Postgres.

CREATE TABLE users (country_code char(2));

The money type's meaning depends on the server's lc_monetary locale: no currency is stored, fractional precision is fixed, and dumping/restoring across locales reinterprets the values. numeric plus an explicit currency column is the boring, correct answer.

CREATE TABLE orders (total money);

serial is a legacy macro: the sequence it creates is only loosely attached, permissions and ownership drift from the column, and anyone can still insert arbitrary values. GENERATED ... AS IDENTITY is the standard replacement with tighter semantics.

CREATE TABLE users (id bigserial PRIMARY KEY);

An int primary key tops out at 2,147,483,647. Tables that get there discover it as an outage, and the fix, retyping the primary key and every referencing column, is one of the worst migrations there is. bigint costs 4 more bytes now and removes the cliff.

CREATE TABLE orders (id serial PRIMARY KEY);

An unbounded INSERT ... SELECT runs to completion inside the migration's transaction: its duration scales with the source data, the transaction stays open the whole time (holding locks and holding back vacuum), and a failure rolls back everything, a backfill wearing a migration's clothes.

INSERT INTO orders_archive SELECT * FROM orders WHERE closed_at < '2024-01-01';

A B-tree index on (a) is fully covered by an existing index on (a, b): the planner can use the wider index for every query the narrow one serves. The extra index buys nothing and costs a write on every INSERT, UPDATE and DELETE, plus disk, plus vacuum time, forever.

CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

A B-tree whose leading column has two values splits the table in half at best. The planner will usually ignore it in favor of a sequential scan, and when it does use it the index returns half the table. Either way every write pays to maintain it. The useful form is a partial index, WHERE is_active, that only contains the rows you actually look up.

CREATE INDEX idx_users_active ON users (is_active);

A B-tree on a jsonb, array, or tsvector column only serves equality and ordering on the whole value, never the containment (@>), key-existence (?), or text-search (@@) operators those types exist for. The queries that motivated the index still sequential-scan, and the index (often huge, since it stores whole documents) taxes every write.

CREATE INDEX idx_events_payload ON events (payload);

Postgres never indexes the referencing side of a foreign key itself; the index someone added later is what keeps parent-side UPDATE and DELETE cheap. Drop it and every change to the referenced table sequential-scans the child table to enforce the constraint, the same regression BV017 warns about, arriving through the back door.

DROP INDEX CONCURRENTLY idx_orders_user;

Every index on a table is maintained on every INSERT, on every DELETE, and on every UPDATE that touches an indexed column (or cannot use a HOT update). On a table taking sustained write traffic, the ninth index is not free: it is another page write, more WAL, and more vacuum work on the hottest path in the system. The right response is usually to drop an index nobody uses before adding one.

CREATE INDEX CONCURRENTLY idx_events_region ON events (region);

A B-tree answers a query by walking leading columns with equality first, then scanning one range. With the range column first, (created_at, status) for WHERE status = 'x' AND created_at > …, the index has to scan every row in the date range and filter status afterwards. Put the equality column first and the scan shrinks to exactly the matching rows. Only fires when a query in the migration itself shows that shape.

CREATE INDEX idx_orders_created_status ON orders (created_at, status);
UPDATE orders SET archived = true WHERE status = 'closed' AND created_at < '2024-01-01';

A partial index only serves queries whose WHERE clause provably implies its predicate; an expression index only serves queries that use the identical expression. An index on lower(email) does nothing for WHERE email = …, and an index WHERE status = 'archived' does nothing for WHERE status = 'active'. When the migration itself contains such a query, the mismatch is visible before it ships.

CREATE INDEX idx_users_lower_email ON users (lower(email));
UPDATE users SET verified = true WHERE email = 'a@example.com';

An UPDATE or DELETE with a WHERE clause looks bounded, but if no index leads with any of the filtered columns, Postgres reads the whole table to find the rows, once per statement, and once per batch when the backfill is looped. On a large table that is minutes of I/O and a long-held lock per pass. Only fires with a live snapshot proving both the size and the absence of the index.

UPDATE orders SET legacy = false WHERE legacy_flag = 'y';

SET STATISTICS 0 tells ANALYZE to collect nothing for the column. From then on the planner estimates every predicate on it with hard-coded defaults (selectivity 0.5 for equality, 1/3 for ranges), row estimates go wrong by orders of magnitude, and with them join order, index choice, and memory sizing for every query that touches the column.

ALTER TABLE orders ALTER COLUMN region SET STATISTICS 0;

autovacuum_vacuum_scale_factor is the fraction of the table that must change before autovacuum touches it; the server default is 0.2. At 0.5 or above, a table has to accumulate dead rows equal to half its size before cleanup starts, bloat, slower scans, and index bloat pile up in the meantime, and the eventual vacuum is a long one. The same holds for the analyze scale factor and stale planner statistics. Escalates when the live schema shows the table is large or hot.

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.8);

Adding a column is normally a schema-only change in SQLite. With a CHECK constraint (or NOT NULL on a generated column) the whole table is scanned to verify existing rows first, under the write lock, proportional to the table's size.

ALTER TABLE orders ADD COLUMN priority INTEGER DEFAULT 0 CHECK (priority BETWEEN 0 AND 9);

Turning foreign keys back on does not validate what happened while they were off. Step 10 of the SQLite rebuild recipe is PRAGMA foreign_key_check before COMMIT, without it, an orphaned row created during the rebuild is discovered by the first user query that joins on it.

PRAGMA foreign_keys = OFF;
BEGIN;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;
PRAGMA foreign_keys = ON;

SQLite has no CONCURRENTLY: CREATE INDEX scans the table and writes the index under the database's write lock, and every other writer waits (or gets SQLITE_BUSY) for the duration. Fine on a small table; on a large one this is the moment the application stalls.

CREATE INDEX orders_status_idx ON orders (status);

Only a column declared exactly INTEGER PRIMARY KEY becomes an alias for the rowid. INT PRIMARY KEY (or BIGINT, or INTEGER PRIMARY KEY DESC) is an ordinary column with a separate unique index: every lookup by primary key is an extra B-tree hop, the value is not the rowid, and VACUUM may renumber the real rowid underneath it.

CREATE TABLE orders (id INT PRIMARY KEY, qty INTEGER);

AUTOINCREMENT makes every insert also update the sqlite_sequence table, and the SQLite manual itself says it should be avoided unless strictly needed. Its one guarantee, a deleted rowid is never reused, is rarely what a migration author meant.

CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, qty INTEGER);

Without STRICT, SQLite's column types are suggestions: a TEXT value goes into an INTEGER column, a blob into a date, and nothing complains until the application reads it back. STRICT (3.37+) makes the declared type an actual constraint, and it only exists as a CREATE TABLE option, it cannot be added later without a rebuild.

CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER, note TEXT);

SQLite indexes the parent side of a foreign key (the referenced key must be unique) but never the child side. Every DELETE or UPDATE of a parent row then scans the whole child table to look for references, with foreign keys on, deleting one user scans every order. The manual's advice is an index on every child key column.

CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id));

In a rowid table, a PRIMARY KEY column that is not INTEGER does not imply NOT NULL, a long-standing SQLite bug kept for compatibility, so NULL keys are accepted and the uniqueness the key promised is gone (every NULL is distinct). In a WITHOUT ROWID table the same NULL is refused at insert time instead, so the schema behaves differently depending on one table option.

CREATE TABLE orders (ref TEXT PRIMARY KEY, qty INTEGER);

A plain BEGIN is DEFERRED: it takes no lock until the first write, then tries to upgrade a read lock to a write lock. If another connection holds the write lock at that moment the upgrade fails immediately with SQLITE_BUSY, busy_timeout does not apply to a lock upgrade, and the migration aborts part way through its reads.

BEGIN;
ALTER TABLE orders ADD COLUMN region TEXT;
COMMIT;

An index whose columns are a leading prefix of another index on the same table, a duplicate of one, or an index on the INTEGER PRIMARY KEY (the rowid, which is the table's own B-tree) changes no query plan, SQLite already answers those lookups from the wider index or the table itself. It still costs a write on every insert, update and delete, and space in the file.

CREATE INDEX orders_user ON orders (user_id);
CREATE INDEX orders_user_created ON orders (user_id, created_at);

In a table rebuild the copy is the expensive step. Creating the new table's indexes before INSERT ... SELECT makes SQLite maintain every index row by row during the copy, random B-tree inserts for each, instead of one sorted build per index afterwards. The manual's own recipe creates the indexes after the copy.

CREATE TABLE orders_new (id INTEGER PRIMARY KEY, user_id INTEGER, qty INTEGER) STRICT;
CREATE INDEX orders_new_user ON orders_new (user_id);
INSERT INTO orders_new SELECT id, user_id, qty FROM orders;

On a rowid table a TEXT or BLOB primary key is not the table's key: rows live in a B-tree keyed by the hidden rowid, and the primary key is a separate unique index. Every lookup by key walks the index and then the table, and every row is stored twice over (key in the index, key in the row). WITHOUT ROWID makes the primary key the table's own B-tree, the manual recommends it exactly for this shape.

CREATE TABLE sessions (token TEXT PRIMARY KEY NOT NULL, user_id INTEGER) STRICT;

journal_mode is stored in the database file: a migration that sets DELETE, TRUNCATE or PERSIST changes every future connection, not just the runner's. In those modes a writer blocks readers and readers block the writer for the length of each transaction; WAL lets readers proceed during a write and is the mode the application almost certainly wants.

PRAGMA journal_mode = DELETE;

cache_size, synchronous, temp_store, mmap_size and busy_timeout live on the connection that set them. In a migration they tune the runner's connection for a few seconds and reach the application never, and synchronous=OFF removes durability for exactly the connection doing the schema change.

PRAGMA synchronous = OFF;
PRAGMA mmap_size = 268435456;
ALTER TABLE orders ADD COLUMN region TEXT;