1

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

1
  • Can update your question with more details describing your use case, which is present in a comment discussion? This will allow to give a better answer if TimescaleDB can provide benefits. Or may be it is better to have another question, since the answer of 1 bit for null is good enough for the current question.
    – k_rus
    Jan 21, 2021 at 11:37

2 Answers 2

0

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.

8
  • TimescaleDB is an extension of PostgreSQL, so it doesn't require to learn new API, but will require to move data from one table to another, i.e., hypertable.
    – k_rus
    Jan 18, 2021 at 15:27
  • @k_rus Fair enough, not a trivial change either way I suppose.
    – J.D.
    Jan 18, 2021 at 15:50
  • @k_rus but does TimescaleDB helps somehow? considering the table inserts won't be ordered in time as said in the question. Jan 19, 2021 at 9:23
  • @J.D.Is the null array (float/double/numeric) also accounted in this 1 bit of data or arrays are handled differently ?? Jan 19, 2021 at 9:26
  • @user4772933 I believe all NULL data type values will account for the same amount of data usage, 1 bit.
    – J.D.
    Jan 19, 2021 at 14:19
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.