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?