How to store a large table with the same values?

Question

PostgreSQL

Hi all. Need help from more experienced colleagues.

You need to create a table where a large number of records with the same values ​​will be entered.

There can be a dozen different values, but there are millions of records.

As I understand it, if you create a simple table, then it will bloat on disk for nothing.

How can you create such a table (or an index for it) so that it takes up minimal space on disk?

The table will only be populated with data. Sampling will be done infrequently and manually when necessary. The sampling rate is not important.

Thank you!

Answer 1

For example, like this: a table of values ​​reference and a main table:

create table datavalues (
  id serial primary key,
  	value text -- json or any suitable type
);

create table manydata (
  id bigserial primary key,
  	value_id int,
  	inserted_at timestamp with time zone default current_timestamp,
  	updated_at timestamp,
    foreign key (value_id) references datavalues(id)
);
create index on manydata(value_id);

PostgreSQL fiddle

Answer 2

And what's so terrible about millions of records? As you speak - sampling speed is not basic.
If you are worried about the place - use some kind of compression method .

Answer 3

A million lines is nothing to worry about.
You can, to create brakes and complacency, put the base on ZFS and enable compression.

Answer 4

Well, save the delta if you really need to save space.
And for the temporary series , everything has long been invented.

The sampling rate is not important.

And 3 minutes is also good?