Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

On Wed, Jul 26, 2023 at 11:26 AM Dionisis Kontominas <dkontominas(at)gmail(dot)com>
wrote:

> On Wed, 26 Jul 2023 at 11:18, Thomas Kellerer <shammat(at)gmx(dot)net> wrote:
>
>> Dionisis Kontominas schrieb am 26.07.2023 um 11:00:
>> > do not want two records to overlap, for the same user, the same role
>> > and also when the f_is_deleted is TRUE only.
>> > I do not care for the records when the f_is_deleted is FALSE on them;
>> i.e. they should not be part of the restriction/constraint.
>> > How can I achieve this?
>>
>> EXCLUDE USING gist (f_portal_user_id WITH =, f_portal_role_id WITH =,
>> DATERANGE(f_start_date, f_end_date, '[]') WITH &&) where (f_is_deleted)
>>
>
But that requires the btree_gist extension [1] extension, no?

Just confirming, because I'm been considering a similar approach for
storing chunks of large files (> 1GB),
to enforce those chunks don't overlap, per-"file". Seems ideal to enforce
no-overlap, but OTOH,
you can't seem to see how to enforce "no-holes" for chunks. One concern is
the cost of adding that
enforcement of no-overlap. Most "files" will be small (a few bytes to a
single digit MBs), while some
definitely go into multi-GB territory. So how well do exclusion constraints
scale to 100K or 1M rows?
What's their time-complexity? In other words, should "smaller" (i.e. < 1MB)
"files" go into a separate
table w/o an exclusion constraint and w/o chunking, while only the larger
ones go to the chunked table?

Thanks, --DD

[1]: https://www.postgresql.org/docs/current/btree-gist.html