I am storing in postgres timeseries data, the table grows quick in size.
I started of first by having one point being one row, like this
create table item_value
(
item_id uuid default uuid_generate_v4() not null,
value double precision,
timestamp timestamp with time zone
)
The disadvantage of this, I saw that it grows in size very quick and it takes up a lot of disk space
Then I went for version 2, where I store values in a jsonb column, on a hour one basis. So if my lowest data point is one minute, then I have max 60 values in
create table item_value
(
host_item_id uuid default uuid_generate_v4() not null,
values jsonb,
timestamp timestamp with time zone,
created_at timestamp with time zone not null
)
both tables have a unique index on item_id and timestamp.
create unique index item_value_host_item_id_timestamp_idx
on item_value (host_item_id, timestamp);
This helps with the disk space and it's also faster to search for specific values, but now I'm afraid that it might hurt performance when autovacuum occurs, as I rely on upsert to insert the rows for specific hours, so when a row gets updated a dead tuple also gets created.
What option would be better or what other more performant way can I design my table to store my timeseries data?