TimescaleDB Knowledge Patch Skill

TimescaleDB Knowledge Patch

Use this skill when designing, migrating, or debugging TimescaleDB schemas, columnstore storage, continuous aggregates, chunk operations, or background jobs. Check the installed extension and PostgreSQL versions before applying version-sensitive SQL. Prefer public informational views and APIs over private catalog objects.

Reference index

| Reference | Topics | | --- | --- | | Columnstore and ingest | Columnstore terminology, compression, sparse indexes, Direct Compress, recompression, and scan correctness | | Continuous aggregates | Refresh batching, invalidations, functions, rewrites, GapFill, and maintenance | | Hypertables and chunks | Declarative DDL, dimensions, triggers, chunk split/merge/attach, UUIDv7, and publications | | Jobs and observability | Job names, history retention, memory, cancellation, and progress reporting | | Upgrades and compatibility | PostgreSQL support, blocked upgrades, removed APIs, image migration, and correctness fixes |

Start with upgrade blockers

Before changing extension versions:

Find relations that still use the experimental hypercore table access method. Convert them to heap; upgrades to 2.22 or later reject them.
Match PostgreSQL to the target TimescaleDB release. PostgreSQL 14 ends with 2.19, and PostgreSQL 15 ends with the 2.28.x series.
Remove sparse bloom indexes on compressed int2 columns before a 2.27 upgrade. The upgrade is intentionally blocked while affected indexes exist.
Repair composite bloom metadata created by 2.26 before expecting 2.27 to use it. Run the provided 2.27.x-fix-composite-bloom-columns.sql utility; it is catalog-only and does not require recompression.
Rebuild bloom sparse indexes on chunks compressed before 2.24 unless the documented AMD64 APT-package exception applies.
Remove dependencies on adaptive chunking before 2.28, where that facility is gone.
Replace WAL-based continuous-aggregate invalidation before 2.25, where the experimental path is removed.

See Upgrades and compatibility for the complete preflight, downgrade, and removed-API guidance.

Use current columnstore terminology

Write new code with the columnstore API names:

| Deprecated compression name | Columnstore name | | --- | --- | | compress_chunk | convert_to_columnstore | | decompress_chunk | convert_to_rowstore | | add_compression_policy | add_columnstore_policy | | remove_compression_policy | remove_columnstore_policy | | timescaledb.compress | timescaledb.enable_columnstore | | timescaledb.compress_segmentby | timescaledb.segmentby | | timescaledb.compress_orderby | timescaledb.orderby |

The stats and settings views were renamed similarly. Treat the compression aliases as compatibility shims scheduled for removal in a major release. The short tsdb reloption prefix is accepted in WITH and SET clauses.

Create declarative hypertables

The declarative API can enable both hypertabling and columnstore storage at creation time:

CREATE TABLE metrics (
    time timestamptz NOT NULL,
    device_id text,
    value double precision
) WITH (
    tsdb.hypertable,
    tsdb.partition_column = 'time',
    tsdb.columnstore
);

The partition-column option can be omitted in newer definitions. Enabling columnstore in declarative DDL also creates the columnstore policy. Use ALTER TABLE ONLY when a reloption change should apply only to future chunks.

Configure continuous-aggregate refreshes

Refresh work can be split into smaller transactions and ordered newest-first. For a manual refresh:

CALL refresh_continuous_aggregate(
    'hourly_metrics',
    '2026-01-01'::timestamptz,
    '2026-06-01'::timestamptz,
    buckets_per_batch => 24,
    max_batches_per_execution => 5,
    refresh_newest_first => true
);

Policy refreshes default to incremental operation, and non-overlapping ranges can refresh concurrently. Policy buckets_per_batch defaults to 10. Use the optional force argument when a range must be recomputed; a forced refresh also consumes its invalidations.

When bulk loading with timescaledb.skip_cagg_invalidation = on, explicitly refresh every affected aggregate afterward. The setting deliberately avoids recording changes and defaults to off.

Add aggregates without rebuilding a view

Add an aggregate as a stored generated column, then force-refresh history:

ALTER MATERIALIZED VIEW hourly_metrics
ADD COLUMN max_value double precision
GENERATED ALWAYS AS (max(value)) STORED;

CALL refresh_continuous_aggregate(
    'hourly_metrics',
    '2025-01-01'::timestamptz,
    '2026-01-01'::timestamptz,
    force => true
);

Existing materialized rows are NULL until refreshed; newly materialized rows populate the column immediately.

Treat Direct Compress as guarded behavior

Direct Compress can write COPY, INSERT, and continuous-aggregate refresh input directly to columnstore. Each path has its own enablement GUC. Batch sorting is the safe default; declare client-sorted input only when its ordering is guaranteed.

Do not use client-ordered Direct Compress for INSERT ... SELECT from a compressed hypertable on versions before the 2.26 fix, because that combination has a data-loss path. Tuple-sort limits separately bound COPY and INSERT memory use. Automatic segmentby selection is deferred until flush in newer Direct Compress behavior.

Handle sparse-index transitions

Columnstore chunks create bloom sparse indexes automatically. Composite bloom indexes are enabled by default and support multi-column pushdown. Keep these version-specific hazards in upgrade and query plans:

Old bloom hashing can silently miss matches after changing package builds; decompress and recompress affected pre-2.24 chunks.
Bloom indexes on compressed int2 columns can omit matching rows and block a 2.27 upgrade until dropped.
Earlier min/max sparse-index pushdown can return wrong results for IS NULL; rely on the 2.29.2 correction before using that predicate path.
Compressed SkipScan before 2.29.2 can lose uncompressed rows when sort and distinct keys differ.

Use EXPLAIN to inspect batch pruning, bloom false positives, and pushed-down filters. See Columnstore and ingest for GUCs and repair procedures.

Choose recompression and locking deliberately

Recompression is nonblocking by default. Enable the legacy exclusive-locking GUC only when that behavior is required. convert_to_columnstore(..., recompress := true) can recompress entirely in memory when its GUC is enabled; newer behavior also accepts unordered chunks and changed order/index settings. VACUUM FULL may therefore include recompression work.

Compression can fall back from TRUNCATE to DELETE when locks are unavailable, can limit batch size, and emits poor-ratio warnings by default. Plan operational headroom for these choices.

Validate query semantics after upgrades

A time_bucket_gapfill timezone must be constant.
DATE input rejects sub-day time_bucket offsets.
Chunk intervals cannot be negative, and continuous-aggregate bucket widths must be positive.
UUIDv7 can be the partition key and can be passed to time_bucket.
Cross-type comparisons against partition columns are safe in corrected versions; verify plans when older servers are still present.
Compressed column WHERE comparisons and vectorized aggregates follow PostgreSQL NaN semantics.

Keep administration on public surfaces

The database owner can configure hypertables and policies. VACUUM and ANALYZE accept a continuous aggregate and redirect to its materialization hypertable. Avoid building integrations on _timescaledb_catalog objects: chunk_constraint is already only a temporary compatibility view, and the background-job catalog has moved. Prefer informational views and supported job APIs.

Agent Skills: TimescaleDB Knowledge Patch

Install this agent skill to your local

Skill Files