Optimizing table for timeseries Postgres data table

Question

I have the below table which maintains a timeseries result. The row only becomes relevant when the signal is true, When signal is false, it just marks that for that particular timestamp we got a result but it is not a valid one, so the res and other columns just contains null values. When signal is null, it marks that we are yet to receive result for this timestamp. The signal is very sparse in nature, it is only true for maybe less than 7% of the records. Also the inserts made to this table are not ordered according to timestamp, older dates could arrive at later time.

CREATE TABLE public.res
(
 pid integer NOT NULL,
 aid integer NOT NULL,
 cid integer NOT NULL,
"time" timestamp without time zone NOT NULL,
 signal boolean,
 price numeric,
 res double precision[] NOT NULL,
 ...<Many more columns of numeric/numeric array data types>
 CONSTRAINT res_pkey PRIMARY KEY (pid, aid, cid, "time")
)

This table can contains millions of records and is growing exponentially as my database is growing. I want to optimize this table. So have following questions

• Is each row the same size? Or since the row only makes sense if Signal is true, can it be dynamically sized? Hence keeping the overall size of the table low? A min row size would contain (pid,aid,cid,time,signal,price) and the max will additionally contain (res and the remaining columns)? Is it possible to this in Postgres with its dataTypes? I do not want to create separate tables because run time joins could be very expensive when there are millions of records.

• This SO answer says "In effect NULL storage is absolutely free for tables up to 8 columns." , I have many more columns

• Any other suggestion that you might have to deal with such problems?

• I read about timescaleDB, but since the records do not get inserted in order of timestamps does it has any advantage over Postgres in this usecase?

Thanks

Answer 1

If you were seriously considering switching database systems, I could tell you Microsoft SQL Server already has an out of the box feature for sparse columns that fits your use case we'll. But my recommendation would be to not look to change database systems only just to optimize data storage. PostgreSQL is a very capable database system itself.

To answer your question, NULL values only take up 1 bit of data regardless of how many columns there are, and therefore is very lightweight so it reflects your sparseness accurately.

Answer 2

As @J.D. answered NULL values are cheap in PostgreSQL. So it should be good for your use case.

Regarding TimescaleDB: TimescaleDB is an extension of PostgreSQL. The difference to PostgreSQL is that TimescaleDB automatically partitions the table, called hypertable, into child tables, called chunks. Chunks are still PostgreSQL tables, so storing NULL values is the same.

Partitioning to chunks is done by time ranges, which benefits timeseries data, when the most of data are inserted into current time range. TimescaleDB supports insertion of old data and backfilling. However, inserting data always randomly in time will not benefit TimescaleDB.

Other advantages of TimescaleDB for timeseries workload available under the community license:

• Timeseries functions such as time_bucket, time_bucket_gapfill;
• Continuous aggregates, which allows to continuously materialize aggregates query over a hypertable;
• Compression, which reduces the data size considerably. However, the current implementation doesn't allow to insert new data into compressed chunk, which might not be acceptable for the use case int the question. To insert data it is necessary to decompress and then compress again.
• Retention policies, which allows automatically remove old data.
• Support for distributed hypertables so different chunks are stored on different TimescaleDB instances. The current version of it has many limitations.