# Timescaledb Knowledge Patch > TimescaleDB - Skill: `nevaberry/timescaledb-knowledge-patch-2` (Agent Skill, multi-file: 7 files) - Install (CLI): `npx skillmds@latest add nevaberry/timescaledb-knowledge-patch-2` - Raw SKILL.md: https://api.skillmd.com/api/skills/nevaberry/timescaledb-knowledge-patch-2/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - License: MIT - Author: nevaberry (https://skillmd.com/u/nevaberry) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/nevaberry/timescaledb-knowledge-patch-2 --- # TimescaleDB Knowledge Patch Use this skill when writing, reviewing, debugging, upgrading, or operating TimescaleDB. Determine the installed TimescaleDB and PostgreSQL versions before applying version-sensitive advice. Prefer public APIs and informational views over private catalog objects. ## Reference index | Reference | Topics | |---|---| | [Columnstore and Compression](references/columnstore-and-compression.md) | Columnstore APIs, compression, sparse indexes, Direct Compress, recompression | | [Continuous Aggregates](references/continuous-aggregates.md) | Refresh, invalidation, definitions, GapFill, maintenance | | [Hypertables, Chunks, and DDL](references/hypertables-chunks-and-ddl.md) | Declarative hypertables, chunk operations, constraints, UUIDv7, triggers | | [Operations, Jobs, and Query Behavior](references/operations-jobs-and-query-behavior.md) | Jobs, configuration, publications, planner behavior, correctness fixes | | [Upgrades and Compatibility](references/upgrades-and-compatibility.md) | Breaking changes, removals, PostgreSQL support, required migrations | ## Upgrade blockers and removals ### Remove the Hypercore access method before a 2.22+ upgrade The experimental `hypercore` table access method was deprecated in 2.21 and removed in 2.22. The upgrade is blocked while any relation still uses it. Convert every such relation back to `heap` first: ```sql DO $$ DECLARE relid regclass; BEGIN FOR relid IN SELECT cl.oid FROM pg_class AS cl JOIN pg_am AS am ON am.oid = cl.relam WHERE am.amname = 'hypercore' LOOP EXECUTE format('ALTER TABLE %s SET ACCESS METHOD heap', relid); END LOOP; END $$; ``` Do not confuse the removed table access method with the supported columnstore feature. See the upgrade and columnstore references for their separate lifecycle. ### Respect PostgreSQL support boundaries - TimescaleDB 2.19 is the last minor release supporting PostgreSQL 14. - TimescaleDB 2.23 supports PostgreSQL 15, 16, 17, and 18. - TimescaleDB 2.28.x is the final minor series supporting PostgreSQL 15; TimescaleDB 2.29 supports PostgreSQL 16, 17, and 18 only. Upgrade PostgreSQL before crossing either support boundary. ### Handle sparse-index upgrade hazards Before upgrading to 2.27, drop bloom sparse indexes on compressed `int2` columns; affected indexes can omit matching rows and block the upgrade. After moving from 2.26 to 2.27, run the timescaledb-extras `utils/2.27.x-fix-composite-bloom-columns.sql` migration so 2.27 can use 2.26 composite bloom metadata. This catalog-only migration needs no recompression. Bloom indexes created before 2.24 use a build-dependent hash format and are disabled after upgrade. Decompress and recompress affected chunks, except that official APT AMD64 installations whose hash scheme did not change may enable `timescaledb.read_legacy_bloom1_v1 = on` for reads. ### Replace removed APIs and behavior - Adaptive chunking is removed as a backward-incompatible change in 2.28. - WAL-based continuous-aggregate invalidation is removed in 2.25; return to trigger-based invalidation. - `time_bucket_ng` and `_timescaledb_debug` are removed in 2.25. - `_timescaledb_functions.create_chunk_table` is removed in 2.20. - The deprecated partial continuous-aggregate format and experimental policy helpers were scheduled for removal after 2.24; migrate with `cagg_migrate` and use the Jobs API. - `_timescaledb_catalog.chunk_constraint` became a temporary compatibility view in 2.28 and will itself disappear; move integrations to informational views. ## Columnstore quick reference ### Use columnstore names Prefer `convert_to_rowstore`, `convert_to_columnstore`, `add_columnstore_policy`, and `remove_columnstore_policy`. Prefer `hypertable_columnstore_stats`, `chunk_columnstore_stats`, the corresponding `*_columnstore_settings` views, and `columnstore_settings`. Use the reloptions `timescaledb.enable_columnstore`, `timescaledb.segmentby`, and `timescaledb.orderby`. Their compression-named predecessors are deprecated for removal in the next major release. The `tsdb` reloption prefix aliases `timescaledb`, and `columnstore` aliases `enable_columnstore` where supported: ```sql ALTER TABLE metrics SET (tsdb.enable_columnstore = true); ALTER TABLE metrics SET (timescaledb.chunk_time_interval = '1 day'); ``` ### Choose current compression behavior deliberately Recompression is nonblocking by default. Set `timescaledb.enable_exclusive_locking_recompression = on` only to restore legacy exclusive locking. Boolean compression became enabled by default in 2.20; data created with the early 2.19 format must be prepared with the timescaledb-extras `utils/2.19.0-downgrade_new_compression_algorithms.sql` script before downgrading below 2.19. Composite bloom indexes are created by default where supported. Use `timescaledb.enable_composite_bloom_indexes` to control them and inspect `EXPLAIN` for batch-pruning and false-positive statistics. ### Recompress after layout changes `convert_to_columnstore(..., recompress := true)` can recompress in memory when `timescaledb.enable_in_memory_recompression` is enabled. Later behavior also supports unordered chunks and changes to `orderby` or index settings. `VACUUM FULL` can therefore include recompression work. ## Continuous-aggregate quick reference ### Batch refresh work Refresh policies process recent data first and can split work into smaller batches. The policy default `buckets_per_batch` is `10` as of 2.25. Manual refresh also accepts batching controls: ```sql 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 ); ``` Non-overlapping ranges can refresh concurrently, and forced refreshes consume their invalidations. Use `force => true` for an explicit historical backfill. ### Add an aggregate without rebuilding An aggregate can be added as a stored generated column. Existing materialized rows start as `NULL`; new rows populate it. Force-refresh the historical range to backfill: ```sql ALTER MATERIALIZED VIEW hourly_metrics ADD COLUMN max_value double precision GENERATED ALWAYS AS (max(value)) STORED; ``` `VACUUM` and `ANALYZE` on the continuous aggregate redirect to its materialization hypertable. ### Suppress invalidations only with a refresh plan `timescaledb.skip_cagg_invalidation` skips DML and DDL invalidation tracking in the current session or transaction and defaults to off. It can reduce bulk-load overhead, but changes made while it is enabled require an explicit refresh. ```sql BEGIN; SET LOCAL timescaledb.skip_cagg_invalidation = on; INSERT INTO metrics SELECT * FROM staging_metrics; COMMIT; ``` ## Hypertable and DDL quick reference ### Prefer declarative creation Create a hypertable and enable columnstore through reloptions. The partition column may be inferred where supported, and enabling columnstore declaratively also creates its policy: ```sql CREATE TABLE metrics ( time timestamptz NOT NULL, device_id bigint, value double precision ) WITH ( tsdb.hypertable, tsdb.columnstore ); ``` Use `ALTER TABLE ONLY` when reloption changes should affect future chunks only. PostgreSQL and TimescaleDB reloptions can coexist in one `ALTER TABLE SET`. ### Treat Direct Compress as an explicit path Direct Compress can write `COPY`, `INSERT`, and continuous-aggregate refresh input directly in compressed form. Its primary enablement GUCs default off. Batch sorting is safer than declaring client-sorted input; only enable a `*_client_sorted` mode when the source order is guaranteed. Earlier releases had a data-loss path for client-ordered `INSERT ... SELECT` from a compressed hypertable, fixed in 2.26. ## Query correctness quick reference - Require 2.29.2 before relying on compressed SkipScan for affected `DISTINCT` queries over mixed compressed and uncompressed data. - Require the fixed sparse-index behavior before relying on `IS NULL` min/max pushdown over compressed data. - Keep columnar filter pushdown enabled by default; disable it only for targeted diagnosis with `timescaledb.enable_columnar_scan_filter_pushdown = off`. - `time_bucket_gapfill` requires a constant timezone argument as of 2.26, although other GapFill arguments can later arrive as executor parameters. - `compressed_data_column_size` returns `bigint`; update casts and clients that assumed a narrower integer. Consult the topic references before implementing migrations or enabling experimental paths; they retain all version-specific defaults, limitations, and operational caveats.