Proposition for better performance

[hmidi slim <hmidi.slim2@xxxxxxxxx>]

Hi,

I'm trying to create an availability table for existing products. I'm fetching the suitable schema to design in order to get good performance when I fetch products in a table contains millions of rows.

I think to make a schema like this:

create table availability (product_id integer, product_name varchar(255), start_date date, end_date date).

E.g:

Given an example 'product-1' which has a product_id = 1 and available from 27/03/2018 to 31/03/2018:

product_id     product_name    start_date        end_date
     

     1                product-1          27-03-2018    31-03-2018

However if the product is not available in 29/03/2018 I have to divide the period to 2 intervals:

product_id     product_name    start_date        end_date
     

     1                product-1          27-03-2018        28-03-2018

     1                product-1          30-03-2018        31-03-2018

With a table contains millions of products and if I divide the availability by intervals I will got a huge number of rows and fetching data will degrade the performance. Is there any solution to use such as range types or anything which mentions the unavailable dates.

Based on the example mentioned above, can I mention a data type like range type that take the start_date and end_date and exclude the unavailable dates?