Migrate Postgres Tables To Hypertables

-- Enable extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Convert to hypertable (locks table)
SELECT create_hypertable(
    'your_table_name',
    'timestamp_column',
    chunk_time_interval => INTERVAL '7 days',
    if_not_exists => TRUE
);

-- Configure compression
ALTER TABLE your_table_name SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id',
    timescaledb.orderby = 'timestamp DESC',
    timescaledb.sparse_index = 'minmax(value_1),...'
);

-- Adjust `after` parameter based on update patterns
CALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');

-- 1. Create new hypertable
CREATE TABLE your_table_name_new (LIKE your_table_name INCLUDING ALL);

-- 2. Convert to hypertable
SELECT create_hypertable('your_table_name_new', 'timestamp_column');

-- 3. Configure compression
ALTER TABLE your_table_name_new SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id',
    timescaledb.orderby = 'timestamp DESC'
);

-- 4. Migrate data in batches
INSERT INTO your_table_name_new
SELECT * FROM your_table_name
WHERE timestamp_column >= '2024-01-01' AND timestamp_column < '2024-02-01';
-- Repeat for each time range

-- 4. Enter maintenance window and do the following:

-- 5. Pause modification of the old table.

-- 6. Copy over the most recent data from the old table to the new table.

-- 7. Swap tables
BEGIN;
ALTER TABLE your_table_name RENAME TO your_table_name_old;
ALTER TABLE your_table_name_new RENAME TO your_table_name;
COMMIT;

-- 8. Exit maintenance window.

-- 9. (sometime much later) Drop old table after validation
-- DROP TABLE your_table_name_old;

-- Check foreign keys
SELECT conname, confrelid::regclass as referenced_table
FROM pg_constraint
WHERE (conrelid = 'your_table_name'::regclass
    OR confrelid = 'your_table_name'::regclass)
  AND contype = 'f';

-- Rough estimate: ~75k rows/second
SELECT
    pg_size_pretty(pg_total_relation_size(tablename)) as size,
    n_live_tup as rows,
    ROUND(n_live_tup / 75000.0 / 60, 1) as estimated_minutes
FROM pg_stat_user_tables
WHERE tablename = 'your_table_name';

-- View chunks and compression
SELECT
    chunk_name,
    pg_size_pretty(total_bytes) as size,
    pg_size_pretty(compressed_total_bytes) as compressed_size,
    ROUND((total_bytes - compressed_total_bytes::numeric) / total_bytes * 100, 1) as compression_pct,
    range_start,
    range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'your_table_name'
ORDER BY range_start DESC;

-- 1. Time-range query (should show chunk exclusion)
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*), AVG(value)
FROM your_table_name
WHERE timestamp >= NOW() - INTERVAL '1 day';

-- 2. Entity + time query (benefits from segment_by)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM your_table_name
WHERE entity_id = 'X' AND timestamp >= NOW() - INTERVAL '1 week';

-- 3. Aggregation (benefits from columnstore)
EXPLAIN (ANALYZE, BUFFERS)
SELECT DATE_TRUNC('hour', timestamp), entity_id, COUNT(*), AVG(value)
FROM your_table_name
WHERE timestamp >= NOW() - INTERVAL '1 month'
GROUP BY 1, 2;

-- Monitor compression effectiveness
SELECT
    hypertable_name,
    pg_size_pretty(total_bytes) as total_size,
    pg_size_pretty(compressed_total_bytes) as compressed_size,
    ROUND(compressed_total_bytes::numeric / total_bytes * 100, 1) as compressed_pct_of_total,
    ROUND((uncompressed_total_bytes - compressed_total_bytes::numeric) /
          uncompressed_total_bytes * 100, 1) as compression_ratio_pct
FROM timescaledb_information.hypertables
WHERE hypertable_name = 'your_table_name';

-- Verify chunks are being excluded
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM your_table_name
WHERE timestamp >= '2024-01-01' AND timestamp < '2024-01-02';
-- Look for "Chunks excluded during startup: X"

-- Get newest compressed chunk name
SELECT chunk_name FROM timescaledb_information.chunks
WHERE hypertable_name = 'your_table_name'
  AND compressed_total_bytes IS NOT NULL
ORDER BY range_start DESC LIMIT 1;

-- Analyze segment distribution
SELECT segment_by_column, COUNT(*) as rows_per_segment
FROM _timescaledb_internal._hyper_X_Y_chunk  -- Use actual chunk name
GROUP BY 1 ORDER BY 2 DESC;

SELECT
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan
FROM pg_stat_user_indexes
WHERE tablename LIKE '%your_table_name%'
ORDER BY idx_scan DESC;

-- Monitor chunk compression status
CREATE OR REPLACE VIEW hypertable_compression_status AS
SELECT
    h.hypertable_name,
    COUNT(c.chunk_name) as total_chunks,
    COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL) as compressed_chunks,
    ROUND(
        COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL)::numeric /
        COUNT(c.chunk_name) * 100, 1
    ) as compression_coverage_pct,
    pg_size_pretty(SUM(c.total_bytes)) as total_size,
    pg_size_pretty(SUM(c.compressed_total_bytes)) as compressed_size
FROM timescaledb_information.hypertables h
LEFT JOIN timescaledb_information.chunks c ON h.hypertable_name = c.hypertable_name
GROUP BY h.hypertable_name;

-- Query this view regularly to monitor compression progress
SELECT * FROM hypertable_compression_status
WHERE hypertable_name = 'your_table_name';