warn-timescale-hypertable-ddl
DDL on a TimescaleDB hypertable propagates to every chunk, so its cost scales with chunk count.
- operation
- Tables
- lock taken
- ACCESS EXCLUSIVE
- remediation
- Manual rewrite
- category
- Extensions
- Blocks writes
- Blocks reads
What triggers it
ctx.tableExtensions.isHypertable must be true, which only the TimescaleDB catalog can establish. Then either an IndexStmt — reported whether or not concurrent is set, with a different lead sentence for each, since TimescaleDB does not support CREATE INDEX CONCURRENTLY on a hypertable at all — or an AlterTableStmt or RenameStmt, which get the chunk fan-out message. chunkCount and compressionEnabled come from the same catalog row and only shape wording.
What does not
Every run without --database-url: hypertable membership is not in the migration file, and a create_hypertable() call in some other file is out of scope by design (requiresDatabaseUrl is set). Statement shapes other than IndexStmt, AlterTableStmt and RenameStmt — a DELETE on a hypertable belongs to MP106, not here. Ordinary tables and Citus or partman tables, which carry different flags.
Where it applies
Applies to every PostgreSQL version MigrationPilot targets. It only fires on tables managed by TimescaleDB. It needs --database-url: without a connection it has nothing to read and stays silent.
Hypertable membership, chunk count and compression state are read from timescaledb_information.hypertables, with _timescaledb_catalog.hypertable as a fallback on versions where the view columns differ.
The lock, and what it blocks
For the ALTER TABLE branch, ACCESS EXCLUSIVE on the hypertable and on every chunk, all held until the statement commits, so the blocking window is set by the slowest chunk rather than by the statement. The CREATE INDEX branch takes SHARE across the whole set inside one transaction, unless WITH (timescaledb.transaction_per_chunk) splits it so only one chunk is blocked at a time.
Why it matters
A hypertable is a facade. The data lives in chunks, each a real table, and TimescaleDB applies schema changes to the hypertable and to every one of them — so a statement that reads like one table's worth of work takes locks across the whole set and runs until the slowest chunk is done. Index creation has a specific catch: TimescaleDB does not support CREATE INDEX CONCURRENTLY on a hypertable at all, so that statement fails rather than running slowly. WITH (timescaledb.transaction_per_chunk) is the documented alternative.
Unsafe, and safe
Flagged
-- metrics is a hypertable with 420 chunks CREATE INDEX CONCURRENTLY idx_metrics_device ON metrics (device_id); -- ERROR: CREATE INDEX CONCURRENTLY is not supported on hypertables
Safe alternative
-- Check the fan-out before writing the DDL: the chunk count is what -- the statement actually costs. SELECT hypertable_name, num_chunks, compression_enabled FROM timescaledb_information.hypertables WHERE hypertable_name = 'metrics';
Deploy and transaction boundaries
WITH (timescaledb.transaction_per_chunk) commits per chunk, so it cannot run inside the migration transaction, and a failure partway leaves some chunks holding the index while the hypertable index is marked invalid. It also does not work for CREATE UNIQUE INDEX.
What it assumes
The chunk count is whatever the catalog held at analysis time; on an actively ingesting hypertable there are more chunks by the time the migration runs. The rule does not separate an ALTER that only touches the catalog from one that rewrites every chunk — both get the same finding — and it cannot see chunk sizes, so 2 chunks of a terabyte each and 2,000 tiny ones are the same number to it. The claim that CONCURRENTLY is unsupported comes from TimescaleDB's documented behaviour, not from a version check against the installed extension.
This rule reads live catalogue state, so it says nothing at all without --database-url. That is the trade: no connection, no guess.
What the CLI prints
⚠ [MP105] WARNING (line 2)
CREATE INDEX CONCURRENTLY is not supported on hypertables. TimescaleDB documents WITH (timescaledb.transaction_per_chunk) as the alternative. This statement targets hypertable "metrics".
Safe alternative:
-- Build chunk by chunk so only one chunk is blocked at a time:
CREATE INDEX idx_metrics_device ON metrics (...)
WITH (timescaledb.transaction_per_chunk);
-- Note: this is not supported for CREATE UNIQUE INDEX, and if it fails partway
-- through, some chunks keep the index and the hypertable's index is marked invalid.
-- A unique index on a hypertable must also include all partitioning columns.
Why: A hypertable stores its data in chunks, each a real table. TimescaleDB applies schema changes to the hypertable and to every chunk, so an ALTER that looks like one statement takes locks across the whole set and runs for as long as the slowest chunk. Index creation has its own catch: TimescaleDB does not support CREATE INDEX CONCURRENTLY on a hypertable, and offers WITH (timescaledb.transaction_per_chunk) instead, which builds chunk by chunk in separate transactions so only one chunk is blocked at a time.
Docs: https://migrationpilot.dev/rules/mp105Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here. The catalogue figures come from the production context this rule documents.
Turning it off
For one statement, put a comment on the line before it:
-- migrationpilot-disable MP105 CREATE INDEX CONCURRENTLY idx_metrics_device ON metrics (device_id);
For the whole project, in .migrationpilotrc.yml — by name or by id:
rules:
MP105: false
# or keep it, and downgrade it
rules:
MP105:
severity: warningTry it
Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.
Run MP105 in the playground