How to identify tables in TimescaleDB that aren't optimized for performance

Here is an easy to use query to reveal the tables which are not taking advantage of the special features of TimescaleDB, specifically retention policies, automatic moving, and high performance chunking.

In Creating a retention policy in TimescaleDB after the fact and realizing it's not even a hypertable we discussed the fact that the Telegraf TimescaleDB Postgres output plugin does not properly create hypertables automatically. I wrote the following query to reveal them easily:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
pg_size_pretty(pg_total_relation_size(pt.relid)),
pt.relname,
ht.hypertable_name
from pg_catalog.pg_statio_user_tables pt
LEFT JOIN
timescaledb_information.hypertables ht
ON
pt.relname = ht.hypertable_name
WHERE
pt.relname NOT LIKE '_hyper_%_chunk' -- Filter out the underlying hypertable chunks --
AND
ht.hypertable_name IS NULL -- Select those who are lacking a hypertable so we know what we need to create--
order by pg_total_relation_size(pt.relid) desc
limit 20;

At this point you can use the truncation method to quickly make them into hypertables, e.g.:

1
2
3
4
5
BEGIN;
TRUNCATE TABLE mem;
SELECT create_hypertable('mem', 'time');
SELECT add_retention_policy('mem', INTERVAL '2 weeks');
COMMIT;