Dominique Devienne schrieb am 26.07.2023 um 11:39: > On Wed, Jul 26, 2023 at 11:26 AM Dionisis Kontominas <dkontominas@xxxxxxxxx <mailto:dkontominas@xxxxxxxxx>> 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? > Yes, but that would also be the case if you didn't include the WHERE clause. The "WITH =" is the reason you need the btree_gist extension. > So how well do exclusion constraints scale to 100K or 1M rows? > What's their time-complexity? They are using a GIST index, so I would expect all restrictions and advantages that apply to GIST indexes in general, also apply to exclusion constraints. The main drawback is most probably the slower update compared to a Btree index. Unless you have a really high update frequency, I wouldn't worry about that for such a small table.
