On 03/27/2018 07:42 AM, hmidi slim wrote:
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)*.
I would use a tstzrange (or daterange) instead of separate start_date
and end_date columns. Then you can create an exclusion constraint that
has `EXCLUDE USING gist (id WITH =, available_during WITH &&)`. That
will automatically add a GiST index on those columns that should help
with faster lookups. (It will also prevent contradictions where a
product has two overlapping rows.)
You didn't mention the queries you want to be fast, but that index
should cover the reasonable ones I think.
> Is there any solution to use such as range types or anything which
> mentions the unavailable dates.
For any product, there should be the same number of unavailable periods
as available, right---or often one more? So I don't see any performance
benefit in doing it that way, and it certainly seems less intuitive to
store when something is *not* available.
--
Paul ~{:-)
pj@xxxxxxxxxxxxxxxxxxxxxxxx
