1

SELECT extversion FROM pg_extension where extname = 'timescaledb';

1

SELECT create_hypertable('public.cheese_table_name', 'time');

1
2

SELECT set_chunk_time_interval('public.cheese_table_name', INTERVAL '24 hours');
SELECT set_chunk_time_interval('public.cheese_table_name', INTERVAL '3 days');

1
2
3
4

SELECT table_name, pg_size_pretty(pg_relation_size(quote_ident(table_name))) , pg_relation_size(quote_ident(table_name)) 
FROM information_schema.tables 
WHERE table_schema = 'public' 
ORDER BY 3 

1
2
3
4
5

SELECT table_schema, table_name, pg_size_pretty(pg_relation_size('"'||table_schema||'"."'||table_name||'"')) , pg_relation_size('"'||table_schema||'"."'||table_name||'"') 
FROM information_schema.tables 
ORDER BY 4

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 5일보다 오래된 chunk 찾기 
SELECT show_chunks('cheese_table_name', older_than => INTERVAL '5 days');

-- 특정 chunk compression 하기 
SELECT compress_chunk( '<chunk_name>');
SELECT compress_chunk('_timescaledb_internal._hyper_5_53187_chunk');

-- compression 결과 확인 
SELECT * FROM chunk_compression_stats('example');

-- 기간 내 compression 안된게 있는 경우 수동 compression 
SELECT compress_chunk(i, if_not_compressed => true)
    FROM show_chunks(
        'example',
        now()::timestamp - INTERVAL '1 week',
        now()::timestamp - INTERVAL '3 weeks'
    ) i;

1

SELECT decompress_chunk('_timescaledb_internal.<chunk_name>');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- chunk 확인하기 
SELECT show_chunks('public.cheese_table_name'); 

-- policy 먼저 확인하기 
SELECT j.job_id
    FROM timescaledb_information.jobs j
    WHERE j.proc_name = 'policy_compression'
        AND j.hypertable_name = 'public.cheese_table_name'; -- 1006

-- policy 멈춰주기 
SELECT alter_job(<job_id>, scheduled => false);
SELECT alter_job(1006, scheduled => false);

-- chunk decompression 하기 
SELECT decompress_chunk('_timescaledb_internal.<chunk_name>');

-- policy 다시 활성화 시켜주기
SELECT alter_job(<job_id>, scheduled => true);
SELECT alter_job(1006, scheduled => true);

1

SELECT * FROM timescaledb_information.jobs;

1
2
3
4
5

SELECT 
  CASE 
    WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0
    ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) 
  END AS log_delay;

1
2

SELECT *
FROM timescaledb_information.jobs j

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

|job_id|application_name|schedule_interval|max_runtime|max_retries|retry_period|proc_schema|proc_name|owner|scheduled|config|next_start|hypertable_schema|hypertable_name|check_schema|check_name|

|------|----------------|-----------------|-----------|-----------|------------|-----------|---------|-----|---------|------|----------|-----------------|---------------|------------|----------|

|1|Telemetry Reporter [1]|24:00:00|00:01:40|-1|01:00:00|_timescaledb_internal|policy_telemetry|postgres|true||2022-12-18 08:02:08.220 +0900|||||

|1001|Refresh Continuous Aggregate Policy [1001]|01:00:00|00:00:00|-1|01:00:00|_timescaledb_internal|policy_refresh_continuous_aggregate|nftbankci|true|{"end_offset": "01:00:00", "start_offset": "3 days", "mat_hypertable_id": 7}|2022-12-17 12:48:56.433 +0900|_timescaledb_internal|_materialized_hypertable_7|_timescaledb_internal|policy_refresh_continuous_aggregate_check|

|1003|Compression Policy [1003]|35 days|00:00:00|-1|01:00:00|_timescaledb_internal|policy_compression|nftbankci|true|{"hypertable_id": 7, "compress_after": "5 days"}|2023-01-05 20:39:47.486 +0900|_timescaledb_internal|_materialized_hypertable_7|_timescaledb_internal|policy_compression_check|

|1011|Retention Policy [1011]|1 day|00:05:00|-1|00:05:00|_timescaledb_internal|policy_retention|nftbankci|true|{"drop_after": "90 days", "hypertable_id": 20}|2022-12-17 16:55:15.014 +0900|_timescaledb_internal|_materialized_hypertable_20|_timescaledb_internal|policy_retention_check|

1
2

SELECT delete_job(<job id>);
SELECT delete_job(1000);