Postgres timeseries database table design

Question

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?

Answer 1

Since time series data can grow quickly, storage efficiency is important. My advice would be to create a table with items with {id number, description etc.} and a table with measurements with {item_id, value, clock}, all numeric items. Store clock as unixtime.

This can handle many samples for many items.

If you want other data types, create a different table do the other datatype, like measuremants_text for text values, measurements_float for float values etc.

For a very nice example you can look at the schema definition of the zabbix monitoring application. They do exactly this and that scales very well.

Sorry to see you can not use timescaleDB in rds.